November 3, 2014 at 12:02 pm
Is there any way in SSRS to do the following:
I have a very basic query from SQL that feeds a simple table report static data like amount billed, hours billed, etc (Y-Axis). by month in a year (X-Axis).
My client wants to be able to enter the 'budget' number which could change yearly, monthly or even mid-monthly, it is totally dynamic. Not so hard using a parameter to enter, however, they don't want to have to enter past budget data each time they run the report - they would like to only have to change the current budget field so I would need to store the previously input data for 12 months running.
First is this possible, and second how?
November 3, 2014 at 12:37 pm
If your report is based on a stored procedure, it's not hard at all. Say your report is based on a parameterized stored procedure, something like
CREATE PROC GetReportData @Param1 VARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
SELECT ... FROM MyTable WHERE SomeColumn = @Param1
END
You could create a "logging" stored procedure (just so the logic is in one place and you can reuse it elsewhere), like this:
CREATE PROC [dbo].[LogReportExec]
@ReportName VARCHAR(255)
,@ParamValue VARCHAR(255)
AS
INSERT INTO LogReportParam(ReportName, ParamValue, RunDateStamp)
VALUES (@ReportName, @ParamValue,GETDATE());
Then you would just execute the logging stored procedure after your SELECT statement. This is the stored procedure I used to test it:
ALTER PROC [dbo].[uspEnrollmentDemographics]
@ProtocolNo VARCHAR(20)
AS
SET NOCOUNT ON;
BEGIN
SELECT enrollmentID
, e_ProtocolNo
, enrollDate
, PatientID
, Gender
, BirthDate
, DATEDIFF(yyyy,Birthdate,EnrollDate) AS Age
, ABS(DATEDIFF(wk,OpenToEnrollDate,enrollDate))+1 AS EnrollWeek
FROM (Protocol p INNER JOIN Enroll e ON p.ProtocolNo = e.e_ProtocolNo) INNER JOIN Patient pa ON e_PatientID = PatientID
WHERE e_ProtocolNo = @ProtocolNo;
EXEC dbo.LogReportExec 'DemographicsReport',@ProtocolNo;
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply