When creating reports, it’s usually preferred that rows with irrelevant data are excluded—as this results in a tidier, more readable report. When it comes to PivotTable reports, this can include rows with blank items. Below is an example of a report with them shown.
To get rid of them, we have an add-in specifically for this, the ExcludePivotBlanks add-in. In this tip, I’m going to show you how to use it.
1. In the Report Manager, select the report you want to remove the blanks items from.
2. Show its advanced options by selecting the Show Advanced checkbox at the bottom of the Properties tab.
3. And then click the ellipses to the right of the Run Add-Ins field. This will open the Choose Add-In Function dialogue.
4. Choose Alchemex Add-In1 from the Select Add-In Library drop down.
5. Then General Excel extensions Module from the Select Add-In Library Module drop down.
6. And select the ExcludePivotBlanks add-in and click OK. The add-in will open.
7. In the PivotTable to apply the field, enter the name of the PivotTable you want to remove blanks from. You can find this by running out the report normally, selecting the PivotTable and then looking at the PivotTable Name field under the Analyze tab.
8. Then under the list of PivotFields to exclude blanks from, enter the name of the PivotTable field you want to check for blank items. You can check more than one field by entering their names separated by a colon (:) without any spaces on either side. Otherwise, you can use an asterisk (*) to check all fields.
9. Once finished, click Apply and then OK. The add-in is now set up and you can see the syntax for it included in the Run Add-Ins field of your report.
10. It will be applied the next time the report is run. You can see the result of mine based on the parameters I used as follows.
The post Conveniently remove blank item rows in your PivotTable reports appeared first on Sage Intelligence.
Powered by WPeMatico