February 28, 2008 at 7:58 am
I am seeking some expertise with an MDX query in SSRS Report Designer. I can construct and execute the query with no problem in an MDX query window in SSMS. However, when I try to build the same functionality, using parameters in Report Designer, I absolutely cannot get it to work. I have tried numerous ways to construct the query, Instr, settostr, settomember, filter, union, subcubes, etc., nothing works. I have a good understanding of parameters in Report Designer, and know how to link a query parameter with a report parameter, and am aware of the Dataset that is created behind the scenes for the query parameter. Here is the MDX query that I can run in SSMS;
select { [Measures].[Fact Email Count], [Measures].[Course Key Count],
[Measures].[AvgEmailPerCourse]} on columns,
order (
{ ([Dim Date].[Year Num].[2007], [Dim Date].[Month Name].members,
[Dim User Right].[Dim User Right].currentmember, [Dim Account].[Dim Account].[angel administrator]),
([Dim Date].[Year Num].[2007], [Dim Date].[Month Name].members,
[Dim User Right].[Dim User Right].[system editor], [Dim Account].[Dim Account].currentmember)
},
settostr( {([[Dim Date].[Year Num], [Dim Date].[Month Name], [Dim User Right].[Dim User Right],
[Dim Account].[Dim Account])} )
, asc)
on rows
from [ANGEL74DWproto3Cube];
The query produces the following output;
Fact Email Count Course Key Count AvgEmailPerCourse
2007 All All ANGEL Administrator 28 7 4
2007 All System Editor All 65 14 4.64285714285714
2007 January All ANGEL Administrator (null) (null) (null)
2007 January System Editor All 1 1 1
2007 February All ANGEL Administrator (null) (null) (null)
2007 February System Editor All (null) (null) (null)
2007 March All ANGEL Administrator (null) (null) (null)
2007 March System Editor All null) (null) (null)
2007 April All ANGEL Administrator 1 1 1
2007 April System Editor All (null) (null) (null)
2007 May All ANGEL Administrator 27 6 4.5
2007 May System Editor All 62 13 4.76923076923077
2007 June All ANGEL Administrator (null) (null) (null)
2007 June System Editor All 2 1 2
2007 July All ANGEL Administrator (null) (null) (null)
2007 July System Editor All (null) (null) (null)
2007 August All ANGEL Administrator (null) (null) (null)
2007 August System Editor All (null) (null) (null)
2007 September All ANGEL Administrator (null) (null) (null)
2007 September System Editor All (null) (null) (null)
2007 October All ANGEL Administrator (null) (null) (null)
2007 October System Editor All (null) (null) (null)
2007 November All ANGEL Administrator (null) (null) (null)
2007 November System Editor All (null) (null) (null)
2007 December All ANGEL Administrator (null) (null) (null)
2007 December System Editor All (null) (null) (null)
What I need to do is get two parameters working with this query. One parameter needs to replace the "angel administrator" literal in the [Dim account] hierarchy, and the other parameter needs to replace the "system editor" literal in the [Dim user right] hierarchy.
Any help with this problem would be greatly appreciated.
P.S. - I also have a copy of the document "Integrating Analysis Services with Reporting Services". This document speaks to the concept of using a dynamic MDX string for the query. I still can't get it to work. When I use the parameter in the string, that field gets removed from my dataset, so I no longer have it available for usage on the report. Here is a stripped down version of the query (in dynamic MDX form) - still does not work.
="select {[Measures].[Fact Email Count], [Measures].[Course Key Count],[Measures].[AvgEmailPerCourse]} on columns, order ({([Dim Date].[Year Num].["+Parameters!DimDateYearNum.Value+"], [Dim Date].[Month Name].currentmember, [Dim User Right].[Dim User Right].currentmember, [Dim Account].[Dim Account].[angel administrator]), ([Dim Date].[Year Num].["+Parameters!DimDateYearNum.Value+"], [Dim Date].[Month Name].currentmember, [Dim User Right].[Dim User Right].[system editor], [Dim Account].[Dim Account].currentmember)},settostr({([[Dim Date].[Year Num], [Dim Date].[Month Name], [Dim User Right].[Dim User Right],[Dim Account].[Dim Account])}), asc) on rows FROM [ANGEL74DWproto3Cube]"
March 3, 2008 at 1:42 am
Hi Robert,
last week I had to fiddle with mdx parameters myself. One report had to transfer the chosen member to a second report. This way it worked:
1) The parameter in 'navigation' has to be the UniqueName, not the fields' value - e.g.
=Fields!Receipt_Number_Search.UniqueName
2)You have to manually edit the mdx query of the parameter-receiving report; to correctly translate the parameter use the 'STRTOMEMBER'-function. I modified the query after 'clicking it together'; hence the subcube-definition:
Select
non empty {blabla} on 0,
non empty {thisandthat} on 1
from {
SELECT ( STRTOMEMBER(@DimReceiptNumberReceiptNumberSearch) ) ON COLUMNS FROM [CubeNameHere]))
WHERE ( and so on )
Hope that provides the necessary clue -
Cheers,
Juergen
March 14, 2008 at 12:50 pm
I don't know if you have found an answer to this yet but I thought I'd post my solution since I have found that a lot of people (including myself) appear to have problems in this area.
My requirement was to be able to use a datetime picker to set the value of a parameter that I wanted to include in my MDX script. I am using SSRS and SSAS.
My initial confusion was all to do with how parameters are defined and used in SSRS. I was always using the Report Parameters dialog accessed via the "Reports/Report Parameters" menuitem in SSRS. This dialog is used to defined parameters that are displayed in the Reporting Services UI (your report). You can, of course, create a parameter here that is not visible by checking the "Hidden" checkbox. When it comes to defining parameters that you want to use within your MDX query, though, this is not the place to go. There are two other places where you configure MDX parameters. The first is in the "Query Parameters" dailog. I think of this dialog as a place where you declare your parameters. This dialog is accessed via the "Query Parameters" toolbor button (the one with the "@" symbol on it) in the "Data" pane of your report. Using this dialog, you declare the parameter that you want available to your MDX script. The next area (see below) will be how you define the value of this parameter.
The next area for configuring parameters is accessed by clicking the "..." button next to the dataseet list in the "Data" pane of your report.This will dislay a "Dataset" dialog box. Click the "Parameters" tab. You will have to enter the name of the parameter you declared in the step above (it is not available in any pulldown list, go figure). In the "Value" field next to the name you just entered, you can enter an expression to set the value of the parameter. Remember, this parameter you are configuring is the one you want to use in the MDX script. It will be getting its value from a UI parameter; the datetime picker that you configure in the "Reports/Report Parameters" menuitem.
The expression I used was:
="[Start Hour].[Day].&["&Trim(Str(Day(Parameters!EndDay.Value)))&"]"
This constructs a member string that matches the hierarchy defined in my underlying OLAP cube.
February 5, 2009 at 5:27 am
Hi,
Can anyone please help me regarding the same issue.
Not able to use the dynamic MDX as we use to do in SSRS 2005(="MDX query")
When we use strtomember then whenever we need to provide the value we have to specify entire hierarchy.
Thanks,
Ramani
April 5, 2010 at 9:38 am
Hi, just wanted to point out that it is very important to find out how the members for the date store the value (like [Date].[Date].[12101225] or [Date].[Date].&[1] using key). If it stores by key then parameter expression will be very different.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply