Month and Year options parameter in SSRS report

Question

--Hello,

I would like to know if there is a way that the user can have a choice to select the date range in month (mm/dd/yyyy) or in year (yyyy)...(e.g. using drop down list)

For example......

If the user select "DateByMonth" from drop down list in the Preview mode, then the user can enter the date range in month.

Start Date: 1/1/2014 End Date: 1/1/2016

The output should be something like this.

    1/2014  | 2/2014 | 3/2014 | .... | 10/2015 | .... | 1/2016|
qty   1         6        9                3               6
rev   2         3        2                12              0

If the user select "DateByYear" from drop down list in the Preview mode, then the user can enter the date range in year.

Start Date: 2014 End Date: 2016

The output should be something like this.

    2014 | 2015 | 2016
qty 16     3      6
rev 7      12     0

You might have a better solution.

Thanks.

Chanom First Chanom First · Accepted Answer · 2016-08-23T03:30:30

I used to make report with parameter for choose display type (Daily,weekly,monthly,yearly or quarter)

and I use Expression in DataSet for make dynamic Query.

This's some Expression in my DataSet

="DECLARE @paramSdate AS DATE = @Strdate "&
"DECLARE @paramNdate AS DATE = @endDate "&
Switch(
Parameters!displayType.Value = "D",
    .... Generate Daily into temp table .....
Parameters!displayType.Value = "W" ,
    .... Generate weekly into temp table ......
Parameters!displayType.Value = "M" ,
    .... Generate monthly into temp table ......
Parameters!displayType.Value = "Y",
    .... Generate yearly into temp table ......
Parameters!displayType.Value = "Q",
    .... Generate quarter into temp table .......
)

I use displayType parameter for determine what query use.

It's just Guide not complete code.

UPDATE 1

Ok.. Thank you for script create table and data

It's look like you expect result must use UNPIVOT

I make sample 2 Query monthly and yearly

Monthly

DECLARE @paramSdate AS DATE = CAST('2015-02-01' AS DATE)
DECLARE @paramNdate AS DATE = CAST('2015-09-30' AS DATE)
DECLARE @temp AS DATE = @paramSDate

CREATE TABLE #tmp (label VARCHAR(10),m INT,yy INT)
 -- Monthly
WHILE @temp < @paramNdate
BEGIN
    INSERT INTO #tmp
        SELECT FORMAT(@temp,'MM/yyyy'),MONTH(@temp),YEAR(@temp)
    SET @temp = DATEADD(MONTH,1,@temp)
END

;WITH CTE 
AS
(
    SELECT upv.col
        ,upv.yy
        ,upv.m
        ,upv.value
    FROM (
        SELECT t.label
            ,t.m
            ,t.yy
            ,ISNULL(val.Qty,0) AS [Qty]
            ,ISNULL(val.Rev,0) AS [Rev]
        FROM #tmp t
        LEFT JOIN (
            SELECT CAST(Rev AS INT) AS [Rev]
                ,CAST(Qty AS INT) AS [Qty]
                ,MONTH(DateDA) AS [mm]
                ,YEAR(DateDA) AS [yyy]
            FROM Main_Table m with(nolock)
            WHERE m.DateDA BETWEEN  CAST('2/13/2015' AS DATE) AND  CAST('9/23/2015' AS DATE)
        ) val ON val.mm = t.m AND val.yyy = t.yy
    ) src
    UNPIVOT
    (
        value
        FOR col IN ([Rev],[Qty])
    ) upv
)

SELECT CTE.col
    ,CAST(CTE.m AS varchar(10)) + '/' + CAST(CTE.yy AS varchar(10)) AS [label]
    ,CTE.value
FROM CTE
ORDER BY CTE.yy,CTE.m

DROP TABLE #tmp

Explain : First, I create temporary table for generate date range record Then use UNPIVOT to get result like this image below

and Finally, I use grouping in SSRS like this

And you will get this result when Run

and for Yearly I make query are same monthly

Yearly

DECLARE @paramSdate AS DATE = CAST('2015-02-01' AS DATE)
DECLARE @paramNdate AS DATE = CAST('2017-09-30' AS DATE)
DECLARE @temp AS DATE = @paramSDate

CREATE TABLE #tmp (yy INT)
 -- Yearly
WHILE YEAR(@temp) <= YEAR(@paramNdate)
BEGIN
    INSERT INTO #tmp
        SELECT YEAR(@temp)
    SET @temp = DATEADD(YEAR,1,@temp)
END

;WITH CTE 
AS
(
    SELECT upv.col
        ,upv.value
        ,upv.yy
    FROM (
        SELECT t.yy
            ,ISNULL(val.Qty,0) AS [Qty]
            ,ISNULL(val.Rev,0) AS [Rev]
        FROM #tmp t
        LEFT JOIN (
            SELECT CAST(Rev AS INT) AS [Rev]
                ,CAST(Qty AS INT) AS [Qty]
                ,YEAR(DateDA) AS [yy]
            FROM Main_Table m with(nolock)
            WHERE m.DateDA BETWEEN  CAST('2/13/2015' AS DATE) AND  CAST('9/23/2017' AS DATE)
        ) val ON val.yy = t.yy
    ) src
    UNPIVOT
    (
        value
        FOR col IN ([Rev],[Qty])
    ) upv
)


SELECT CTE.col
    ,CTE.yy AS [label]
    ,CTE.value
FROM CTE 

DROP TABLE #tmp

Then if you want to use parameter for user select display type you can use query like this

DECLARE @displayType NVARCHAR(100) = @param

IF @displayType = 'DateByMonth' 
BEGIN
    ..... Code for Monthly ......
END
ELSE
BEGIN
    ..... Code for Yearly ......
END

Hope it help.

Month and Year options parameter in SSRS report

1 Answers