Aggregate function in dimensional reporting

[Dineshpullagura]

Hello,

I am using cube as a data source for reporting. I am trying to use aggregate function to aggregate a measure on different levels. I am using the expression below

aggregate(measure within set (the level i want to aggregate on)) , but it is summing up all the rows instead of aggregating.

Looking forward for your help.

Thanks for your time.

[MFGF]

Hello,

I am using cube as a data source for reporting. I am trying to use aggregate function to aggregate a measure on different levels. I am using the expression below

aggregate(measure within set (the level i want to aggregate on)) , but it is summing up all the rows instead of aggregating.

Looking forward for your help.

Thanks for your time.

Hi,

Can you explain what you are trying to achieve? Are you building a list report? Crosstab? What do you mean by "aggregate a measure on different levels"? Can you give us an example?

MF.

[Dineshpullagura]

Hi MF,

I have a case database(which is in star schema and i have built a cube on top of it) which contains time taken to resolve a case, case creation date, case resolution date and the case owner(person resolved it).

I have resolution time as a measure and creation date and owner in different dimensions. My date dimension contains the following levels year,month,week,day,hour.

I want to build a chart report which prompts user for time range and case owner. Time range is relative like 1 day, 1 week, 1month, 3 months,1 year,2 years..

Suppose if user selects a case owner and  time period as one year, i want my chart to display average resolution time for each and every month in the last year for all the case owners and the average resolution time of the case owner selected by user. I have attached the chart image for better understanding.

And i want my X-axis to change dynamically with the time range selected by the user, so if user selects one week, i want to aggregate the resolution times on day level and average it and show it on the chart what are the resolution times averages for last 7 days for all the investigators and for the current investigator. 


Please reply if anything is not clear.

Thanks for your time.

[MFGF]

Hi MF,

I have a case database(which is in star schema and i have built a cube on top of it) which contains time taken to resolve a case, case creation date, case resolution date and the case owner(person resolved it).

I have resolution time as a measure and creation date and owner in different dimensions. My date dimension contains the following levels year,month,week,day,hour.

I want to build a chart report which prompts user for time range and case owner. Time range is relative like 1 day, 1 week, 1month, 3 months,1 year,2 years..

Suppose if user selects a case owner and  time period as one year, i want my chart to display average resolution time for each and every month in the last year for all the case owners and the average resolution time of the case owner selected by user. I have attached the chart image for better understanding.

And i want my X-axis to change dynamically with the time range selected by the user, so if user selects one week, i want to aggregate the resolution times on day level and average it and show it on the chart what are the resolution times averages for last 7 days for all the investigators and for the current investigator. 


Please reply if anything is not clear.

Thanks for your time.

Hi,

Ok - so the Series will comprise two members from your cube - the "All Case Owners" member, and a prompted-for member, derived by a query calculation [your case owner level] -> ?Case owner?
The measure will be the measure you want to display. It will automatically get aggregated when used in the chart, based on its aggregation type defined in the cube.
The categories will be another dimensional expression based on time. Probably children([Your time hierarchy] -> ?Time range?)

That should be all you need.

MF.

[Dineshpullagura]

Hi MF,

Thanks for your help. That worked.

But, i am trying to show the average of the resolution time. Is there a way to get the average resolution time for the relative time user selected.

Thanks.

[MFGF]

Hi MF,

Thanks for your help. That worked.

But, i am trying to show the average of the resolution time. Is there a way to get the average resolution time for the relative time user selected.

Thanks.

What you are seeing is a facet of the way the measure is resigned to roll up in the cube - the cube is returning aggregated values to your report (which I assume are totals), it's not the report that is aggregating the values.

If you build a very simple crosstab you can see this. I created one with years in columns, Product Line in rows, and Quantity in cells. The MDX generated to retrieve the data from the cube was this:

SELECT [Years]..[Year 2].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), [Products]..[Product line 2].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1), {[MEASURES]..[Quantity sold]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(2) FROM [The Sample Outdoors Co.]

Note there are no summary or aggregate functions in the expression. The cube knows how to roll up the measure based on the rules defined when the cube was built. With a relational source, the aggregation is passed to the database within the SQL expression, and there are properties for the measure within the report that allow you to change this, but as you can see with a cube, no such facility exists.

You're going to need to get another version of the measure added to the cube with a rollup type of Average to achieve what you desire.

Cheers!

MF.

[Robl]

You could possible do a count of transactions and then do an average by dividing the two.
It all depends on the granularity of the data and the level of the average required.