hi, everyone,
struggle to write an report to produce report with all income statement GL accounts for 12 months (July 2014 to Jun 2015)?
Can anyone help?
Thanks
Ian
Date
Votes
11 comments
-
Jet Reports Historic Posts Hello Ian -
Does Brian Petersen's solution help? –> https://community.jetreports.com/viewtopic.php?f=65&t=4151&hilit=gl+balance+month -
Jet Reports Historic Posts Hi, HP,
thanks for your help.
I had a look the link you provided me, it doesn't seem to be want I need.
at the moment, what I did is create a list of GL transactions by transaction date, by Dimension1, Dimension2 and Dimension3.
i will then be able to use pivot table to re-arrange the data into a report in the format i need.
it works but with problems: 1. when run it, it's very slow, takes nearly 10 minutes to run a whole Year's report.
2. it download so much data(basically every single transaction by dimension.
this makes the excel file so big and slow.
what I am trying to do is if I could reduce the amount data retrieved by summerising data by month. after all, when i create pivot table, i will need only each GL month end figure for 12 months to show trend movement.
I attach the JET report i have written.
any help is very welcome and appreciated.
thanks,
Ian -
Jet Reports Historic Posts what about starting by grouping your report - having your ROWS command give you the period you want to report on?
CELL D4 =NL("ROWS=2","DATE","PERIOD START","PERIOD START",Options!D8,"PERIOD TYPE","MONTH")
CELL E4 =NP("DATEFILTER",D4,NL(,"DATE","PERIOD END","PERIOD START",D4,"PERIOD TYPE","MONTH")
build your next layer of the group based on whatever values you need uniquely in your report (GL Category/Account, etc) - but reference it to the date range you have already pulled
After that, use NL(SUM) to pull in the sum total for the month you are looking at in the date filter
if you haven't created any grouping reports yet, take a look at this article from the knowledge base: http://kb.jetreports.com/article/AA-00519/0/ -
Jet Reports Historic Posts hi, Heather,
thanks for your reply.
I tried the formula you provided, but it seems not working.
it only pulls out the date existing for the date range the NL formula creates.
but because there are entries under different dimension combination, I might need to repeat the Date range in order to retrieve the data.
I attached a worksheet to explain what I mean:
those data highlighted needs to be summerised because they all incur in the same month and have the same dimension combination.
whereas the rest of unhighlighted records cannot be grouped as they have different dimension despite the same GL account and transaction month.
Thanks for your help.
Ian -
Jet Reports Historic Posts Ian, I'll be honest with you - I don't know if this is going to be faster than your current report or not - but here goes :)
start with the date grouping that I showed you - it should create a datefilter for each month of your date range (or, if you are only doing this report monthly - then don't worry about that and just run the report for a monthly date range)
then you need to build a unique key for your dimensions (I'm assuming your database doesn't have such a key already created). So, assuming you are using D4 for your first ROWS command:
D5 = D4
E5 =NL("ROWS","G/L ENTRY",{"G/L ACCOUNT NO.","G/L ACCOUNT NAME","ACTIVITY CODE","SERVICE TYPE CODE","LOCATIONS CODE"},..FILTERS HERE..,"POSTING DATE",D5)
The section in the brackets is creating your key, so you will only pull the combination of those features a single time for each month.
The posting date is referencing back to the month filter you created with the first line, so you'll have your first month, the dimensions for that month after that, then your next month and the dimensions, and so on.
there are probably other ways to do this, but this is the method I have used. -
Jet Reports Historic Posts hi, Heather,
Thank you very much for your help.
I tried again but it doenst produce the result I need.
The whole point of doing that is to create a line/trend chart with 12 months or more months figure and filter those data by pivottable slicer(Dimensions or G/L accounts)
e.g I could filter 6-0580 Travel expenses by combination of Dimension 200-2006-Q001. in addition, all these combinations are not fixed. it varies from date to date.
at the moment, the Formula I created it download every single transaction by G/L, date and by dimension. but I only need it to be created by month because my line chart is only by month.see the attached report
Sorry this has been really confusing.
really appreciate your help.
Regards,
Ian -
Jet Reports Historic Posts Okay - so the solutions I have given are to create a two-tiered grouping report, with the first layer being your 'total by month' and the next layer being your Dimensions, creating a key for the dimension based on a combination of your GL/Account Number, G/L Account Name, Activity Code, Service Type Code, and Locations Code.
So those solutions should have created something that looks like this:
01/01/2015..31/01/2015
01/01/2015..31/01/2015 NL Filter for: G/L Acct Name AA|G/L Acct No. 1|Activity Code A|Service Type Code D| Locations Code A
anytime one of those items changes (G/L Acct No, Activity Code, etc) - you should have a new unique line
your amount needs to be an NL("SUM"… command, not an NF command, and it will sum up the totals for each month - as defined in the Date filter
For instance: =NL("SUM","G/L Entry","Amount","G/L Account No.",POINT TO CELL WITH G/L ACCT NO RETURNED,"Activity Code",POINT TO CELL WITH ACTIVITY CODE,"Service Type Code",POINT TO CELL WITH SERVICE TYPE CODE,"Locations Code",POINT TO CELL WITH LOCATIONS CODE,"Service User Code",POINT TO CELL WITH SERVICE USER CODE,"Company=",Options!$D$3,"Posting Date",POINT TO DATE FILTER AT BEGINNING OF ROW)
note that everywhere that I have said "POINT TO", I am talking about the cell reference in that row - and this needs to be a relative cell reference (so it will expand with the rows command)
The PDF you attached shows the totals in a columnar layout, if that is what you want, then your date filters need to be generated with a NL("COLUMNS".. command, and then the cell references to the date need to be pointed to date filter in each column. But I'm assuming that you are planning to take the Jet data and transform it with a Pivot or other method, which is why I've kept my explanations to the ROWs grouping.
One other thing - which I should have included in my earlier instruction - you'll want to put a HIDE in column A for the row that only returns the date filter. -
Jet Reports Historic Posts hi, Heather,
i created a report as you advised.it retrieved some data but seems that it has missed a lot of data.
not sure what is missing in the formula.
would you mind have a look at the worksheet i attached see what's wrong?
much appreciate your help.
Regards,
Ian -
Jet Reports Historic Posts Ian,
I don't have the fields you are dealing with in your G/L Entry Table - so I can't see exactly what information is missing when you run this report.
The one problem I DO see is that you have not made this a grouping report yet - you need to change the formula in D5 to say "ROWS=2", instead of "ROWS"
And I would put a "HIDE" into cell A5 so that the row with only the date filter is hidden in your final output.
What Rows=2 is doing is telling Jet to run the two rows together, so first create the date filter row (row 5 in your report), then create the rows for to create your unique lines, and when all of those are created, go back and build the next month (from ROWS=2)
Other than that, I believe the logic is working as expected - it appears to be when I run it using fields available in my system. -
Jet Reports Historic Posts hi, Heather,
just tried to change Rows to Rows=2, it then works perfectly.
I actually changed to Rows because at that time I did not realise what is the purpose of using Rows=2 .
the report runs just a little bit faster than the one I created, but much much smaller in size.
Thank you very much for your kind help.
really appreciate your time and efforts in answering my queries.
Ian -
Jet Reports Historic Posts Glad I could help - I know I got a lot of assistance from this forum when I was starting off, so it's good to be able to repay that assistance now by paying forward.
Have a great weekend Ian!
Please sign in to leave a comment.