How can I sum columns from several different tables?

[Eva Kröger]

Hi,

I need to sum columns from several different tables. I have used Table Excerpt and Table Excerpt include already to transfer two of the tables to the page on which I need the final table. Currently I have two tables that look like this but they are inside one 'Table excerpt include' macro if that makes sense.

But I need to sum these two tables so that I have one table with the sums of goal reached (here: 1 + 5), committed and sum of cases.

Could you help me please?

Thanks!

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Eva Kröger ,

As I understand your case correctly, you collect these two tables via the Table Excerpt Include macro, tick the "Show as a report table" option...

... and get one big table where you need to sum values and group your data by the first column.

In my example it will be a table where I have the departments 'DeptId' to be repeated several times with different values.

Here I have a manually created table wrapped in the Table Transformer macro, but you will need to place your Table Excerpt Include macro inside the Table Transformer macro.

Then you go to the Table Transformer macro and use the following SQL query:

SELECT 'DeptId',
SUM('ManagerId') AS 'ManagerId',
SUM('EmployeeId') AS 'EmployeeId',
SUM('Salary') AS 'Salary'
FROM T* GROUP BY 'DeptId'

And here goes your result report:

Hope this helps.

[Eva Kröger]

Hi, first everything was working well but now it doesn't create the table anymore. I get the message "No Table Excerpt macro with name All Teams found. Did you publish the page with the Table Excerpt macro?". I published the pages with the Table Excerpt macro but these tables were updated. Could that be a problem?

Thanks in advance.

[Katerina Kovriga {Stiltsoft}]

Hi @Eva Kröger ,

The error points that the Excerpt with such name can't be found on the corresponding pages. Please check if there are no any typos in the Excerpts' names: the names should be equal in the Table Excerpt Include macro and all the Table Excerpt macros that you are trying to collect. Then check the path leading to your Excerpts (labels, pages and child pages, etc.).

The fact that you update the tables inside your Excerpts shouldn't be the cause of the problem at all.

If the issue persists, please raise a support request here.

[Eva Kröger]

Everything is fine with the excerpts' names. If I look at the path leading to my excerpts, I'm not sure what to do. Before, I used "Pages with labels" because it seemed to work but I don't know how it should be correctly.

[Katerina Kovriga {Stiltsoft}]

Here I see that you choose "Pages with labels", but the field with Page labels is blank - your labels are missing. Seems that somebody deleted them. 

[Eva Kröger]

It was blank before aswell but somehow it worked.. So should I use a different option here? Maybe you have an article explaining the different options or how to label pages etc.? 

[Katerina Kovriga {Stiltsoft}]

It couldn't work before: by this configuration you tell the macro to find Excerpts with the name "All Teams" on the pages that contain labels ... and then don't specify the label itself.

So go the pages that contain your Excerpts, check what labels they have and enter their names in the field.

If you didn't use labels before, then you might use another option - page and childpages, page and descendents.

[Eva Kröger]

Thank you, I got it now, it's working. 

[Eva Kröger]

Thanks so much, that worked well!