Report Builder, Aggregation Scope and Running Values

Dear Anyone,

I need to create a measure/column that is percent of column (or row, not sure). In Reporting services, we were able to do this because aggregations there had scope.

=Sum(Fields!Open_Demand.Value)/Sum(Fields!Open_Demand.Value,"GSD")

I can't seem to do this in RB because the aggregation there does not seem to have a scope. Nor does it have the function running value.

Does anyone have an idea on how to do this on Report Builder?
Thanks,
Joseph

Report Builder does not expose the scope parameter for aggregation. You can often get the same results, however, by including a separate field that retrieves the total for the set you want.

For example, you can create a (hypothetical) report like this:

Category | Product Name | Total Sales | % of Category

...if Category is an entity in your report model. If it is a lookup entity, you will need to turn on View->Advanced Mode in RB to see it.

Here's how:

Select Product Entity
Add Product Name field to report
Add Category field to left of Product Name
Add Sales->Total Sales to report TWICE
Right-click on the value cell of the second Total Sales field, choose Edit Formula
Type "/" after the Total Sales field in the formula textbox
Add Product->Category->Products->Sales->Total Sales to the formula
- this is the total sales for all products in the same category as this product
Click OK
Change the format on the second field from $ to %
Run the report

This will be notably slower than using RDL scoping, but it should work.

PS: Due to a known issue in RTM, the subtotals you display for the % field above will be incorrect. This will be fixed in SP1. Meanwhile, if you choose "Server Totals" in the Report->Report Properties dialog, you will get the correct subtotals as well.

|||

Hi Bob,

We've tried what you suggested but we seem to be getting a 1 result. We've enabled server totals and the answer still is the same. We are currently using the RTM build.

Thanks,

Joseph

Report Builder, Aggregation Scope and Running Values

Dear Anyone,

I need to create a measure/column that is percent of column (or row, not sure). In Reporting services, we were able to do this because aggregations there had scope.

=Sum(Fields!Open_Demand.Value)/Sum(Fields!Open_Demand.Value,"GSD")

I can't seem to do this in RB because the aggregation there does not seem to have a scope. Nor does it have the function running value.

Does anyone have an idea on how to do this on Report Builder?
Thanks,
Joseph

Report Builder does not expose the scope parameter for aggregation. You can often get the same results, however, by including a separate field that retrieves the total for the set you want.

For example, you can create a (hypothetical) report like this:

Category | Product Name | Total Sales | % of Category

...if Category is an entity in your report model. If it is a lookup entity, you will need to turn on View->Advanced Mode in RB to see it.

Here's how:

Select Product Entity
Add Product Name field to report
Add Category field to left of Product Name
Add Sales->Total Sales to report TWICE
Right-click on the value cell of the second Total Sales field, choose Edit Formula
Type "/" after the Total Sales field in the formula textbox
Add Product->Category->Products->Sales->Total Sales to the formula
- this is the total sales for all products in the same category as this product
Click OK
Change the format on the second field from $ to %
Run the report

This will be notably slower than using RDL scoping, but it should work.

PS: Due to a known issue in RTM, the subtotals you display for the % field above will be incorrect. This will be fixed in SP1. Meanwhile, if you choose "Server Totals" in the Report->Report Properties dialog, you will get the correct subtotals as well.

|||

Hi Bob,

We've tried what you suggested but we seem to be getting a 1 result. We've enabled server totals and the answer still is the same. We are currently using the RTM build.

Thanks,

Joseph

Report Builder - Filter on an aggreated measure possible ?

Is it possible to use filter on an aggregated measure without bringing the server to its knees ?

I've got a 2005 UDM with a single fact table:

-accidentID

-accidentTypeID

-statusID

-countryID

-areaID

-operationID

-consequenceID

-lightConditionsID

...(18 more FKs to simple dimensions)

-Exits (always = 1 to indicate existens)

In addition, I've added a calulation to the cube

