9 Replies Latest reply on Sep 18, 2009 12:01 PM by nvandenburgh

    Try to summarize a summary field



      Try to summarize a summary field


      I have this solution that works really well for Medicaid billing, however Medicaid just changed the rules and I need to group things differently.  It's hard to explain without seeing the solution, but here goes.


      I have a table report layout where I list billing records for a given billing rate, day and time.(see image below).  The trick is that if a client is seen more than once per day, using the same billing code, but a different time then the minutes and units need to be grouped together and truncated to the nearest 1/4 billing unit rounding down.  The report below shows this. The sub totals work fine, but the grand total does not.  If a report has a set of grouped units on it the grand total is off.  With the example below, the sub totals are correct, but the grand total should be 70, not 71.  That's because the grand total summary can not summarize a summary field correctly.


      I've tried summarizing in other table, but then you run into the problem of the summary field summarizing every record instead of just the grouped summary field data.  I've even tried exporting the data, but again, the export exports the summary field for each record, again throwing off the grand total.


      Does anyone have any suggestions or similar solution.  Let me know if you need further explanation.




        • 1. Re: Try to summarize a summary field
             Try dividing the billable units by the count of records in the group, then summarizing the result.
          • 2. Re: Try to summarize a summary field
               Could this be a rounding error? Be sure to round your billable units, and summarize the rounded billable units.
            • 3. Re: Try to summarize a summary field
                 Counting the summary records and dividing did not work and it's not a round issue, I'm getting the correct value for the sub summary. It's when I am trying to summarize each sub summary that I'm getting the wrong total.  For all the sub summaries that have more than one record it takes the summary and adds in the summary of the first record in that group.
              • 4. Re: Try to summarize a summary field
                   "did not work" is not a good description.
                • 5. Re: Try to summarize a summary field

                  After looking at your table closely, and recaulculating your expected summaries, I'm sure it's a rounding issue!


                  For example,your subtotal of 37.67 is rounded to 37.00 on the same line, which does not follow your intended calculation where the billable units are rounded down to the nearest 1/4 unit. That subtotal should be 37.50, right?


                  So, if you add up the subsummary Units field (not Billable units) you get 71.21. This number, if rounded, would give you 71.00 which is the error evident on your table.


                  Again, be sure that your grand summary is using the Billable Units field.


                  Here's an example that works: You should have the following fields in your database ("n" means Number field, "c" means Calculation field, "s" means Summary field), and let's say that your record identifier is a Medical Record Number (MRN):

                  field1: nMRN

                  field2: nUnits

                  field3: cBillableUnits, which is a calculation = nUnits-Mod(Mod(nUnits,1),0.25)

                  field4: sBillableUnits , where Summary is Total of cBillableUnits


                  For your Layout you will need these sections and fields:


                  Sub-Summary by MRN

                        include fields: MRN



                        include fields: nUnits, cBillableUnits


                  Sub-Summary by MRN

                        include sBillableUnits


                  Trailing Grand Summary

                        include sBillableUnits 



                  That should do it - works on this end.

                  • 6. Re: Try to summarize a summary field
                       I believe it's the subsummary value that needs to be truncated - not individual record values.
                    • 7. Re: Try to summarize a summary field

                      <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;o:DocumentProperties&gt;   &lt;o:Template&gt;Normal.dotm&lt;/o:Template&gt;   &lt;o:Revision&gt;0&lt;/o:Revision&gt;   &lt;o:TotalTime&gt;0&lt;/o:TotalTime&gt;   &lt;o:Pages&gt;1&lt;/o:Pages&gt;   &lt;o:Words&gt;227&lt;/o:Words&gt;   &lt;o:Characters&gt;1294&lt;/o:Characters&gt;   &lt;o:Company&gt;Wildwood Programs&lt;/o:Company&gt;   &lt;o:Lines&gt;10&lt;/o:Lines&gt;   &lt;o:Paragraphs&gt;2&lt;/o:Paragraphs&gt;   &lt;o:CharactersWithSpaces&gt;1589&lt;/o:CharactersWithSpaces&gt;   &lt;o:Version&gt;12.0&lt;/o:Version&gt; &lt;/o:DocumentProperties&gt; &lt;o:OfficeDocumentSettings&gt;   &lt;o:AllowPNG/&gt; &lt;/o:OfficeDocumentSettings&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:TrackMoves&gt;false&lt;/w:TrackMoves&gt;   &lt;w:TrackFormatting/&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:DrawingGridHorizontalSpacing&gt;18 pt&lt;/w:DrawingGridHorizontalSpacing&gt;   &lt;w:DrawingGridVerticalSpacing&gt;18 pt&lt;/w:DrawingGridVerticalSpacing&gt;   &lt;w:DisplayHorizontalDrawingGridEvery&gt;0&lt;/w:DisplayHorizontalDrawingGridEvery&gt;   &lt;w:DisplayVerticalDrawingGridEvery&gt;0&lt;/w:DisplayVerticalDrawingGridEvery&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:DontGrowAutofit/&gt;    &lt;w:DontAutofitConstrainedTables/&gt;    &lt;w:DontVertAlignInTxbx/&gt;   &lt;/w:Compatibility&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;276&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:Cambria;      panose-1:2 4 5 3 5 4 6 3 2 4;      mso-font-charset:0;      mso-generic-font-family:auto;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Lucida Grande&quot;;      mso-font-charset:0;      mso-generic-font-family:auto;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ascii-font-family:Cambria;      mso-fareast-font-family:Cambria;      mso-hansi-font-family:Cambria;      mso-bidi-font-family:&quot;Times New Roman&quot;;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ascii-font-family:Cambria;      mso-ascii-theme-font:minor-latin;      mso-fareast-font-family:&quot;Times New Roman&quot;;      mso-fareast-theme-font:minor-fareast;      mso-hansi-font-family:Cambria;      mso-hansi-theme-font:minor-latin;      mso-bidi-font-family:&quot;Times New Roman&quot;;      mso-bidi-theme-font:minor-bidi;} &lt;/style&gt; &lt;![endif]  --><!--  StartFragment  -->


                      First let me say thank you for your responses.  Let me start over and give you what I have now.  Refer to the new example below.  The last example was showing 71 because it was rounding the total units, but what I’m trying to get and what I did not have on the last example was the true summary filed of the billable units because is completely inaccurate. Note now it shows 83, when it should be 70.  That’s because it’s taking each single record and summarizing in addition to the first billable unit in each grouped set of records.  Ex. 2+8+11+3+7+5+37+3+3+0+4.  So here’s what I have in the solution and why then last suggestion will not work.


                      I need to group billable units if the billable records have the same day, same billing rate and same client ID.  In order to do that I have a sub summary sorted by billing rate.  Here’s what the fields look like in order to get the sub summary correct.


                      ReportBillableUnits- Truncate ( ServiceMinutesBillableTotal.runsumm/15 ; 0 )


                      ServiceMinutesBillableTotal.runsumm- is a summary of the field ServiceMinutesBillable, with running total and restarting the summary all together restarted summary for each sorted group of the field billingrate.


                      ServiceMinutesBillable is the field mentioned above and is a cal field - (EndTime - StartTime)/60 ; 0 )


                      ReportBillableUnits.summ- this is a summary of the reportBillableUnits in a grand total part on the report layout that should total the example as 70, but is displaying 83.


                      Hope this makes sense, but as you can see it’s not a rounding issue, it’s a summary issue.






                      <!--  EndFragment  -->

                      • 8. Re: Try to summarize a summary field

                        I don't realy follow what you did, so let me try again by saying what I think you should do - starting with StartTime, EndTime and Rate (which is the field to group by):

                        cTime, Calculation (result is Time) = EndTime -StartTime

                        sTotalTime, Summary, Total of cTime

                        sCount, Summary, Count of <any field that cannot be empty>

                        cTotalTimeByRate, Calculation (result is Time) = Floor ( GetSummary ( sTotalTime ; Rate ) / 900 ) * 900

                        cSplitTotal, Calculation (result is Time) = cTotalByRate / GetSummary ( sCount ; Rate )

                        sTotalTimeAdj, Summary, Total of cSplitTotal



                        The last field goes into your grand summary part, while cTotalTimeByRate goes into the subsummary part. You might need to add another field to round the grand total.

                        • 9. Re: Try to summarize a summary field
                             Tweaked a little to fit my current solution with rates instead of time and it worked.  THANK YOU VERY MUCH!!!!!!