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.