none
Need help Report Bulder: how do i find last 3 months data

    Question

  • Hello Everyone,

    We are generating few reports using SQL Server 2008R2 Report builder 3, we have a data dim table (in this dim i have Year, Quarterly, Month column) this reports contain HR domain data, now as per the requirements i need to create report that i can find last 3 months (not quarterly based) data inn report. 

    please help me how we can get last 3 months data (in the report have year and month 2 parameters,  when user select Month as Aug, we need to get Jun, July, august data)

    thank you for your help

    Srininvas 

     

    Tuesday, August 27, 2013 2:24 AM

Answers

  • You can write a stored procedure to extract data from table, the parameter should be your report parameter, but when you select the Year and Month on report, you should assign values for Year and Month selection.

    The label for Month may show Aug , the value can be 8 when you design it.

    Then pass the value to stored procedure, and you can use T-SQL to select data from your table.

    The best practice for your Dim table, it not only include column Year, Quarter or Month, but also should include Date Key, the format should be YYYYMMDD 20120827, you can easily filter with this value to get the data.


    Please vote if it's helpful and mark it as an answer!

    Tuesday, August 27, 2013 2:42 AM
  • Hello Srininvas,

    You can do a simple calculation in T-SQL to get start month/year from a given end month/year:

    DECLARE @fromMonth smallint;
    DECLARE @fromYear smallint;
    
    DECLARE @tilMonth smallint = 1;
    DECLARE @tilYear smallint = 2013;
    
    SET @fromMonth = @tilMonth - 2;
    SET @fromYear = @tilYear;
    
    IF @fromMonth <= 0
    BEGIN
        SET @fromMonth = 12 + @fromMonth;
        SET @fromYear = @fromYear - 1;
    END
    
    SELECT @fromMonth, @fromYear


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 27, 2013 4:59 AM

All replies

  • You can write a stored procedure to extract data from table, the parameter should be your report parameter, but when you select the Year and Month on report, you should assign values for Year and Month selection.

    The label for Month may show Aug , the value can be 8 when you design it.

    Then pass the value to stored procedure, and you can use T-SQL to select data from your table.

    The best practice for your Dim table, it not only include column Year, Quarter or Month, but also should include Date Key, the format should be YYYYMMDD 20120827, you can easily filter with this value to get the data.


    Please vote if it's helpful and mark it as an answer!

    Tuesday, August 27, 2013 2:42 AM
  • Hello Srininvas,

    You can do a simple calculation in T-SQL to get start month/year from a given end month/year:

    DECLARE @fromMonth smallint;
    DECLARE @fromYear smallint;
    
    DECLARE @tilMonth smallint = 1;
    DECLARE @tilYear smallint = 2013;
    
    SET @fromMonth = @tilMonth - 2;
    SET @fromYear = @tilYear;
    
    IF @fromMonth <= 0
    BEGIN
        SET @fromMonth = 12 + @fromMonth;
        SET @fromYear = @fromYear - 1;
    END
    
    SELECT @fromMonth, @fromYear


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 27, 2013 4:59 AM