Generating Reports for LimeSurvey CPD Tracking

From U of M Internal Medicine Wiki
Jump to navigationJump to search

Early Prep

  • Request Exceptions to 1hr CPD sessions from CPD coordinators
    • email them to find out which sessions were not 1 hr long

Extract data from CPD Tracking

In CDP Tracking, under Configuration, back up entire database. This results in a .sql file.

Get data into Access

The data can't go straight into MS Access because Access doesn't understand the format that LimeSurvey exports. One option is to set up XAMPP on the local PC and set up an ODBC connection to import into Access from , the other option is to set up an ODBC connection to the MySQL database where it is hosted and connect with Access directly.

Option 1 - XAMPP


Assuming you have already set up XAMPP XAMPP set up

  1. start XAMPP by starting the XAMPP control panel
  2. start Apache and MySQL (click "start")

Open phpMyAdmin

phpMyAdmin is the program used to manipulate data in a mySQL DB.

  1. open phpMyAdmin at http://localhost:7777/phpmyadmin (can't use the admin button because we need to use non-std port 7777)

Import Data

  • in phpMyAdmin, click the import button and import the several exported .sql files

Option 2 - ODBC connection to server

This might be possible now that we are hosting this at U of M, but not sure.

MS Access

Get a copy of the reporting mdb

  • copy the newest .mdb file (something like !CPD Reporting -) to a local directory from
SRG Y drive\CPD Tracking\Reporting
go to last year's reporting folder and use the .mdb file from there; the name has changed over time, that's why it's not listed here.

Importing / connecting

assuming ODBC driver for XAMPP is set up

  • Open the reporting .mdb
  • external data link, choose import (doing a link into mySQL is painfully slow)
  • under "machine data source tab" find and choose your DB
  • select all tables and OK (it will take a few minutes to complete importing)
  • open the module LimeSurveyCombiner and run sub survey_processor()

Adding 1hr exception data

  • Clean-out table 1_namefix_hrs in the reporting .mdb
  • For each survey that was other than 1 hr long, copy the name and survey IDs from table 1_combined_surveys and manually add the nr of hours.

Naming Conventions

  • Surveys whose names don't follow the CPD Tracking survey naming convention will not sort properly on the reports. Survey names that have misspellings in them will look unprofessional on the reports. These will have to have their names corrected. For each survey that needs to have the name corrected or that was other than 1 hr long, copy the names and survey IDs from table 1_combined_surveys and the nr of hours. - it is not so much important that the names follow this directly as that that they are consistent within any one series.
  • 1_namefix_hrs is used to fix the survey names that don't follow the naming conventions. Wrong name in the name_orig column, the correct name in the name_report column and the string 'sp fix' in the question column has been used to keep track of when cpd coordinators have been contacted.

emailing report to coordinators for submission to U of M

search for ".Send" and change it to .display if you want to test first

  • in the reporting .mdb, open the module ReportGeneration_Coordinators and run sub Generate_coordinator_emails().

emailing report to physicians

search for ".Send" and change it to .display if you want to test first

  • in the reporting .mdb, open the module ReportGeneration and run sub Generate_report_emails().

Related articles

Related articles: