SQL Reporting Services 2005 - How to get current date as a ReportParameter
Solution 1
Replace the hard-coded
[Date].&[2008-09-26T00:00:00]
to formula
=DateAdd("m", -1, Now)
For "ToDate", just pass a formula that returns current date
=Now
Now the result looks something like this.
<ReportParameters>
<ReportParameter Name="FromDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=DateAdd("m", -1, Now)</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>FromDate</Prompt>
</ReportParameter>
<ReportParameter Name="ToDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=Now</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>ToDate</Prompt>
</ReportParameter>
</ReportParameters>
[UPDATE]
It looks like I have forgotten to paste <ReportParameters>
correctly for ToDate; it's updated.
Above RDL was generated by configuring Report Parameter.
This is how I have configured date in GUI.
FromDate:
ToDate:
Solution 2
You actually cant use the TSQL Date formats you ahve to use the .net methods:
=Now()
=DATEADD("m", -1, now())
MariusCC
Unix aficionado, lazy enough to try to automate everything. Had some years in purgatory using Windows and MS products but came back to Unix.
Updated on March 12, 2020Comments
-
MariusCC about 4 years
I have some working reports that must be deployed on SSRS. One more customization that I want to be added is to automatically select the FromDate as today - 1 month, and ToDate as today.
Specifically, I want to replace the fragment bellow with a piece that accomplish the requirements above:
<ReportParameter Name="FromDate"> <DataType>String</DataType> <DefaultValue> <Values> <Value>[Date].&[2008-09-26T00:00:00]</Value> </Values> </DefaultValue> <Prompt>From Date</Prompt> <ValidValues> <DataSetReference> <DataSetName>FromDate2</DataSetName> <ValueField>ParameterValue</ValueField> <LabelField>ParameterCaption</LabelField> </DataSetReference> </ValidValues> </ReportParameter> <ReportParameter Name="ToDate"> <DataType>String</DataType> <Prompt>To Date</Prompt> <ValidValues> <DataSetReference> <DataSetName>ToDate</DataSetName> <ValueField>ParameterValue</ValueField> <LabelField>ParameterCaption</LabelField> </DataSetReference> </ValidValues> </ReportParameter>
Thanks in advance.