SSRS display records for Null or 0 Values

Posted on 2011-05-10
Medium Priority
Last Modified: 2012-08-13
Hi Experts,

I've been struggling with how best to run my SQL query and display the results in SSRS.  I have a table with Time Values, and I want to display all of the time values in my final result.  The problem I am running into, is if there is no total count or records in that time segment value...the record does not show up at all.  This should be a simple query, but I'm having difficulty in filling in the null and 0 value records.  So what I would like to see is 2 rows for every time value (1 for Urban and 1 for Rural) even if there is no value....just display a 0.

When I try to bring the results over to SSRS, of course it only displays the values with data.  But what I want is Every time time value for Urban and Rural even if it is 0.
SELECT     isnull([MIN],0) as [MIN]
		, ISNULL(HOTFractile.Urban_Rural,@Urban_Rural) As Urban_rural
		, ISNULL(HOTFractile.Jurisdiction,@Jurisdiction) as Jurisdiction
		, HOTFractileRanges.FractileRange
into #temp2		
FROM HOTFractileRanges 
		LEFT JOIN HOTFractile 
		ON HOTFractileRanges.ID = HOTFractile.FractileID
ORDER BY HOTFractileRanges.FractileRange
select HOTFractileRanges.FractileRange
		from HOTFractileRanges
			Left Join #temp2	
				on HOTFractileRanges.FractileRange = #temp2.Fractilerange
		group by HOTFractileRanges.FractileRange

Open in new window

Question by:Aaron Goodwin
  • 4
  • 3
  • 3
  • +1
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35728619
You could start with 2 insert statements that select all the distinct FractileRanges into the temp table. Once with Urban, once with rural. Next you'll run an update statement that fills in the right value for the query.


If you use as matrix in your report with FractileRange in the rows and Urban_rural in the columns you'll get a matrix with all possible combinations (and some empty cells).
LVL 29

Expert Comment

ID: 35728650
What I would do if it were me, is to go ahead and run your query that you say works.

Then report designer, I would right-click on the field, click on expression and then do something like this:

=IIf(Fields!Urban_Rural.Value Is Nothing, 0, Fields!Urban_Rural.Value)
This way, if no values, then a 0 will display
LVL 60

Accepted Solution

Kevin Cross earned 1200 total points
ID: 35728929
Just to clarify, I would expect Urban_Rural to contain the value to differentiate the rows, but see in original query you are grouping by CALLTYPE which is the issue by the way.  When you do a COUNT() you will get 0 if no rows exist, except when you are grouping by data -- since no data is returned, grouping by that data eliminates the nulls and hence all your rows of 0.

To counteract this, you can build a list of everything you want to see in a CROSS JOIN for example and then LEFT JOIN to your data:
select hfr.FractileRange
     , ct.CallType
     , count([MIN]) as Cnt
from HOTFractileRanges hfr 
cross join (select 'Urban' as CALLTYPE union 'Rural') ct
left join #temp2 t2 on hfr.FractileRange = t2.Fractilerange and t2.CALLTYPE = ct.CALLTYPE
group by hfr.FractileRange, ct.CALLTYPE

Open in new window

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

by:Aaron Goodwin
ID: 35729037
Thanks for the responses:
Sammy, I had tried what you suggested earlier, the problem is that not all of the Fractile Range values show.

so my results look like

Range               count
00:00 - 6:59          1300
07:00 - 7:59             2
11:00 - 11:59           1

What I would like is to have all the Fractile range values from 0 to 50+ display in the report.  And fill the Count value as 0 if nothing exists.  Any ideas on how I can accomplish that?

Nicobo, I am going to try the Matrix option, but I don't know if I can do a Running total on the count value in the adjacent column in a matrix....but I'll take the option if I can display all the fractileRange values
LVL 60

Expert Comment

by:Kevin Cross
ID: 35729080
If the table HOTFractileRanges includes all 50+ values, then my suggestion here should work: http:#a35728929
You should tweak it for the appropriate source and column name of the Urban/Rural split you want per Fractile Range.  If this is stored in another table, it may be better practice to select from that source, so that in the future if you add another option beside Urban and Rural those are automatically picked up.
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35729487
I agree the cross join thing should work. But here is another technique (although simplified) that might also work:

create table #temp (FractileID int, Cnt int);
insert into #temp (FractileID) select ID from HOTFractileRanges;
update #temp set Cnt = (select count(*) from HOTFractile where HOTFractile.FractileID = #temp.FractileID);
select * from #temp inner join HOTFractileRanges.......

Open in new window


Author Comment

by:Aaron Goodwin
ID: 35729579
Hi Mwvisa1,

I am trying your solution but I get a syntax error.  I havn't used the cross joins before so I am not sure where the issue is yet.  When I pasted in your code and tried to run it i get:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'Rural'.

Sorry, syntax is not my strongest point.
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 800 total points
ID: 35729691
I think line 5 should be:
cross join (select 'Urban' as CALLTYPE union select 'Rural') ct

Open in new window


Author Comment

by:Aaron Goodwin
ID: 35729713

Author Closing Comment

by:Aaron Goodwin
ID: 35729761
Thanks Very Much, I think I can work with this.  Thank you for responding so quickly and offering lots of great ideas
LVL 60

Expert Comment

by:Kevin Cross
ID: 35730325
Yes, sorry for my type-o.  Thanks, @Nicobo, for following up for me.
Glad we could help.

Best regards and happy coding,


Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question