[sims] SIMS Reports and duplicates in output

[forrager]

Hi Guys,

 

First foray into the world of SIMS Reporting and having a complete head scratch ATM.

 

I created a report in SIMS and when run within SIMS the output is in correct format (csv) without any duplicates.

Running the very same report via CommandReporter, the report is run and output is in correct format except that there are now duplicate entries in the data. Sometimes one, two or, three for nearly every entry?

 

Any ideas what is going please?

 

Thanks in advance

[PhilNeal]

There is a suppress duplicate flag are you sure that is selected? Duplicates occur when you have selected a data item that has multiple records. Sometimes you require the names etc duplicated and sometimes you don't.

[forrager]

Thanks for responding. Suppress duplicates is selected. Have escalated to SIMS reporting team - ATM it is just a basic listing of all pupils on roll so there should not be any duplicates. In the meantime I am going to create a new report to see if that replicates the issue?

[forrager]

Looking back through the report with fresh eyes and I have found the cause!

 

Picking up the field medical note - if it has more than one entry it is creating a separate record for the same pupil?

 

Now just need work out how to prevent the duplication...

[forrager]

Response from Capita There have been issues with that field - export as excel then save as csv. Doh! Why would I want a manual step in an automated process?

[vikpaw]

I hate the way medical notes are stored, it was supposed to be changed, but is still a pain.

 

If you have a student focussed report, and you are pulling in the medical note from the medical section then opening the sub-table for medical notes, then this will always happen. The only way to fix it is if you click on the line with blue icon that says medical notes, then the sub-report settings button (red) on the right, then you can add a filter to that query, or tick the only show first record box. this will limit the data to one note. This may of course, not be suitable for you.

 

The only other fix would be if you output to Excel, and then use the advanced reporting techniques to pivot the data so it moves recurrence across. I'm not even sure if that would work well.

 

I don't think just exporting to Excel is going to give you a straight line report without duplication. Suppressing duplicates in this kind of report is a pain, it's only useful if you want to look at the data as you can see the breaks in pupil, but for the purposes of sorting and manipulating the data, you need those duplicates back in.

 

I'm not even sure it would be that straight forward to write an SQL query to get what you want out.

[forrager]

Thanks for the input Vikpaw. I will certainly attempt your suggestions to see what is output.

 

One other option I can think of (although impracticable?) is to copy/merge the data into a generic Note and then pull from there maintaining one line per student. Not pretty but may save a lot of time until a more elegant solution can be found.

[Cache]

Looking at a report I created, I've got the same issue.

 

I gave up on it in the end, cobbling together a vbs script to manipulate the csv into an excel to add a filter to the top row, so atleast you can filter it to the student name and just view their details but it's still over multiple rows. If you want a copy let me know.

[forrager]

Thanks for the offer Cache. Would like to take a look plus I am a bit of a magpie when it comes to scripts so yes please - no point reinventing the wheel :0)

[vikpaw]

A pivot table didn't really work. In Excel, what you could do is try and get a macro to select the notes for all duplicated students then copy and paste (transpose). It would probably be easier to have suppress duplicates ticked on this occasion to allow the programmatic finding of duplicate kids. It's gonna be a bit complex, but worth it in the end if you can do it.This can autorun as part of the built-in Excel report macro.I'm not sure how this works for command-reporter though, and whether it will run at runtime, or if it waits until it's opened. i'm pretty sure it would have to run at run time.

[Cache]

Here you go, try this.

 

Needs to be run on a machine which Excel installed on it and at the moment sets a password on the file of password. We use Office 2010 and remember having to adapt slightly when we upgraded from Office 2003, but it was mainly finding the correct save as version number.

 

Source and Target files can be changed using the variables at the top and if you want to remove the password just take

, "password"

off the objWorksheet1.SaveAs line near the bottom.

 

' ##############################################################################
' ###########							################
' ###########		  Trip Conversion Script		################
' ###########		   Medical Details CSV			################
' ###########		  version 1 - 30/09/2010		################
' ###########							################
' ##############################################################################


Const ForReading = 1
Const ForWriting = 2


' ########################################################################
' ########################################################################
' ##############					##################
' ##########		Converts to XLS File		   ###############
' ##########						   ###############
' ##########	http://jeffkinzer.blogspot.com/2010/06	   ###############
' ##########	/vbscript-to-convert-csv-to-xlsx.html	   ###############
' ##########						   ###############
' ##############					##################
' ########################################################################
' ########################################################################


'======================================
' Convert CSV to XLS
'
' arg1: source - CSV path\file
' arg2: target - Excel path\file
'======================================

srccsvfile = "S:\trips\medical.csv"  
tgtxlsfile = "S:\trips\medical.xls"

'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(,"Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then  '> 0
 Set objExcel = CreateObject("Excel.Application")
End If

objExcel.Visible = false
objExcel.displayalerts=false

'Import CSV into Spreadsheet
Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet1 = objWorkbook.Worksheets(1)

'Adjust width of columns
Set objRange = objWorksheet1.UsedRange
objRange.EntireColumn.Autofit()
'This code could be used to AutoFit a select number of  columns
'For intColumns = 1 To 17
'    objExcel.Columns(intColumns).AutoFit()
'Next

'Make Headings Bold
objExcel.Rows(1).Font.Bold = TRUE

'Freeze header row
With objExcel.ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
End With
objExcel.ActiveWindow.FreezePanes = True

'Add Data Filters to Heading Row
objExcel.Rows(1).AutoFilter

'set header row gray
objExcel.Rows(1).Interior.ColorIndex = 15
'-0.249977111117893

' ###### Converts the whole worksheet to text format and removes the NUM inserted in the CSV

Const FromValue = "NUM "
Const ToValue = ""

Set objRange = objWorksheet.UsedRange

objRange.Style.NumberFormat = "@"
objRange.Replace FromValue, ToValue

'Save Spreadsheet, 51 = Excel 2007-2010, 56 = Excel 2003
objWorksheet1.SaveAs tgtxlsfile, 56, "password"

'Release Lock on Spreadsheet
objExcel.Quit()
Set objWorksheet1 = Nothing
Set objWorkbook = Nothing
Set ObjExcel = Nothing

 

Pivot tables and the likes are beyond me and moving data around using Macros would probably be the tidiest like VikPaw says, but too much of a faff and beyond me.

Edited June 23, 2013 by Cache

[forrager]

Thanks guys. I haven't found the time to get back to this just yet but will do so tomorrow.

 

Where there's a will there's a way :0)

[Rawns]

We experienced something similar a few months ago. We found running reports resulted in duplicate entries but previewing them then running them resulted in the correct data being pulled through. There is a patch to address this duplicate data issue, I'll see if I can dig out the patch number.

[vikpaw]

But i do think on this occasion, they are supposed to be duplicated if there is more than one medical note.

[forrager]

You are correct Vikpaw, it makes sense that as you can have individual dated entries, you preserve this in the output. However, this causes the problem if you only need a summary of a given record and this field.

 

I have not been able to spend as much time on this as i would have liked today but I have made some progress using excel concatenation.

 

Still work to do but this looks promising.

[forrager]

Hi Rawns. Initially the preview showed expected results but the output showed many duplications which masked the identification of the medical notes issue.

 

Once I removed the field and reran the report the 'inexplicable' duplications disappeared and made it easy to ID the root cause. However, once I added the field back in the spurious duplications did not reoccur?