locked
How to set current Week as Default one in SSRS RRS feed

  • Question

  • Hi Friends,

    I set week 39 2011 is the default week.. So my Question is when we open the Report each time it would display week 39 2011 data...If we are in 43rd week, by default week 39 2011 should change into week 43 2011...

    is that possible in SSRS....and one more point , Now we are getting the data from Cube.. 

    Could any one tell me pleaseeee If possible

    Tuesday, November 15, 2011 1:04 PM

Answers

  • In my experience if the report isn't selecting a default value when the report is run/previewed then it's likely that the syntax of your default value expression is incorrect.

    I haven't seen the issue you're describing Laurent, but I haven't worked with anything apart from SSRS 2008 R2 for some time. I frequently use expressions to set the start/end date for the current month.

    We're talking about two different things. You're talking about setting a date using a date picker parameter. I'm talking about populating available values for a parameter using a week attribute from the cube, and setting the default value of the parameter using an expression.

    Try changing the expression to... It seems the functions may have changed, I'm guessing a bit though

    ="[Time].[Fiscal Year And Week].&[" + CStr( Year(Today) ) + "-" + CStr( Week(Today) ) + "]"
    



    David Stewart | My Microsoft BI Blog | @dstewartbi
    • Marked as answer by Zilong Lu Tuesday, December 13, 2011 10:31 AM
    Wednesday, November 16, 2011 1:06 AM

