none
pass parameter to ssrs report based on cube

    Question

  • hi folks:

      I am working on a weekly report based on cube. Users are able to pull out data by themselves using excel. Now they want this to be scheduled to run on a weekly basis via SSRS. The date hierarchy (date.calendar)  is like this: Year -> Quarter -> Month -> Week

     I want users to be able to pick any week (only one week) they want and run the report.  I understand I need to create a parameter @week .

    The problem is how to populate this parameter @week in mdx since the source is a cube db.

     Any idea?

     Thanks

     Hui


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Thursday, July 17, 2014 11:00 PM

Answers

  • Thanks Charlie, your solution is pretty close now. When I try out the shareddataset via cube, I found the following statement in the query via query designer:

    WHERE ( [Checkoutdate].[Fiscal Year Calendar].[Week].&[23]&[2014]&[2]&[6] )

    23 is the week no, 2014 is the year, 2 is the quarter and 6 is the month. I intend to create 4 parameters so that users could pick year, quarter , month and week. 

    The question here is the sequence, do I have to strictly follow the sequence  [week - year - quarter - month] or it doesn't matter? 

    SELECT {STRTOMEMBER("[Date].[Calendar Weeks].[Calendar Week].&["+@Week+"]&["+@Year+"]&["+@quarter+"]&["+@month+"]
    ")} ON 0

    or can I go like this as it's more intuitive:  

    SELECT {STRTOMEMBER("[Date].[Calendar Weeks].[Calendar Week].&["+@year+"]&["+@quarter+"]&["+@month+"]&["+@week+"]
    ")} ON 0


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Hi Cat_ca,

    Sorry for the delay.

    You needn't force uses to pick year, quarter , month and week in parameter area. You can just create a DateTime data type parameter which will display as a calendar to users. Then create another 4 hidden parameters year, quarter, month and week, and set those parameters default value as
    Year      =DatePart("yyyy",Parameters!Date.Value)
    Quarter  =DatePart("qq",Parameters!Date.Value)
    Month    =DatePart("mm",Parameters!Date.Value)
    Week     =DatePart("ww",Parameters!Date.Value)
    In this case, users just need to pick one parameter value for the Date paramete. Then you can use the query
    SELECT {STRTOMEMBER("[Date].[Calendar Weeks].[Calendar Week].&["+@Week+"]&["+@Year+"]&["+@quarter+"]&["+@month+"]")} ON 0
    in your query designer.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, July 22, 2014 2:08 AM
    Moderator

All replies

  • Hi Cat_ca,

    According to your description, you want to add a parameter to your MDX query to filter the week data, right?

    In this case, you can STRTOMEMBER function to achieve your requirement. Here is a sample query for your reference.

    SELECT {[Measures].[Internet Sales Amount]} ON 0,
    [Date].[Date].MEMBERS ON 1
    FROM
    (
    SELECT {STRTOMEMBER("[Date].[Calendar Weeks].[Calendar Week].&["+@Week+"]&["+@Year+"]")} ON 0
    FROM [Adventure Works]
    )

    Then create a Week parameter in Query Designer. At last use the expression below to set the default value of Week parameter in parameter properties window.
    =DatePart("ww",Parameters!Date.Value)

    The screenshots below are for you reference.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, July 18, 2014 5:55 AM
    Moderator
  • Thanks Charlie, your solution is pretty close now. When I try out the shareddataset via cube, I found the following statement in the query via query designer:

    WHERE ( [Checkoutdate].[Fiscal Year Calendar].[Week].&[23]&[2014]&[2]&[6] )

    23 is the week no, 2014 is the year, 2 is the quarter and 6 is the month. I intend to create 4 parameters so that users could pick year, quarter , month and week. 

    The question here is the sequence, do I have to strictly follow the sequence  [week - year - quarter - month] or it doesn't matter? 

    SELECT {STRTOMEMBER("[Date].[Calendar Weeks].[Calendar Week].&["+@Week+"]&["+@Year+"]&["+@quarter+"]&["+@month+"]
    ")} ON 0

    or can I go like this as it's more intuitive:  

    SELECT {STRTOMEMBER("[Date].[Calendar Weeks].[Calendar Week].&["+@year+"]&["+@quarter+"]&["+@month+"]&["+@week+"]
    ")} ON 0


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Friday, July 18, 2014 6:45 PM
  • Also Charlie, in your example, @week and @year parameters. If I want to use a query to populate , do I have to use mdx query or sql query? 

    thanks


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Friday, July 18, 2014 9:42 PM
  • Thanks Charlie, your solution is pretty close now. When I try out the shareddataset via cube, I found the following statement in the query via query designer:

    WHERE ( [Checkoutdate].[Fiscal Year Calendar].[Week].&[23]&[2014]&[2]&[6] )

    23 is the week no, 2014 is the year, 2 is the quarter and 6 is the month. I intend to create 4 parameters so that users could pick year, quarter , month and week. 

    The question here is the sequence, do I have to strictly follow the sequence  [week - year - quarter - month] or it doesn't matter? 

    SELECT {STRTOMEMBER("[Date].[Calendar Weeks].[Calendar Week].&["+@Week+"]&["+@Year+"]&["+@quarter+"]&["+@month+"]
    ")} ON 0

    or can I go like this as it's more intuitive:  

    SELECT {STRTOMEMBER("[Date].[Calendar Weeks].[Calendar Week].&["+@year+"]&["+@quarter+"]&["+@month+"]&["+@week+"]
    ")} ON 0


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Hi Cat_ca,

    Sorry for the delay.

    You needn't force uses to pick year, quarter , month and week in parameter area. You can just create a DateTime data type parameter which will display as a calendar to users. Then create another 4 hidden parameters year, quarter, month and week, and set those parameters default value as
    Year      =DatePart("yyyy",Parameters!Date.Value)
    Quarter  =DatePart("qq",Parameters!Date.Value)
    Month    =DatePart("mm",Parameters!Date.Value)
    Week     =DatePart("ww",Parameters!Date.Value)
    In this case, users just need to pick one parameter value for the Date paramete. Then you can use the query
    SELECT {STRTOMEMBER("[Date].[Calendar Weeks].[Calendar Week].&["+@Week+"]&["+@Year+"]&["+@quarter+"]&["+@month+"]")} ON 0
    in your query designer.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, July 22, 2014 2:08 AM
    Moderator