Default Query Item to 0 (versus blank) when no results returned

[tlynnm4591]

How do you specify a default value of zero for a query item when no results are returned? For example, when I display the grid data for my query I get nothing because the query shouldn't return any values based on the filters applied:



However, assuming that both return no results, what I need to get is:

Net Sales       Layaway Deposits   
0                    0

(Querylink is not relevant towards the discussion)

I need these to be 0 versus just blank because I need to do a calculation and because of that I can't just set the missing value on the report to 0, i know how to do that but won't work here, I need the query to return zero.

I have tried all the usual, if net sales is null, then 0 else net sales, case statement saying the same. But i t wont return the 0.

Help!
I am developing in Report Studio 10.1.1.

[calson33]

The reason your attempts have been unsuccessful is because no rows are being returned. There are no null values to operate on, hence no way to set them to zero.

There are various ways to get around this in SQL, but I am not sure about dimensional data.

In SQL, one way is to create a dummy query that joins to the main query like this (oracle):

Select ... value list....
from
(
select 1 as dummy_value from dual
) dummy_query
left outer join
(
main query sql
) main_query
on 1=1

This way, even if your main query returns no rows, you will still get one row from the dummy query. Then your null checking will work.

[Lynn]

A union is another option you can use to "fabricate" a row of data.

For a non-query solution, you could simply specify no data contents. Drag in a two row x 3 column table and format it to look like the list with text items for the zero values. I suspect this would be faster than messing with the SQL.