SSRS format Today() to dd/MM/yyyy

Question

nf75 on Sun, 28 Apr 2013 09:26:21


Hi,

In SSRS 2008 I have a report with 4 parameters:

1. TYPE: a Visible Text parameter, including 2 values "7" and "30", indicates number of days to go backwards from Today's date.

2. DATE: a Visible Date/Time parameter with Default value, Expression: =Today() .

3. FromDate: a Visible Text parameter with Default value, Expression:  =iif(Parameters!TYPE.Value=7,FormatDateTime(DateAdd("d",-7,parameters!DATE.Value),dateformat.ShortDate),FormatDateTime(DateAdd("d",-30,parameters!DATE.Value),dateformat.ShortDate))

4. ToDate:  a Visible Text parameter with Default value, Expression: =FormatDateTime(DateAdd("d",-1,parameters!DATE.Value),dateformat.ShortDate)

The Date member in the Dataset MDX Query is in dd/MM/yyyy format.

The results of the parameter are all in MM/dd/yyyy.

Therefore I'm getting this error: "the restrictions imposed by the constrained flag in the strtomember function were violated date parameter"

I tried some different technics but always got different errors.



What should I do?

Thanks!






Replies

Srini Koduru on Sun, 28 Apr 2013 15:50:00


Hi,

Try using Format(<yourdate>,"dd/MM/yyyy").

-Srini

nf75 on Sun, 28 Apr 2013 16:20:11


Hi,

Thanks for the reply. i've tried the following:

For DATE parameter -  Expression: =Format(Today,"dd/MM/yyyy")      **it gave me a sytax error when i wrote it 'Today()'**

and when I run the report I get : "The property 'DefaultValue' of report parameter 'DATE' doesn't have the expected type"

disssss on Mon, 29 Apr 2013 02:44:05


For DATE parameter -  Expression: =Format(Today,"dd/MM/yyyy")      **it gave me a sytax error when i wrote it 'Today()'**
Try it with the () anyway - seems to work despite the syntax error in the expression editor.

nf75 on Mon, 29 Apr 2013 04:43:15


Hi,

 

I've tried that

=Format(Today(),"dd/MM/yyyy")

still getting the same message: "The property 'DefaultValue' of report parameter 'DATE' doesn't have the expected type"

Srini Koduru on Mon, 29 Apr 2013 11:09:36


Hi,

By looking at your error message, STRTOSET constrained is failing. What it mean is - the parameter you passed is not in right format to be converted to set. I don't know where you are using the format() function. Please look on your dataset parameter, where you are passing date. There you should use "dd/MM/yyyy" format, not any place else.

-Srini.

nf75 on Mon, 29 Apr 2013 11:15:04


Hi Srini,

I don't understand what you mean... do you refer to the first error message or the second one after I tried the other suggestion..?

Srini Koduru on Mon, 29 Apr 2013 11:18:05


Hi,

First Error message.

-Srini

nf75 on Mon, 29 Apr 2013 11:45:24


hi srini,

yes i realize that its a different format because as you can see in the screenshot from the first post it's on MM/dd/yyyy in the running mode,

and in my dataset it's dd/mm/yyyy

Select {[Measures].[Cost],[Measures].[Actives],[Measures].[Cost/Active],[Measures].[Regs],[Measures].[Cost/Reg]} On Columns, HIERARCHIZE( NONEMPTYCROSSJOIN ({Filter ({[Getways Dim].[Gateways].[Gateway].members},(([Measures].[actives]) > 0) OR ([Measures].[regs] > 0))})) On Rows From [BOEasyForex2] WHERE ( [Accounts Dim].[Accounts].[client].&[7] ,{STRTOMEMBER(@FromDate, CONSTRAINED) : STRTOMEMBER(@ToDate, CONSTRAINED) })


Srini Koduru on Mon, 29 Apr 2013 12:15:22


Hi,

If you are still having errors - please check all the steps (I am thinking you are on SSRS 2008 R2)

  1. Date parameter is Date/Time - Its values should be date/time (no format is used). In you second error message it is indicated that you are getting error. Why are you setting the Format on this parameter default value?
  2. FromDate parameter is text - you can use Format to display in dd/MM/yyyy (MM capitals)
  3. ToDate parameter is text  - you can use Format to display in dd/MM/yyyy (MM capitals)

On the dataset properties, dataset parameters FromDate and ToDate should be linked to Report Parameters FromDate and ToDate.

Let me know if you need any further help.

-Srini