CODEWITHSUNDEEP
reporting-services
Mario
Mario

Reputation: 5

How I keep visible a month header in SSRS

I need to know how I can keep visible a month header in a table for a entire year even if no data are available. For example, I have this table when I select all item for entire year:

|Name    |Jan  |Feb  |Mar  |Apr  |May  |Jun  |Jul  |Aug  |Sep  |Oct  |Nov  |Dec  |
|Bike    |     |     |     |     |     |     |102  |500  |450  |250  |754  |125  |
|Glove   |154  |625  |245  |200  |147  |365  |248  |784  |987  |145  |204  |321  |

But when I select only one item like (Bike) the result is as follow:

|Name    |Jul  |Aug  |Sep  |Oct  |Nov  |Dec 
|Bike    |102  |500  |450  |250  |754  |125

I need to see the entire table even the month data is empty

|Name    |Jan  |Feb  |Mar  |Apr  |May  |Jun  |Jul  |Aug  |Sep  |Oct  |Nov  |Dec  |
|Bike    |     |     |     |     |     |     |102  |500  |450  |250  |754  |125

Thank you for your help.

Upvotes: 0

Views: 53

Answers (1)

Wes H
Wes H

Reputation: 4439

In your dataset query, you need to create a set containing all of your month names. Then left join that set to your existing dataset.

Select [columns]
From MonthNames as MN
left join Sales as S
on MN.MonthName = S.MonthName

Months that do not have data will still have a record, but the value will be NULL. Make sure you're using the month name field from the MonthName set, not the Sales set. this will ensure you will have at least one record for every month, even if there were no sales for that month.

I can't tell where all of your columns came from so I had to make some assumptions. In the future, it would be helpful to use the table alias in front of every column.

Try this:

SELECT  Name,
        UnitPrice,
        Q1.MONTH,
        Q1.MONTH2
FROM    TEST.dbo.MonthList AS Q1
  LEFT JOIN
        ( SELECT    *,
                    YEAR(ModifiedDate) AS YEAR
          FROM  AdventureWorks2012.Sales.SalesOrderDetail AS Q2
            INNER JOIN AdventureWorks2012.Production.Product AS Q3
              ON Q2.ProductID = Q3.ProductID
          WHERE YEAR(ModifiedDate) = @YEAR
                AND Name IN ( @NAME )
        ) AS Q
    ON Q1.MONTH2 = MONTH(Q.ModifiedDate)
WHERE   Q1.MONTH IN ( @MONTH );

The simplified version is

Select [columns]
From MonthNames as MN
left join (select [columns] from Products Inner join Sales ON ... WHERE [Product & Sales filters]) as S
on MN.MonthName = S.MonthName

Upvotes: 2

Related Questions