I keep getting this kind of problem 1

[smcnulty2000]

I keep getting this kind of problem and I want a better solution.

I'm in Crystal 8.0. Working with an Oracle DB. Not able to get a lot of changes implemented on the db side.

This db groups every location with a PH number, which is stored as a string with two characters: "00", "01", etc. And also with a RT number "00", "01", etc.

RT is a subgroup of PH.

For a particular month, each subvalue might increase. But in a given month, I may not see any values added to an RT or a PH. What I am already able to do is group by date, then sum by PH and RT.

What I need to do is to show as these values increase, each RT and PH and how much got added to them. Easy so far.

My problem is that if no locations get added to a particular RT or PH I still need to display them at their current levels for that month.

So the result I want would look like this;

[tt]July Count Running
PH00
RT01 15 15
RT02 13 13

PH01
RT01 12 12
RT02 20 20

August Count Running
PH00
RT01 0 15
RT02 23 36

PH01
RT01 12 24
RT02 0 20 [/tt]

But what I get is, of course more like this:

[tt]July Count Running
PH00
RT01 15 15
RT02 13 13

PH01
RT01 12 12
RT02 20 20

August Count Running
PH00
RT02 23 36

PH01
RT01 12 24
[/tt]

with all of the zeros with previous running total values disappearing for a current month and reappearing later if more changes there.

Also, I don't know ahead which RT numbers or PH numbers I will have. Or I'd just hardcode it.

My best solution so far involves a subreport- which reduces performance substantially.

Am I missing an obvious and simple solution?

 

[kenhamady]

Nothing simple. CR can't create a group without a record.

1) You could hard-code running totals for each potential group
2) Use subreports
3) Put a table in the database with all Code/Month combinations, and use an outer join to generate the results.

Since you can't change the database, and there are too many to hard code, it seems like subreports are you only option. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[smcnulty2000]

Nuts. I was afraid of that. That's the list as I saw it too.

You've almost certainly saved me some time, though. I have a similar problem with timestamps and the struggle is exactly the same. I guess my client gets to choose between having their hard drive thrash for lengthy periods and altering the db.

Thanks for the help.

 

[kenhamady]

At least they have a choice. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/