Calculated Member help

Hi, I have a calculated member that computes the Sales per week for any given level. I am using a time dimensiona and a sales dimension. The time dimension has these levels: Year, Quarter, Month, and Weeks. I am using this formula

[Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

The problem with this is that it works only if the user select a single time member. I am using Excel as frontend and I fixed the pivot table in such a way that Sales is on the column, and Store is on the Rows. The time dimension is on the top serving as filter (Page axis). Because you can choose multiple dimension member in the dropdown boxes of any dimension in Excel, I am having trouble making it work in these scenario. It only works if you select one time member at a time. Please help.I don't fully understand what you are trying to do, but since the database server is returning a simple dataset to your Excel pivot table, the problem would appear to be with the pivot table and not the query or table you are using. When you filter the pivot table you are fiiltering its stored snapshot of the query results. It does not pass the parameters back to the database server.

blindman|||Hi, thnks so much for the reply. Anyway, to make it clear to you:

I'm using an Analysis Service Cube as my backend and Excel Pivot table as my frontend.

I have two dimension in my cube: 1. Time(Yr, Qtr, Month, Week) and 2. Store

I have two measures: 1. Sales and 2. Sales per week (this measure is a calculated member

The formula I use to make the Sales per week measure is:
[Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

Here's my setup in Excel: The sales is broken down by store and the time is on top acting as a filter

Time: 2003

Store Sales Sales/Week
Store1 100 25
Store2 200 12

My problem occurs evrytime the user selects more than one Time value to filter on (like all Jan AND Feb data). It works only if one time value is selected at a time. When user chooses more than 1 time value, the Sales/Week data appears as: #Num!|||Sorry smb, but I don't have experience with data cubes syntax. Hopefully now that your post is at the top of list someone else will check it out and give you an answer!

blindman|||Thnks anyway blindman :)

Calculated Member Formatting

I'm running Panorama Novaview 4.5 against AS 2005 April CTP (Panorama doesn't work against June CTP yet, but I tested the MDX against June CTP and the same problem occurs). In my cube, I have a Metrics dimension with several associated calculated members. When I run a query where I'm not slicing or slicing by one member per attribute, the calculated members formatting works as expected. However, when I run the query and slice by multiple members of an attribute, all calculated members take on the formatting of the selected measure. This behavior still applies if I run the MDX that Novaview generates in SSMS. The following is the MDX and results:

Query with only single member selection:
With
Member [Metrics].[Metrics].[pnMetaX] As 'Null'
Member [Transaction Type].[Transaction Type].[pnMetaY] As 'iif([Metrics].[Metrics].currentmember is[Metrics].[Metrics].[pnMetaX], count([NewSetY]), Null)'
Set [SetAxisX] As '{Except({[Metrics].[Metrics].[Metrics].[Actual],[Metrics].[Metrics].[Metrics].[Current YTD],[Metrics].[Metrics].[Metrics].[Prior YTD],[Metrics].[Metrics].[Metrics].[YTD % Change]},{[Metrics].[Metrics].[pnMetaX]})}'
Set [SetAxisY] As '{HIERARCHIZE ( Except([Transaction Type].[Transaction Type].[Transaction Type].AllMembers,{[Transaction Type].[Transaction Type].[pnMetaY]}))}'
Set [NewSetY] As '{NonEmpty([SetAxisY],[SetAxisX])}'
Set [NewSetX] As '{NonEmpty([SetAxisX],[NewSetY])}'
Set [MainAxisX] As '{Except([NewSetX],{([Metrics].[Metrics].[pnMetaX])}), ([Metrics].[Metrics].[pnMetaX])}'
Set [MainAxisY] As '{Except(Subset ([NewSetY],0,1000 ), {([Transaction Type].[Transaction Type].[pnMetaY])}) ,([Transaction Type].[Transaction Type].[pnMetaY])}'

Select [MainAxisX] On Columns, [MainAxisY] On Rows
From [Simonton Sales]
WHERE ([Date].[Calendar Monthly].[All Dates].[2005], [Measures].[Dollars], [Plant].[Plant].[Plant].[Ritchie County])

Results: (calc members have correct formatting)
Actual Current YTD Prior YTD YTD % Change pnMetaX
Order $81,249,243.32 81,249,243 191,376,964 -57.54% (null)
Repair $345,643.88 345,644 907,068 -61.89% (null)
Credit ($1,775,492.36) -1,775,492 -3,723,484 -52.32% (null)
Debit $56,010.77 56,011 121,655 -53.96% (null)
pnMetaY (null) (null) (null) (null) (null)

Query with multiple member selection:
With
Member [Metrics].[Metrics].[pnMetaX] As 'Null'
Member [Transaction Type].[Transaction Type].[pnMetaY] As 'iif([Metrics].[Metrics].currentmember is[Metrics].[Metrics].[pnMetaX], count([NewSetY]), Null)'
Member [Plant].[Plant].[pnMulti] As 'Sum({[Plant].[Plant].[Plant].[Ritchie County],[Plant].[Plant].[Plant].[Paris]})'
Set [SetAxisX] As '{Except({[Metrics].[Metrics].[Metrics].[Actual],[Metrics].[Metrics].[Metrics].[Current YTD],[Metrics].[Metrics].[Metrics].[Prior YTD],[Metrics].[Metrics].[Metrics].[YTD % Change]},{[Metrics].[Metrics].[pnMetaX]})}'
Set [SetAxisY] As '{HIERARCHIZE ( Except([Transaction Type].[Transaction Type].[Transaction Type].AllMembers,{[Transaction Type].[Transaction Type].[pnMetaY]}))}'
Set [NewSetY] As '{NonEmpty([SetAxisY],[SetAxisX])}'
Set [NewSetX] As '{NonEmpty([SetAxisX],[NewSetY])}'
Set [MainAxisX] As '{Except([NewSetX],{([Metrics].[Metrics].[pnMetaX])}), ([Metrics].[Metrics].[pnMetaX])}'
Set [MainAxisY] As '{Except(Subset ([NewSetY],0,1000 ), {([Transaction Type].[Transaction Type].[pnMetaY])}) ,([Transaction Type].[Transaction Type].[pnMetaY])}'

Select [MainAxisX] On Columns, [MainAxisY] On Rows
From [Simonton Sales]
WHERE ([Date].[Calendar Monthly].[All Dates].[2005], [Measures].[Dollars], [Plant].[Plant].[Plant].[pnMulti])

Results: (calc members all take the formatting of the selected measure)
Actual Current YTD Prior YTD YTD % Change pnMetaX
Order $124,225,094.82 $124,225,094.82 $292,160,648.80 ($1.15) (null)
Repair $501,626.07 $501,626.07 $1,211,672.17 ($1.11) (null)
Credit ($2,809,978.53) ($2,809,978.53) ($5,447,738.20) ($0.92) (null)
Debit $85,167.51 $85,167.51 $197,187.21 ($1.15) (null)
pnMetaY (null) (null) (null) (null) (null)

Definition of YTD % Change calc member:
YTD % Change:
CREATE MEMBER CURRENTCUBE.[Metrics].[Metrics].[YTD % Change]
AS Iif ([Metrics].[Prior YTD] = 0, NULL,
([Metrics].[Current YTD] - [Metrics].[Prior YTD]) / [Metrics].[Prior YTD]),
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = { [Units], [Dollars] },
VISIBLE = 1;

I tried to recreate this in the AWDW cube, but the formatting didn't seem to work at all there. I ran the following query (pulled from Novaview) and the Budget Variance % calc member wasn't formatted as %:

With
Member [Scenario].[Scenario].[pnMetaX] As 'Null'
Member [Date].[Fiscal].[pnMetaY] As 'iif([Scenario].[Scenario].currentmember is[Scenario].[Scenario].[pnMetaX], count([NewSetY]), Null)'
Set [SetAxisX] As '{AddCalculatedMembers(Except([Scenario].[Scenario].[Scenario].AllMembers,{[Scenario].[Scenario].[pnMetaX]}))}'
Set [SetAxisY] As '{HIERARCHIZE ( Except([Date].[Fiscal].[(All)].AllMembers,{[Date].[Fiscal].[pnMetaY]}))}'
Set [NewSetY] As '{NonEmpty([SetAxisY],[SetAxisX])}'
Set [NewSetX] As '{NonEmpty([SetAxisX],[NewSetY])}'
Set [MainAxisX] As '{Except([NewSetX],{([Scenario].[Scenario].[pnMetaX])}) ,([Scenario].[Scenario].[pnMetaX])}'
Set [MainAxisY] As '{Except(Subset ([NewSetY],0,1000 ) ,{([Date].[Fiscal].[pnMetaY])}) ,([Date].[Fiscal].[pnMetaY])}'

Select [MainAxisX] On Columns, [MainAxisY] On Rows
From [Finance]
WHERE ([Measures].[Amount])

Results:
Actual Budget Budget Variance Budget Variance % pnMetaX
All Periods $12,609,503.00 $5,583,900.00 $7,025,603.00 1.25818925840362 (null)
pnMetaY (null) (null) (null) (null) 1

Any ideas what could be causing these formatting problems?

Thanks,
Dirk

I upgraded to the June CTP and I'm still having the same problem. Any ideas what could be causing this?

Thanks,
Dirk|||I'm still looking for a solution to this problem. Anyone have any ideas?

Thanks,
Dirk

Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

If you are trying to create a measure using the query it will not work as a calculation is an expression, not the result of a query.

I think what you want is an expression something like the following, which will give you the session count for the month.

(StrToMember("[Time].[Month].["+ Format(now(), "yyyy/MM") + "]"), [Measures].[SESSION Count])|||

Darren,

Thanks for your quick response. Yes I have tried that way aswell.

But when I use that expression in calculated member its not returning the value, where as I query it in mgmt studio I am getting

the value. Any idea? or any otherway that I can get the measure for the current month.

Your help is appreciated.

Thanks

|||

The calculation should be the same as the query. You should note that you are not using the calculation in the sample you posted. You are defining it in the WITH MEMBER section, but then you do not reference it anywhere else in the query

Something like the following should show you the currentMonth measure (which should only be for the current month as defined by the clock on the server) and the [SESSION Count] measure which should be at some sort of aggregated level like the total for all time. It depends on how your time dimension is configured.

Code Snippet

select
{[Measures].[SESSION Count], [Measures.CurrentMonth} ON COLUMNS
from Ecgkpi

If you do the following you will see why your original query was not quite right as currentMonth will return the same as the raw session count measure

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]

}
select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

