5 Replies Latest reply: May 15, 2017 9:45 PM by Lech Miszkiewicz RSS

    Nprinting Filters not working on Excel Documents

    Joe Crane

      First of all, this was the only possible way I was able to create an Excel report, if there was ANY other way I would have done it, as Nprinting has been nothing but a pain in my side since using it. But I digress...

       

      I am using nprinting 17.0.2.0

       

      I am attempting to create a report in Excel. I have two issues:

       

      1. My filters are not working in excel. I have tried to add a "YearMonth" filter via the "Report" tab as well as the "Task" tab and the Actually template itself in the nprinting console:

       

      np1.png

      np2.png

      np3.png

       


      Yet the data still is not being filtered... Why?

       

      2. I have specified for nprinting to keep its source formats, yet when my data comes out, my percentages are all wiped, even though they are coming directly as percentages from qlikview:

       

      np4.png

       

      nprinting (the data is different because the filter isn't working):

       

      np5.png

       

      Why aren't my numbers coming in as percentages?

       

       

      Any help would be greatly appreciated.

       

      Thank you,

       

      Joe

        • Re: Nprinting Filters not working on Excel Documents
          Stephen Jasionowski

          What is the syntax being used for the YearMonth filter?

           

          Also, you only need the filter applied in one of the three places that you applied it.

           

          It looks like CH14 is a pivot table in QlikView. If that is the case, the reason that you are losing the formatting is b/c NPrinting does not support pivot tables.

           

          You need to use a QV straight table as a source and then create the pivot in the Excel template. That process is outlined here: Creating pivot table using Excel table columns ‒ Qlik NPrinting

          • Re: Nprinting Filters not working on Excel Documents
            suchit dhulgundi

            Hi Joe,

             

            Did you find any solution to this? I'm facing a similar issue.

             

            Thanks,

            Suchit

            • Re: Nprinting Filters not working on Excel Documents
              Lech Miszkiewicz

              Hi,

              Regarding your filters: Be aware of "datatypes" recognised by NPrinting in filters. Because of that you need to consider following scenarios:

              • applying filter on text field (for example Country) - then the filter will be a text string (option Value is)
              • applying filter on numerical fields (for example Day number) - then filter has to be Numerical
              • applying filters on dual values(for example Date, or Month or Psiibly MonthYear) -then filter in NPrinting needs to be numerical. (so for January/2017 it would be actuly "Numeric value is" 42736

              I belive in your case Month/Year is simply converted from Date using MonthStart function in Qlik and is still kept as a number and only represented as formated text. That is why you would probably have to apply numerical filter with corresponding numerical value! The easiest way of checking it is to put MonthYear field in listbox and changing it number formatting to number or intiger.

              2.png

               

              regarding your second question - keeping source formatting:

              how did you achieve this formatting. Is it coming from your expression - i think it is.

               

              So if you just format whole column using "NUMBER" tab in qlikview, then NPrinting will inherit this formatting when using an option "keep source formatting" and it will transfer percentages properly. As your example is more advanced (mixture of % and numbers in one column) this becomes more advanced and complicated.

               

              By default num() function will still be converted to general number - as you still have default column fromatting set to "Expression default" in NUMBER tab in QlikView.

              The only option i can think of is converting your numbers to text by applying TEXT in front of your num() function in expression. text(num()). 

              for example:

              text(num(sum(Expression1)/ sum(Total Expression1),'#,##0%'))

               

              I know it will convert all your percentages to text and allign them to left (because it will be recognised as text and marked with this little green triangle in cell corner) - but this is all you can do in this case. I am not aware of any other solution at the moment.

               

              2.png

               

              I hope it will get you closer to what you expect to build.

               

              I know it is painful to work with NPrinting  - "i have been there, I have done this", but after gaining some experience now i am actually appreciating it as well. I am aware of its limitations and i am looking forwar to new features, but for now it is what it is.

               

              regards

              Lech

               

              ---------------------------------------------

              When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

                • Re: Nprinting Filters not working on Excel Documents
                  Lech Miszkiewicz

                  ps:

                  Joe, would you be able to share .qvw with your table? If not, could do screenshot of your dimension and expression tab + provide expressions for measures.

                   

                  I actually think you may get away with it by using pivot table and switching measures with dimensions and applying formatting on Number tab rather then on expression.

                   

                  Then you would have working as expected report.

                   

                  cheers

                  Lech

                  ---------------------------------------------

                  When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

                    • Re: Nprinting Filters not working on Excel Documents
                      Lech Miszkiewicz

                      Last comment on this:

                       

                      I suggest an upgrade to NPrinting 17.3.1

                      here is the path you need to take in order to succesfully upgrade your version

                      img.png

                       

                      cheers

                      Lech

                      ---------------------------------------------

                      When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.