cancel
Showing results for 
Search instead for 
Did you mean: 

date calculated measures in OLAP universe for time hierarchy.

Former Member
0 Kudos

Hi,

I want to create the calculated measure for date filed, because i am creating the universe from cube (ssas 2008), the date field is converted to character, so to convert it back to date i need to create the calculated measrue.

This is my hierarchy of date

Date --> Calender --> Date.Calendar -->Date

So i want to create calculated measure for Date like

<EXPRESSION> CDate([Date].[Calendar].CurrentMember.MemberValue) </EXPRESSION

when i use this object in webi and see following generated mdx in sql profiler

WITH MEMBER [Measures].[F7018C7E-DFE3-4B93-80,7D,4D,73,F3,71,4,81] AS

VBAMDX!CDate([Date].[Calendar].currentmember.membervalue)

SELECT { [Measures].[F7018C7E-DFE3-4B93-80,7D,4D,73,F3,71,4,81] } ON COLUMNS

FROM [Adventure Works]

when execute the above query in mdx editor then i got the #Error.

have someone has idea how can i resovle this issue.

Because I have to generate the date object and on date object i want to create the time hierarchy.

Anyone help would be much appreicated.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

First of all Designer is only parsing metadata that have been generated and Designer functions such as @Select or @Prompt, MDX syntax is never parsed.

The syntax you wrote seems to be correct, so did you turn your object data type to date? If not that is explaining the error.

If you did the data type change, can you copy and paste the generated MDX in a MDX application such "MDXSample" or "MDXStudio" and see if the query is successfully ran?

Didier

Former Member
0 Kudos

Thank you very much Didier for your response.

I Converted the type from charcater to date.

When i create the webi report using that object, I got the following generated MDX in profiler.

WITH MEMBER [Measures].[46FA4332-725C-48B0-95,4B,7F,5D,F9,37,62,CC] AS

CDate([Date].[Date].[Date])

SELECT { [Measures].[46FA4332-725C-48B0-95,4B,7F,5D,F9,37,62,CC] } ON COLUMNS ,

NON EMPTY {[Customer].[Customer Geography].DEFAULTMEMBER} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME ON ROWS

FROM [Adventure Works]

When I run this query in MDXStudio, this is excuted successfully but i got the #Error in returned results.

I dont know what I am doing wrong.

Do u have any idea how to resolve this issue.

Regards,

Tayyab

Former Member
0 Kudos

Hi,

An MDX calculated member does not support a collection of members as you wrote, only named sets are supporting collection of members.

So you need to modify your expression to retrieve and convert a single member to date that means that you also need to add the dimension date in your query.

Here is the MDX that works and solves your problem:

WITH MEMBER [Measures].[ToDate] AS 'CDate([Date].[Calendar].Currentmember.MemberValue)'
SELECT
NON EMPTY  HIERARCHIZE ( DISTINCT( { [Date].[Calendar].[Date].MEMBERS } )  ) ON ROWS,
NON EMPTY  HIERARCHIZE ( DISTINCT( { [Measures].[ToDate] } )  ) ON COLUMNS
FROM [Adventure Works]

Didier

Former Member
0 Kudos

Thank you very much Didier.

How can we translate the following expressions in designer.

WITH MEMBER [Measures].[ToDate] AS 'CDate([Date].[Calendar].Currentmember.MemberValue)'
SELECT
NON EMPTY  HIERARCHIZE ( DISTINCT( { [Date].[Calendar].[Date].MEMBERS } )  ) ON ROWS,
NON EMPTY  HIERARCHIZE ( DISTINCT( { [Measures].[ToDate] } )  ) ON COLUMNS
FROM [Adventure Works]

The above MDX expression run successfully in mdx studio, but with two columns as you know in designer, object corresponds to one column.

Can you please write the expression which i can use in universe designer,

Your help will be much appreciated.

Regards,

Tayyab

Edited by: Tayyab Muhammad on Oct 21, 2010 1:02 AM

Former Member
0 Kudos

Hi,

First you have to create a date calculated member in the universe with the following expression:

CDate([Date].[Calendar].Currentmember.MemberValue)

Then you need to build a query with the object you have create and the object mapped on the Date level with the following expression:

[Date].[Calendar].[Date]

