All the Aggregate fileds in one row

[VincenzoCocciolo]

Hi All

I created a Report Datapage and added to the Result table three different Aggregate fields.

The calculation is correct but unfortunatelly they are positioned in three different rows. See the picture.

Is possible to have all of them in a single row without the labels?

or, in alternative, if I use a HTML block can you suggest the JS to use?

Regards

 

[CoopperBackpack]

Hello @VincenzoCocciolo,

I would like to update this thread since I wanted to achieve the same result as in your request. 

I need to create a Report like this:

So, I added 3 Totals&Aggregations fields. I cannot find the option to have only 1 Totals&Aggregations field, since I used custom Formulas (I need to display the sum of values for Sales, Costs, Profit without the Central Region). 

My formulas have the following syntax:

SELECT SUM(Sales) FROM Sales_By_Region WHERE Region_Name !='Central'

SELECT SUM(Costs) FROM Sales_By_Region WHERE Region_Name !='Central'

SELECT SUM(Profit) FROM Sales_By_Region WHERE Region_Name !='Central'

 

And I tried the following steps:

1) In the Header section I added the CSS code to hide the second and third Totals&Aggregations fields. Also, there is the CSS property to align the values:

<style>

tr[data-cb-aggregation="Aggregate2"], 
tr[data-cb-aggregation="Aggregate3"] {
display: none;
}

td.cbResultSetTotalsData{
text-align: right !important;
font-weight: bold !important;
}

</style>

2) In the Footer section I added this JS code:

<script>
document.addEventListener('DataPageReady', moveTotalsToOneRow)

function moveTotalsToOneRow() {

const totalsSecond = document.querySelector('tr[data-cb-aggregation="Aggregate2"] td:nth-child(2)').innerHTML; //select the value from the Aggregate2
const totalsThird= document.querySelector('tr[data-cb-aggregation="Aggregate3"] td:nth-child(2)').innerHTML; //select the value from the Aggregate3

document.querySelector('tr[data-cb-aggregation="Aggregate1"] td:nth-child(3)').innerHTML = totalsSecond; //assign the value of the Aggregate2 to the Aggregate1 row
document.querySelector('tr[data-cb-aggregation="Aggregate1"] td:nth-child(4)').innerHTML = totalsThird; //assign the value of the Aggregate3 to the Aggregate1 row
  
document.removeEventListener('DataPageReady', moveTotalsToOneRow);
}

</script>

Maybe you will need to change the element order number in selectors like td:nth-child(2), etc. 

 

3) Here is the result:

Also, I want to add that I need a Report without the Search option, so this solution works fine. However, there can be some nuances with the Search to display the correct result.

Hope this helps.  And maybe someone has more ideas. 

[CoopperBackpack]

Upon further investigation it turned out that is it possible not to specify the field name in the formula if the Aggregation function is the same for those fields.

So, I used this formula and it works correctly for each field:

However, if it is needed to use different Aggregation function (Sum, Avg, Count, etc.) and display the result in 1 row (1 Aggregation), then the solution that I described before can be helpful. 

[sandy159]

Hello @VincenzoCocciolo,

If you would like to perform the same aggregate function on multiple field columns, you can add multiple fields to one aggregation, and the results will automatically appear in one row but in the appropriate column. Otherwise, if you need different calculations for each column, you must use multiple aggregate fields and it will appear in different rows.

Please refer to the Totals and Aggregations article.

Also, you may look into the Pivot instead of the Tabular Report DataPage. I found the forum post with a similar issue that may help you:

Feel free to update this thread if you have further questions.

Regards

[MatthewK]

@CoopperBackpack thank you so much for your solution!  I'm not the original poster but this helped me tremendously.  Quick question: how do you get this to work for groupings in the table?  The TOTAL row works great with var % showing up using your solution.  But notice in the gray "group" rows underneath, the var % are not showing up (denoted by red x).

Thank you in advance!

 

[CoopperBackpack]

Hello @MatthewK, 

I am glad if the information I shared is helpful. 

As I understand, you use the Group-level aggregation option:

 

It works differently.  As far as I know, grouped values are considered separate subqueries. 

And I am afraid it is difficult to understand the reason for the issue in your case without the formula you use.

Could you provide me with a formula you use in the Totals&Aggregations? 

[MatthewK]

On 10/27/2021 at 11:43 AM, CoopperBackpack said:

Hello @MatthewK, 

I am glad if the information I shared is helpful. 

