How to use internal variables in Report Builder Textboxes

ssrs

I have a query in my dataset that is calculating a date range:

DECLARE @StartDate AS DATE = DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 1, 0)
DECLARE @EndDate AS DATE = DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 0)

I've setup the parameters as an internal value with no prompt.

I'm trying to use these variables/parameters in a report title:

"Resource Activity Between Two Statuses - " & Parameters!StartDate.Value & " - " & Parameters!EndDate.Value

…and I'm getting an error. Unsure if I'm stepping outside of the design of what SSRS can do, or if I'm going about it the wrong way, but the end result should be obvious – I'm wanting to show the date range in the title of the report.

The report parameter ‘StartDate’ has a DefaultValue or a ValidValue that depends on the report parameter “StartDate”. Forward dependencies are not valid.

Best Answer

Ok, found out that if you're using SSRS, you cannot declare variables in the query and expect to use them on the layout of the report. If you do, you need to declare and set the values in SSRS, not in the query.

Here is the page I used to set dates for start and end dates for the variables through SSRS: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a62672c2-4005-4dc6-935f-7c90e9e55491/ssrs-expression-retrieve-last-week?forum=sqlreportingservices

Related Question