How to Display Zero Instead of Null 1

[sconti11]

I have a matrix using a datasource that may or may not have values based on parameter selections. What I need to do is be able to display zeros within the rows of the matrix.

Right now, the matrix will not display at all if there are no values to show.

I am new in SSRS, so any assistance will be helpful!

 

[akalinowski]

in your sql statement use the ISNULL(value,0)

akalinowski

http://www.olivelabs.com

 

[sconti11]

are you referring to the sql statement within the datasource container?

If so, I have attempted to do something similar with COALESCE, as seen in my code below:

SELECT COALESCE (a.CM_Paid_Dol, 0) AS CM_Paid_Dol, COALESCE (a.CM_Paid_Num, 0) AS CM_Paid_Num, COALESCE (a.Warranty_Dol, 0) AS Warranty_Dol,
COALESCE (a.Sales_Dol, 0) AS Sales_Dol, COALESCE (b.CM_Recruited, 0) AS CM_Recruited, COALESCE (c.CM_Started_Num, 0) AS CM_Started_Num,
COALESCE (b.franchiseID, a.franchiseID, c.franchiseID) AS franchiseID, COALESCE (b.RAAG_Year, a.RAAG_Year, c.RAAG_Year) AS RAAG_Year
FROM (SELECT SUM(laborTotal) AS CM_Paid_Dol, COUNT(laborTotal) AS CM_Paid_Num, SUM(warrantyTotal) AS Warranty_Dol, SUM(amount) AS Sales_Dol,
YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM PYMNT
WHERE (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (turfID = @Turf) AND (MONTH(dateCreated) = @Month) OR
(YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (turfID = @Turf) AND (@Month IS NULL)
GROUP BY YEAR(dateCreated), franchiseID) AS a FULL OUTER JOIN
(SELECT COUNT(dateCreated) AS CM_Recruited, YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM CRAFTSMEN
WHERE (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (MONTH(dateCreated) = @Month) OR
(YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (@Month IS NULL)
GROUP BY YEAR(dateCreated), franchiseID) AS b ON a.franchiseID = b.franchiseID AND a.RAAG_Year = b.RAAG_Year FULL OUTER JOIN
(SELECT COUNT(craftsmenConvDate) AS CM_Started_Num, YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM CRAFTSMEN AS CRAFTSMEN_1
WHERE (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (craftsmenConvDate IS NOT NULL) AND (MONTH(dateCreated)
= @Month) OR
(YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise) AND (@Month IS NULL)
GROUP BY YEAR(dateCreated), franchiseID) AS c ON a.franchiseID = c.franchiseID AND a.RAAG_Year = c.RAAG_Year

I have also tried the ISNULL too....when I run this statement for certain parameters, I get zero rows...nothing appears. I need to have zeros appear..

 

[dhulbert]

When SSRS creates a matrix it is very similar to a pivot table in excel if there is no value for a particular row or column then nothing is displayed.

The solution, ensure there is always a value to display, If you create a copy of your results table populatd with 0's then use this to fill in the gaps, It's quite longwinded to set up but once it's done it is really easy to use.

Out of interest last time i did it, the users decided afterwards they didn't like it.........

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[sconti11]

Can you ellaborate on what you mean by a copy of the results table populated with 0's??

Are you saying to have another table that mirrors the one I am using but only has 0's in it?

 

[RiverGuy]

In your cell:

=IIf(Fields!YourField.Value Is Nothing, 0, Fields!YourField.Value)

 

[dhulbert]

sconti11

Yes I was but if the code from RvierGuy works then that's a whole world easier......

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[sconti11]

The IIF statement within the report cell does not work, I attempted that before coming here with my issue.

THat would have been great if it worked....have you used that solution before within the cell?

I will see about the alternate solution of a zeroed out table....or is it possible to do some conditional formatting where if a there is no data in a table (datasource) then I can show a whole new matrix with static 0's in it?

 

[RiverGuy]

The IIF statement within the report cell does not work, I attempted that before coming here with my issue.

You're incorrect. Use the following query with a test report wizard:

SELECT 1 AS ID, 'One' AS Category, 1 AS Val
UNION ALL
SELECT 1 AS ID, 'One' AS Category, 2 AS Val
UNION ALL
SELECT 2 AS ID, 'Two' AS Category, 3 AS Val

Make it a matrix, with ID as columns and Category as Rows. Use Val for the details.

In the expression, replace

=Sum(Fields!Val.Value)

with

=iif(Sum(Fields!Val.Value) is nothing, 0, Sum(Fields!Val.Value))

You will see that it does, in fact, work.

 

[xlbo]

It works as long as at least one of the "hooks" is there i.e. a value must have at least a column or row group associated with it for the isnothing to work. If the whole set of data is not there, i.e. neither of the grouping items then the data is completely absent and cannot be shown as 0 in SSRS.

As an example, lets say you are selling cars and you want to know how many cars of each colour sell by month. You have a table with months across the top and colours down the side. If you have a month where you only sell red cars, you can force the other colours' sales to show as 0 using the isnothing formula. If, however, you have a month where NO cars were sold, there is no data for either the month of the car colour so the row will be totally missing and no amount of clever formulae will show it

this I believe is the situation the OP is asking about and the only way around it is to force the return of at least one of the hooks

There are a number of ways of doing this - LEFT OUTER JOIN the query to a known static set of records if the number of records is small for example or the dummy table filled with zeroes

In this case it looks like "Year" is the hook to use so could set up a table holding all your years and use something like


SELECT a.year, b.* FROM

(Distinct Year from AllYears) a

LEFT OUTER JOIN

(Your Existing Query Here) b

ON a.Year = b.RAAG_Year

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[sconti11]

Sorry for the late reply...but I need some more clarification on this solution.

Am to assume that I will need to create a new table within my database to hold zeros for each year, and then join that table with the table I am using for the report, so that if there is nothing to show, then it will use the zeros from the dummy table???

Just a little confused...

Thanks

 

[dhulbert]

sconti11

It gets a bit tricky but basically yes, you have a new table populated with 0's then join that in so that where you have a value you keep that value but where you don't currently have a value you use the one from th enew table.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[RiverGuy]

You don't need a new table. Read xlbo's post. Just used a derived table inline with your query.

 

[xlbo]

This is a perfect situation for a calendar table - as per Riverguy, you don't need a physical table filled with zeroes - you can create your own set of years either by referencing another table or even populating your own temp table in a stored proc

SELECT a.CalYear, b.* FROM

(SELECT DISTINCT CalYear from Calendar) a

[b]LEFT OUTER JOIN[/b]

(Your Existing Query Here) b 

ON a.CalYear = b.RAAG_Year

DECLARE @Yrs TABLE 
( 
    Yr INT 
    
)

DECLARE @YrCtr INT

SET @YrCtr = 1990

DECLARE @YrNow INT

SET @YrNow = YEAR(GETDATE())

WHILE @YrCtr <= @YrNow

BEGIN

INSERT INTO @Yrs (Yr) VALUES(@YrCtr)

SET @YrCtr = @YrCtr + 1

END


SELECT a.Yr, b.* FROM

(SELECT DISTINCT Yr from @Yrs) a

[b]LEFT OUTER JOIN[/b]

(Your Existing Query Here) b 

ON a.Yr = b.RAAG_Year

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question