Issues while passing parameter value to SSRS report which uses SSAS Cube as datasets and MDX Query

Question

Hello All,

I have created one SSRS report to display monthwise  report for leave taken by employee .

My report having parameter as Startdate,enddate,Department,Section,Group,Project

It works well when I call this report from C# and pass parameter like

           ParameterValue[] parameters = new ParameterValue[7];
            parameters[0] = new ParameterValue();
            parameters[0].Name = "StartDate";
            parameters[0].Value = "[Dim Time].[StartDate].&[2011-01-01T00:00:00]";
            parameters[1] = new ParameterValue();
            parameters[1].Name = "EndDate";
            parameters[1].Value = "[Dim Time].[StartDate].&[2011-12-31T00:00:00]";
         .,......ETC

 SELECT NON EMPTY
        { [Measures].[EmpLeaveCount]        
        } ON COLUMNS,
        NON EMPTY
        { ([Dim Employee].[Name].[Name].ALLMEMBERS
         * [Dim Time].[LeaveDateMonth].[StartUtcMonth].ALLMEMBERS
         * [Dim Time].[LeaveDateYear].[StartUtcYear].ALLMEMBERS ) } ON ROWS
 FROM ( SELECT ( STRTOSET(@GroupID, CONSTRAINED) ) ON COLUMNS
        FROM ( SELECT ( STRTOSET(@SectionID, CONSTRAINED) ) ON COLUMNS
               FROM ( SELECT ( STRTOSET(@DeptId, CONSTRAINED) ) ON COLUMNS
                      FROM ( SELECT ( STRTOSET(@RegId, CONSTRAINED) ) ON COLUMNS
                             FROM ( SELECT ( STRTOSET(@ProjectId, CONSTRAINED) ) ON COLUMNS
                                    FROM ( SELECT ( STRTOMEMBER(@StartDate, CONSTRAINED)
                                                  : STRTOMEMBER(@EndDate, CONSTRAINED) ) ON COLUMNS
                                            FROM [EmployeeLeave]))))))
 WHERE (
        IIF(STRTOSET(@ProjectId,CONSTRAINED).Count=1,STRTOSET(@ProjectId,CONSTRAINED),[Projects].[Projects].currentmember ),
        IIF(STRTOSET(@RegId,CONSTRAINED).Count=1,STRTOSET(@RegId,CONSTRAINED),[Region].[Region].currentmember),
        IIF(STRTOSET(@DeptId,CONSTRAINED).Count=1,STRTOSET(@DeptId,CONSTRAINED),[Department].[Department].currentmember),
        IIF(STRTOSET(@SectionID,CONSTRAINED).Count=1,STRTOSET(@SectionID,CONSTRAINED),[Section].[Section].currentmember),
        IIF(STRTOSET(@GroupID,CONSTRAINED).Count=1,STRTOSET(@GroupID,CONSTRAINED),[Group].[Group].currentmember)
        )

But I would like to pass parameter like this

            ParameterValue[] parameters = new ParameterValue[7];
            parameters[0] = new ParameterValue();
            parameters[0].Name = "StartDate";
            parameters[0].Value = "2011-01-01 00:00:00";
            parameters[1] = new ParameterValue();
            parameters[1].Name = "EndDate";
            parameters[1].Value = "2011-12-31 00:00:00";
         .,......ETC

I tried to pass the same values for parameter but It says "Please pass value for StartDate"

Let me know how can I do this ?

---

--RDBurmon

Answer 1

The problem is that MDX needs a qualified name, so [Dim Time].[StartDate].&[...]. The two issues with what you want to pass in are that you need to then clarify the name to that format - in which case you can't pass in more than one item, and secondly that your dimension is set up with the StartDate in the format YYYY-MM-DDTHH:MM:SS while you want to pass it in as YYYY-MM-DD HH:MM:SS which wont match since it will do a string comparison to look for the item.

So either way you need to manipulate the parameters - either in the declaration or in the report itself. You could write the report with member declarations for the date

