Report to display 0 When not present

[aman98]

Hi,

I have a report where there are 5 different items.
If I run a report for a particular date it may or not have the item or the value.
For Example. Here it has all the 5 items with values.

Items      Value1    Value2
Item1      10          15
Item2       4           20
Item3       15         30     
Item4        5          9 
Item5       21         19

But in some cases for a particular date there is no item. But I still want to display it and want to show the values a 0 and 0 for value1 and value2.

[Lynn]

If it is a relational source you can create two queries and union them together. First is the query you already have. The other is a query from whatever table holds all the items. You hard code value1 and value2 to zero.

In the union query, set value1 and value2 to total.

[aman98]

Thanks Lynn. I tried that. But the problem I am facing is it is displaying the Item twice if it has a value in query 1.

Items      Value1    Value2
Item1      10          15
Item2       4           20
Item2       0            0
Item3       15         30     
Item4        5          9
Item5       21         19

[Lynn]

You need to set the aggregation property for Items to none, and set aggregation property for value1 and value2 to total.

[aman98]

Thanks again Lynn. It is working. I need to do more test. I will update this thread if I get any issues.