4 Replies Latest reply: May 15, 2014 10:14 AM by Kamawell2 _ RSS

    Nulls and Internal Errors

    Kamawell2 _

      Hi there

       

      I really need some help with a recurring problem regarding summaries.  Here's the most recent example which I've tried to break down as much as possible in case someone can say where I go wrong. (I'm using 10.5 Pro.)

       

      I have a very simple report that's easy to trap (the real version has about 800 lines and several union names.)

      What I need to get out are two separate summaries for each union - one that shows just a total of deductions taken and one that has a list of staff members and their individual deductions.

       

      There's a lookup table and some filters involved and that seems to be where something (possibly me) goes wrong.

       

                                                                            Report Date:  29-AUG-2012

       

       

                                                Union Deductions Report

       

       

       

       

       

       

       

                                         Report Parameters

       

       

       

                                               NHS Trust :   999 Trumpton Health Payroll Group Top

       

                                              Union Name :   ALL

       

                                                 Payroll :   ALL

       

                                              Start Date :   01-APR-2012

       

                                                End Date :   30-JUN-2012

       

                                        Start Acc Prd/Yr :

       

                                          End Acc Prd/Yr :

                                                                                      Report Date: 29-AUG-2012 09:37

                                                                                      Page:    1    of 2

       

       

                                                     Union Deductions Report

       

       

      Trust:                  999 Trumpton Health Payroll Group Top

      Payroll:                ALL

      Start Date:             01-APR-2012

      End Date:               30-JUN-2012

       

       

      Union Name:   NHS UNISON

       

       

      Full Name                               Employee  Element      Element End  Deduction Taken    No Of

                                              Number    Start Date   Date                            Deductions Taken

      -


        -


        -


        -


        -


         -


       

      Black, Mrs. Abbie                       12345678                                       34.50                    3

      White, Mrs. Zoe                         87654321                                       34.50                    3

       

       

                                                                              -


      -


                                      TOTAL for NHS UNISON                                   69.00                    6

                                                                              -


      -


                                                                                      Report Date: 29-AUG-2012 09:37

                                                                                      Page:    2    of 2

       

       

                                                     Union Deductions Report

       

       

      Trust:                  999 Trumpton Health Payroll Group Top

      Payroll:                ALL

      Start Date:             01-APR-2012

      End Date:               30-JUN-2012

       

       

                                                                              -


      -


                                      TOTAL for REPORT                                       69.00                    6

                                                                              -


      -


       

       

       

                                                        

          • End of Report ***

      /CODE

       

      I trap [I]Employee Name[/I][/B], [B]Employee Number[/B][/I], [I]Deduction Taken[/I][/B] and [I]Number of Deductions[/I][/B] as the detail template and [I]Union Name[/I][/B] (in this example there's only NHS Unison) in an append template.

       

      There's a piece of information missing from this report that I need to add as the basis for a filter.  I have a large excel sheet with details of all the staff members on it and I use an external lookup with [I]Employee Number[/I][/B] as the linking column to retrieve a text field for each employee called [B]Payroll[/B][/I].

       

      In the table view everything looks good.  I make a filter to exclude a certain value for [I]Payroll /I[/B]and a filter for each value of [I]Union Name.[/I][/B]  I then make compound filters, each combining a union filter and the filter to exclude the unwanted payroll.  It still all looks good in the table view.

       

      If I try to create a really simple summary that uses one of the compound filters with just [I]Employee Name /I[/B]as the key and [I]Deduction Taken /I[/B]as the measure I get an internal error warning.  From previous bouts I know that this always happens if I don't use the field I imported from the external lookup so I add [I]Payroll[/I][/B] as an item.  Now I don't get the internal error but both the [I]Employee Name /I[/B]and [I]Deduction Taken /I[/B]fields in the summary are all Null.

       

      Am I setting up the summary incorrectly?  How can everything appear as expected in the table view but vanish in the summary?  I'm seeing this same problem on a number of summaries on different reports.

       

      Thanks

       

      Andy

        • Nulls and Internal Errors
          Olly Bond

          Hello Andy,

           

          Have you tried exporting the table (twice, if necessary, with the appropriate filters applied) as a local Access mdb file, and then creating a second model that opens this as the database, and building the summary on that? Sometimes breaking it down into steps, one in each model, helps diagnose where things are tripping over each other. Once you have a stable series of models, you can import objects from these into a new, final, clean model.

           

          Hope this helps,

           

          Olly

            • Nulls and Internal Errors
              Kamawell2 _

              Hello Olly

               

              Thanks for your help as always.  I have other models with the same problem and in those I've used the method above but exporting as an Excel file, saving that as a csv file and then opening that in a second model.

               

              My real problem comes from the need to keep the process as streamlined as possible.  We have several Monarch licences (and are looking at acuiring more,) all but one of which are currently used by staff who just need to open a troublesome report, apply a model I've made and get the result out as quickly as possible.  For the first couple of years that's worked very well for us and we've come to rely on it.  Exporting and applying a second model isn't a huge task by itself of course but if you have multiple reports that are run regularly that all require this treatment it starts to be a problem.

               

              It's rather frustrating to see everything displaying correctly in the table but then for it to get lost on the way to the summary.  It always feels like I'm making an error in defining the summaries.

                • Nulls and Internal Errors
                  Olly Bond

                  Hello Andy,

                   

                  If the other users aren't building their own models, but just applying your models to their reports, then it might be worth talking to Datawatch about the other options. You could push data out to users with DataPump, or let the users come and pull their data from BI Server. You have to manage the server, but you don't need so many desktop licences, and you keep a little bit more control.

                   

                  Best wishes,

                   

                  Olly