[sims] SIMS Individual Reports Mail Merge Hiding Rows with Only Certain Empty Cells

[Xeba37]

Morning all! Another day, another challenge to overcome....

 

Is there any way in Individual Reports in AM that you can get the Word document to remove a row from a table if a certain cell is empty. I know you can if the row is empty but I need a subject with past grades to be excluded if the latest assessment point is not populated.

 

We include the historical grades for the previous assessment points of the year in all our reports but if the pupil has no result for the current assessment we want this row to be blank. The same problem is affecting targets all our pupils have targets for all subjects irrespective of whether they are taking them and of course, we do not want to display targets for subjects a student is not taking.

 

Any advice appreciated, cheers!

[LosOjos]

You can do this with macros in the "ThisDocument" module of your template, but there is a caveat - if you upload the document to linked documents, VBA will throw an error if anyone tries to open it (unless they have macros disabled). The problem is that your template document references a file called IndRepBaseTemplate.dot, but when the file is opened via linked documents, that reference is missing which will cause VBA to throw a random unrelated error (very helpful!).

 

I opted to still use macros as the upload to SLG works fine and it makes printing from Individual Reports quicker for me.

 

I've put the code I use below as an example, but you will need to tweak it. In my template, there is a table used as a header (as headers/footers cause Ind Reps to run extremely slowly) followed by the table with the grades in. That table's final column is an effort grade, hence the check for the last but one column containing data.

 

Anyway, here's the code, let me know if you need more guidance on tweaking it for your needs:

 

Private Sub Document_Open()
On Error Resume Next
Dim x, y As Integer

With ActiveDocument.Tables(2).Columns(ActiveDocument.Tables(2).Columns.Count - 1)
y = .Cells.Count

'check we're not in template editing mode
If Left$(.Cells(2).Range.Text, 1) <> "<" Then
   
   'delete rows containing no grade
   For x = 2 To y
   If x > y Then Exit For
       If Asc(Left$(.Cells(x).Range.Text, 1)) < 32 Or _
           Asc(Left$(.Cells(x).Range.Text, 1)) > 126 Then
           .Cells(x).Row.Delete
           x = x - 1
           y = .Cells.Count
       End If
   Next x

   'fake document save to suppress warnings
   ActiveDocument.Saved = True
   
End If
End With
End Sub

[Xeba37]

The problem with that is that they open the reports in linked documents all the time and telling a hundred or so teachers to change their settings won't go well I can guarantee!

Aaarggh! :/

 

Interesting about the headers and footers though. I just tried removing them and my document seems to refuse to let me. I clearly have a lot to learn (I recently moved jobs from being Primary Assessment and Tracking LA Advisor to a Secondary Data Manager).

[LosOjos]

Interesting about the headers and footers though. I just tried removing them and my document seems to refuse to let me. I clearly have a lot to learn (I recently moved jobs from being Primary Assessment and Tracking LA Advisor to a Secondary Data Manager).

 

You can't delete them as such, but as long as they're empty they're as good as gone. Now I must admit, I haven't tried headers/footers recently so there's a chance SIMS handles them fine now, I have so little time to experiment these days though I tend to stick to my tried and tested methods!

 

Another alternative is to script the macro as VBS, so rather than embedding the code inside the document you could export the reports from SIMS, run them through the VBS to clean them up, then print them. Problem then though is that the linked documents version will still contain the rows you want removing!

[CarpeDiem]

Why not simply define your result tags by date? I.E Result for English between 01/10/2013 and 01/12/2013?

 

Have you followed the "things to remember when creating individual reports in Word 2010" helpsheet? Lots of little things to change and it really helps zap those Gremlins.....