+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
MIS Systems Thread, SIMS Reports and duplicates in output in Technical; Hi Guys, First foray into the world of SIMS Reporting and having a complete head scratch ATM. I created a ...
  1. #1

    Join Date
    Nov 2011
    Posts
    35
    Thank Post
    0
    Thanked 5 Times in 4 Posts
    Rep Power
    6

    SIMS Reports and duplicates in output

    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

  2. #2

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,326
    Thank Post
    36
    Thanked 353 Times in 238 Posts
    Rep Power
    79
    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.

  3. #3

    Join Date
    Nov 2011
    Posts
    35
    Thank Post
    0
    Thanked 5 Times in 4 Posts
    Rep Power
    6
    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?

  4. #4

    Join Date
    Nov 2011
    Posts
    35
    Thank Post
    0
    Thanked 5 Times in 4 Posts
    Rep Power
    6
    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...

  5. #5

    Join Date
    Nov 2011
    Posts
    35
    Thank Post
    0
    Thanked 5 Times in 4 Posts
    Rep Power
    6
    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?

  6. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,970
    Thank Post
    673
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  7. #7

    Join Date
    Nov 2011
    Posts
    35
    Thank Post
    0
    Thanked 5 Times in 4 Posts
    Rep Power
    6
    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.

  8. #8
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,214
    Thank Post
    454
    Thanked 176 Times in 173 Posts
    Blog Entries
    3
    Rep Power
    64
    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.

  9. #9

    Join Date
    Nov 2011
    Posts
    35
    Thank Post
    0
    Thanked 5 Times in 4 Posts
    Rep Power
    6
    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)

  10. #10

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,970
    Thank Post
    673
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  11. #11
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,214
    Thank Post
    454
    Thanked 176 Times in 173 Posts
    Blog Entries
    3
    Rep Power
    64
    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
    Code:
    , "password"
    off the objWorksheet1.SaveAs line near the bottom.

    Code:
    ' ##############################################################################
    ' ###########							################
    ' ###########		  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.
    Last edited by Cache; 23rd June 2013 at 12:19 PM.

  12. Thanks to Cache from:

    vikpaw (24th June 2013)

  13. #12

    Join Date
    Nov 2011
    Posts
    35
    Thank Post
    0
    Thanked 5 Times in 4 Posts
    Rep Power
    6
    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)

  14. #13
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 490 Times in 375 Posts
    Rep Power
    0
    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.

  15. Thanks to Rawns from:

    vikpaw (24th June 2013)

  16. #14

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,970
    Thank Post
    673
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    But i do think on this occasion, they are supposed to be duplicated if there is more than one medical note.

  17. #15

    Join Date
    Nov 2011
    Posts
    35
    Thank Post
    0
    Thanked 5 Times in 4 Posts
    Rep Power
    6
    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.

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •