asked on

SSRS 2016 how to reset the value of a variable using a function

I am trying to convert a stored proc to an SSRS report for a lot of reasons. We are tying to get away from writing stored procs for reports and move more towards a self service BI type thing.

One of the things I am struggling with is how to reproduce this type of thing in SSRS:

* Stored proc has variables @startDate, @endDate
CREATE PROC usp_report1(@startDate, @endDate)
AS ....
SELECT @startDate = fn_convert_date(@startDate)
SELECT @endDate = fn_convert_date(@startDate)

The function fn_convert_date takes the date passed in and converts it to the local time zone of the state running the report.

Is there a way to reproduction this behavior in SSRS?

HainKurt

whats the rest of that sp?

if it is a select statement, then embed into query as

select ...
from ...
where somedatecol between dbo.fn_convert_date(@startDate) and  dbo.fn_convert_date(@endDate)

Open in new window

are those out parameters?
The code you put does not make sense to me...

sqlagent007

ASKER

the @startDate and @endDate are input parameters for a stored procedure.

sqlagent007

ASKER

So what you are saying is that within SSRS Report Builder, I can just create a dataset with this statement:

SELECT @startDate = fn_convert_date(@startDate)

HainKurt

you can use this query

SELECT fn_convert_date(@startDate) as StartDate

Open in new window

and get the result

or maybe

myvar = fn_convert_date(@startDate)

Open in new window

not sure how / where it is used...

sqlagent007

ASKER

Ok, but I need to be able to use these variables everywhere in the report and the report will have about 8 datasets. How can I convert the dates 1 time, then use that converted date in all my datasets for the entire report?

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlagent007

ASKER

thx