4 Replies Latest reply: Mar 20, 2017 3:23 PM by Teresa Diamond RSS

    Hide dimensions from pivot table in Nprinting Excel report

    Thu Nguyen

      Hi.  I am working with an Excel template that already has set formatting.  I need to place the following report into the Excel template.

       

      Image.JPG

      The Excel template formatting cannot change.  In Nprinting, I unchecked keep source format.  However, I need to hide the highlighted header and dimension from the above pivot table.  I am able to hide the header but is there a way to hide the left two highlighted columns?  The dates and blank column to the right of the dates should not be pulled into the Excel template. This is causing the background of the highlighted cells to change from white to grey.

       

      Image2.JPG

       

      Thanks in advance.

        • Re: Hide dimensions from pivot table in Nprinting Excel report
          Nate Richardson

          Unfortunately, is it not possible to customize the output from a pivot table in NPrinting due to a limitation with QlikView and exporting data.

          If you are able to, you could convert the object to a straight table which then allows you to customize the columns being pulled in and add spaces as necessary.

           

          You can see the attached image which shows object CH237 (straight table) pulling in selected columns with header.

          Also notice that object CH299 on the left column does not have a + symbol next to it as it is a pivot table.

          950214.jpg

          • Re: Hide dimensions from pivot table in Nprinting Excel report
            Teresa Diamond

            Try entering a blank space as the "Submit MonthYr" label in your PivotTable dimensions.  This will hide the column when you export the PivotTable from QlikView to Excel.  As far as NPrinting, sometimes it will pull in the blank column and other times it will not.  I'm not sure as to the reasoning for either scenario. 

             

            Here's an example of a PivotTable where the Month dimension label is blank and does not show up in NPrinting.

            prior.png

            Here's the same table in a different Excel Nprinting report in which the blank column shows up.  Again, I'm not sure what is causing the two different behaviors, but you should at least give it a try.

            today.png

            • Re: Hide dimensions from pivot table in Nprinting Excel report
              Teresa Diamond

              Here are two more suggestions:

              1. Create a PivotTable in Excel.  You will need to create a new QV object as a straight table for this method.  here are two posts on how to achieve this.
              2. As long as the number of columns and rows are the same every time you run the report, you could:
                • Put the QV PivotTable into a hidden Excel tab and then reference only the cells you need.  Because the rows expand from your PivotTable, you would need to use a specific formula to reference the very first row, this is where OFFSET() comes in.  The other thing is that you may need to convert the PivotTable values from text to currency or to a number.  For currency, use Dollars() and for numbers use Value().
                  • Example:
                  • example1.pngexample2.pngexample3.png