"Aggregate" of a calculated measure

Hi all,

I've got a calculated measure called [Measures].[Coverage]. I need a "report" with the following columns:

MAT = Moving Annual Total

Account Group, Coverage MAT, coverage for last MAT, and Variance.

The query should look something like this:

WITH

MEMBER

[Measures].[Coverage MAT]

AS

'AGGREGATE(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2006]), [Measures].[Coverage])',

FORMAT_STRING = 'Percent'

MEMBER

[Measures].[Coverage LMAT]

AS

'AGGREGATE(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2005]), [Measures].[Coverage])', //Note the 2005 year instead of 2006

FORMAT_STRING = 'Percent'

MEMBER

[Measures].[Variance]

AS

'IIF([Measures].[Coverage LMAT]=0,null,(([Measures].[Coverage MAT] - [Measures].[Coverage LMAT])/[Measures].[Coverage LMAT]))',

FORMAT_STRING = 'Percent'

SELECT

NON EMPTY {

[Account Group].[Account Group].[All].Children

} ON ROWS,

(

[Measures].[Coverage MAT],

[Measures].[Coverage LMAT],

[Measures].[Variance]

) ON COLUMNS

FROM [MyCube]

This query doesn't work because the Measures hierarchy appears more than once in the tuple and because you cannot aggregate calculated members. Any thoughts on how could I solve this issue. Thanks in advance.

It's unclear how Coverage MAT is computed from Coverage - but assuming that it's computed by averaging coverage over the last 12 months, you could substitute Avg() for Aggregate():

>>

WITH

MEMBER

[Measures].[Coverage MAT]

AS

'AVG(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2006]), [Measures].[Coverage])',

FORMAT_STRING = 'Percent'

MEMBER

[Measures].[Coverage LMAT]

AS

'AVG(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2005]), [Measures].[Coverage])', //Note the 2005 year instead of 2006

FORMAT_STRING = 'Percent'

MEMBER

[Measures].[Variance]

AS

'IIF([Measures].[Coverage LMAT]=0,null,(([Measures].[Coverage MAT] - [Measures].[Coverage LMAT])/[Measures].[Coverage LMAT]))',

FORMAT_STRING = 'Percent'

SELECT

NON EMPTY {

[Account Group].[Account Group].[All].Children

} ON ROWS,

{

[Measures].[Coverage MAT],

[Measures].[Coverage LMAT],

[Measures].[Variance]

} ON COLUMNS

FROM [MyCube]

|||

Hi Deepak,

I also tried to used the averages but the thing is that coverage can't be computed like this. Coverage is a calculated measure that gets the ratio of planned # calls with the actual number of calls. So if for example you have the following records:

month 1: Planned 3, Actual 0 -> Coverage 0 or 0%

month 2: Planned 3, Actual 0 -> Coverage 0 or 0%

month 3: Planned 3, Actual 9 -> Coverage 3 or 300%

Period: Planned 9 (3 + 3 + 3), Actual 9 (0 + 0 + 9) -> Coverage 1 or 100%

The problem is that the average and the real coverage is not the same (usually small difference, considerable in some cases), any ideas on how to deal with this difference?.

|||

A couple of questions then:

What is the exact definition of the Coverage calculated measure?|||

Deepak,

Coverage is the number of calls vs the number of planned calls for a certain period of time. Since you always want to follow your plan, you try that coverage is always 100%. The problem with my query is that I need to get the coverage for 2 different periods and then compare these two. With the average, we are calculating the coverage for each subperiod. Coverage is a calculated measure as I explained in the previous post, it is a very simple calculated measure (a division) but it takes care of a division by 0 (In case you have a plan for 0 calls).

I am using AS2005.

|||

One way would be apply Aggregate() separately to numerator and denominator:

MEMBER

[Measures].[Coverage MAT]

AS

AGGREGATE(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2006]), [Measures].[ActualCalls])

/ AGGREGATE(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2006]), [Measures].[PlannedCalls]),

FORMAT_STRING = 'Percent'

"#Value!" error for calculated cells

