topic Re: Extract total cells from all Sheet Level in Qlik NPrinting

Extract total cells from all Sheet Level

pacoarandiga — Wed, 01 Apr 2020 16:00:48 GMT

Hi everyone!

I'm trying to solve a problem in Qlik Nprinting that I don't know if it has a solution.

In my problem, I have a report divided by a page level. In each one, all the possible cases will appear in the report, and there will be a cell prepared with the formula sum () that will make the sum of all those lines created in each sheet.

Well, what I need to do is to take those totals from each sheet and take them to a new normal sheet (without any page level created).

When I put the link to that cell on my normal sheet, it only shows the total from the first sheet. But of course, I need all the totals of all the sheets and I don't know if this can be achieved.

For example, if I put it to be done by the page level, the number of totals created is correct, but the references are not correct.

Does anyone know if this can be done and how?

Thanks!

Re: Extract total cells from all Sheet Level

Lech_Miszkiewicz — Thu, 02 Apr 2020 04:21:59 GMT

Hi,

Obviously NPrinting development is actually combo of Qlik Sense and NPrinting development and the approach I would take for the purpose of this exercise would be to create an object directly in Qlik Sense application which would return the required values.

but if you insist...you could use a Excel sheet with macro...

create template xlsm
load that template
create EVAL function in Module

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Convert text strings to formulas

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

use PAGE tag to create references by concatenating required cells and formulas (in my case I used vlookup)

=CONCATENATE("=VLOOKUP(",CHAR(34),"Tender Total TTW",CHAR(34),",",B2,"!A:B",",","2",",","0)")

use custom EVAL () function to evaluate it post report generation

see short video for demonstration (attached).

Depending on report requirements you may find this overcomplicated and could stick to simpler solutions. Definitely reference to the sheet will not flow with the <Page> tag the way you tried to use it, so that is no-no!!!

Re: Extract total cells from all Sheet Level

pacoarandiga — Fri, 03 Apr 2020 09:35:24 GMT

Hello Lech.

Thanks for your answer, I think it is a very good solution. I'm testing it, but I can't get it to work. Adapting the "concatenate" and the "vlookup" function to my example, Nprinting creates the Excel and I get the string correctly. Also, if I put it manually (the string) it gives me the result correctly. The problem I have is that when I create the function with VBA code, I'm doing something wrong because it doesn't return me anything.

I'm creating the module as you indicate. Do I have to save it in any special way?

The result, when I put the Eval () function, is that it doesn't return anything to me when transforming the string. It also puts it between { }, which isn't the same result as yours. And this is not from Nprinting alone, I create it in any normal Excel and the function created still doesn't work for me.

But if I put the text manually, it returns the value correctly.

I also have some doubts with this:

The totals that we extract from the different pages, will they be updated on our summary page if they are modified on their corresponding page?
Although the creation of the Eval () function does not work for me, does the module stay saved forever? Because if I close the report and reopen it, the module that I had saved has disappeared. If every time I want to make the report I have to create the function it's not good for the solution (I suppose I'm doing something wrong too)

Thanks for everything again.

Greetings.

Re: Extract total cells from all Sheet Level

Lech_Miszkiewicz — Fri, 03 Apr 2020 09:43:41 GMT

Hi,

So in my previous comment i said that you need to prepare xlsm template.

It means that you need to create xlsm excel file first outside of the nprinting. On that ocasion you can create module in it and save it like that.

before creating report you have an option to choose a template file - this is where you point to your xlsm file (excel with macro)

so the steps are:

1. go to excel and create xlsm file with module, save it as xlsm file

2. create nprinting report and use a previously created file as template.

3. remaining steps are the same.....

Remeber to preview it as XLSM file and in publish task also use XLSM format to allow for macro run.

Re: Extract total cells from all Sheet Level

Lech_Miszkiewicz — Fri, 03 Apr 2020 09:46:54 GMT

your other questions:

I also have some doubts with this:

The totals that we extract from the different pages, will they be updated on our summary page if they are modified on their corresponding page?
- I believe they will update as EVAL is just like any other formula.
Although the creation of the Eval () function does not work for me, does the module stay saved forever? Because if I close the report and reopen it, the module that I had saved has disappeared. If every time I want to make the report I have to create the function it's not good for the solution (I suppose I'm doing something wrong too)
- It is important to use xlsm template to create whole report in NPrinting. Module will stay in it. You saw on the short video i did that it stayed, but the output was .xlsm

Re: Extract total cells from all Sheet Level

pacoarandiga — Mon, 06 Apr 2020 15:09:15 GMT

Hi Lech,

Correcting the wrong steps I had made, I have advanced, but it still doesn't work.

To make it easier, I have created an Excel (xlsm) outside of Nprintng where I'm testing the formulas (once they work pass it to Nprinting). I have the same module that you created, but it keeps giving me an error.

and doing the string like a normal function works ..

I have thought that the created formulas do not read them to me, but creating a simpler one it works correctly.

Therefore, it's a problem that the function to go from string to formula does not work for me. And I'm going a little bit crazy trying everything I see on Google, but nothing works.

Any help is still welcome.

Thanks.

Re: Extract total cells from all Sheet Level

Ruggero_Piccoli — Tue, 07 Apr 2020 09:04:56 GMT

Hi,

Qlik NPrinting adds the rows it needs during the report creation so original rows are moved down and you lost the references.

Instead of working only on VBA did you evaluated the possibilities to:

- develop a Qlik Sense object with the totals you need and import it into the template?

- use the template Variables or Formulas to calculate the totals and insert them into the template?

Note that VBA code is never executed during reports generation so you need to associate it with an event, for example on opening the .xlsm file, or ask to the recipients to execute it.

Best Regards,

Ruggero

Re: Extract total cells from all Sheet Level

pacoarandiga — Thu, 09 Apr 2020 13:45:24 GMT

Hi!

Thank you both for your responses. What you propose Ruggero, I'm aware of these possibilities, but I need them to be linked to the excel totals, because if they are modified in the sheets, they also be updated in the Summary sheet.

I've finally got it to work. It was a problem of how Excel and VBA collects the code (?). The Lech example has concatenate () function working with ','. In my case, I had to put ";" for the function to work, but that the string on VBA part didn't work.

At the end the string that has worked for me has been the following:

=CONCATENATE("VLOOKUP(";CHAR(34);"DN Total";CHAR(34);",";"'";B3;"'";"!D:H";",";"5";",";"0)")

And with the same code in VBA it works.

Thanks very much both for your help!

Greetings.