Hi all,
I have a report that calculates GL Expenditure. On the first worksheet is where you enter the chosen date range. I also have 12 other worksheets that automatically calculate the information for each calendar month. This report (due to all the calculations) takes about 8 minutes to run.
Is there a way to disable the 12 worksheets from running in Jet? If so, I could do some sort of IF statement whereby a user could enter YES or NO in a field in answer to if they wanted to run the monthly calculations. A 'YES' would enable the 12 worksheets to run and a 'NO' would stop them running, thus saving a lot of time.
Is this possible?
Many thanks, Paul
Date
Votes
11 comments
-
Jet Reports Historic Posts Official comment Hi Paul,
Here's the modified spreadsheet. I've marked the cells where I made modifications with orange background.
In sheet "Defined Dates" only the report options being modified. And I think you'll understand why that's being modified.
Another modification is in sheet "JAN" cell D19 where you pull the Purchase Inv. Line No.
And frankly, that's all the modification you need to do in order to control whether the calculation will run or not. And it's because all other NL and NF function you have in that sheet depend on the result yielded from cell D19. If this cell fail to yield any result, then all other NL & NF will fail also. And therefore, no calculation will be performed. Saving you time running the report.
Now, I intentionally only modify Sheet JAN, and leave it to you to modify it for Sheet Feb-Dec. I think you'd understand & learn more if you are able to do it yourself.
But first, go ahead and run the report. When the report option show up, answer "No" for question "Show Monthly Calc ?".
Finger crossed, when the report done running, Sheet JAN should result in nothing at all, while all other sheets will show calculations.
Please let me know how it go, would this modif work for you ?
Andy -
Jet Reports Historic Posts Hi Paul,
You could go into all the Jet replicator functions and put an IF into the table argument to make them return an empty array in this case. You can shortcut NL replicator (rows, columns, sheets) function evaluation like this:=NL("Rows",IF(Options!$C$5="Yes","Customer",{""}),….)
Then you would have to put Excel IF statements around things like NL(Sum) functions or GL functions. This would be a lot of work and probably make your report harder to edit and maintain.
A much easier solution is just to make a 2nd report that only has the part that you want to see and refresh. Then when you only want to refresh and view the summary data, just open that report instead of this one.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for the reply. That does look like a ton of work. I had hoped to avoid doing two reports (one with the months, one without) but I may have to go down this route.
Many thanks, Paul -
Jet Reports Historic Posts Hi Paul,
What you ask is actually possible with a small tweak. I already posted an answer for this kind of problem in another thread, go and take a look.
http://community.jetreports.com/viewtopic.php?f=6&t=2453
Andy -
Jet Reports Historic Posts Hi Andy,
Thanks for that link. I tried to implement it, but it didn't work. I was clearly doing something wrong. I take it I need to insert that code in every NL function?
Also, I didn't understand this part:
**************************
For this example, I will assume that you have the Report Options copied to the report sheets also. Example:
________________M_________________________N
11 _____________Run Sheet 1 ? _______________Yes
12 _____________Run Sheet 2 ? _______________Yes
13 _____________Run Sheet 3 ? _______________No
**************************
How does this pick up the results of the 'option'? Why not just reference the option itself (E.G. D2 would show the Yes or No).
Regards, Paul -
Jet Reports Historic Posts Hi Paul.
It's probably easier if I can see your spreadsheet & modify it for you.
Do you mind sharing it here?
Andy -
Jet Reports Historic Posts Hi Andy,
Thanks very much for this - I have uploaded it with this message.
Regards, Paul -
Jet Reports Historic Posts Hi Andy,
Thank you very much for this - it works perfectly. I really appreciate you taking the time top look at my spreadsheet.
EDIT:
I just noticed the amended code in JAN (K20) has been changed to:
=NL("Rows","Purchase Header",,"No.",IF(I20<>"",I20,""))
from
=IF(I20<>"",NL("Rows","Purchase Header",,"No.",I20),"")
Your notes say: "I made modification here, because the replication function of NL(Rows) will not work INSIDE an IF funct. Instead, you need to put IF funct. Inside the NL funct."
I'm not sure what these changes do, as the results are the same. Can you explain?
Regards, Paul -
Jet Reports Historic Posts Hi Paul,
Don't forget to check out the Knowledge Base of Jet Reports where you can find a lot of information.
Also the thing your asking: http://kb.jetreports.com/article/AA-00485.
Regards,
Bert -
Jet Reports Historic Posts Hi Paul,
Well, the official explanation can be found in the link that Bert gave you. But, I can give you added explanation to make it cleared for you to understand.
Let's say this formula:=NL("Rows","Purchase Header","No.","No.",IF(I20<>"",I20,""))
give you the following results (and this is how it's supposed to be. It's the correct results):
_______________K_________________L
Row 10________123
Row 11________456
Row 12________789
—————————————————————–
Then, this formula:=IF(I20<>"",NL("Rows","Purchase Header",,"No.",I20),"")
will only produce this result:
_______________K_________________L
Row 10________123
Here, the REPLICATION function of NL Row / Column / Sheet will not work because the NL function is INSIDE the Excel IF funct. And that's why Row 11 and Row 12 is omitted.
I understand that you can't see the difference before in your worksheet, and that's because I believe the function you have will only yield 1 result only. Try it with a function where it should give out multiple rows, then you'll understand the difference between them.
Cheers,
Andy -
Jet Reports Historic Posts Hi Andy,
OK - that makes sense. I didn't understand the Replication bit in the link Bert sent me.
Thanks very much for your help.
Regards, Paul
Please sign in to leave a comment.