How to add aggregation on multiple objects in a single webi table?

[Former Member]

Hi experts,

I recently face an requirement that show a report table in a specific format.

Let me show you in a simple example, let's say we have three dimensions and one measure. Product, Region, Quarter and Sales. When put these objects in an vertical table, it display data in this way.

Product	Region	Quarter	Sales
TV	North	Q1	37
TV	North	Q2	6
TV	North	Q3	4
TV	North	Q4	89
TV	East	Q1	81
TV	East	Q2	5
TV	East	Q3	32
TV	East	Q4	99
Mobile	North	Q1	13
Mobile	North	Q2	50
Mobile	North	Q3	95
Mobile	North	Q4	65
Mobile	East	Q1	58
Mobile	East	Q2	80
Mobile	East	Q3	76
Mobile	East	Q4	97

However, we expect to append some aggregation value in this table as this.

Product	Region	Quarter	Sales
TV	North	Q1	37
TV	North	Q2	6
TV	North	Q3	4
TV	North	Q4	89
TV	East	Q1	81
TV	East	Q2	5
TV	East	Q3	32
TV	East	Q4	99
Mobile	North	Q1	13
Mobile	North	Q2	50
Mobile	North	Q3	95
Mobile	North	Q4	65
Mobile	East	Q1	58
Mobile	East	Q2	80
Mobile	East	Q3	76
Mobile	East	Q4	97
TV	All region	Q1
TV	All region	Q2
TV	All region	Q3
TV	All region	Q4
Mobile	All region	Q1
Mobile	All region	Q2
Mobile	All region	Q3
Mobile	All region	Q4
All product	North	Q1
All product	North	Q2
All product	North	Q3
All product	North	Q4
All product	East	Q1
All product	East	Q2
All product	East	Q3
All product	East	Q4
All product	All region	Q1
All product	All region	Q2
All product	All region	Q3
All product	All region	Q4

As you see, we add "All region" and "All product" here, and combine them with real product, region and quarter value to show aggregations.

Can anyone tell me how to get such table in webi? Can we implement it simply in report level?

Best regards and thank you,

Ocean

[sateesh_kumar1 · Accepted solution]

Hi Ocean,

To get your requirement exactly,we need to add too many  footer cells with predefined formulas (As of now you have TV and Mobile only as products , guess in future you can have so many) in the table.

It is better to use Outline functionality(fold/unfold) with breaks applied on Region and Quarter.

Remove all break headers.

use=sum([Sales]) in footer cells.

You'll get below format in the table.

use can expand/collapse when needed.

Or

Have a table with all data (first part)

have a tables with summarized data .One for Product/Quarter specific , one for region/Quarter specific ... and arrange them as they look like single table.

[Former Member · Accepted solution]

Thanks Sathish!

Can report footer solve the problem? Can you show me an example?

I am wondering can the problem be solved in a single table.

[Former Member · Accepted solution]

Yes, keep adding rows to the table footer for all the rows that you want. Unfortunately you're heading down the hard-coding route in terms of row labels, but it's certainly achievable in a single table.

[sateesh_kumar1 · Accepted solution]

To get the format in a table ,you need to add 20 footer cells and need to write 20 formulas with where conditions.

Ex: =Sum([Sales] Where ([Product]="TV" And [Quarter]="Q1"))

TV ,All Region,Q1 is hard coded.

[former_member210032]

Hi Yang,

This link will help you

http://www.perpendulum.com/2011/12/hierarchy-in-one-column/

Regards

Mustafa

[Former Member]

Actually, this question is more focus on the report structure. I know the calculation context. In my first thought, I create the report similar with above one. The problem is in that case, there are a lot of duplicate rows. If you don't know what I am talking about, you can just take a little try in a real webi report sample.

The key of the question is about how to put the aggregated "All product", "All region" rows in the right place with right frequency and in a single table. As I said, it is more about the format/structure problem.

Any thought?

[Former Member]

If you need this in a single table, you have to go with the report footer and add multiple rows.

[Former Member]

One way is to get the "All product" and "All region" in the LOVs for product and region.

Or create another table and align this table by setting the relative position just below the first table.

You can use =[Sales] ForAll([region]) for showing Sales for "All region" for a product and Quarter similarly,

= [Sales] ForAll([product]) for displaying "All products" sales data.

Let me know if this helps.

[Former Member]

Thanks Charuta!

So do you mean there is no way to create a single table in that format?

[Former Member]

If it suits, go for the second table and set the relative position, it will avoid creating multiple static rows in the footer.

[Former Member]

You need to use calculation contexts, such as ForEach, ForAll, In and Where.

It's probably easier to go through the user guide to pull out specific examples that would suit your needs.