Search code examples
reporting-servicesstylingssrs-tablix

SSRS - How to keep tablix expanded even if no data

I have a report with two tablix; one for income and one for expenditure.

Income and Expenditure

The tablix will expand out for the full 12 financial months if there is some income or expenditure.

The issue is if there isn't any income or expenditure, at all, the report doesn't show anything at all and I'd like the full 12 months showing with 0.00 in each cell.

Income I've attached a screen grab if that will help.

Solution

  • One common design pattern is to add a 'nil' row to your dataset, how you construct it depends on your database. You need to make sure there is one row for each combination of account/date.

    E.g.

    Select Name, value, date from Table
union all
select Accounts.Name, 0 as value, Periods.StartDate 
from Accounts 
    join 
Periods 
on Periods.date between @StartDate and @EndDate

    Edit: This would give you one row for every account in Accounts.. you would then have to filter it to only accounts with some data in some period; which is obviously even more DB specific.. you could do that in the SSRS. If you don't have a 'Periods' table.. then you'll have to make one, or use CTE to construct it on the fly.