Where as something like this should work the way you want, which is the same logic that we were putting in the calculated measure in the cube.

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
([Measures].[SESSION Count], StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") )

select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

|||

Darren,

Thanks for your much handy information.

The expression for the current month

(StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now()-1, "yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count])

is working in the KPI expression.

But, I am trying to find it otherway aswell, Need your inputs again!.

My data source is from Oracle. I have the date information say the field Start in timestamp format YYYY-MM-DD hh:mms

viz: Start

2006-10-17 13:48:24.603

2007-01-05 08:41:12.411

2007-06-04 16:24:31.839

Without time dimension I want to extract the measure for the current month like the expression

(StrToMember("[table].[Start].[Start].["+ Format(now(), "yyyy/MM") + "]"),[Measures].

[SESSION Count])

But it is giving null. Any idea how to extract current month from the timestamp. This is

much helpful working with Oracle timestamp.

Hope I am clear with this.

Thanks

|||

Darren,

Here is the clear picture. In the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(), "yyyy/MM") + "]"),

[Measures].[ECG GUI SESSION Count]) the filed [ECG GUI SESSION].[MONTH].[MONTH] is extracted as new named query using to_char

function. In this case only it is working

But when I have created server time dimension the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(),

"yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count]) is giving null .

I am trying with different options. But extracting month from timestamp using to_char into a new field is not a correct solution. I

