Forcing 'zero' rows

[elsolo21]

I thought I saw this somewhere but I can't seem to find it. I have a list report from Report Studio 8.3 where I have 2 data elements plus the month number (Jan=1, Feb=2, etc) and then a count. I concatonate those first three fields so I have that and the count. The problem is that not every combination of the 3 elements exist. For example, the first two elements don't exist for month 5 so there's no row for that. How do I force a row in so I have 12 months for every element combination?

thanks,
-Eric

 

[AngelaCognos]

How are you concatenating them? I mean, do you have queries joined together to get to your final presentation query or just one query and all the elements in it? If it's just one query then you have a modelling problem and I think you need left joins somewhere. If you're using sub queries, I'd pick the one that ALWAYS has data and left join it to the others. If you don't have any data that is ALWAYS there, I'd make a new sub query and just put the months in it with a left join to the others so you always have that info.

 

[masterj1109]

Depending on how you made your joins/queries, you should be able to write a "case" SQL statement that puts in any value you choose when it returns nothing. Or you could make something as simple as another column that will always return a value for ordering or sorting purposes that will be filled reguardless of what the fields you are combining or counting return.

 

[blom0344]

masterj1109,

Do not confuse null values with 'nothing'. In order for a CASE to work the actual combination HAS to be returned which is indeed usually covered by outer joins.
A CASE construction can never add missing combinations.

If using outer joins has not the desired affect, it is often possible to write a union with zeroes for measures to add ALL possible combo's to the dataset. Since adding zeroes to exsting values has no impact you then end up with ALL possible combinations and still correct measures..

Ties Blom