Using Parameters when Reporting in SSRS 2005 against a 2000 AS Cube
Nasty old issue over the past 24 hours….
Re: No Parameter Pane when Reporting in 2005 against a 2000 AS Cube – MSDN Forums
I was wondering what had happened to my ability to add parameters to an MDX cube report….
For those that are interested. SSRS 2005 will not support the parameter pane when connecting to an SSAS 2000 cube. The steps you need to follow are as follows:
1. Create a connection (data source) to your AS2000 cube using OLE DB connection (In my case I used version 8 – Provider=MSOLAP.2)
2. Add a new dataset – this will be the old text based MDX editor rather than the snazzy new SSRS 2005 AS GUI so you will need to put your MDX hat on
3. Construct a base MDX query with the filter hard coded, e.g.
SELECT NON EMPTY { [Measures].[Area Controlled] } ON COLUMNS,
NON EMPTY { [Developer].[Tracked].[Actively Tracked],
[Developer].[Tracked].[Actively Tracked].CHILDREN } ON ROWS
FROM [QMSLand]
WHERE ( [Time].[Quarter].[Quarter].[Q1 2007])
4. Set up a second dataset to return the values for your parameter list e.g.
WITH MEMBER Measures.NullColumn AS ‘Null’
SELECT {Measures.NullColumn} ON COLUMNS,
{[Time].[Quarter].[Quarter].Members } ON ROWS
FROM [QMSLand]
5. Create a Parameter in the normal way, specifying the values to be selected from the query in 4. Let’s say we call it Quarter
6. Finally, edit your initial MDX query to include the new parameter as opposed to the hard-coded value e.g.
=”SELECT NON EMPTY { [Measures].[Area Controlled] } ON COLUMNS, NON EMPTY { [Developer].[Tracked].[Actively Tracked], [Developer].[Tracked].[Actively Tracked].CHILDREN} ON ROWS
FROM [QMSLand] WHERE ( [Time].[Quarter].[Quarter].[" + Parameters!Quarter.Value + "])”
Important things to note about step 6
- The command must be enclosed in quotes i.e. =”….mdx here…”
- Note also the way the parameter reference is wrapped in speech marks
- Ensure you have your complete dataset before wrapping the command – you cannot add fields once the command is enclosed in parentheses
- The command must be a SINGLE line (no carriage returns)…so I used notepad to ensure this
Painful and very lightly documented… but a solution none the less
Great!!!!!!!
i was stuck and you finally saved me
i would like to know how to pass more vtahn one parameters where i am stuck again.
your answer is very very valueable for me
waiting
In what way do you require to pass more than one parameter? From a multi-parameter pick list for example? As with all things there is more than one way to do it –
One way I sometimes use in this specific arena is to set up a picklist SQL “view” that contains friendly names and their MDX equivalents using SQL to genereate the view. So you’d get something like this
Dallas [Region].&[152]
Denver [Region].&[154]
Atlanta [Region].&[204]
I generally pass the ID as opposed to the text name ([Region].&[Dallas] for example) as it’s a good practice to get in to…
If the user selected Dallas & Denver, you might pass the MDX values to the MDX statement using the Join statement… i.e.
WHERE ( + ” Join(Parameters!Location.Value,”, “) + “)”
This is mostly from memory – don’t have my old code to hand right now, however a good resource for all things MDX can be found here
http://www.databasejournal.com/features/mssql/article.php/3549496
Basically anything by Bill Pearson is good to go
Hope this helps,
Will
Thank you so muchhh!!!
One more question i have please response it because that is very important and since i am a beginer in mdx so unable to parse it in mdx
MEASURE: USERS,paiddownload,totaldownload,freedownload
DIMENSIONs:
1. FirstSeenMONTH– year,quarter,month(three hierarcy year–>quarter–>month, quarter–>months ,similiar for months
2.Purchase MONTH — year,quarter,month same hierarchies here
3. CLIENTS– micr,
4.Products –alert tone,ringtones etc
what i want is to get the users who are new to this month, and who are returning( that means except new ) ,and same for downloads purchase too.
i need to find out these fields using Mdx from the cube
NEW USERS: CURRENT MONTHS USERS
RETURNING USERS =ALL USERS – NEW USERS
NewdownloadPurchase =current month download purchase,
Retrning download purchase= total purchase -current month download purchase
how to implement this using mdx since i am new in mdx unable to figure it out.
Please help me out as you did with my earlier problem.
Thank you and waiting for your response.
dib
also i am having problem when using
WITH MEMBER Measures.NullColumn AS ‘Null’
SELECT {Measures.NullColumn} ON COLUMNS,
{ [FirstSeenMonth].[All FirstSeenMonth].[2008].members} on ROWS
from [AllFirstSeen1]
this says invalid token
any guess??
Thanks
Doesn’t work with Report Builder 2.0?
At least I can’t get it to work.