March 19, 2009 at 10:06 am
I am writing a MDX query against an OLAP Cube and using Reporting Services to display the results. When I create the query utilizing the wizard, the query for the parameter dataset defaults to display ALLMEMBERS. I've gone to the parameter query and tried to modify it there, changing it from ALLMEMBERS to what I want, but it will only reflect the first member that I specify.
Here is the MDX query for the parameter: I have highlighted what I changed in bold.
WITH MEMBER [Measures].[ParameterCaption] AS [ServiceCharge].[Sector Type].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [ServiceCharge].[Sector Type].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [ServiceCharge].[Sector Type].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , Descendants([ServiceCharge].[Sector Type].&Electric, [ServiceCharge].[Sector Type].&Gas) ON ROWS FROM ( SELECT ( STRTOSET(@StartCalendarPeriodCalendarYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@CustomerSiteAndGroupCustomerName, CONSTRAINED) ) ON COLUMNS FROM [ValidatedChargesCube]))
The results show only Electric in the parameter dropdown, not Gas.
Is there an easy way to change the results to limit the dataset? I feel like I must be overlooking something in the wizard because this is so easy using SSRS and SQL Queries in SSRS. I've searched the internet, I've looked in books. I can't find anything.
Someone please help. My head is slamming against my keyboard!
Thanks in advance.
March 19, 2009 at 10:30 am
Am assuming that [ServiceCharge].[Sector Type].[Electric] actually has child members. If it doesn't, or you're looking for the members at the 'Electric' level (so am again assuming this is likely to be 'Gas', 'Electric', 'Deisel' etc) then you could use [ServiceCharge].[Sector Type].MEMBERS instead.
WITH
MEMBER [Measures].[ParameterCaption] AS [ServiceCharge].[Sector Type].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [ServiceCharge].[Sector Type].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [ServiceCharge].[Sector Type].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[ServiceCharge].[Sector Type].[Electric].CHILDREN ON ROWS
FROM
( SELECT ( STRTOSET(@StartCalendarPeriodCalendarYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT
(STRTOSET(@CustomerSiteAndGroupCustomerName, CONSTRAINED) ) ON COLUMNS FROM [ValidatedChargesCube]))
HTH,
Steve.
March 19, 2009 at 11:37 am
Thanks Steve,
Unfortunately that did not work. When I ran your query against the cube it came back with no results.
Ann
March 19, 2009 at 11:44 am
I think the problem is my assumption that Electric is a level in the dim.
Are you looking to see Sector Types as the available options for the drop down/filter list?
If so, modify the rows statement to be something like
[ServiceCharge].[Sector Type].MEMBERS ON ROWS
If you are looking for the children of Electric, changing it to the following might work...
[ServiceCharge].[Sector Type].[&Electric].CHILDREN ON ROWS
Steve.
March 19, 2009 at 12:18 pm
The first line of code returns everything, which is what I am trying to avoid in my parameter dropdown list and the second line of code returns nothing. I am new to MDX. What I want it to do with the parameter is populate it with a data set that is like this SQL query - Select SectorType from SectorTypeLookup where SectorType in ('Electric', 'Gas'). I know MDX is completely different that SQL, but, there has to be a way to do this.
Thanks,
Ann
March 19, 2009 at 12:34 pm
So you only want the two? Well, you could hard code that in to a parameter list (making sure you use MDX unique names for the values), or you could still run a query (though, this is a bit superfluous as you'll be limiting the resultset, so it's a bit of 'whats the point?').
Anyhoo, you could try the following.....
{[ServiceCharge].[Sector Type].[&Electric], [ServiceCharge].[Sector Type].[&Gas]} ON ROWS
basically making a set of the two members.
Steve.
March 19, 2009 at 2:22 pm
Thanks for your help, Steve.
That line of code returns nothing,
but if I do this Descendants([ServiceCharge].[Sector Type].&Electric, [ServiceCharge].[Sector Type].&Gas) ON ROWS
I get Electric and not Gas.
Ann
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply