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?

Epilimnion

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

Answer 1

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.

Claudicant

what if i can only do this in fetchxml? – Delgado

Answer 2

I switched from a query to Stored Procedure and was getting this error. Things I tried:

• Ensured I had sufficient permission on the database (you need EXEC rights or DBO to run teh sproc)
• Delete the existing parameters (and then use refresh fields to refresh/get the correctly named ones back)
• Remove the square brackets around the stored procedure if you've specified that

Surround

Removing square brackets around the Procedure worked for me. Thanks! – Salutatory

Answer 3

Sometimes, Expressions can get a bit verbose. I have created a Report Code Function and then used that as the Parameter Value.

For example, I created a Code function called "CalculateDateSet" and then set the Report Parameter to this expression:

"=Code.CalculateDateSet(Parameters!Month.Value, Parameters!Year.Value"

Pika

Could you be so kind as to elaborate a bit on your proposed workaround? – Miserere