Skip to main content

Importing Subheaders in a print report file

Hi 
What would you do to achieve bringing in print reports that have multilevel subheader. In the example below there's a sub header will fill in all blanks lines below it should the "use value from previous record" option is selected. Picking you brain on this matter. Any suggestion is much appreciated.
       
 
 Accounts Payable
           135   XYZ             2017-12-31         8.00
           137   ABX             2017-12-31         5.00
 Total Accounts Payable                           13.00
 Credit Cards
        MasterCard
           114   CC Charge    2017-11-16      -5.32
           211   CC Charge    2017-11-27      -4.66
           217   CC Charge    2017-12-16      -9.98
           Total MasterCard                          -18.96
        Total Credit Cards                            -18.96
  Chequing
          156   Deposit         2017-11-30        88.87
  Total Chequing                                        88.87
 
Regards,
G
 
 

Brian Element Fri, 07/06/2018 - 07:09

I see your problem, the Mastercard gets attached to transactions that are not a MasterCard transaction.  What I would do depends on the file and there isn't enough info for me to say.  One thing I might do is import all the transactions normally adding the account name to it, if there is only MasterCard then I might add that through a virtual field in IDEA saying if the account name is Credit Cards then enter MasterCard.  If there are multiple credit card types then this might not be feasable and if you did that you would need something in the information to say what type of card it might be.

Another way would be to do two passes, the first bringing in all the line information and the second only the Credit Card info, probably using the Credit Card name as the trap.  In the second pass you can bring in the Credit Card name as you are only importing Credit Card info.  Once it is in IDEA you can either remove all Credit Card info from the first file and then do an append to create the new file or if that first numer is unique you could do a join adding the credit card name to the first file.

There might be a better way to do this, another user on the site, Steve, has done more of these then I have and he might have a better way of importing this file structure.

Brian

Steven Luciani Fri, 07/06/2018 - 08:50

Thanks for the vote of confidence Brian. The report reader exercise from IDEA's level 1 training contains a file that has this issue. Normally we trap the first detail line in the report as the base layer, then trap the first account name header line as the pre-append layer to fill down the account names. The sub account names are ignored for the purpose of this exercise.

I've attached the sample file and the jpm that incorporates the account name and sub account name. The key to getting the sub account name to append to the file and fill down correctly is predicated on which detail record line you choose as the base layer. In the attached example you must scroll down the report until you find an account that has a sub account as well. In this case it is the detail record below property plant and equipment - computer equipment cost or line 225 of the report. (see bottom right corner of the report reader window)

Use this as your base layer then use the property plant and equipment line as your first pre-append layer and computer equipment cost as your second pre-append layer.

The key to sucessful use of report reader is to find a pattern that matches what you want to draw from the report then follow that pattern to create your IDEA file. Remember like this example the pattern may not always be on the first couple lines of the report.

Good luck with future imports.

 

 

idea2018 Mon, 07/09/2018 - 02:35

Brian & Steve, a big thank you for your suggestions.
Once the file has been imported the Visa subheader is also populated where AcctName is GST/HST Payable. See attached pic.
My workaround is to append a char field with a logic to remove subheaders that were wrongly populated i.e @If(@LIST(ACCTNAME,"GST/HST Payable","PST Payable (ON)","Term Loan","Construction Income")," ", SUBHEADER)
 
Do you have other methods of achieving the same?
 

Brian Element Mon, 07/09/2018 - 07:40

In reply to by idea2018

Yes, that is one of the ways I would probably handle this report.

Steve, I was looking at your solution and it seems that the Sub AcctName field continues when it should be blank.  An example is the Acctname for Accounts Payable - CNY has a sub acctname of Office Equipment where I would think it is blank.

osaajah Fri, 07/13/2018 - 05:18

Hi idea2018,
I think you are so close to the solution.
In my opinion, account name and its total should be populated together in Report Reader.
Then after importing to IDEA, we just need to remove all sub accounts that start with "Total".
 
Firdaus Sentosa.