want to work out from the server dim or from the timestamp field directly.

Afore said code snippets are working when we are mentioning the month

[Time].[Month].&[2007-06-01T00:00:00] explictly.

But what I am thinking is if I use the expression like now(), the cube will be processed every month and it will update the measure

on monthly.

I hope I will get the right direction from expertisee.

Thanks

|||You need to figure out what the unique member name for your time dimension looks like. I don't normally use server time dimensions myself so I am not sure off the top of my head, but I don't think that they use "yyyy/MM" at the month level. The easiest thing to do is to open a new MDX query in SQL Server Management Studio and drag one of the month members onto the main window. This will show you the unique name for that member. Then you just need to replicate that format when you use the StrToMember() function.

Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

You need to use CREATE MEMBER syntax instead of WITH MEMBER. Otherwise, it should work.sql

Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

If you are trying to create a measure using the query it will not work as a calculation is an expression, not the result of a query.

I think what you want is an expression something like the following, which will give you the session count for the month.

(StrToMember("[Time].[Month].["+ Format(now(), "yyyy/MM") + "]"), [Measures].[SESSION Count])|||

Darren,

Thanks for your quick response. Yes I have tried that way aswell.

But when I use that expression in calculated member its not returning the value, where as I query it in mgmt studio I am getting

the value. Any idea? or any otherway that I can get the measure for the current month.

Your help is appreciated.

Thanks

|||

The calculation should be the same as the query. You should note that you are not using the calculation in the sample you posted. You are defining it in the WITH MEMBER section, but then you do not reference it anywhere else in the query

Something like the following should show you the currentMonth measure (which should only be for the current month as defined by the clock on the server) and the [SESSION Count] measure which should be at some sort of aggregated level like the total for all time. It depends on how your time dimension is configured.

Code Snippet

select
{[Measures].[SESSION Count], [Measures.CurrentMonth} ON COLUMNS
from Ecgkpi

If you do the following you will see why your original query was not quite right as currentMonth will return the same as the raw session count measure

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]

}
select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

