Generating Reports for LimeSurvey CPD Tracking

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

Early Prep

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.

ODBC connection to server

ODBC Installer

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

  • Requirements (Software available at Y:\CPD Tracking\ODBC. Install everything as Administrator.)
    • Visual Studio C++ 2015 Redistributable (32/64bit) (vc_redist.x86/vc_redist.x64)
    • mysql-connector-odbc-8.0.13-win32 (32/64bit)
      • Copy msi files to local drive (Desktop) and run them as admin from CMD.

ODBC Connection Setup

  • Run ODBC as admin (Both 32/64)
  • Click on Add
  • Select MySQL (ANSI)
  • Fill out the rest. Credentials are located on Y:\CPD Tracking\UofM\PWD U of M.txt

MS Access

Get a copy of the reporting mdb

  • copy the newest .mdb/.accdb 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 is set up

  • Run MS Access (not .mdb/.accdb file but MS Access) as admin so the ODBC connections work.
    • imoort the data into a file of its own, not the reporting tool; the import takes a long time, and by storing it in its own file we can always go back to it
  • 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)

initial processing of limesurvey data

  • open the reporting .accdb
  • 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.
Interro-01.gif Once the duration is part of the survey name we might be able to simply take this whole bit out, and remove that column from the report. Will need to see how it looks next time we run this.

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.

Update the dates

  • make sure they uses this year's dates:
    • report template heading
    • outout_dir in module ReportGeneration

emailing report to coordinators for submission to U of M

Test

Once this starts it will send emails all on its own; so make sure we test first.

  • open the module ReportGeneration_Coordinators and find sub Generate_coordinator_emails()
  • search for ".Send" and change it to .display if you want to test first

Sending

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

emailing report to physicians

Test

Once this starts it will send emails all on its own; so make sure we test first.

  • open the module ReportGeneration and find sub Generate_coordinator_emails()
  • search for ".Send" and change it to .display if you want to test first

Sending

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

Related articles

Related articles: