on 10-15-2010 1:46 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
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
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
Is this Parsed ?? <EXPRESSION> CDate(Date.Calendar.CurrentMember.MemberValue) </EXPRESSION>
YES, then what is the output??
Thank You!!
Sent from iPhone
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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,
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
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.