4 Replies Latest reply: May 15, 2014 9:53 AM by MartyN _ RSS

    Separating SubReport Information

    MartyN _

      I have a report that contains several subsections. The detail in two of them have exactly the same column/field headings, so using a conventional trap captures both sections' data. I need to separate each section's info to separate output streams.  As shown in the sample report below, the detail can be line-by-line text strings. I have attempted multi-line output but the result is one long string and I need each line separately. Is there a way to accomplish this? (See end of report for desired output for the sample data.)

      [font="courier"]

       

      Sample Report Information   (XXXX = Header Info, YYYYY = Detail Type 1, ZZZZZ = Detail Type 2)

       

      See Last Page for other comments and what desired output would be.

       

      _____________________________________________ SAMPLE REPORT INFORMATION _________________________________________

       

      Run Date: 09/12/06 1:50pm                           Account Collection Report                                             Page:   1

      Sys Date: 08/01/06                                HOSPITAL IMAGING SERVICES                                        nmmcolrp 1.4c

       

      GUARANTOR INFORMATION                  Account # : 222221  Birth Dt: 12/03/22        Home Phone: (425) 222-2222     Sex: M

      Gxxxx, AL K                                                SocSecNm: 222-22-2222     Work Phone: (000) 000-0000     Ext:

      123 ABC St                             Ref Dr     :     1 RESERVED, OMI           

      REDMOND, WA  98052                     Doctor     :     1 RESERVED, OMI           

                                             ID         :

      Status: 1 Active Account               ID2        :

      Class :            BillType: 11        Empl Status:                          Marital Status:             Last Visit: 04/06/06

       

      Balance    :     95.58   Dt Last Pay  :            Amt Last Pay :         0.00   Last Hist Bal :      0.00   Discount  : 0 %

      Balance Fwd:     95.58   Dt Last Stmt : 06/21/06   Amt Last Stmt:        95.58   Unapplied Crdt:      0.00   Collection: 0 Days

      Pat Due Bal:     95.58   Dt of Account: 04/06/06   YTD Charges  :        95.58   Budget Pmt Amt:      0.00   Priority  : 0

       

      Pri Ins   : 9.0 TRUSTEED PLANS SRVC/FC  Rel: S     3rd Ins   : 0.0  Rel:         5th Ins   : 0.0  Rel:       7th Ins: 0.0  Rel:

      Sec Ins   : 0.0                         Rel:       4th Ins   : 0.0  Rel:         6th Ins   : 0.0  Rel:

       

      INSURANCE INFORMATION                  SUBSCRIBER INFORMATION                  INSURED PARTY INFORMATION

      9.0 TRUSTEED PLANS SRVC/FC             Subscriber #: 222222222                 Gxxxx, AL K

                                             Group #     : 45675                     Birth Date: 12/03/22          Sex: M

      PO BOX 1894

      TACOMA, WA  98401                      Phone: (800) 426-9786

          -


                                      -


                                       -


       

      TRANSACTION INFORMATION

       

          1.   Date   FirstName Dr#  Doctor  Serv Cd  Procedure Descriptn  Diag  Diagnosis Descriptn Voucher Ins  Lc Unit  Amount    ChgBal

      --- -


      -


      --- -


      -


      -


      -


      -


      -


      -


      -- -


      -


      -


      1   04/06/06 AL        144 YOLER, M 73030   SHOULDER COMPLETE MI 719.41 PAIN - SHOULDER             9/0  T  1      105.00    50.74

      1    06/08/06                       ADJ   #18           FIRST CHOICE DISCOUN                                         -54.26

      2   04/06/06 AL        144 YOLER, M 73000   CLAVICLE COMPLETE    719.41 PAIN - SHOULDER             9/0  T  1       93.00    44.84

      2    06/08/06                       ADJ   #18           FIRST CHOICE DISCOUN                                         -48.16

       

      Tot Chgs  Tot Pmts  Ins Pmts  Pat Pmts  Tot Adjs  Tot Bal   Ins Bal   Pat Bal

      ========= ========= ========= ========= ========= ========= ========= =========

         198.00      0.00      0.00      0.00    102.42     95.58      0.00     95.58

       

      TRANSACTION HISTORY INFORMATION

       

          1.   Date   FirstName Dr#  Doctor  Serv Cd  Procedure Descriptn  Diag  Diagnosis Descriptn Voucher Ins  Lc Unit  Amount    ChgBal

      --- -


      -


      --- -


      -


      -


      -


      -


      -


      -


      -- -


      -


      -


      1   04/06/06 AL        144 YOLER, M 73030   SHOULDER COMPLETE MI 719.41 PAIN - SHOULDER             9    0  1      105.00   105.00

      1    06/08/06                       ADJ   #18           FIRST CHOICE DISCOUN                                         -54.26

      2   04/06/06 AL        144 YOLER, M 73000   CLAVICLE COMPLETE    719.41 PAIN - SHOULDER             9    0  1       93.00    93.00

      2    06/08/06                       ADJ   #18           FIRST CHOICE DISCOUN                                         -48.16

       

      Tot Chgs  Tot Pmts  Ins Pmts  Pat Pmts  Tot Adjs  Tot Bal   Ins Bal   Pat Bal

      ========= ========= ========= ========= ========= ========= ========= =========

      11297.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

       

       

       

       

      Run Date: 09/12/06 1:50pm                           Account Collection Report                                             Page:   1

      Sys Date: 08/01/06                                HOSPITAL IMAGING SERVICES                                        nmmcolrp 1.4c

       

      GUARANTOR INFORMATION                  Account # : 222222  Birth Dt: 02/19/22        Home Phone: (425) 222-2222     Sex: F

      Gxxxxxxx, Exxxxxxxx F                                      SocSecNm: 222-222-2222    Work Phone: (000) 000-0000     Ext:

      22222 SE 40TH LN                       Ref Dr     :    94 ISSAQUAH, WHI           

      ISSAQUAH, WA  98029                    Doctor     :    94 ISSAQUAH, WHI           

                                             ID         : I025825

      Status: 1 Active Account               ID2        :

      Class :            BillType: 11        Empl Status:                              Marital Status: M           Last Visit: 10/09/98

       

      Balance    :      0.00   Dt Last Pay  : 11/20/98   Amt Last Pay :         5.81   Last Hist Bal :      0.00   Discount  : 0 %

      Balance Fwd:      0.00   Dt Last Stmt :            Amt Last Stmt:         0.00   Unapplied Crdt:      0.00   Collection: 0 Days

      Pat Due Bal:      0.00   Dt of Account: 06/29/98   YTD Charges  :         0.00   Budget Pmt Amt:      0.00   Priority  : 0

       

      Pri Ins   : 1.0 MEDICARE                Rel: S     3rd Ins   : 0.0  Rel:         5th Ins   : 0.0  Rel:       7th Ins: 0.0  Rel: 

      Sec Ins   : 610.0 CLALLAM COUNTY PHYS S Rel: S     4th Ins   : 0.0  Rel:         6th Ins   : 0.0  Rel: 

       

      INSURANCE INFORMATION                  SUBSCRIBER INFORMATION                  INSURED PARTY INFORMATION

      1.0 MEDICARE                           Subscriber #: 222222222A                Gxxxxxxx, ESSELLYNE F

      PART B-HOSPITAL                      Group #     :                           Birth Date: 02/19/22          Sex: F

      PO BOX 6700

      FARGO, ND  58108-6700                  Phone: (877) 908-8431

          -


                                      -


                                       -


      87.0 REGENCE BLUE SHIELD               Subscriber #: 222222222A                Gxxxxxxx, ESSELLYNE F

                                             Group #     : 099980                    Birth Date: 02/19/03          Sex: F

      PO BOX 30271

      Salt Lake City, UT  84130              Phone: (800) 322-1737

          -


                                      -


                                       -


      610.0 CLALLAM COUNTY PHYS SERVI        Subscriber #: 222222222A                Gxxxxxxx, ESSELLYNE F

      CLAIMS                                 Group #     : 099980                    Birth Date: 02/19/03          Sex: F

      PO BOX 1307

      PORT ANGELES, WA  98362                Phone: (000) 000-0000

          -


                                      -


                                       -


       

      TRANSACTION INFORMATION

       

          1.   Date   FirstName Dr#  Doctor  Serv Cd  Procedure Descriptn  Diag  Diagnosis Descriptn Voucher Ins  Lc Unit  Amount    ChgBal

      --- -


      -


      --- -


      -


      -


      -


      -


      -


      -


      -- -


      -


      -


       

      Tot Chgs  Tot Pmts  Ins Pmts  Pat Pmts  Tot Adjs  Tot Bal   Ins Bal   Pat Bal

      ========= ========= ========= ========= ========= ========= ========= =========

           0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

       

      TRANSACTION HISTORY INFORMATION

       

          1.   Date   FirstName Dr#  Doctor  Serv Cd  Procedure Descriptn  Diag  Diagnosis Descriptn Voucher Ins  Lc Unit  Amount    ChgBal

      --- -


      -


      --- -


      -


      -


      -


      -


      -


      -


      -- -


      -


      -


       

      Tot Chgs  Tot Pmts  Ins Pmts  Pat Pmts  Tot Adjs  Tot Bal   Ins Bal   Pat Bal

      ========= ========= ========= ========= ========= ========= ========= =========

        2753.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

       

       

       

      Run Date: 09/12/06 1:50pm                           Account Collection Report                                             Page:   1

      Sys Date: 08/01/06                                HOSPITAL IMAGING SERVICES                                        nmmcolrp 1.4c

       

      GUARANTOR INFORMATION                  Account # : 22223   Birth Dt: 07/14/22        Home Phone: (602) 222-2222     Sex: F

      Gxxx, MARIE                                                SocSecNm: 222-22-2222     Work Phone: (000) 000-0000     Ext:

      12345 E CEDAR CT                       Ref Dr     :    91 BELLEVUE, WHIB          

      SUN VALLY, AZ  22222                   Doctor     :    91 BELLEVUE, WHIB          

                                             ID         : B007900/R030512

      Status: 0                              ID2        : M035149

      Class :            BillType: 11        Empl Status:                              Marital Status: O           Last Visit: 10/16/00

       

      Balance    :      0.00   Dt Last Pay  : 07/02/99   Amt Last Pay :       774.79   Last Hist Bal :      0.00   Discount  : 0 %

      Balance Fwd:      0.00   Dt Last Stmt :            Amt Last Stmt:         0.00   Unapplied Crdt:      0.00   Collection: 0 Days

      Pat Due Bal:      0.00   Dt of Account: 08/17/98   YTD Charges  :         0.00   Budget Pmt Amt:      0.00   Priority  : 0

       

      Pri Ins   : 87.0 REGENCE BLUE SHIELD    Rel: S     3rd Ins   : 0.0  Rel:         5th Ins   : 0.0  Rel:       7th Ins: 0.0  Rel: 

      Sec Ins   : 0.0                         Rel:       4th Ins   : 0.0  Rel:         6th Ins   : 0.0  Rel: 

       

        DUPLICATE ACCT-SEE ACCT 13231

       

      INSURANCE INFORMATION                  SUBSCRIBER INFORMATION                  INSURED PARTY INFORMATION

      87.0 REGENCE BLUE SHIELD               Subscriber #: ZLP222222222              Gxxx, MARIE /

                                             Group #     : 015353                    Birth Date: 07/14/22          Sex: F

      PO BOX 30271

      Salt Lake City, UT  84130              Phone: (800) 322-1737

          -


                                      -


                                       -


       

      TRANSACTION INFORMATION

       

          1.   Date   FirstName Dr#  Doctor  Serv Cd  Procedure Descriptn  Diag  Diagnosis Descriptn Voucher Ins  Lc Unit  Amount    ChgBal

      --- -


      -


      --- -


      -


      -


      -


      -


      -


      -


      -- -


      -


      -


       

      Tot Chgs  Tot Pmts  Ins Pmts  Pat Pmts  Tot Adjs  Tot Bal   Ins Bal   Pat Bal

      ========= ========= ========= ========= ========= ========= ========= =========

           0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

       

      TRANSACTION HISTORY INFORMATION

       

          1.   Date   FirstName Dr#  Doctor  Serv Cd  Procedure Descriptn  Diag  Diagnosis Descriptn Voucher Ins  Lc Unit  Amount    ChgBal

      --- -


      -


      --- -


      -


      -


      -


      -


      -


      -


      -- -


      -


      -


      1   06/02/99 MARIE     20  CLEMETT, 76700   ULTRASOUND ABDOMEN C 789.07 ABDOMINAL PAIN-GENE 100707  87   0  1      337.00   337.00

      1    07/02/99                       PMT   CHK   0630199 Ins #87 REGENCE BLUE SHIELD                                 -142.76

      1    07/02/99                       ADJ   #10   0630199 BLUE SHIELD DISALLOW                                        -194.24

      2   06/02/99 MARIE     9   CLEMETT, 74160   CT ABDOMEN W/CONTRAS 195.2  NEOPLASM - ABDOMEN  65173   87   0  1      775.00   775.00

      2    07/02/99                       PMT   CHK   0630199 Ins #87 REGENCE BLUE SHIELD                                 -391.15

      2    07/02/99                       ADJ   #10   0630199 BLUE SHIELD DISALLOW                                        -383.85

      3   06/02/99 MARIE     9   CLEMETT, 72193   CT PELVIS W/CONTRAST 195.2  NEOPLASM - ABDOMEN  65173   87   0  1      775.00   775.00

      3    07/02/99                       PMT   CHK   0630199 Ins #87 REGENCE BLUE SHIELD                                 -383.64

      3    07/02/99                       ADJ   #10   0630199 BLUE SHIELD DISALLOW                                        -391.36

       

      Tot Chgs  Tot Pmts  Ins Pmts  Pat Pmts  Tot Adjs  Tot Bal   Ins Bal   Pat Bal

      ========= ========= ========= ========= ========= ========= ========= =========

        2513.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

       

       

      Desired Output would look like this -

       

      Dataset/Output Stream 1 (Header plus Transaction Detail 1)  (Where "…" indicates same as above)

       

      PtName     AcctNo     SSN     DOB     Transaction Detail Type 1  YES, ONE LONG STRING

      Gxxxx, AL K     222221     222-22-2222     12/03/22     1   04/06/06 AL        144 YOLER, M 73030   SHOULDER COMPLETE MI 719.41 PAIN - SHOULDER             9/0  T  1 etc

      Gxxxx, AL K     222221     …     …     1    06/08/06                       ADJ   #18           FIRST CHOICE DISCOUN

      Gxxxx, AL K     222221     …     …     2   04/06/06 AL        144 YOLER, M 73000   CLAVICLE COMPLETE    719.41 PAIN - SHOULDER             9/0  T  1 etc

      …     …     …     …     2    06/08/06                       ADJ   #18           FIRST CHOICE DISCOUN

      Gxxxxxxx, Exxxxxxxx F     222222     222-22-2222     02/19/22     

      Gxxx, MARIE     222223     222-22-2222     07/14/22     

       

       

       

      Dataset/Output Stream 2 (Header plus Transaction Detail 2)  (Where "…" indicates same as above)

       

      PtName     AcctNo     SSN     DOB     Transaction Detail Type 1

      Gxxxx, AL K     222221     222-22-2222     12/03/22     1   04/06/06 AL        144 YOLER, M 73030   SHOULDER COMPLETE MI 719.41 PAIN - SHOULDER             9    0  1      105.00   105.00

      Gxxxx, AL K     222221     …     …     1    06/08/06                       ADJ   #18           FIRST CHOICE DISCOUN                                         -54.26

      Gxxxx, AL K     …     …     …     2   04/06/06 AL        144 YOLER, M 73000   CLAVICLE COMPLETE    719.41 PAIN - SHOULDER             9    0  1       93.00    93.00

      …     …     …     …     2    06/08/06                       ADJ   #18           FIRST CHOICE DISCOUN

      Gxxxxxxx, Exxxxxxxx F     222222     222-22-2222     02/19/22     

      Gxxx, MARIE     222223     222-22-2222     07/14/22     1   06/02/99 MARIE     20  CLEMETT, 76700   ULTRASOUND ABDOMEN C 789.07 ABDOMINAL PAIN-GENE 100707  87   0  1      337.00   337.00

      Gxxx, MARIE     222223     …     …     1    07/02/99                       PMT   CHK   0630199 Ins #87 REGENCE BLUE SHIELD                                 -142.76

      Gxxx, MARIE     …     …     …     1    07/02/99                       ADJ   #10   0630199 BLUE SHIELD DISALLOW                                        -194.24

      …     …     …     …     2   06/02/99 MARIE     9   CLEMETT, 74160   CT ABDOMEN W/CONTRAS 195.2  NEOPLASM - ABDOMEN  65173   87   0  1      775.00   775.00

      …     …     …     …     Etc, etc

      Gxxx, MARIE     222223     222-22-2222     07/14/22     3    07/02/99                       ADJ   #10   0630199 BLUE SHIELD DISALLOW                                        -391.36

      /font[/quote]

        • Separating SubReport Information
          Grant Perkins

          Hi Marty and welcome to the forum.

           

          That's one heavy report you're working with. While it might not be as complex as it looks at first for the data extraction you require it gives the impression of needing a lot of time to properly understand what you want.

           

          On the other hand ...

           

          You required outputs look pretty much like the entire existing lines (or most of the existing lines) for the TRANSACTION INFORMATION and TRANSACTION HISTORY INFORMATION (I assume that is what you are referring to as stream 1 and stream 2?) with some patient info appended to the beginning of the line. So one approach might be to select all or many of the lines on the report for either transaction type (possibly as a single field?), append the type and the other details required and in the table apply a filter to retain only the lines of interest (assuming some spurious lines may be picked up in the trap.)

           

          That may be pretty much all you need to do if the required output is simply a revised version of certain lines from the existing report with a little additional info added to the line.

           

          If you need to make the extracted line give up their data into specific fields you seem to have more of a challenge on your hands since the individual lines don't look much like they would slice and dice cleanly to provide that sort of result.

           

          Does this help at all?

           

           

          Grant

          • Separating SubReport Information
            MartyN _

            Grant, thank you for the response.  Your assumptions relative to Stream1 and Stream2 are correct, and the detail can consist of each line as a string as opposed to individual fields. I think my stumbling block at this point is how to create the append to capture which of the sub-sections each line is coming from (ie, Transaction Info. Vs Transaction Hist. Info.)  The patient identification information is easy, since I can "reference" from the first occurrence on the report.  Any help on how to "pick up" the subsection "title" would be appreciated.  (PS - as you alluded, the report is way too complex, but is the only history we have at this point.)

            • Separating SubReport Information
              Grant Perkins

              Hi Marty,

               

              Hmm. I feared as much.

               

              My initial thinking (no trials so far attempted using the sample posted) is that the transaction lines are the detail lines if they can be trapped easily given the variable format of the lines.

               

              The Stream type looks like it could simply be an append trapping on "TRANSACTION" and then including the full name as the data field. (One could be more sophisticated about that if desired of course!) Hide the field for output.

               

              The other fields should be OK to extract as you have confirmed.

               

              The problem I can foresee is that some records do not have transaction lines in one or other stream but still need an entry.

               

              Sometimes you can 'spoof' a record (in this case just an empty field required as a substitute 'detail' field) but I'm not seeing anything here that might readily allow that using any trapping  that would also work for the real detail records.

               

              So you may need to consider a 2 step process where one of the steps extracts the records that do exist and the other specifically looks in each Streams where no transaction record exist and creates a record with what would normally be the append info. and a blank field for the transaction record. Same record format for both extracts. (

              Trap the report area of the TRANSACTIONS and paint a field for the first line under the TRANSACTION column headers. Filter to include only the record where that field is blank.)

               

              Both outputs can be sent to the same file (by stream I assume) which can then be sorted, based on Patient Name and Stream type (and any other field which may be required although in theory I guess there should not be many additional requirements where the 'spoof' records are concerned.)

               

              Thats the idea in outline. Does it make sense as a description? I appreciate that the practicalities and process specifics may require deeper consideration.

               

              HTH.

               

               

              Grant

              • Separating SubReport Information
                MartyN _

                SUCCESS!  With appends of the Subsection Title (TRANSACTION INFO. and TRANS. HIST. INFO.) with the detail line, plus use of filters on the resulting table, I retrieve the necessary detail and discriminated which section of the report the detail is from.

                 

                Grant, thank you for your help!   [img]smile.gif[/img]