June 3, 2011 at 8:09 am
Hi there,
I'm new to SSRS so really need some help off you guys.
I have a SQL query that basically has multiple calculations credit card funded, expiry and start dates etc. At the beginning of the report I have declared a variable called @ReportEndDate, the idea was originally to run the script manually and simply change this date each time. I have now decided to create a SSRS report for this script and wondered how I can get this variable so that the date can be entered on running the SSRS report. I thought this might have been a Report Parameter but having a look I am now completely baffled by the expression builder, or by selecting columns to filter on but none of these work. Can anyone help me with this?
The variables are here, the start date is to be left as hard coded its the end date I need to allow users to edit.
DECLARE @ReportStartDate smalldatetime,
@ReportEndDate smalldatetime
SELECT @ReportStartDate = '2000-01-01',
@ReportEndDate = '2009-09-29'
An example of the calculations where the @ReportEdnDate variable is used:
INSERT INTO @HistoricCardLocationDetails
(Location_Id,
Card_Id)
SELECT DISTINCT Location_Id,
Card_Id
FROM Locations_Cards (NOLOCK) LCA
WHERE DATEDIFF(day, LCA.From_Date, @ReportEndDate) >= 0
AND ((LCA.To_Date IS NULL) OR (DATEDIFF(day, @ReportEndDate, LCA.To_Date) >= 0))
And another
(
Card_Id,
Issue_Number,
Product_Id,
Ordered_Date,
VBN_Funded_Date
)
SELECT CAR.Card_Id,
CAR.Issue_Number,
CAR.Product_Id,
CAST(CAST(CONVERT(SMALLDATETIME, ORD.Order_Date, 102) AS VARCHAR(11)) AS DATETIME),
CAST(CAST(CONVERT(SMALLDATETIME, CAR.VBN_Funded_Date, 102) AS VARCHAR(11)) AS DATETIME)
FROM Cards CAR (NOLOCK)
INNER JOIN Orders_Cards (NOLOCK) ORC
ON ORC.Card_Id = CAR.Card_Id
INNER JOIN Orders (NOLOCK) ORD
ON ORD.Order_Id = ORC.Order_Id
WHERE CAR.VBN_Funded_Date IS NOT NULL
AND DATEDIFF(day, @ReportStartDate, CAR.VBN_Funded_Date) >= 0
AND DATEDIFF(day, CAR.VBN_Funded_Date, @ReportEndDate) >= 0
I didn't write the script so not 100% sure exactly what is going on with it.
June 3, 2011 at 10:16 am
Back to basics. You have a dataset query that feeds your report. In that query you have a parameter @ReportEndDate. When you click OK and close the dataset it will create the @ReportEndDate parameter for the report. Look in the Parameters Folder and right click on the @ReportEndDate Parameter and go to properties. Change the data type to Date and you should be good to go. Your report dataset query should be able to use the parameter input just as it would in SSMS with T-SQL, unless I misunderstand you don't need expressions unless you need to manipulate the value of the @ReportEndDate parameter before it gets to the dataset.
June 6, 2011 at 12:51 am
Hi there thanks for the reply, I'll give this a try. The idea is to be able to allow users to enter the report end date as they run the report, this may be different each time whereas the start date will remain fixed.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply