locked
Parameter for date value RRS feed

  • Question

  •  I am trying to use a parameter as a date in a report. The parameter contains today's date, "02/26/2009". The parameter is named "@ReportDate".

    I am trying to use it in my MDX query to get the day before yesterday. I've tried every variation of [Time Dimension].[Date].&[@ReportDate].PREVMEMBER.PREVMEMBER I can think of, but nothing works. The above example, for instance, results in the error, "The @ReportDate string cannot be converted to the date type".

    What is the correct way to use SSRS parameters in this manner?
    Thursday, February 26, 2009 5:48 PM

Answers

    1. On the Layout tab of your report, open the Toolbox and select Textbox from the Report Items list.
    2. Click and drag your mouse in an empty are of the report to draw the control.
    3. Right-click the control and select Expressions...
    4. Paste the portion of your query where you're doing the concatenation. 
      The expression should look like the following (or similar):

      ="[Time Dimension].[Date]." & Parameters!<param name>,Value & ".PREVMEMBER.PREVMEMBER "


    Now view the report and look at the string displayed in the text box.  Is it what you expect? The value may not be in the expected format.

    You can explicitly convert the parameter value using the CDate() function i.e. 

        CDate(Parameters!<param name>.Value)



    Orlanzo
    • Marked as answer by Tab Alleman 2 Thursday, February 26, 2009 8:48 PM
    Thursday, February 26, 2009 7:46 PM

All replies

  • I don't have any experience with the construction of multi-dimensional queries.  However, set the expression as the value of a text box and look at the resultant string.  Is it what you expect?  Do you need to explicitly convert the value to a date type?


    Orlanzo
    Thursday, February 26, 2009 6:08 PM
  •  I don't know how to do what you're proposing:  "set the expression as the value of a text box and look at the resultant string"...   I'm very new to SSRS.

    Perhaps I should mention that due to the complexity of my MDX query, I had to hand-type it, rather than use the query-builder.   Is that where the textboxes come into play?
    Thursday, February 26, 2009 6:12 PM
    1. On the Layout tab of your report, open the Toolbox and select Textbox from the Report Items list.
    2. Click and drag your mouse in an empty are of the report to draw the control.
    3. Right-click the control and select Expressions...
    4. Paste the portion of your query where you're doing the concatenation. 
      The expression should look like the following (or similar):

      ="[Time Dimension].[Date]." & Parameters!<param name>,Value & ".PREVMEMBER.PREVMEMBER "


    Now view the report and look at the string displayed in the text box.  Is it what you expect? The value may not be in the expected format.

    You can explicitly convert the parameter value using the CDate() function i.e. 

        CDate(Parameters!<param name>.Value)



    Orlanzo
    • Marked as answer by Tab Alleman 2 Thursday, February 26, 2009 8:48 PM
    Thursday, February 26, 2009 7:46 PM
  • Ok, I figured out what you meant about populating a text box, and I tried it, and found that indeed it was not returning the format that I expected.   So I corrected that, and now it returns a value like "02/26/2009".   I played with the report parameter dialog box, setting the parameter to return a DateTime data type, and to use the desired Value field and Label field.   But still when I put it in my query, it doesn't work.

    In the query itself, where I use the parameter, it looks like this:

    [Time Dimension].[Date].[@ReportDate].PREVMEMBER.PREVMEMBER,

    and then in the dataset definition, under the parameters tab, I have defined the ReportDate parameter as =Parameters!ReportDate.Value

    The query doesn't error, but it returns no rows, which shouldn't happen if it runs properly.

    Thursday, February 26, 2009 8:23 PM
  • Well, it sounds like you're heading in the right direction.

    As I mentioned in my original post, I'm not familiar with multi-dimensional queries.  Its been years since I've had an opportunity to play with them.  But, the fact that it isn't returning any rows now that you've gotten the formatting correct may be related to the underlying table being queried. 

    Is the "date" in the table stored in the same format as you're using in your query?  In the database, does the date include the time as well i.e. "2009-02-25 21:46:33.653", "2009-02-25", or "02/25/2009"?  You may need to adjust the parameter value further.  (You can do that on the parameters tab.)

    Or, you may need to modify your query to use the Convert() function in Transact-SQL or a similar function in the database language of the server you're querying.


    Orlanzo
    Thursday, February 26, 2009 8:41 PM
  • Ok, with further experimentation, I've got it.

    In the parameters, tab, I defined my parameter as ="[Time Dimension].[Date].[" & Parameters!ReportDate.Value & "]"

    and then in the query, I use:  

    STRTOMEMBER(@ReportDate).PREVMEMBER.PREVMEMBER,

    and that one works.


    Thursday, February 26, 2009 8:45 PM
  • Thanks very much for pointing me in the right direction!   Your hint to look at my parameter expression was the tip I needed.
    Thursday, February 26, 2009 8:47 PM