4 Replies Latest reply: May 15, 2014 10:11 AM by Joe Berry RSS

    Modeling Help

    dpetrauskas _

      I just wanted to say that everyone in this forum is such a tremendous help, I thank you all for your time and help.  I really appreciate it.

       

        Now to the issue....

          I have a report that I am gathering all the informatiion from.  I have created a model that uses a hidden formula to create tabs when I export it out to an Excell spreadsheet. I currently have a sort for charges.  That is to do the top 20 tab correctly.  I do need to change the sorting to the discharge date for most of the other tabs.  I also need to have a total row (not column) for the charges on each tab.  SO maybe my question is is that possilbe to sort it for charges for the Top 20 tab, then during the output (using summary) sort it be discharge date?

        The next issue with this report and model is that I am also tryiing to remove duplicate amounts from the charges line while keeping the rest of the row intact.  Is this possible?  I feel as though I am missing something.  Everything I have tried in removing duplicates has removed the row not just the duplicate charge.  Such as with the first patient listed in the example below it puts 34590.55 for all of the errors.  I will still need the value for the errors in other tabs but I do not need to have it duplicated within each tab.

       

                                          PBY29341       PAGE    1

                                                  PATIENT  BILLING  SYSTEM                                               PRINTED 10/11/11

                                                      ALERT REPORTING                                                       TIME 03:37:14

                                                                                      P.E. DATE 10/10/11

       

      -


      PATIENT----


        MEDICAL    ADMIT    DISCH    PAYOR  P  F  RPT MISSING/ILLOGICAL                     TOTAL       SEV

      NAME                 ACCOUNT RECORD NBR DATE     DATE     CODE   T  C  LOC INFORMATION                         CHARGES

       

      Patient Name Here    1234567 1234567890 08/30/11 08/30/11 918MCR OS EM GDS PAYOR REQUIRES M/R SIGNOFF (918)       34590.55  **HARD

                                                                                 BOTH 0360 AND 0361 REVENUE PRESENT               **HARD

                                                                                 MISSING ICD9 PX WITH O.R. CHARGES                **HARD

                                                                                 SURGERY HCPC REQ FOR SURGERY REV                 **HARD

       

      Patient Name Here    1234567 1234567890 09/06/11 09/06/11 556    OU M  GXR PATIENT HAS ZERO BALANCE                    .00  **HARD

       

      Patient Name Here    1234567 1234567890 09/01/11 09/02/11 242PPO OJ D  WC  DSCH STATUS REQ                         7255.18  **HARD

                                                                                 MISSING FINAL DX                                 **HARD

                                                                                 PAYOR REQUIRES M/R SIGNOFF (242)                 **HARD

       

      Patient Name Here    1234567 1234567890 09/02/11 09/26/11 556    OJ M  WC  CHECK APC ALERT (CCI EDITS)-ABY193      6838.27  WARNIN

       

      Patient Name Here    1234567 1234567890 09/01/11 09/01/11 666CPL OL F  MD  PATIENT HAS ZERO BALANCE                    .00  **HARD

       

      Patient Name Here    1234567 1234567890 09/06/11 09/13/11 556    OJ M  WC  GAR NAME CONTAINS INVALID CHAR(S)       2314.96  **HARD

       

      Patient Name Here    1234567 1234567890 09/12/11 09/12/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                 CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                 CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

       

      Patient Name Here    1234567 1234567890 09/15/11 09/15/11 556    OS M  GDS SURGERY HCPC ON UB - NO REV MATCH       7862.57  **HARD

       

      Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

       

      /Code

        • Modeling Help
          Olly Bond

          Hello,

           

          There's a lot going on here. It seems you're using sorts, an advanced filter to handle duplicates, and exporting multiple summaries (based on key value, or filters?)

           

          Do you just have a detail template here? You mention the "errors" but to an outsider like me reading the report, I don't know which field would contain the errors, or how to spot them.

           

          Any chance of emailing a larger sample, and a model file over, and I'll have a look at what's going on?

           

          Best wishes,

           

          Olly

            • Modeling Help
              Grant Perkins

              I was 2 keystrokes away form hitting the submit button on a detailed reply wheh windows decided to shut down as part of auto update - which I'm pretty sure is not enabled for auto install. Grrrrr.

               

              The abbreviated version:

               

              Select for the detail template based on Account number of similar.

               

              For the Error and Sev columns create a field for each that is multi-row. Use "End on " (and a setting that works for you) in the Advanced Properties tab.

               

              That should eliminate the duplicated value lines.

               

              If you need to put the errors into their own fields create calculated fields for as many errors as you might possibly see on the report - and then a couple more if you want to be safe - and use the TEXTLINE function in LINE NUMBER mode to split them out. OR, if the errors are all taken from a table of possible errors, you could also do something with SEARCH mode and simply set a flag to say that the error is reported against the record.

               

              For the export tabs in Excel I would keep things simple and create an export for ech tab required with appriate filter and sorts. Then use a project export to populate Excel - same Workbook, multiple worksheets. I think that wouold give you what you want and be easy to maintain if some tings are tweaked in the future  - but I may have missed something interpreting your requirements.

               

              If a lot of the summaries would be the same and this seems to be an approach that introduces duplication of effort you might want to check out Linked Objects functionality. (I'm suggesting that without actully checking which version of Monarch you are using but hopefully it will be something not too ancient!

               

              HTH.

               

               

              Grant

                • Modeling Help
                  dpetrauskas _

                  Here is a larger sample and the model information included.

                   

                  PBY29341       PAGE    1

                                                              PATIENT  BILLING  SYSTEM                                               PRINTED 10/11/11

                                                                  ALERT REPORTING                                                       TIME 03:37:14

                                                                                                  P.E. DATE 10/10/11

                   

                  -


                  PATIENT----


                    MEDICAL    ADMIT    DISCH    PAYOR  P  F  RPT MISSING/ILLOGICAL                     TOTAL       SEV

                  NAME                 ACCOUNT RECORD NBR DATE     DATE     CODE   T  C  LOC INFORMATION                         CHARGES

                   

                  Patient Name Here    1234567 1234567890 08/30/11 08/30/11 918MCR OS EM GDS PAYOR REQUIRES M/R SIGNOFF (918)       34590.55  **HARD

                                                                                             BOTH 0360 AND 0361 REVENUE PRESENT               **HARD

                                                                                             MISSING ICD9 PX WITH O.R. CHARGES                **HARD

                                                                                             SURGERY HCPC REQ FOR SURGERY REV                 **HARD

                   

                  Patient Name Here    1234567 1234567890 09/06/11 09/06/11 556    OU M  GXR PATIENT HAS ZERO BALANCE                    .00  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/01/11 09/02/11 242PPO OJ D  WC  DSCH STATUS REQ                         7255.18  **HARD

                                                                                             MISSING FINAL DX                                 **HARD

                                                                                             PAYOR REQUIRES M/R SIGNOFF (242)                 **HARD

                   

                  Patient Name Here    1234567 1234567890 09/02/11 09/26/11 556    OJ M  WC  CHECK APC ALERT (CCI EDITS)-ABY193      6838.27  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/01/11 09/01/11 666CPL OL F  MD  PATIENT HAS ZERO BALANCE                    .00  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/06/11 09/13/11 556    OJ M  WC  GAR NAME CONTAINS INVALID CHAR(S)       2314.96  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/12/11 09/12/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/13/11 09/13/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 556    OS M  GDS SURGERY HCPC ON UB - NO REV MATCH       7862.57  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 412MCR OS EM GDS SURGERY HCPC ON UB - NO REV MATCH       7985.87  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                    PBY29341       PAGE    2

                                                              PATIENT  BILLING  SYSTEM                                               PRINTED 10/11/11

                                                                  ALERT REPORTING                                                       TIME 03:37:14

                                                                                                  P.E. DATE 10/10/11

                   

                  -


                  PATIENT----


                    MEDICAL    ADMIT    DISCH    PAYOR  P  F  RPT MISSING/ILLOGICAL                     TOTAL       SEV

                  NAME                 ACCOUNT RECORD NBR DATE     DATE     CODE   T  C  LOC INFORMATION                         CHARGES

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 412MCR OS EM GDS SURGERY HCPC ON UB - NO REV MATCH       7873.39  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666LDS OL FN MD  PATIENT HAS ZERO BALANCE                    .00  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/21/11 550MOL OJ EA WC  DSCH STATUS REQ                         2176.81  **HARD

                                                                                             MISSING FINAL DX                                 **HARD

                                                                                             PAYOR REQUIRES M/R SIGNOFF (550)                 **HARD

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 235HUM OX G  MD  PATIENT HAS ZERO BALANCE                    .00  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE          12.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/15/11 09/15/11 666LDS OL FN MD  PATIENT HAS ZERO BALANCE                    .00  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                    PBY29341       PAGE    3

                                                              PATIENT  BILLING  SYSTEM                                               PRINTED 10/11/11

                                                                  ALERT REPORTING                                                       TIME 03:37:14

                                                                                                  P.E. DATE 10/10/11

                   

                  -


                  PATIENT----


                    MEDICAL    ADMIT    DISCH    PAYOR  P  F  RPT MISSING/ILLOGICAL                     TOTAL       SEV

                  NAME                 ACCOUNT RECORD NBR DATE     DATE     CODE   T  C  LOC INFORMATION                         CHARGES

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/30/11 09/30/11 918CHC OU D  GXR PAYOR REQUIRES M/R SIGNOFF (918)        3927.68  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666LDS OL FN MD  PATIENT HAS ZERO BALANCE                    .00  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/16/11 09/16/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/14/11 09/14/11 556    OJ M  WC  DSCH STATUS REQ                          398.76  **HARD

                                                                            200              MISSING FINAL DX                                 **HARD

                                                                                             PAYOR REQUIRES M/R SIGNOFF (556)                 **HARD

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666LDS OL FN MD  PATIENT HAS ZERO BALANCE                    .00  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666CPL OL F  MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             CHECK APC ALERT (CCI EDITS)-ABY193               WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                    PBY29341       PAGE    4

                                                              PATIENT  BILLING  SYSTEM                                               PRINTED 10/11/11

                                                                  ALERT REPORTING                                                       TIME 03:37:14

                                                                                                  P.E. DATE 10/10/11

                   

                  -


                  PATIENT----


                    MEDICAL    ADMIT    DISCH    PAYOR  P  F  RPT MISSING/ILLOGICAL                     TOTAL       SEV

                  NAME                 ACCOUNT RECORD NBR DATE     DATE     CODE   T  C  LOC INFORMATION                         CHARGES

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/19/11 09/19/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                                                                                             DOCTOR NPI MISSING/INVALID (9999)                **HARD

                                                                                             DOCTOR NPI MISSING/INVALID (9999)                **HARD

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT HAS ZERO BALANCE                    .00  **HARD

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666CPL OL F  MD  DOCTOR NPI MISSING/INVALID (9999)          6.00  **HARD

                                                                                             DOCTOR NPI MISSING/INVALID (9999)                **HARD

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666CPL OL F  MD  CHECK APC ALERT (CCI EDITS)-ABY193         6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                   

                  Patient Name Here    1234567 1234567890 09/20/11 09/20/11 666LDS OL FN MD  PATIENT OVER 65 BUT NOT MEDICARE           6.00  WARNIN

                  /Code

                   

                  Monarch Model Information

                  -


                   

                   

                  Templates Fields Filters Sorts Summaries Audit Trail

                   

                   

                  Input Settings

                   

                  Date Pattern  Start Year (19..)  Date/Time Recognition Patterns  Decimal Character  Use Case Sensitive Traps  Use Alternate Trap Characters 

                   

                  M/D/Y 

                  50 

                  D,DT,T 

                  true 

                  false 

                   

                   

                  Ignore Leading Control Characters  Ignore Form Feeds  Ignore Control Characters  Interpret Input As ASCII  Force Page Break After n Lines  Auto

                   

                  Trim Character and Memo Fields 

                   

                  false 

                  false 

                  true 

                  100 

                  false 

                   

                   

                   

                   

                  Templates

                   

                  Name  Floating  Multi Column  Trap Line  Trap Characters  Sample Line  Comment 

                  Append  false  false  1  Ã Ø Ø  -


                  PATIENT----


                  MEDICAL ADMIT DISCH PAYOR P F RPT MISSING/ILLOGICAL TOTAL SEV  

                  Detail  false  false  1  Ø Ø  Patinet name here 1234567 1234567890 04/14/10 04/14/10 556 OS M MISSING FINAL DX 38629.51 **HARD  

                   

                   

                   

                   

                  Fields

                   

                  Name  Type  Data Length  Display Length  Decimals  Alignment  Formula  Comment 

                  Name  Character  20  22   Left   

                  Account  Character  7  7   Left   

                  Admit  Character  8  8   Left   

                  Disch  Date  8  9   Left   

                  INS  Numeric  3  3   Right   

                  PT  Character  2  3   Left   

                  FC  Character  1  3   Left   

                  Error  Character  35  32   Left   

                  Type  Character  6  7   Left   

                  Charge  Numeric  18  11  2  Right   

                  Full summary  Character  20  20   Left  Rsplit(File();2;" - ";1)  

                   

                   

                   

                  Filters

                   

                  Name  Type  Expression  Comment 

                  X-ray  formula_based  PT .In.("OC","OM","ON","OO","OU","OX") .and. Error .NotIn.("DSCH STATUS REQ","GUAR UNDER 18 YEARS","MCR REQ RETIREMENT

                   

                  DATES","PRIMARY INS NOT VERIFIED","PATIENT OVER 65 BUT NOT MEDICARE","TX MEDICAID REQUIRES PRECERT").and. substr(error,1,34) <>'Check APC Alert

                   

                  (CCI Edits)-ABY193' .or. Substr(error,1,16) ='DUP CXR SAME DAY' .or. substr(error,1,25) ='MODIFIER LT & RT EQUAL 50' .or. substr(error,1,16) ='DUP

                   

                  CXR SAME DAY'  

                   

                  Admitting  formula_based  Substr(error,1,18) ='DOCTOR NPI MISSING' .or. substr(error,1,30) ='GAR NAME HAS AN INVALID FORMAT' .or.

                   

                  substr(error,1,19) = 'GUAR UNDER 18 YEARS' .or. substr(error,1,28) = 'MISC PAYOR/PLAN NOT VERIFIED' .or. substr(error,1,25) = 'MISSING GUARANTOR

                   

                  ADDRESS' .or. substr(error,1,25) = 'PAT NAME CONTAINS INVALID' .or. substr(error,1,30) = 'PAT NAME HAS AN INVALID FORMAT' .or. substr(error,1,26)

                   

                  = 'PATIENT ADDRESS INCOMPLETE' .or. substr(error,1,25) = 'PAYOR REQUIRES NEIC GROUP' .or. substr(error,1,31) = 'MISSING GUARANTOR EMPLOYER INFO'

                   

                  .or. substr(error,1,32) = 'MISSING ATTENDING PHYSICIAN – IP' .or. substr(error,1,29) = 'MISSING INSURED DATE OF BIRTH' .or. substr(error,1,28) =

                   

                  'MISSING INSURANCE PAYOR INFO' .or. substr(error,1,13) = 'HIC # INVALID' .or. substr(error,1,7) = 'MCR REQ' .OR. substr(error,1,13) = 'ICD9

                   

                  REQUIRES' .or. substr(error,1,29) = 'PAYOR 201 POLICY NUMBER ERROR' .or. substr(error,1,17) = 'PRIMARY INSURANCE' .or. substr(error,1,30) =

                   

                  'NEWBORN LINK TO MOTHER MISSING' .or. substr(error,1,26) = 'WRONG PAYER USE 201 IF HMA' .or. Substr(error,1,3) ='R&B' .or. substr(error,1,6) = 'NO

                   

                  R&B' .or. substr(error,1,30) ='INSURANCE CODE 100 NOT ALLOWED' .or. substr(error,1,23) ='INSURED MUST BE PATIENT' .or. substr(error,1,29) ='TX

                   

                  MEDICAID REQUIRES PRE CERT' .or. substr(error,1,30) ='INSURANCE CODE 100 NOT ALLOWED'  

                  RT  formula_based  substr(pt,1,2) ='OE'  

                   

                  Alert Report  formula_based  Substr(type,3,6)='HARD' .And. substr(type,1,6) = '**HARD'  

                   

                  Lab  formula_based  Substr(error,1,11) ='MCR LAB QTY' .or. substr(error,1,25) ='BLOOD ADM QTY > 1 FOR DAY'  

                   

                  Wound Care  formula_based  PT .In.("OJ","PJ","RJ") .and. substr(error,1,15) <>'DSCH STATUS REQ'  

                   

                  Case Management  formula_based  (Substr (pt,1,2) = 'O5' .or. Substr (pt,1,2) = 'I1') .and. Substr(error,1,29) ='TX MEDICAID REQUIRES PRE CERT'

                   

                  .or. substr(error,1,11) ='OBSERVATION' .or. substr(error,1,20) ='CASE MANAGEMENT HOLD' .or. substr(error,1,19) ='CHGS NEAR STOP LOSS'  

                  top 20  formula_based  (today()-[disch]>=5) .And. Substr(type,3,6)='HARD' .And. substr(type,1,6) = '**HARD'  

                   

                  IS  formula_based  substr(error,1,13)='REV/NEGATIV(1'  

                   

                  ER  formula_based  substr(pt,1,1) ='E' .and. Substr(error,1,14) = 'CROSS DEP ERRS' .or. substr(error,1,25) ='NO ER E-M CPT4 CODE FOUND' .or.

                   

                  substr(error,1,30) ='ER 450 REVENUE CHARGE REQUIRED' .or. (substr(pt,1,1) ='E' .and. substr(error,1,24) ='PATIENT HAS ZERO BALANCE') .or.

                   

                  substr(error,1,8) ='CHG 480/' .or. substr(error,1,13) ='96361 QTY > 8'  

                  Business Office  formula_based  Substr(error,1,7) ='201 POL' .or. substr(error,1,12) ='CHP REQUIRES' .or. substr(error,1,16) = 'INVALID MEDICARE'

                   

                  .or. substr(error,1,18) = 'MAMMOGRAM REQUIRES' .or. substr(error,1,6) = 'NEG LI' .or. substr(error,1,8) = 'OCCUR CD'.or. substr(error,1,10) = 'OD

                   

                  ACCOUNT' .or. substr(error,1,14) = 'ROSTER BILLING'.or. substr(error,1,12) = 'REV/NEGATIV(' .or. substr(error,1,13) = '801 REV ON OP' .or.

                   

                  substr(error,1,9) = '42X OCCUR' .or. substr(error,1,9) = '44X OCCUR' .or. substr(error,1,22) = 'ESA RX REQ VALUE 48/49' .or. substr(error,1,12)

                   

                  ='OBV HRS > 23' .or. substr(error,1,22) ='NON ESRD ESA NEEDS MOD' .or. substr(error,1,24) ='TOB 131 W/DRG – CONFLICT'  

                   

                  Surgery  formula_based  Substr(error,1,29) ='Error placeholder to keep tab'  

                   

                   

                  Sorts

                   

                  Charge

                  Sort Field  Sort Order 

                  Charge  Descending 

                   

                   

                  Summaries

                   

                  Name  Filter  Comment  Custom Page Setup  Number of Keys  Number of Measures 

                  Full Summary  Default Filter   No  1  1 

                   

                   

                   

                   

                  Summaries - Key Fields and Measures

                   

                   

                   

                  Summary - Full Summary

                   

                  Key Field Name  Key Field Title  Key Field Display Length  Key Field Sort Direction 

                  Full summary   19  Ascending 

                  Measure Title  Measure Display Length  Measure Expression 

                  Running Total  13  CUM(Charge) 

                  /Code

                    • Modeling Help
                      Joe Berry

                      Hi dpetrauskas,

                       

                       

                        I’m going to try to answer your questions.  This will require a project to get this accomplished.

                       

                       

                        First for the top 20 by charges excluding duplicates:  Create a sort (Top 20) that sorts by charges, descending.  Then create a filter (Top 20).  On the formula tab enter the name of the filter and 1 in the expression.  Go to the filter advanced tab and choose “Select rows according to their uniqueness with respect to the specified keys” and check unique values.  Under row count limit enter 20 and check the account number field under specified keys.  Save and make this filter active.  These will now display in the Table Window the Top 20 accounts and their specifics.  To get the total row, create a summary.  For the summary, create a new summary (Top 20).  On the general tab be sure to use the Top 20 Filter.  Check the Insert total line.  On the Fields Tab, I chose as key fields, Charges, Account Number, and Discharge Date.  As the measure I used Charges.  Since this report should be fairly small, you can choose whatever other fields you will want to export.  For the Charges field, go to properties and in the display tab, choose Hide key Column.  Under the sorting tab choose descending.  These allow the summary to sort by charges, but not display that field in the first column.  The summary should now display the top 20 accounts with their charges and a total line for export to Excel.

                       

                       

                        To sort your other exports create a sort for discharge date.  To have the dollar amount of each error included only in that errors listing with no duplicates you use the same logic as the charges example.  You can actually use a summary to export all of the different error codes to a different tab.  You would use the Error Code as the first key field in the summary.  Under the properties for the error code, in the display tab, you can add a display for subtotals by error code.  Then when you create the export, you choose check export each distinct value of the first key to a separate file or table on the second window of the project export.  You can then select export to separate files or separate tab(les) within the same file.

                       

                       

                        If you would send me a private mail with your email address I would be happy to forward the model and project file I created.