[sims] Hiding Rows in SIMs Individual Report

[KealeyA]

Hi

 

I am not sure if I am posting this in the correct thread but......

 

I am trying to write a report and I need it to hide rows in the mail merge if there is no data, very similar to how the Individual Report in SIMs works.

I have tried to look at the Macros in the template that is used by SIMs for the individual reports but it is password protected.

 

Has anybody achieved this feat and if so, how?

 

Thank you in anticipation

[bobsmith]

Can you give us an example of what your report is trying to achieve?

 

There's the ability to filter out rows in subreports already - maybe that's the solution?

[KealeyA]

Hi

 

I have dumped all the assessment data for a year group in order to do a mail merge into a word document. Some students don't do some subjects so I don't want that line to appear on that students report. The spreadsheet that I am using for the mail merge has some calculated fields on it so I don't want to use the Individual report functionality in SIMs. At present I have got all rows for every subject on every report and I would like to remove the rows that have no grade attached to them.

[bobsmith]

So you're doing this entirely outside of SIMS?

 

An excel file and a word document?

 

how did you achieve the "many to one" mail merge trick?

[KealeyA]

I have created a spreadsheet using Power Query that takes the SPI value with Progress 8 data for English maths and ebacc subjects. This is matched using the merge tables function in power query, like vlookup. I am basically recreating a SISRA type analysis because we can’t dump the data we want from SISRA. SLT use this data and, at present, we have to create and populate the tables manually which is not good and very time consuming. I would like to merge into another Excel rather than Word but one thing at a time. Power query is really powerful when combined with data from SIMs but it is a bit of a steep learning curve. I need to hide rows without values for subjects that don’t exist to make the finished thing more understandable.

[KealeyA]

Well I have come up with a really dirty solution which I would still like to streamline.

 

I do the mail merge into a word document, save the document as a single web page. Import the web page into excel and run a macro to remove any rows that have nothing in one of the columns. Then I apply a bit of conditional formatting and it seems to work.

 

This has meant adding some invisible text into rows I want to retain i.e. titles and spacing rows, but on the whole it works.

 

It's a shame it can't be done in one hit but it has cut 2 days of manually entering data into a pre-prepared spreadsheet down to half a day fiddling with the data so, I suppose, that is a bit of a win and does cut out any human error that could be introduced when copying all the figures manually.

 

I will keep on experimenting and, maybe, I will stumble across a really slick solution

[bobsmith]

I think you've outstripped a lot of us