MDX scripts

Hi all,
I would like to retrive data from my datawarehouse cube and make a report. But I got a problem when I ran a report dataset by MDX. First I checked the same MDX script in the MDX sample application and it worked. Then I ran it in the report, it took a long time and gave me an internal error. So, I simplified the script to remove one crossjoin and got the results. I don't know what's wrong with the reporting service.
I would very appreciate if anyone give me some suggestions.
Thanks
ymliu
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Use SQL Profiler to see the SQL being executed
Jeff
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:#VN8fF2lEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I would like to retrive data from my datawarehouse cube and make a report.
But I got a problem when I ran a report dataset by MDX. First I checked the
same MDX script in the MDX sample application and it worked. Then I ran it
in the report, it took a long time and gave me an internal error. So, I
simplified the script to remove one crossjoin and got the results. I don't
know what's wrong with the reporting service.
> I would very appreciate if anyone give me some suggestions.
> Thanks
> ymliu
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Mdx query

Hello guys,

Assume I have a date dimention in a cube and I want to retrive data based on the StartDate and EndDate value. In short, I want to filter all records between StartDate value and EndDate value which is given by a user. Can any body give a general syntax or mdx query to achive this goal?

Sincerely,

Amde

Hello Amde

Assuming Analysis Services 2005:

SELECT {[Measures].[MyMeasures]} ON 0,

[MyDimension].[MyHierarchy].[MyLevel].members ON 1

FROM [MyCube]

WHERE {[Time].[Date].[<StartDate>] : [Time].[Date].[<EndDate>]}

|||

Thanks a lot michael,

But what if the the <StartDate> and <EndDate> values are parameter values that should be specified by a user? What will be the where condition in this case?

Sincerely,

Amde

|||The query itself needs to be the same. How would the user specify the parameters? Which client is the user using?|||

Hey michael,

That is a good question. I am going to write this query in the Report Designer; there will be report parameters <StartDate>and<EndDate> by which the user will specify and based on the parameter, I want to filter the record. I think this makes sense.

Sincerely,

Amde

|||

Ahhh... OK. Then I guess you just need to create two report parameters (for instance, named @.StartDate and @.EndDate). The values have to contain the unique member name for the dates. This could be something like [Time].[Date].&[20060518]. This value can the be passed to the MDX query. Now, I don't remember the exact syntax when you want to use report parameters in an MDX query, but I believe the WHERE statement should be something like this:

WHERE {StrToSet(@.StartDate.Value + ":" + @.EndDate.Value)}

|||

Hey Michael,

Thank you for sharing your idea, I know the syntax in reporting service; I will change the syntax and use it properly.

Sincerely,

Amde