SSRS reports to have parameters with increasing time intervals

Question

How can I automate a report with SSRS having to execute a stored procedure with start date and end date. The way the report has to run is:- 1st run:- Jan 1 till Jan 15 (start date Jan 1 and end date DateAdd function to add 15 days) 2nd run:- Jan 1 till Jan 31 (how to program end date here dynamically) 3rd run:- Jan 1 till Feb 15 4th run:- Jan 1 till Feb 28/29 till march 31st After March, the quarter changes so it has to be run with:- kth run:- April 1 till April 15 and so on till the quarter changes in July so , kth run:- July 1 till July 15 and so on till quarter changes again Oct kth run: Oct 1 to Oct 15 and so on...

Any ideas will be greatly appreciated.

Are u talking about Data Sriven Subscription. We dont have SQL server enterprise edition. The way I have automated earlier reports is by using defaultValue tag using a dataset having query within ReportParameter xml tag. — anu

anu anu · Accepted Answer · 2016-01-19T20:54:57

I finally found out a way for doing this by creating a new stored procedure which will be run in SSRS daily. This stored procedure will provide the values for the input parameters required by the other stored procedure 'spCalculateSomething' for working based on the quarterly conditions:

CREATE PROCEDURE automated
AS
BEGIN
Declare @startDate1 DateTime;
    Declare @endDate1 DateTime;
    Declare @month int;
    set @endDate1=GETDATE();
    set @month= DATEPART(mm,Getdate());

    if(@month=1 or @month=2 or @month=3)
        begin
            set @startDate1= '01/01/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate())) --'01/01/2016'
            set @endDate1='03/31/'+ CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
        end
    else if (@month=4 or @month=5 or @month=6)
        begin
            set @startDate1='04/01/'+ CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
            set @endDate1='06/30/'+ CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
        end
    else if (@month=7 or @month=8 or @month=9)
        begin
            set @startDate1='07/01/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
            set @endDate1='09/30/'+ CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
        end
    else if (@month=10 or @month=11 or @month=12)
        begin
            set @startDate1='10/01/'+ CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
            set @endDate1='12/31/'+ CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
        end

    EXEC    Poin.[dbo].[spCalculateSomething]
        @startDate = @startDate1,
        @endDate = @endDate1
END

The reason this will work for the above questions of having end date extended to 15 days is because even if you run this in first quarter, lets say 15th february, the endDate of 31st march will not affect as the records in our main table where processing is going on will be till 15th february. So this works for me to run every day for a specific quarter based on the month it is run.

SSRS reports to have parameters with increasing time intervals

1 Answers