asked on

SSRS display records for Null or 0 Values

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.CALLTYPE, @CallType) AS CALLTYPE
		, 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
		--,#temp2.CALLTYPE
		--,#temp2.Urban_rural
		--,#temp2.Jurisdiction
		,count([MIN])
		
		from HOTFractileRanges
			Left Join #temp2	
				on HOTFractileRanges.FractileRange = #temp2.Fractilerange
		group by HOTFractileRanges.FractileRange
		--		,CALLTYPE

Open in new window

Results.txt

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.

OR

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).

sammySeltzer

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

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aaron Goodwin

ASKER

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

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.

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

Aaron Goodwin

ASKER

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.

SOLUTION

Nico Bontenbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aaron Goodwin

ASKER

Bingo

Aaron Goodwin

ASKER

Thanks Very Much, I think I can work with this. Thank you for responding so quickly and offering lots of great ideas

Kevin Cross

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

Best regards and happy coding,

Kevin