+ Post New Thread
Results 1 to 5 of 5
MIS Systems Thread, SIMS Individual Reports Mail Merge Hiding Rows with Only Certain Empty Cells in Technical; Morning all! Another day, another challenge to overcome.... Is there any way in Individual Reports in AM that you can ...
  1. #1

    Join Date
    Dec 2013
    Location
    Leeds, UK
    Posts
    26
    Thank Post
    13
    Thanked 0 Times in 0 Posts
    Rep Power
    0

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

    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!

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    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:

    Code:
    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

  3. Thanks to LosOjos from:

    Xeba37 (10th January 2014)

  4. #3

    Join Date
    Dec 2013
    Location
    Leeds, UK
    Posts
    26
    Thank Post
    13
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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).

  5. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by Xeba37 View Post
    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!

  6. #5

    Join Date
    Mar 2012
    Posts
    68
    Thank Post
    24
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.....



SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 18
    Last Post: 18th October 2014, 09:20 AM
  2. [SIMS] SIMS Individual Reports Lost Data
    By Xeba37 in forum MIS Systems
    Replies: 5
    Last Post: 8th January 2014, 04:30 PM
  3. [SIMS] Hide rows with no results Individual Reports
    By tben2505 in forum MIS Systems
    Replies: 2
    Last Post: 15th November 2011, 02:08 PM
  4. Replies: 1
    Last Post: 19th October 2010, 10:48 AM
  5. Replies: 3
    Last Post: 10th June 2010, 04:12 PM

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
  •