That's all.

You can add more objects and measures i n the query but do not forget that the query will fail if the calculated expression is added without the date object.

Didier

Former Member
0 Kudos

Hi Didier,

Thank you very much for your reply.

I have created the calculated measure in designer by following

CDate([Date].[Calendar].Currentmember.MemberValue)

Now how can i build a query with the created object and how to map the date level expression

[Date].[Calendar].[Date]

Your help would be highly appreciated.

Regards,

Tayyab

Former Member
0 Kudos

Hi,

I assume that you already have generated a universe on an MSAS cube. I also assume that based on the information you sent me the cube is AdventureWorks.

So you just need to use the date object that has been generated and the custom object you have created, no less no more.

Here are in attachments WebI query screenshots and a universe based on MSAS AdventureWorks.

Regards

Didier

Former Member
0 Kudos

Thank you Didier Very Very Much

Yes date field is generated with the smaples you provided.

But I have problem when i create the reports with date and other objects, it takes a long time to execute and some time gives the error of not enough memory to excute the mdx statement. So how can i resolve this issue.

and when i create the prompt object for date, we have three prompts for date field when i use the bewteen operator.

Do u have any idea how to resolve this issue.

Your help would be much appreciated.

Regards,

Tayyab

Former Member
0 Kudos

Thank you Didier Very Very Much

My date is working and now i can build the hierarchy on these date objects.

Now I want to create predefined date filter on these date objects, i created the predefined filter by using the Designer's MDX editor and when I try to use this filter object and it use the following expression in profiler

[Measures].[C56F023E-DDCE-4B87-B4,1F,86,67,17,23,8,6F] >=  01/07/01 AND  [Measures].[C56F023E-DDCE-4B87-B4,1F,86,67,17,23,8,6F] <=  30/11/02)


But it is not working, no data is filtered against the give dates.

Your help in this regard is much appreciated..

Regards,

Tayyab

Edited by: Tayyab Muhammad on Nov 3, 2010 1:12 AM

Answers (1)

Answers (1)

Former Member
0 Kudos

Is this Parsed ?? <EXPRESSION> CDate(Date.Calendar.CurrentMember.MemberValue) </EXPRESSION>

YES, then what is the output??

Thank You!!

Sent from iPhone

Former Member
0 Kudos

Desinger is not capable to parse this expression in design mode.

One can see its generated MDX in profiler which i mention in my first post. When execute the MDX i got the #Error.

So, have any idea how to resovle this issue.

Former Member
0 Kudos

Desinger is not capable to parse this expression in design mode.

>> Who told?? What are the changes you did or calculated etc..., should parse at Universe. Otherwise it won't work in Report.

I suggest, even though Date is showing as Character, don't make any changes at Datasource or Universe level (Unless your good at those conversions).

We have many functions, which will take care of conversion like FormatDate(), ToDate() etc..,

FYI, if you want that Date object as Prompt then you have to concentrate at Universe level.

@Universe: You can go with to_date() function for conversion.

Hope it helps you.

Thank You!!

Sent from iPhone

Former Member
0 Kudos

Thank you very much for your reply.

The functions like to_date() is not working in MDX, because it is not included in MDX dictionary. Even i tried but i got the error on parsing like

Parse failed: Exception DBD, The supplied XML is not valid. to_date ([Delivary Date].[Date] )

I want to build the time hierarchy like Year>Quarter>Month>Week>Date and display the Calendar on prompt page.

So i have to setup all these things in universe level.

have any other option to resovle this issue.

Regards,

Former Member
0 Kudos

There is one icon in the universe called Hierarchies or go to Tools -> Hierarchies. You can define the hierarchies here.

Try the following definition for converting the character into date type:

 CDate(@Select(Class\Object) 

The MDX expressions are case sensitive, so make sure you have defined the names of the objects exactly. Also if you are

version 3.1 SP2 then designer should be able to parse the objects correctly. It also works for the earlier versions too but

more accurate parsing it does from SP2.

display the Calendar on prompt page

This can be achieved by the following definiton of the prompts

@Prompt('Date','D',,mono,constrained)

The above @Prompt() definiton between <FILTER> </FILTER> tags will ensure the calendar pop-up.

Regards,

Rohit