Where as something like this should work the way you want, which is the same logic that we were putting in the calculated measure in the cube.

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
([Measures].[SESSION Count], StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") )

select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

|||

Darren,

Thanks for your much handy information.

The expression for the current month

(StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now()-1, "yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count])

is working in the KPI expression.

But, I am trying to find it otherway aswell, Need your inputs again!.

My data source is from Oracle. I have the date information say the field Start in timestamp format YYYY-MM-DD hh:mms

viz: Start

2006-10-17 13:48:24.603

2007-01-05 08:41:12.411

2007-06-04 16:24:31.839

Without time dimension I want to extract the measure for the current month like the expression

(StrToMember("[table].[Start].[Start].["+ Format(now(), "yyyy/MM") + "]"),[Measures].

[SESSION Count])

But it is giving null. Any idea how to extract current month from the timestamp. This is

much helpful working with Oracle timestamp.

Hope I am clear with this.

Thanks

|||

Darren,

Here is the clear picture. In the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(), "yyyy/MM") + "]"),

[Measures].[ECG GUI SESSION Count]) the filed [ECG GUI SESSION].[MONTH].[MONTH] is extracted as new named query using to_char

function. In this case only it is working

But when I have created server time dimension the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(),

"yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count]) is giving null .

I am trying with different options. But extracting month from timestamp using to_char into a new field is not a correct solution. I

want to work out from the server dim or from the timestamp field directly.

Afore said code snippets are working when we are mentioning the month

[Time].[Month].&[2007-06-01T00:00:00] explictly.

But what I am thinking is if I use the expression like now(), the cube will be processed every month and it will update the measure

on monthly.

I hope I will get the right direction from expertisee.

Thanks

|||You need to figure out what the unique member name for your time dimension looks like. I don't normally use server time dimensions myself so I am not sure off the top of my head, but I don't think that they use "yyyy/MM" at the month level. The easiest thing to do is to open a new MDX query in SQL Server Management Studio and drag one of the month members onto the main window. This will show you the unique name for that member. Then you just need to replicate that format when you use the StrToMember() function.

Calculated member for Grand Total...

As part of a cube I would like to have access to the Grand Total for my measure "Events".

Is there a simple way to access the grand total regardless of the Dimensions either as a calculated measure or directly?

for example if the dimensionsion were MonthNumber and EventType and the values for the intersect of these dimensions are

12, a = 5

11,a = 6

12,b = 3

11,b = 2

I'd like to end up with something like :

MonthNumber 12 11

EventType

Events total Events total

a 5 16 6 16

b 3 16 2 16

Where "Events" is the Events value and "total" is combined value of all events in the cube.

Thanks

You would do this by getting the Events measure at the "All Eventtype" level. Your MDX would look something like the following:

WITH
MEMBER Measures.total as '(Measures][Events],[Eventtype].[All Eventtype]'
SELECT
{[Time].[Month].[11],[Time].[Month].[12]}
* {Measures.[Events], Measures.total} ON COLUMNS,
{[Eventyype].members} ON ROWS
FROM <Cube>

Regards
Darren Gosbell [MCSD]
http://geekswithblogs.net/darrengosbell

|||

Thanks for the repy, but I'm kinda looking for access to the grand total functionality, if such a thing exists.

Also this doesn't seem to woirk as a calculated member.

Calculated member for Grand Total...

As part of a cube I would like to have access to the Grand Total for my measure "Events".

Is there a simple way to access the grand total regardless of the Dimensions either as a calculated measure or directly?

for example if the dimensionsion were MonthNumber and EventType and the values for the intersect of these dimensions are

12, a = 5

11,a = 6

12,b = 3

11,b = 2

I'd like to end up with something like :

MonthNumber 12 11

EventType

Events total Events total

a 5 16 6 16

b 3 16 2 16

Where "Events" is the Events value and "total" is combined value of all events in the cube.

Thanks

You would do this by getting the Events measure at the "All Eventtype" level. Your MDX would look something like the following:

WITH
MEMBER Measures.total as '(Measures][Events],[Eventtype].[All Eventtype]'
SELECT
{[Time].[Month].[11],[Time].[Month].[12]}
* {Measures.[Events], Measures.total} ON COLUMNS,
{[Eventyype].members} ON ROWS
FROM <Cube>

Regards
Darren Gosbell [MCSD]
http://geekswithblogs.net/darrengosbell

|||

Thanks for the repy, but I'm kinda looking for access to the grand total functionality, if such a thing exists.

Also this doesn't seem to woirk as a calculated member.