-NumberOfAccidents = DistinctCount of all records with -accidentTypeID = A

On top of this, I've generated a Report Model.

Building reports using Report Builder is a breeze, but if I set a filter on the NumberOfAccidents (the aggregated measure); Analysis Services goes off and never returns.

For instance, a matrix report with Country on one axis and Operation on the other and NumberOfAccidents as the only member will run as expected and quite fast.

However, if I set a filter on NumberOfAccidents to > 10, I'm lost. This query will basically run forever and completely halt Analysis Services.

The fact table contains approximately 270000 records.

Any insight ?

Trond

Sorry for the delay in replying.

Firstly, I suspect that the filter you are actually applying is not the one you expect. It will be filtering all of the rows in the fact table according to the criteria, before grouping by Country & Operation. Given that I don’t think NumberOfAccidents can be >1 for a fact row, I imagine that you intended to filter the results after grouping (which I don’t believe is possible in Report Builder, in a matrix).

Without knowing the details of the calculation, I can't say for sure why performance is so poor. I believe that it would be because the calculation is being evaluated for all combinations of the dimensions related to the measure group. There are two ways to make this much more efficient:

1. Make sure that NON_EMPTY_BEHAVIOR is defined for the calculation. This is a property of a calculated measure (say MC), and states which other measure (say M) where if M is Null, then MC is certainly Null. So if there is some simple Count measure on this measure group, then setting NON_EMPTY_BEHAVIOR to that Count measure, on NumberOfAccidents, should greatly improve

or

2.If this measure is simply a count of records where Accident type = A, then I would suggest simply having a new (non-calculated) measure based on that condition. So in the DSV, add a calculated column that is 1 if Type = A and 0 otherwise. Then have a regular measure built off that column

Report Builder - Filter on an aggreated measure possible ?

Is it possible to use filter on an aggregated measure without bringing the server to its knees ?

I've got a 2005 UDM with a single fact table:

-accidentID

-accidentTypeID

-statusID

-countryID

-areaID

-operationID

-consequenceID

-lightConditionsID

...(18 more FKs to simple dimensions)

-Exits (always = 1 to indicate existens)

In addition, I've added a calulation to the cube

-NumberOfAccidents = DistinctCount of all records with -accidentTypeID = A

On top of this, I've generated a Report Model.

Building reports using Report Builder is a breeze, but if I set a filter on the NumberOfAccidents (the aggregated measure); Analysis Services goes off and never returns.

For instance, a matrix report with Country on one axis and Operation on the other and NumberOfAccidents as the only member will run as expected and quite fast.

However, if I set a filter on NumberOfAccidents to > 10, I'm lost. This query will basically run forever and completely halt Analysis Services.

The fact table contains approximately 270000 records.

Any insight ?

Trond

Sorry for the delay in replying.

Firstly, I suspect that the filter you are actually applying is not the one you expect. It will be filtering all of the rows in the fact table according to the criteria, before grouping by Country & Operation. Given that I don’t think NumberOfAccidents can be >1 for a fact row, I imagine that you intended to filter the results after grouping (which I don’t believe is possible in Report Builder, in a matrix).

Without knowing the details of the calculation, I can't say for sure why performance is so poor. I believe that it would be because the calculation is being evaluated for all combinations of the dimensions related to the measure group. There are two ways to make this much more efficient:

1. Make sure that NON_EMPTY_BEHAVIOR is defined for the calculation. This is a property of a calculated measure (say MC), and states which other measure (say M) where if M is Null, then MC is certainly Null. So if there is some simple Count measure on this measure group, then setting NON_EMPTY_BEHAVIOR to that Count measure, on NumberOfAccidents, should greatly improve

or

2.If this measure is simply a count of records where Accident type = A, then I would suggest simply having a new (non-calculated) measure based on that condition. So in the DSV, add a calculated column that is 1 if Type = A and 0 otherwise. Then have a regular measure built off that column