SSRS display records for Null or 0 Values

Posted on 2011-05-10
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
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    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 28

    Expert Comment

    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 59

    Accepted Solution

    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


    Author Comment

    by:Aaron Goodwin
    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 59

    Expert Comment

    by:Kevin Cross
    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
    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
    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
    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

    Author Closing Comment

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

    Expert Comment

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

    Best regards and happy coding,


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now