Aggregation in Dashboards

[Former Member]

Hello,

I get back from my query (universe) the objects: customer, year, amount. So for each customer I get multiple rows for each year one with an amount.

I am used to WebI, where the values get aggregated automatically. But in Dashboards I get several lines for each customer in my excel sheet, which is a problem when i want to display the result in a graph.

I need the year, because I would like to use it as a filter for what-if analysis (slider component) in my dashboard.

How can i do that? Is an excel pivot table a soluition? I tried it but didn't give me any data

thanks!

[Former Member · Accepted solution]

Hi Hans

Dashboards do not support pivot tables.

The best way to handle aggregation is to have the aggregation performed on the back-end and pass only aggregated records to the Dashboard.  Since you are familiar with WebI, you can create a WebI block that returns the aggregated data and then publish that block as a Web Service.  Then you can use the Query as a Web Service connection in the Dashboard to connect the dashboard to the Web Service.  The QaaWS connection allows you to pass parameters such as year, customer, etc..

If you don’t perform the aggregation on the back-end then you are destined to design a series of SUMIF formulas and “move” combo-boxes (Arijit’s suggestion) in order to build a table for your chart.  This can get complicated, cumbersome. And impact your dashboard’s performance.

So . . . you have a design decision to make . . . . aggregate in back-end, or aggregate in spreadsheet.

[Former Member · Accepted solution]

hi mike,

thanks for your ideas!

I will start to experiment with the published webi block.

is it really possible to use year as a parameter for my webservice, but don't return it as a result? that would be fine. when i change the parameter value for year in xcelsius, gets the data refreshed from the database or just from the webi block?

thanks!

[Former Member · Accepted solution]

That depends . . .

I generally schedule an overnight job to save an instance of the WebI.  I point my Dashboard QaaWS connection to the last instance.  In this case the data would come from the Webi block.

However, I have created connections where I set the Webi to “refresh on open” in order to get the data from the dB.

Also, you may consider a hybrid solution where you have the WebI return an aggregated table of year, customer, amount  . . . then perform the year filter in Xcel.  This will reduce the number of calls to the Web Service and improve performance.

[Former Member · Accepted solution]

to filter on year in xcelsius would only work if i have a equal filter (year=2013). but as soon as i filter on a year range (frmo 2010 to 2013), i would get the unaggregated lines for my customers again.

but when the data doesn't get refreshed from the database if i filter via the web service parameter, the performance should be fine i think.

thanks, you help me a lot!

[arijit_das · Accepted solution]

BIWS can be a solution here. You need to build a vertical table in webi having one single column for the measure without any dimension. In webi query panel, bring year and country. The table then show aggregated data for all countries and years. Then publish the block as a web service. Select country and year as filter. Now, use a qaaws connection in dashboard to connect to the getreportblock method of the web service. In the input section of qaaws connection, you can bind the values and operator for the country and year filters. If you want more than one year selection, use InList operator in filter.

[Former Member · Accepted solution]

hi arijit,

thanks, but how is it working with no dimensions in the block? the block would consist then only of one cell - the aggregated measure value. the qaaws wouldn't return my companies anymore I guess?

BTW: is there a full list of operators i can use to filter my published webi block? Can I also use a between filter? thanks

[arijit_das · Accepted solution]

if you need company wise data then keep company dimension in the block. But do not keep year dimension as you are setting a filter for year.

supported operators:

EQUAL (default)

GREATER

GREATER_OR_EQUAL

LESS

LESS_OR_EQUAL

NOT_EQUAL

INLIST

NOT_INLIST

[Former Member · Accepted solution]

I don't understand why you need a YEAR filter. 

Your problem was trying to aggregate the data.  Just bring ALL the relevant aggregated data into the spreadsheet.  Then add a "filtered rows" combo box to move the selected rows to another area in your spreadsheet -- which you use for your graph.

This would require only one call to the Web Service. . . . much simpler and faster.

[Former Member · Accepted solution]

The user must have the possibility to filter by year ranges (from year a to year b) in the dashboard.

I think that's only possible by using SUMIFs?

or by filtering the webservice via parameters. or is there a better way?

[Former Member · Accepted solution]

hmmmm . . . there are a number of ways to do this.

Personally, I like to minimize my calls to the data source.  So I would pull in all the data I need and then set up some SUMIF formulas.  However, this assumes I have just a few records in my dataset (less than 100).

If the dataset is large and I need to pass a range of YEARS to my datasource I would try one of two ideas:
1.  Use the INLIST operator and pass “2011,2012,2013”
2.  Create two variables in my WebI:  YEAR_from and YEAR_to

          In my WebI block I would need to configure the filters to only display data where [YEAR>=YEAR_from] AND [YEAR <=YEAR_to]

I’m sure there are other ways to satisfy this requirement . . . but hopefully this will give you something to go on.

P.S.  I think it's time to close this thread . . . your original post seems to have been answered.  New topics should be asked in new threads

[former_member190855 · Accepted solution]

Hi

You could add a filter to your Query browser query for From and To Years as prompts. Then pass the user selections to the query. If you dont need the year to be returned, then don't add it to the query result objects list. As user selects a year range it will refresh the query for that parameter.

Thanks

Runali

[arijit_das]

Use a hidden combobox for filtering the data for selected year using filtered rows insertion type. Then you can build a amount by customet chart with the filtered data.

[Former Member]

i also want to filter for date ranges (2010-2013), the measures should get summed up then.