Home > Synergy > Hints and tips > Reports |
I want to produce an activity report for the 85 autistic people registered with our practice. I have so far produced an excellent ANEC report using John Robinson’s excel report which shows “seen in GPs surgery” “home visit” etc. What I want to show next is the number of codes for encounters beginning @ (eg Admin, prescription issue, etc). Can this be done?
You can’t do this sort of breakdown count within a query (yet). However it can be done in excel.
Make a query that finds the encounters you need.
eg
report for each encounter where encounter.read_code = “etc” or …with patient in “somelistmadeearlier”
add a print line that includes the read code and patient number and name for each of identifying the patients. eg
…print Encounter.Read_Code, Encounter.Term, Encounter.Start_Date, Encounter.Patient_Number, Encounter.Surname, Encounter.Forename_1
Make it sort by patient number and read code
…sort by Encounter.Read_Code, Encounter.Patient_Number ;
Run query, show CSV so you know it has worked.
Open windows explorer at your csv directory on c: drive eg c:meddatas6000reportcustommyfolder.
Click the right CSV file and it will should open in excel (excel 2000 in my case).
Or you could use John Robinson’s “single query to excel” format to get the data straight into excel.
Add a new blank row at the top of the sheet. This seems to be needed so excel thinks there are column headings. On menu pick Data, Subtotals.
Excel moans that it hasn’t got headings on the columns.
Ignore this and press OK anyway.
“At each change in column” <- select the column with Patient Number Leave next box at “Count” Select the same column as before in the third box. OK.
This will add subtotals of encounters by patient number.
Play with the grey +/- it has added at left and in particular the little grey numbers.
You can now repeat the process this time selecting the read code column and unchecking the “Replace Current Subtotals” box.
This will add a further level of subtotals per read code within patient. Play with the little grey numbers again. It only works if the data is sorted on those columns, but you can always re-sort in excel itself. This sort of subtotalling has lots of uses.
Excel can do virtually anything these days, the help is good too.
Rik Smithies: endspan