Using a Lookup function in a Parameter Default

Question

Goal: To provide dynamic date calculations for SSRS Parameters to be used in scheduling.

I have a report containing two date parameters, DateRangeBegin and DateRangeEnd. The problem we are running into is that users want to schedule this report for a date range like "Month to Date", "Year to Date", "Last Week", etc. When a user goes to schedule the report, they can only provide static dates for these two parameters.

The idea I had was to create a dataset that will calculate these values and be referenced in another parameter called DynamicDate. The user would then select "Yesterday", from the DynamicDate parameter, and the DateRangeBegin and DateRangeEnd parameters would get updated with the calculated values from the dataset.

The dataset would be something like this:

Select
    2 as DateCalcId,
    'Yesterday' as DateCalcDescription,
    CONVERT(VARCHAR, DATEADD(DAY,-1,GETDATE()), 101) as DateCalcBegin,
    CONVERT(VARCHAR, DATEADD(DAY,-1,GETDATE()), 101) as DateCalcEnd
UNION ALL
Select
    1 as DateCalcId,
    'Today' as DateCalcDescription,
    CONVERT(VARCHAR, DATEADD(DAY,0,GETDATE()), 101) as DateCalcBegin,
    CONVERT(VARCHAR, DATEADD(DAY,0,GETDATE()), 101) as DateCalcEnd
UNION ALL
Select
    3 as DateCalcId,
    'Month to Date' as DateCalcDescription,
    CONVERT(VARCHAR,(CONVERT(datetime, CONVERT(VARCHAR, Year(GetDate())) + '-' + Convert(Varchar,Month(GetDate())) + '-01')), 101) as DateCalcBegin,
    CONVERT(VARCHAR, DATEADD(DAY,0,GETDATE()), 101) as DateCalcEnd
order by
    DateCalcId

I think the function I'm wanting to use is the Lookup() function in the date parameters for their default values, but I'm having a little trouble with the syntax. So far I have:

=Lookup(Parameters!DynamicDate.Value, Fields!DateCalcId.Value, Fields!DateCalcBegin.Value, "CalculatedDates")

But I'm getting this error:

A Value expression used for the report parameter 'DateRangeBegin' refers to a field. Fields cannot be used in report parameter expressions.

Does anyone know how to get this working? Or is there another way people have done this?

Josh Jay Josh Jay · Accepted Answer · 2014-03-17T13:40:34

So I think I've come up with a pretty good solution that fits my requirements.

I really like the idea of having these dynamic date range pairs being calculated in a SQL Dataset because they can be reused across multiple reports, don't require a custom dll to be created, and the pair can be calculated in a single record.

So the solution is to have a single stored procedure that takes a nullable parameter, where passing in null returns the entire set, but passing in the DateCalcId will return a single record that can then be attributed to the range beginning and end parameters.

So the entire dataset would populate the Available Values for the first parameter (@DynamicDate), where the Value field is the "DateCalcId" column, and the Label field is the "DateCalcDescription" column.

Then the date range parameters default values would be tied to the single result dataset using the @DynamicDate value as the input to the stored procedure.

This allows the user to select the Dynamic Date and the date range parameters are defaulted to the calculated dates, and the user is able to overwrite these dates if they desire.

If the dates should always be calculated based on the value selected, then the date parameter Available values can be set to the single result dataset as well.

Here is the stored procedure:

Create PROCEDURE [CNF].[RptCalculatedDateRanges]
    @DynamicDateId int = null
AS
BEGIN
    SET NOCOUNT ON;

    Declare @Today datetime = convert(varchar,getdate(),101)

    Select
        *
    from
        (
            Select
                2 as DynamicDateId,
                'Yesterday' as DynamicDateDescription,
                DATEADD(DAY,-1,@Today) as DynamicDateBegin,
                DATEADD(DAY,-1,@Today) as DynamicDateEnd
            UNION ALL
            Select
                1 as DynamicDateId,
                'Today' as DynamicDateDescription,
                @Today as DynamicDateBegin,
                @Today as DynamicDateEnd
            UNION ALL
            Select
                3 as DynamicDateId,
                'Month to Date' as DynamicDateDescription,
                CONVERT(VARCHAR,(CONVERT(datetime, CONVERT(VARCHAR, Year(@Today)) + '-' + Convert(Varchar,Month(@Today)) + '-01')), 101) as DynamicDateBegin,
                @Today as DynamicDateEnd
        ) D
    where
        @DynamicDateId = D.DynamicDateId or
        @DynamicDateId is null
    order by
        DynamicDateId
END

Using a Lookup function in a Parameter Default

2 Answers