Jump to content
  • 0

All the Aggregate fileds in one row


VincenzoCocciolo
 Share

Question

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.

image.thumb.png.5f4a4c5dca61d00bcffce0da044e44e0.png

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

 

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 1

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:

3GhZ5d5.png

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'

hx3FrJ5.png

 

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:

UKp1YM2.png

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. 

Link to comment
Share on other sites

  • 1

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

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

mR2FVQM.png

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. 

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

  • 0

@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!

 

image.thumb.png.986d721740bd5fb8c43ef45019d8858a.png

Link to comment
Share on other sites

  • 0

Hello @MatthewK

I am glad if the information I shared is helpful. 

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

WOYhm5V.png

 

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? 

Link to comment
Share on other sites

  • 0
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:

WOYhm5V.png

 

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

image.png.6fddadce3ebaf7428e113ef4cb1a56bc.png

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!

image.png.2495c53d4097e4f613e0b9d85d0bd33a.png

Link to comment
Share on other sites

  • 0

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)

eLMv1aZ.png

 

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:

rkPfgFi.png 

Link to comment
Share on other sites

  • 0
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)

CREcys0.png

 

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:

erU46qQ.png 

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...

image.thumb.png.87f7c24cffef06db6cf4f99d479f9bfd.png

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...