Fields cannot be used in report parameter expression

Question

I have to set the start_date of my report depending of a report parameter. The time stamps are calculated in a database query.

My expression looks like this:

=SWITCH (
    Parameters!report_type.Value = 1,First(Fields!daily_start.Value, "Timestamps")
    ,Parameters!report_type.Value = 2,First(Fields!weekly_start.Value, "Timestamps")
    ,Parameters!report_type.Value = 3,First(Fields!monthly_start.Value, "Timestamps")
)

Unfortunately I get the error message:

A value expression used for the report parameter 'time_from' refers to a field. Fields cannot be used in report parameter expression

I know, that this is not allowed because SSRS cannot be sure in which order datasets are called. But I think this is not dangerous.

All time stamps are received by query without parameter. The parameter report_type is selected by a user before the report will be generated.

Can someone give me a hint for a workaround?

Could you use fixed hidden parameters for your daily_start, weekly_start, monthly instead of fields ? — jbl

Chris Latta Chris Latta · Accepted Answer · 2012-11-27T23:34:02

Here's the workaround - get the value using SQL.

Create a new Dataset called StartDates:

SELECT CASE 
    WHEN @report_type = 1 THEN daily_start
    WHEN @report_type = 2 THEN weekly_start 
    WHEN @report_type = 3 THEN monthly_start
END AS StartDate
FROM MyTable

You already have the @report_type and @time_from parameters. With the @time_from parameter, set its Default Values to Get values from a query using the StartDates dataset and the Value field StartDate.

Now, you'd think this might be enough to make it work - you're referencing this query as the default value and as you change the @report_type parameter the other parameters refresh, but the first date in the @time_from parameter never changes. That's because the refresh happens on the Available Values query, not on the Default Values query.

So you also need to wire up the Available Values query to the StartDates query. Now your query will fire on the change of @report_type and the default value will be set to the appropriate date for your selection.

Fields cannot be used in report parameter expression

3 Answers