3

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].&amp;[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.

MariusCC
  • 617
  • 2
  • 6
  • 11

2 Answers2

8

Replace the hard-coded

[Date].&amp;[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:
    alt text

  • ToDate:
    alt text

Community
  • 1
  • 1
dance2die
  • 35,807
  • 39
  • 131
  • 194
  • @Sung, It doesn't work. Some more elements: I'm trying to deploy the reports in a TFS instance (using the reporting services for that instance) and the actual value field looks like this: [Date].[Date].&[2008-09-26T00:00:00] – MariusCC Mar 11 '09 at 12:50
1

You actually cant use the TSQL Date formats you ahve to use the .net methods:

=Now()

=DATEADD("m", -1, now())
Glennular
  • 17,827
  • 9
  • 58
  • 77