6 Replies Latest reply: May 15, 2014 10:06 AM by sxschech _ RSS

    PDF template and Summary by Year Conditional Summing

    sxschech _

      Two issues:

       

      Template Issue:

      I have two reports output in PDF.  The layout is the same, however when opened in Monarch V8.01, the first report and second report are aligned slightly different, so had to widen the trap which was confusing.

      Example:

      Report 1

                               GPA

      Year     Month    College     Level    Total ........

      2009    Jan       Education  Doct          5

      2009    Jan       Arts       Grad          2

      2009    Jan       Science    Grad          6

      2009    Jan       Education Grad           3

                  

      2010    Jan       Education  Doct          4

      2010    Jan       Arts       Grad          8

      2010    Jan       Science    Grad          8

      2010    Jan       Education  Grad          6

       

      -


      Report 2

      Page 1                Score Type AA

      Year     Month    College     Level    Total ........

      2009    Jan       Education  Doct          5

      2009    Jan       Arts       Grad          2

      2009    Jan       Science    Grad          6

      2009    Jan       Education  Grad          3

       

      Page 1                Score Type AA

      Year     Month    College     Level    Total ........

      2010    Jan       Education  Doct          4

      2010    Jan       Arts       Grad          8

      2010    Jan       Science    Grad          8

      2010    Jan       Education  Grad          6

       

      Page 2                Score Type BB

      Year     Month    College     Level    Total ........

      2009    Jan       Arts       Grad          2

      2009    Jan       Science    Grad          6

      2009    Jan       Education  Grad          3

       

      Page 2                Score Type BB

      Year     Month    College     Level    Total ........

      2010    Jan       Education  Doct          4

      2010    Jan       Arts       Grad          8

      2010    Jan       Education  Grad          6

       

      /code

       

      When I open report 2 in Monarch, the trap originally didn't work because Year appear slightly to the right of where it looks like it is appearing in report number one in the template.

       

      Although I managed to solve this particular problem by expanding the width of the painted area and moved the trap numeric position over two spaces, was wondering how I can get the reports to actually line up since at least on paper, the layout and position seem the same.

       

      Now for the hard part.

       

      I would like to double check that the totals appearing on each report are matching the totals on a different report.  Since report 2 above is showing the same totals for each Score Type by College, Monarch is summing them together on the summary view.

       

      Example:

      Data Table view

      GPA 2009      Education     5

      GPA 2009      Arts          2

      GPA 2009      Science       6

      GPA 2009      Education     3

      GPA 2010      Education     4

      GPA 2010      Arts          8

      GPA 2010      Science       8

      GPA 2010      Education     6

      Sco 2009  AA  Education     5

      Sco 2009  AA  Arts          2

      Sco 2009  AA  Science       6

      Sco 2009  AA  Education     3

      Sco 2010  AA  Education     4

      Sco 2010  AA  Arts          8

      Sco 2010  AA  Science       8

      Sco 2010  AA  Education     6

      Sco 2009  BB  Arts          2

      Sco 2009  BB  Science       6

      Sco 2009  BB  Education     3

      Sco 2010  BB  Education     4

      Sco 2010  BB  Arts          8

       

      /code

      At the summary Level, I want to check that the totals are correct, but I don't want to total them up (Only education needs to be summed within the same year)

       

      This is what I would like to see at the summary level:

      GPA 2009      Education     8

      GPA 2009      Arts          2

      GPA 2009      Science       6

      GPA 2010      Education    10

      GPA 2010      Arts          8

      GPA 2010      Science       8

      Sco 2009      Education     8

      Sco 2009      Arts          2

      Sco 2009      Science       6

      Sco 2010      Education    10

      Sco 2010      Arts          8

      Sco 2010      Science       8

      /code

      In other words, the Sco portion of the report is showing the same total for each college on each page of the report, only the score type is different.  However, some of the score types do not have an entry for a particular year so don't even display on the original report

      The only summing would be to combine the Education values together (Educ Doctor + Educ Grad)

       

      Hope this makes sense.

        • PDF template and Summary by Year Conditional Summing
          Grant Perkins

          For the PDF problem - yes they can tend to do that.

           

          Do you know what PDF creator was used to generate them?

           

          If you work around seems reliable enough I would be tempted to leave it like that. (I have recently been looking at a PDF in tabular form from a database reporting application, a top of the range one at that, and it doen't even  display properly page on page if you scroll through the PDF report!)

           

          For the subtotal matter - I assume you just want the numbers once to check against the Monarch exrtaction? In which case you may be looking for either a 2 model double extraction and then 'put them back together' for the purpose of the check OR, if all lines are extracting anyway, work out a filter that allows you to segregate the detail lines from the sub-total lines and then export the Subtotal version to be looked upo for the comparison. Basically the same idea as 2 models and 2 extracts but without having to have 2 models.

           

          If you have Monarch Pro it should be quite straightforward. If not it may be a little more work but that depends on whether you just need to side by side visual check or a full audit trail proof of process. Also if you have re-groupoed the records for reporting purposes some levels of the sub-totals, perhaps all of them, will not be comparabe any more. Grand totals should be ok.

           

          I hope this helps in some way. To be much more specific about exactly the best way to segregate the detail and the (sub)totals would likely mean viewing a copy of the real report layout. I have assumed the sample is illustrative not the actual format you are working with. ...

           

           

          Grant

            • PDF template and Summary by Year Conditional Summing
              sxschech _

              Hi Grant,

               

              Thanks for replying, previously received email notifications when a response occurred, but didn't get one this time and was busy with other reports, so hadn't had a chance to login until now. 

               

              As for PDF, using Adobe Acrobrat Professional Ver 7.1.0.  Printing the report in MS-Access using the Print dialog and choosing Printer Name: Adobe PDF 

               

              Regarding the PDF reports, how would I show them on the forum for you to see the layout? 

               

              You are correct that the sample is illustrative.  Is what you are saying is that I need to export the data and then relink in as a separate model somehow?

                • PDF template and Summary by Year Conditional Summing
                  Grant Perkins

                  There is no way of making the PDF's available other than loading them to a public starage site and providing a link OR sending them by email if the content is not private and the file sizes are not prohibitive.

                   

                  If email is an option (it would be less public) send me a Private Message with your email address and we can take thinggs off-line.

                   

                  I suspect that your report structures are subtly different - maybe the year field is wider on one report for some reason or a different font is set on the original report and Monarch interprets the lines differently. It's difficult to know for sure at a distance.

                   

                  Is there any way that you could get to the analysis you want directly from Access rather than through an intermediate print program? Alternatively what other print options do you have available if you elect not to use a PDF output?

                   

                  For checking the totals the idea of extracting and exporting the totals and/or subtotals to a separate file and then using that as an external lookup to check against the values being calculated by Monarch seems like an option. A two stage (or even a multiple stage) process group is something that is quite common for Monarch and can usually be reduced to a single 'user' activity by setting up a Project and a batch processing script. There may be other approaches for that particular need but multi stage process is likely to be quite popular.

                   

                  HTH.

                   

                   

                  Grant

                    • PDF template and Summary by Year Conditional Summing
                      sxschech _

                      Hi Grant,

                       

                      I will find out if it will be okay to email you the pdfs.  In the meantime, what I ended up doing was exporting the results to a csv and importing into MS-Access and running a query in there to summarize the results.  Was hoping to have been able to do it all in Monarch so don't have to go back and forth, but at least it seems to get results this way.  I did a check of the report structures and since report 2 was created from report 1 (Opened report 1 and did a save as report 2) the alignments and widths are identical.  The difference is the record source (query) and the headers.  Report 1 is for GPA and report 2 is for test scores so the headers on report 2 change depending on name of test score, the detail section of both reports are identical with respect to the position of the text boxes and width of each text box.  Since the headers are irrelevant to the summary, alignment of headers is not an issue.  For the detail section of the reports, I tried changing the alignment from defaults to explicitly be left aligned for text and right aligned for numbers, however, this did not seem to change the positioning when the pdf is viewed in Monarch between the two reports.

                        • PDF template and Summary by Year Conditional Summing
                          Grant Perkins

                          Ah!. The "nearly but not quite the same" problem.

                           

                          I stumbled across something very similar the other day. A public download from a Govt. (UK) web site, presunably dragging stuff out of the same database for each page of the 'report' BUT the position of the columns varied with number of characters in the descriptions in the the first 2 columns. Some fo teh descriptions also split onto a second line - just to make things even more interesting. Sometimes they split when the reason was not entirely obvious.

                           

                          I love it when people set their own standards ...   :rolleyes:

                           

                          If you are able to share the reports I'll gladly take a look and provide whatever suggestions I can. No promises of a solution though!

                           

                          You could read in the csv file as a database in Monarch to save the Access step (maybe?)  I guess t depends in whether the structure of the interpreted PDF report helps or hinders the analysis you need to deliver.

                           

                           

                          Grant

                            • PDF template and Summary by Year Conditional Summing
                              sxschech _

                              With Grant's help, determined we needed a two pass solution.

                               

                              First Pass, use existing template and

                               

                                 1. Add Level to Summary (Month, College, Term, Level)

                                 2. Instead of Sum, choose Unique for  Ap, Ac and En

                                 3. Change Font to Fixed width (non proportional Font) such as Courier 10 Pt

                                 4. Adjust col widths to be sure all cols are displayed without cutting off

                                 5. Export as .txt file

                                 6. Close Original Report and Model

                               

                              Second Pass

                               

                                 7. Open New Report (txt file)

                                 8. Build Template for the second report

                                 9. In Summary Include Term, Month, College

                                10. Do a Sum on Ap, Ac and En