ie: WITH SET [Relevant Dates] AS {

StrToMember ("[Dim Time].[StartDate].&[" + CStr(REPLACE(@StartDate, " ", "T") + "]", CONSTRAINED) :

StrToMember ("[Dim Time].[StartDate].&[" + CStr(REPLACE(@EndDate, " ", "T") + "]", CONSTRAINED)

}

SELECT ....

FROM (...(SELECT [Relevant Dates] ON 0 FROM [EmployeeLeave]))

....

I'm not sure if either the REPLACE function works in the MDX editor, in which case you may need to find some other way to resolve it, or if the SET declaration can be used in the subselect, in which case you will just need to replace the Set reference with the actual declaration in the subselect.

Hope that helps

Answer 2

Thanks for reply . I modified the query as per your suggession . It gives me error

"Query (17, 14) The dimension '[Relevant Dates]' was not found in the cube when the string, [Relevant Dates], was parsed."

WITH SET [Relevant Dates] AS {

StrToMember ("[Dim Time].[StartDate].&[" + CStr(REPLACE(@StartDate, " ", "T") + "]", CONSTRAINED) :

StrToMember ("[Dim Time].[StartDate].&[" + CStr(REPLACE(@EndDate, " ", "T") + "]", CONSTRAINED)

}

 SELECT NON EMPTY
        { [Measures].[EmpLeaveCount]        
        } ON COLUMNS,
        NON EMPTY
        { ([Dim Employee].[Name].[Name].ALLMEMBERS
         * [Dim Time].[LeaveDateMonth].[StartUtcMonth].ALLMEMBERS
         * [Dim Time].[LeaveDateYear].[StartUtcYear].ALLMEMBERS ) } ON ROWS
 FROM ( SELECT ( STRTOSET(@GroupID, CONSTRAINED) ) ON COLUMNS
        FROM ( SELECT ( STRTOSET(@SectionID, CONSTRAINED) ) ON COLUMNS
               FROM ( SELECT ( STRTOSET(@DeptId, CONSTRAINED) ) ON COLUMNS
                      FROM ( SELECT ( STRTOSET(@RegId, CONSTRAINED) ) ON COLUMNS
                             FROM ( SELECT ( STRTOSET(@ProjectId, CONSTRAINED) ) ON COLUMNS
                                    FROM ( SELECT ( [Relevant Dates] ) ON COLUMNS
                                            FROM [EmployeeLeave]))))))
 WHERE (
        IIF(STRTOSET(@ProjectId,CONSTRAINED).Count=1,STRTOSET(@ProjectId,CONSTRAINED),[Projects].[Projects].currentmember ),
        IIF(STRTOSET(@RegId,CONSTRAINED).Count=1,STRTOSET(@RegId,CONSTRAINED),[Region].[Region].currentmember),
        IIF(STRTOSET(@DeptId,CONSTRAINED).Count=1,STRTOSET(@DeptId,CONSTRAINED),[Department].[Department].currentmember),
        IIF(STRTOSET(@SectionID,CONSTRAINED).Count=1,STRTOSET(@SectionID,CONSTRAINED),[Section].[Section].currentmember),
        IIF(STRTOSET(@GroupID,CONSTRAINED).Count=1,STRTOSET(@GroupID,CONSTRAINED),[Group].[Group].currentmember)
        )

any idea ? Modification to be above query is greatly appreciated .

---

--RDBurmon

Answer 3

why don't you just use 

StrToMember ("[Dim Time].[StartDate].&[" + CStr(REPLACE(@StartDate, " ", "T") + "]", CONSTRAINED)

instead of STRTOMEMBER(@EndDate, CONSTRAINED) in the original query....

The [Relevant Dates] is not required

---

vinu

Answer 4

Hi

As I said, the SET declaration isn't seen in the SubSelect, so you need to replace the

SELECT [Relevant Dates] ON COLUMNS

with

SELECT {

StrToMember ("[Dim Time].[StartDate].&[" + CStr(REPLACE(@StartDate, " ", "T") + "]", CONSTRAINED) :

StrToMember ("[Dim Time].[StartDate].&[" + CStr(REPLACE(@EndDate, " ", "T") + "]", CONSTRAINED)

} ON COLUMNS

and you can get rid of the WITH SET declaration.

Hope that helps