I have designed an AS 2000 cube with quite complex calculated cells, which work fine in cube browser or with MDX. The moment I use Excel 2007 Pivot table (both in default or compatibility mode), it shows #Value! instead of the calculations. I thought GetPivotData simply returned values from the cube and did not do any calculations of its own... I spent a lot of time to design the cube, but now it cannot be used from Excel, which it needs to be. Any help would be greatly appreciated.

Best regards.

Have you tried creating a copy of your cube, removing all the calculated cells and then connecting to it?

This might help isolate if it is just an Excel connectivity issue or an problem to do with the calculations. Unfortunately you cannot use profiler to trace AS 2000 like you can with AS 2005 otherwise we could trace the MDX that Excel is sending to the server.

|||

Thank you Darren,

It is definitely not an Excel connectivity issue, since everything else shows on the cube. It seems to fall over when it comes across calculated cells which are defined on the cube. And it does seem to be specific to Excel only. I remember the was an issue with pivot tables when the was a limitation on the cell definition MDX length, but it is unlikely to be that (I used the very minimun number of dimensions). Maybe there is known issue with the display of certain calculated cells in Excel ...

|||I think I managed to locat ethe actual problem area - those caclulated cells use a User Defined Function. When I remove the function, the "#Value!" message disappears . It is strange, because the function is not called by excel, it should be resolved on the cube where it works 100%...|||

The problem in fact looks similar to one described in

http://support.microsoft.com/kb/238306

But that was valid for much older versions of AS and was supposed to be fixed a while ago. (I am using up to date version of AS 2000).

|||

it should be resolved on the cube where it works 100%...

I am just wondering if this is not the case. AS2k used to do a fair bit of the query resolution on the client. If you run ProcessMonitor while Excel is trying to execute the query you might be able to see if it is trying to execute the UDF on the client (although it might be hard as ProcessMonitor catches a lot of information)|||Actually an easier way might be to try installing your UDF on a client machine and see if that fixes the problem.|||

I tried that and it di dnot work. One thing I noticed Excel looked in C:\Documents and Settings\f2978326\Application Data\Microsoft\Template for UserDefined.UDF file and UserDefined is the name of the library while UDF is the name of the class that calculations use ..... Does anyone actually have the information on how PivotTable service handles cells which reference UDFs from analysis server? So far I have not been able to get it anywhere...

#Error using IIF and divide by zero

I am getting an error in a calculated field that could potentially divide by zero, even though I'm using an IIF. The column displays in the report as "#Error". My expression looks like this:

= IIF(Fields!Qty.Value = 0, "None", Fields!Hours.Value / Fields!Qty.Value)

I have successfully used this approach with INT fields, but this time the Hours field is a NUMERIC(9,2). My workaround is to do this:

IIF(Fields!Qty.Value = 0, "None", IIF(Fields!Qty.Value = 0, 42, Fields!Hours.Value) / Fields!Qty.Value)

I guess the 42 is cast to an INT inside the second IIF and the calculation works.

What's strange is that the division would even be carried out in the event of Qty = 0 from the first IIF, because the expression should just evaluate to "None" and that would be that.

Has anybody run into this problem? Is my workaround the recommended approach?

-Larry

Lawrence

Try

IIf(Fields!Income2.Value = 0, nothing,Fields!Income.Value/Fields!Income2.Value)

This works for me when my value is zero

Ham

|||

Hi Larry,

I recommend to add a custom code function for the division (in Report -> Report Properties -> Code):

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

Then, modify the expression accordingly:

= IIF(Fields!Qty.Value = 0, "None", Code.Divide(Fields!Hours.Value, Fields!Qty.Value))

-- Robert

|||

Thanks Robert, that's a good (dare I say) workaround. I'm still curious why the IIF errors out with the double division but works with integer division.

Also, the Edit Expression dialog has the "Divide" text underlined in red, but my project builds successfully and runs ok too. Any idea why it might think it's invalid?

-Larry

|||

Hi Ham,

Looks like I would still have to do two nested IIF statements -- one for my "None" message, and the other to return Nothing. I'm trying to avoid that. But the Code.Divide approach is working, so I'm on my way.

Thanks.

-Larry