Displaying counts of records,

building reports desktopintelligence
1

each meeting different criteria .

Hi Dennis,

You wrote:

If I use individual objects at the universe level with different where clauses on each, no data is returned.

Use the oracle decode in stead of the where conditions:

DaysOld <= 15 would have a select like:
sum(decode(sign(DaysOld-16),-1,1,0))
adding a one for each age which when 16 is subtracted becomes negative (i.e. age < 16) and a zero otherwise.

DaysOld 16-30 would have a select like:
sum(decode(sign(DaysOld-16),-1,0,dedode(sign(DaysOld-31),-1,1,0)))

etc.

Good luck,
Marianne

PS For all… My most humble apologies for not sizing down the original message in my last post, I forgot!

Listserv Archives (BOB member since 2002-06-25)

2

each meeting different criteria .

Hi Dennis,

I had a similar situation for one of my reports - looking at investigators and a count of cases by several different statuses. What I did was create a view. The view was then added to the universe and the report was based on the view.

The table that is used to count your records would be in the view query 8 times, one time for each of your “buckets”. You could do outer joins to each of these instances of the table to insure you get your data even if the number returned is 0.

I hope this helps.

Marian Cooney
McKessonHBOC
Malvern, PA

Listserv Archives (BOB member since 2002-06-25)

3

each meeting different criteria .

Hi Dennis (again),

You wrote:

I have to replicate a report that displays the number of records, each meeting a different criteria, all within one report.

Just thought up another aproach, why not create a classification dimension object like age-group:

decode(sign(sysdate-trans_date)-15),-1,‘< 15 days’,0,‘15-30 days’,1, decode(trunc((sysdate-trans_date)/15),1,‘15-30 days’,2,‘31-45 days’, 3,‘46-60 days’,4,‘61-90 days’,5,‘61-90 days’, decode(trunc((sysdate-trans_date)/60),1,‘91-120 days’,2,‘121-180 days,’> 180 days’)))

Alternatively you could create a stored function to do this categorization for you… probably faster, say you call this function days_to_agegroup then the object would become:

days_to_agegroup(sysdate-trans_date)

select this age group and the number of records and the group by will take care of the correct counts, in the report you can use a crosstab.

definitive pro is the logic for this agegrouping is in one place, and you only have one object extra in your universe in stead of one per age-group.

Good luck,
Marianne

Listserv Archives (BOB member since 2002-06-25)