As I understand, you use the Group-level aggregation option:

 

It works differently.  As far as I know, grouped values are considered separate subqueries. 

And I am afraid it is difficult to understand the reason for the issue in your case without the formula you use.

Could you provide me with a formula you use in the Totals&Aggregations? 

Absolutely!

Aggregate 1 is very simple.  It's just a sum of several fields

Aggregate 2 is a bit trickier as I'm trying to get the % variance between This Year (TY) and Last Year (LY), 2 Years Ago (LLY), and Plan

The formulas are working as expected, I just can't get your trick to apply to anything but the Report Totals.  Thank you in advance!

[CoopperBackpack]

Hello @MatthewK,

It looks like the values in the Group-level aggregation are absent because in the Header we apply the styles to hide the elements with the attribute: data-cb-aggregation="Aggregate2" (for example).

And Totals and Group-level aggregations have this attribute. Therefore we hide all of them, but we moved values to Aggregate1 only for Totals, but not for the Group-level aggregation.

I hope this makes sense and this is the case. 

I tested another example with SubTotals. 

1) Let`s say there is a Report with 2 Totals&Aggregations fields (the first one is used to calculate the Qty Sum, the second one is used to calculate the Price Min)

 

2) I added this code to the Footer to move the values (of course, everyone needs to specify their field number in the td:nth-child)

<script>
document.addEventListener("DataPageReady", function (event) {

  let totalsSecond = document.querySelectorAll('tr[data-cb-aggregation="Aggregate2"] td:nth-child(2)'); //select all Totals and SubTotals of Aggregate2
  let totalsFirst = document.querySelectorAll('tr[data-cb-aggregation="Aggregate1"] td:nth-child(3)'); //select all Totals and SubTotals of Aggregate1

  totalsSecond.forEach((element, index) => {
    totalsFirst[index].innerHTML = element.innerHTML;
  }); //move all Totals and SubTotals of Aggregate2 to Aggregate1

});
</script>

 

3) After that we can hide all elements related to Aggregate2 and align values if needed.

This code is added to the Header:

<style>

tr[data-cb-aggregation="Aggregate2"]{
display: none;
}
tr[data-cb-aggregation="Aggregate1"] td:nth-child(3){
text-align: right !important;
}

</style>

Also, I deleted the Label for Aggregate1, and here is the result:

 

[MatthewK]

15 minutes ago, CoopperBackpack said:

Hello @MatthewK,

It looks like the values in the Group-level aggregation are absent because in the Header we apply the styles to hide the elements with the attribute: data-cb-aggregation="Aggregate2" (for example).

And Totals and Group-level aggregations have this attribute. Therefore we hide all of them, by moving values to Aggregate1 only for Totals, but not for the Group-level aggregation.

I hope this makes sense and this is the case. 

I tested another example with SubTotals. 

1) Let`s say there is a Report with 2 Totals&Aggregations fields (first is used to calculate the Qty Sum, second is used to calculate the Price Min)

 

2) I added this code to the Footer to move the values (of course, everyone needs to specify their field number in the td:nth-child)

<script>
document.addEventListener("DataPageReady", function (event) {

  let totalsSecond = document.querySelectorAll('tr[data-cb-aggregation="Aggregate2"] td:nth-child(2)'); //select all Totals and SubTotals of Aggregate2
  let totalsFirst = document.querySelectorAll('tr[data-cb-aggregation="Aggregate1"] td:nth-child(3)'); //select all Totals and SubTotals of Aggregate1

  totalsSecond.forEach((element, index) => {
    totalsFirst[index].innerHTML = element.innerHTML;
  }); //move all Totals and SubTotals of Aggregate2 to Aggregate1

});
</script>

 

3) After that we can hide all elements related to Aggregate2 and align values if needed.

This code is added to the Header:

<style>

tr[data-cb-aggregation="Aggregate2"]{
display: none;
}
tr[data-cb-aggregation="Aggregate1"] td:nth-child(3){
text-align: right !important;
}

</style>

Also, I deleted the Label for Aggregate1, and here is the result:

 

Thank you for all your help, but ARGH!  I feel so dumb.  I have 3 groups in my table and this solution only worked for the 1st subtotal group.  Groups 2 and 3 actually now have an undesirable result as some of the aggregates are now missing!  I'm sorry if I'm wasting your time but I have no idea how to proceed...

[futurist]

 Hi,

Jus to add, you may refer to this link on how to apply CSS (such as hiding) multiple sibling elements all at once: