Need to Show No Records in Specified Order Grouping 1

[martinjt]

I need to show all group titles on a report in specified order regardless of whether there are records or not. Each group is showing a running total (count) of that category and I need to show a zero for a category with no records. Without resorting to a rigid format, is there a way this can be done with Specified Order Grouping and perhaps a formula that can "populate" the group when no records exist? Using CR9 with SQL db. Thanks! - JT

 

[CaptainD]

If your SQL statement returns all of the fields that you are grouped on with quantity of 0 or more then it should work.

If your SQL statement does not return all of your fields then I would look there.

 

[martinjt]

Thanks. I'm using Left Outer Joins. If I use any other type of join, the report fails. Can it be done by grouping on some formula that gives a zero value for a nonexistant record instead of just the field?

 

[CaptainD]

A left join should be fine.

With a left join you get all records from the primary table and I assume you are getting a null from the joined table. If that is the case then, if it is MS SQL server, the IsNull() function should take care of your problem.

IsNull(TheFieldYouNeed, 0, TheFieldYouNeed)

The way you've posted your question does not give much information on your problem.

But it sounds to me that your problem is more with your dataset then the report.

 

[martinjt]

I think you're right as this solution didn't work. It's Oracle SQL - I don't know if that makes a difference. I went with the more rigid approach. It wasn't too bad with cut & paste. Thanks much anyways. - JT