All replies

  • Yes this is possible. Assuming you've got a week member in your date dimension that's keyed using W## YYYY, e.g. [Date].[Week].&[W43 2011], then you can construct this value using an expression in the parameter default value screen.

    The expression below will return the current week in the above format...

    ="[Date].[Week].&[W" + CStr(DatePart(DateInterval.WeekOfYear, Today)) + " " + CStr(Year(Today)) + "]"
    



    David Stewart | My Microsoft BI Blog | @dstewartbi
    Tuesday, November 15, 2011 2:59 PM
  • Hi dstewartbi, Thank you for your information......

    This is my default week for me in the report:   [Time].[Fiscal Year And Week].&[2010-49]

    I tried like this below

    =

    "[Time].[Fiscal Year And Week].&[" + cstr( DatePart(dateinterval.WeekOfYear,Today))+""
     

    +cstr(Year(today))+

    "]"

     

    It is showing error at WeekOfYear    and I tried with WeekName and WeekDay as well still showing an error

    Could you modify it ...

     

     

     

    Tuesday, November 15, 2011 3:17 PM
  • According to my experience, setting the default value through an expression is not very satisfying since the default value will only be calculated after the report has been refreshed once (if someone knows a solution to this issue, let me know.)

    My preferred solution is to get these default values from a dataset. For example the SQL query below will display the expected result without issue.

    SELECT '[Time].[Fiscal Year And Week].&[' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + '-' + CAST(DATEPART(WEEK, GETDATE()) AS VARCHAR) + ']'

    Tuesday, November 15, 2011 3:23 PM
  • What version of SSRS are you using? Check the output window and see why the expression is returning an error.

    I'm assuming from your example above that your fiscal year and week is keyed using the calendar year and week... The following should work though...

    ="[Time].[Fiscal Year And Week].&[" + CStr( Year(Today) ) + "-" + CStr( DatePart(DateInterval.WeekOfYear, Today) ) + "]"
    


     


    David Stewart | My Microsoft BI Blog | @dstewartbi
    Tuesday, November 15, 2011 3:24 PM
  • The parameter you are passing to your MDX dataset is a string not a member.

    You may have to modify your MDX query, so it makes use for the STRTOMEMBER function.

    Tuesday, November 15, 2011 3:27 PM
  • This is setting the default value of the parameter it doesn't affect the MDX query
    David Stewart | My Microsoft BI Blog | @dstewartbi
    Tuesday, November 15, 2011 3:28 PM
  • The MDX query designer will use the STRTOMEMBER functionally automatically. An example here:

    http://jsimonbi.wordpress.com/2011/03/22/using-a-date-parameter-in-ssrs-with-mdx/

    If the query is crafted by hand, then this is something one has to be aware of.

    Are we talking about two different things, or am I blatantly wrong? :-)

    Tuesday, November 15, 2011 3:52 PM
  • I am using SSRS 2008 Version ... it is showing error at WeekOfYear which you mentioned after DateInterval
    Tuesday, November 15, 2011 3:56 PM
  • Your function

    =

    "[Time].[Fiscal Year And Week].&[" + cstr( DatePart(dateinterval.WeekOfYear,Today))+""

    +cstr(Year(today))+

    "]"will display

    [Time].[Fiscal Year And Week].&[472011]

     

    David's function

    ="[Time].[Fiscal Year And Week].&[" + CStr( Year(Today) ) + "-" + CStr( DatePart(DateInterval.WeekOfYear, Today) ) + "]"


     will display

    [Time].[Fiscal Year And Week].&[2011-47]

    All as expected.

    Where does the error show up? Do you get some error message?

    Tuesday, November 15, 2011 4:09 PM
  • Just it is showing red mark under WeekOfYear but it is working in the report....

    and I have tried to set  the Default Date as well for my Report like this

    =

    "[Time].[Date No].&[" +cstr(Year(today))+"]"

    Report is working but it is not displaying Current Date as Default Date

    Tuesday, November 15, 2011 4:17 PM
  • Report is working but it is not displaying Current Date as Default Date

     

    If you click on View Report without providing a value for the parameter, does the current date show up?

    If yes, then it is probably the issue I mentioned above. Default values derived from expressions are, in my experience, only available after the report has been run once.

    If your parameter is mandatory, you might not even have the chance to have your default value take effect, since you will have to input something in the textbox before running the report.

    That is why I suggest getting values from a dataset with a simple SQL query without a FROM clause.

    Could not tell why you see this red mark.

    Tuesday, November 15, 2011 4:36 PM
  • [Time].[Date No].&[20111026].... this is the default one I passed Before ... Now I have written an expression like

    =

    "[Time].[Date No].&["+ cstr(year(today))+"-"+cstr(month(today))+"-"+cstr(day(today))+"]"

    this is to display Currentdate as Default date..........

    But when I run the report It was asking me to select the values... It is not displaying current date as Default date............

    Tuesday, November 15, 2011 4:56 PM
  • In my experience if the report isn't selecting a default value when the report is run/previewed then it's likely that the syntax of your default value expression is incorrect.

    I haven't seen the issue you're describing Laurent, but I haven't worked with anything apart from SSRS 2008 R2 for some time. I frequently use expressions to set the start/end date for the current month.

    We're talking about two different things. You're talking about setting a date using a date picker parameter. I'm talking about populating available values for a parameter using a week attribute from the cube, and setting the default value of the parameter using an expression.

    Try changing the expression to... It seems the functions may have changed, I'm guessing a bit though

    ="[Time].[Fiscal Year And Week].&[" + CStr( Year(Today) ) + "-" + CStr( Week(Today) ) + "]"
    



    David Stewart | My Microsoft BI Blog | @dstewartbi
    • Marked as answer by Zilong Lu Tuesday, December 13, 2011 10:31 AM
    Wednesday, November 16, 2011 1:06 AM
  • Steps to reproduce the issue:

    - create a first parameter @ParameterWithoutDefault without a default value

    - create a second parameter @ParameterWithCalculatedDefaultValue with a default value set by expression "=123"

    -> The default value for the second parameter will only be resolved after a value has been provided for the first parameter.

    Move down @ParameterWithoutDefault, so it will be the second parameter in the sequence.

    -> Now the @ParameterWithCalculatedDefaultValue will be filled correctly from the start.

    Wednesday, November 16, 2011 8:40 AM
  • Do you have other parameters in your report? Do they have default values?
    Wednesday, November 16, 2011 8:40 AM