Hello members,
I have created a Jet report using the Jfx i.e. NL("Rows") function. I am using this function to pull data from multiple tables.
For grouping and showing summary, I have used the standard excel Pivot functionality.
After clicking on the Report/Refresh, the report is executes and shows the output. I then, set the pivot to refer to a range of rows & columns.
But, when I refresh the report, the pivot cell references are lost and it points to the header(first row) and the next data row.
I am not sure how to retain the cell references of pivot. I read a few posts in the forum mentioning that the pivot does not loose reference if we create report using table builder. But, reports created through Jfx have pivot reference issues.
I am not sure if I am missing out on something ?
Thanks in advance for the help !
Date
Votes
10 comments
-
Jet Reports Historic Posts Hi
I would suggest using the Table Builder as this returns a Excel table which you can Name. use the resulting Table Name (not the cell references) as the source for the Pivot reports
Alternatively you can use the NL Table function but this give problems when reporting from multiple source (NAV) tables
MJ -
Jet Reports Historic Posts Hi….Thank you for the response.
Actually, I have used the Jfx because it gives more flexibility and offers few functions that are not available using Table Builder.
So, I don't have an option to use the Table builder.
If I have to use the NL(Table) Function, how can I achieve the same?
Is it possible to use a macro for auto-selection of rows & columns in pivot ? -
Jet Reports Historic Posts One trick I have used in the past when needing to do something like this is to change my references for the pivot table to include your anchor row (the blank row after your Jet NL rows formula).
When you run your report - the blank will be included, but you can quickly delete that and refresh the pivot table to remove it's reference to the blank. Better yet, if you have the report scheduled, and are deleting the hidden rows in the final output, your pivot won't reference the blank because it will already be deleted. will this work for your report? -
Jet Reports Historic Posts Adding the anchor row works retains the pivot cell references. That's great.
But, then the data changes….if i apply manual filters to the data set, the data is different…
There is something going wrong. Will post further for help.
Thanks. -
Jet Reports Historic Posts Adding the anchor row solved the problem.
I was getting issue with data. I believe it was because the pivot was starting from column B, whereas in my source data sheet, I had written condition to Hide/Show columns.
By starting the pivot from column C and then referring the Pivot retains the cell references.
But the blank row comes as one of the groups in the pivot.
Can you suggest how tackle with this blank row in pivot. I know you have mentioned it in the previous post, but am unable to figure out.
Thanks. -
Jet Reports Historic Posts There are two options to solve the blank row reference in your pivot table:
1. If you are running the report via the Jet Scheduler: you can output the final copy to delete all hidden rows, columns and sheets. That will take care of your blank on it's own, deleting the blank row and updating the Pivot Table automatically. If you use this method - do NOT hide the data source for your pivot table (your table wont' refer to anything then).
2. If this is a report you just want to run at your desk periodically, or the scheduler wont' work for you - then once the report is run, save the report under another name (so you don't damage your report version), and delete the anchor row (the final empty row) from your source data. Then go back to the page with the Pivot Table and, using the Pivot Table Tools, refresh the Pivot Table.
In either instance the reference to a blank row should now be gone from your pivot table. -
Jet Reports Historic Posts @Heather,
Thanks for your help.
Actually, I am not using a Jet Scheduler, so the option 1 is not applicable.
As mentioned by you in Option 2, I first :
Set the pivot cell references(including the anchor row) in design mode and then executed the report.
Then, I saved a copy of the report and then deleted the anchor row (final empty row) in source data.
On refreshing the pivot, the blank references are eliminated. That works fine.
But, when I refresh the report, the pivot references are lost and it points to the first two rows in source data.
Any other way to handle these blank rows ? -
Jet Reports Historic Posts Yes, that's the drawback and why I had you save the report with a new name - because deleting that line does break the ability to refresh the report.
One trick I used in the past is to put values in the anchor row - no sales or quantity totals (nothing that will add up), but the references - the customer name and so forth.
I have a report that depends on multiple pivot tables, and I designed it before I knew what the scheduler would do (namely, deleting that blank line in the source data)
Example:
Report of sales lines, filtered by a particular product group - my ROWS formula is written as normal
instead of making the anchor line blank - I copy my ROWS formula from the row above, and remove the word "ROWS" from the formula
so the formula appears as =NL(,"SALES LINE", or you could replace the word "ROWS" with "FIRST"
then duplicate all of the other formulas - except for any values that you are doing calculations against
so your report (before running) will show two identical lines - until it gets to the Sales or Quantity or whatever else you are calculating.
Like this:
ACCT | NAME | DATE | SALE | QTY
1234 | ABC Co | 2/1/14 | 30.75 | 8
1234 | ABC Co | 2/1/14 | |
As before, include this line as the final row in your pivot table source data - when the report pivots, you won't notice anything UNLESS you are including a count in your pivot table metrics.
NOTE: if you're not going to delete it, you do need this anchor row to have data that is already in your pivot table, which is why I recommend repeating the first formula instead of just hard-coding a value in. -
Jet Reports Historic Posts @Heather,
Your trick did wonders !
It's an amazing work around to add formulas in the anchor row without having values in calculations.
Thank you for taking up an example an explaining it thoroughly.
Now, my report can work, and you have given me an additional point to think about - "Jet Scheduler".
Thanks. -
Jet Reports Historic Posts Glad I was able to help!
Please sign in to leave a comment.