Home > Home, MSDN, SSRS > SSRS : How do I show all columns in a matrix(ssrs) even if there are no data rows

SSRS : How do I show all columns in a matrix(ssrs) even if there are no data rows

I got this question from MSDN, original question is here.

in brief:-

below is my query for the report, and i want all columns label to display in table report even if there is no data populated for that column. so am selecting Status, daysinservicingstatus and UPB. so now i have to put the daysinservicingstatus in a range with it UPB. 

SELECT  SG_SRStatus AS SG_SRStatus, 
LS.LS_DaysInServicingStatus,
SUM(ls.SG_UPB) ‘SUM UPB’,
CASE WHEN LS.LS_DaysInServicingStatus BETWEEN ‘0’ AND ‘179’
THEN ‘0-179 Days’
 WHEN LS.LS_DaysInServicingStatus BETWEEN ‘180’ AND ‘209’
THEN ‘180-209 Days’
WHEN LS.LS_DaysInServicingStatus BETWEEN ‘210’ AND ‘239’
THEN ‘210-239 Days’
WHEN LS.LS_DaysInServicingStatus >= ‘240’
THEN ‘240+ Days’
END AS days
FROM dbo.LoanSummary(nolock) ls
WHERE SG_SRStatus IN (
‘Matured-Death’,
‘Matured-In Foreclosure’,
‘Matured-Tax Default’,
‘Matured-Move Out’)
GROUP BY SG_SRStatus, LS.LS_DaysInServicingStatus
ORDER BY SG_SRStatus

below is my result set in ssrs: ( because there is no days between the rang of 210 -239days and 240+ days, . this is not showing in the table but i want to show those two columns even if there is no data. 

Basically if someone know there are fixed columns then better to write sql is the way that one can use table instead of Matrix control, as Matrix is used when columns can dynamically increase.

Anyway if user want in Matrix only we can try to create a report on Matrix control for this case.

Let say my sample table dbo.LoanSummary.

Based on above query my result is like

1

So i dont have two other columns in the query result 210 -239days and 240+ days

So we will add one extra row for each status and each day period column.

Basically this below rows needs to be added in the SQL query:-

select ‘Matured-Death’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-Tax Default’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-Move Out’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

 

Final query will be like below:-

SELECT SG_SRStatus AS SG_SRStatus,
LS.LS_DaysInServicingStatus,
SUM(ls.SG_UPB) ‘SUM UPB’,
CASE WHEN LS.LS_DaysInServicingStatus BETWEEN ‘0’ AND ‘179’
THEN ‘0-179 Days’
WHEN LS.LS_DaysInServicingStatus BETWEEN ‘180’ AND ‘209’
THEN ‘180-209 Days’
WHEN LS.LS_DaysInServicingStatus BETWEEN ‘210’ AND ‘239’
THEN ‘210-239 Days’
WHEN LS.LS_DaysInServicingStatus >= ‘240’
THEN ‘240+ Days’
END AS days
FROM (
select * from dbo.LoanSummary
UNION
select ‘Matured-Death’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-Tax Default’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-Move Out’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB
)ls
WHERE SG_SRStatus IN (
‘Matured-Death’,
‘Matured-In Foreclosure’,
‘Matured-Tax Default’,
‘Matured-Move Out’)

GROUP BY SG_SRStatus, LS.LS_DaysInServicingStatus
ORDER BY SG_SRStatus

Then come to report side.

Add matrix control as shown below:-

1

Why count is subtract -1 because we added one extra row in the query.

 

Run the report, report output looks like:-

1

 

So it working, if you like it or you need further help  please add comment.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Advertisements
Categories: Home, MSDN, SSRS Tags: ,
  1. pizaro
    April 14, 2016 at 8:31 pm

    Thanks Prasad, it did work

    • April 15, 2016 at 8:06 am

      Thanks Pizaro, for giving feedback. It really help me to get motivated for doing this kind of stuff.
      Thanks
      Prasad

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: