Hi,
Just attended a wonderful presentation at NAVUG put on by Anthony Bonaduce. He made making pivot tables look so easy!
However when I try it myself, I cannot find the summarize with pivot table function he was using.
I am on Jet Essentials 2015, 15.0.15019.0. 32 bit.
I'm sure I'm just missing something simple, and would appreciate your help!
Pat
Date
Votes
6 comments
-
Jet Reports Historic Posts The "Summarize with PivotTable" only appears if you have created a table and have selected one cell within the table.
When you create the table and select a cell, the Table Tools->Design ribbon appears in Excel.
You can still create a PivotTable from other data, but using a table works best.
If you want to create a PivotTable from data other than a table, select your data, then go to the Insert ribbon.
You can select PivotTable or Recommend PivotTables from there. -
Jet Reports Historic Posts Thank you for responding so quickly!
It looks like I am not creating a table!
I am using a NL Rows function to create the rows of data that I want.
Not sure how to turn this into a table.
What I am ultimately wanting to do, is create a pivot table that will refresh when I refresh the Jet report.
Any help would be greatly appreciated! -
Jet Reports Historic Posts A Table is not required to summarize with a Pivot Table - it's just the easiest method. If a Rows report is what you need to build for your data, then you can use that.
My method is to run the report, then build the pivot table from the report - you can insert a pivot table from Excel's "Insert" tab on the ribbon (Pivot Table is in the first position on my toolbar - Excel 2010).
I select my data as JET-br describes, and then select Insert Pivot table, and build using the Pivot Table tools.
After that, you set the report back to design stage - your pivot table range will update to include only the remaining row(s) in your report.
The trick to making sure that your pivot table updates when the report runs, is to include a blank row at the bottom of your report in the pivot table range.
for instance, if I have a report with row headers on line 6, and data starting on line 7 with a ROWS replicator in that line, I manually update the range (using the Change Data Source option on the menu) to include the blank row 8.
to deal with the blank row - I have two options (because it will be in my pivot as the report expands).
Option 1 - for reports that are set to be run on a scheduler and hidden rows and columns deleted - I just add a HIDE to row 8, and no changes are needed.
Option 2 - for reports that will not be scheduled, row 8 needs to duplicate the info from Row 7 (without the ROWS command) - but do NOT include any SUM formulas - this eliminates the ugly (BLANK) in your pivot table results - but handles all of the sums correctly. -
Jet Reports Historic Posts Hey, that works, thank you!
When I include the row following by ROWS replicator and run the Jet report, my pivot table updates (yeah!), but it returns a "blank" row and a "blank" column in the pivot table. For example I have posting dates in columns and Item No. in rows. I get a Column labeled "blank", where should be a date, and a row labeled "blank" where there should be an Item No. I looked at my data and I do not have any rows of data that have a blank date or Item No.
Any ideas what I'm doing wrong?
Pat -
Jet Reports Historic Posts that's what I was talking about in this section of my response:
to deal with the blank row - I have two options (because it will be in my pivot as the report expands).
Option 1 - for reports that are set to be run on a scheduler and hidden rows and columns deleted - I just add a HIDE to row 8, and no changes are needed.
Option 2 - for reports that will not be scheduled, row 8 needs to duplicate the info from Row 7 (without the ROWS command) - but do NOT include any SUM formulas - this eliminates the ugly (BLANK) in your pivot table results - but handles all of the sums correctly.
Basically - the blank data is coming from that extra row you have included in the pivot table - and unfortunately if you're going to do this with a ROWS report instead of creating a Table, you're going to have to deal with the blank row as I mentioned above.
Example:
Row 7 is your ROWS command (and we'll make this simple for the example) - NL("ROWS","ITEM","NO."), and you have other columns in your report showing date and quantity
Row 8 is the row that is going to be blank - so to avoid this being truly blank (if you're going with Option 2 for my example above) - you need to replicate something from the original line of the report but NOT the quantity (you don't want that to double anything up from your earlier rows). I tend to do a NL("FIRST","ITEM","NO." (and replicate any other filters I'm using at the top)) - to get a row that will have something valid, but I don't include any of the SUM formulas or any quantity or dollar values.
Make sense? I'm heading out for the evening, but I will check in here in the morning. -
Jet Reports Historic Posts to deal with the blank row - I have two options (because it will be in my pivot as the report expands).
Option 1 - for reports that are set to be run on a scheduler and hidden rows and columns deleted - I just add a HIDE to row 8, and no changes are needed.
Option 2 - for reports that will not be scheduled, row 8 needs to duplicate the info from Row 7 (without the ROWS command) - but do NOT include any SUM formulas - this eliminates the ugly (BLANK) in your pivot table results - but handles all of the sums correctly.
There is a third option, a bit more hidden though….
Excel has the ability to base a pivot table on a named range.
And named ranges can be calculated.
Assume your NL(Rows) is in E7, and your reports expands to column N. All on Sheet1.
The titles of the columns are in E6..N6.
The basis of your pivot table is therefore E6:N7.
Knowing this you can go to the Formulas ribbon and choose Names and click New.
Use a recognizable name, e.g. JetPivot and as a reference use the following formula:=OFFSET(Sheet1!$E$6;0;0;COUNTA(Sheet1!$E:$E);10)Be sure there is nothing else in column E than the rows function and the title.
If you have something like Fit in E1, you need to extract that from COUNTA formula.=OFFSET(Sheet1!$E$6;0;0;COUNTA(Sheet1!$E:$E)-1;10)If you have the NL(Rows) column hidden, it is possible to start in the column next to it, by adding one to the offset starting point.=OFFSET(Sheet1!$E$6;0;1;COUNTA(Sheet1!$E:$E)-1;10)Click OK and the name will appear in the overview window. If you tab to the reference itself, Excel will show you the part of the sheet that this name is referring to.
Use JetPivot as a reference for your Pivot table and it will show without blanks
One drawback: if you decide to have more columns, you need to adjust the name accordingly (the '10' in the formula is the number of columns).
HTH
rmw
Please sign in to leave a comment.