I am trying to streamline a report that looks at 27 companies and sums the 5 yr. revenue & expense for each one in columns for historical review purposes. I am using the following to create each column and row:
Columns
=NL("Columns","G/L Account","No.","DataSource=","master","No.","7000..9999") - defines GL Account Number
=NL("Columns","G/L Account","Name","DataSource=","master","No.",I$7) - I$7 refers to the G/L Account number
=NL("Sum","GLDetail","Amt","DataSource=",Options!$G$20,"AcctNu",I$8,"BeginDate",Options!$G$15,"CoNu",$E11)
Rows
=NL("Rows","SSCo","CoNu","DataSource=","NJD SHELBY") - defines Company number
=NL("rows","SSCo","Name","DataSource=","NJD SHELBY","CoNu",$E11) - $E11 refers to Company number calculation
Any thoughts?
I'm using
Jet 2011
Shelby
Serenic
Excel 2007
Thanks.
Richard Pease
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Richard,
You only want one NL(Columns) formula and one NL(Rows) formula in this case. NL(Columns) and NL(Rows) are used to replicate columns and rows. However, your formulas to get the account name and the company name aren't actually going to replicate anything, so you should change those to NL(First) formulas like this:=NL("First","G/L Account","Name","DataSource=","master","No.",I$7) =NL("First","SSCo","Name","DataSource=","NJD SHELBY","CoNu",$E11)
You could also use NP(Eval) to bring your options to the current sheet and then use references to the eval formulas to reference those. Sometimes that can have a minor speed improvement. The formulas would look something like this:=NP("Eval","=Options!$G$20")
Otherwise, the report looks fairly straight forward. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
Thanks, that worked brilliantly. It cut my report time from 11 minutes to 4 1/2. I am new to Jet (I used FRx in my previous firm) and am doing some on the job learning until I attend March's Jet Training in NYC.
Richard
Please sign in to leave a comment.