Re: Help with Report Design

From: Eric Raskin <eraskin_at_paslists.com>
Date: Mon, 6 Dec 1999 08:55:51 -0500
Message-ID: <s4ng1m18qeg29_at_corp.supernews.com>

Paul:

[Quoted] Thanks for the answer. I came up with something similar to this on my own:

1. Create a table of states from the original data:

        insert into states
        select distinct state from customers;

2) Create a view:

create or replace force view vw_statecnts as
select s1.state state,

    (select count(*) from customers s12 where s12.state = s1.state

      and months_between(sysdate, s12.lastdate) <= 6
      and s12.lastamt < 5

    ) l6_0_5,
    (select count(*) from customers s13 where s13.state = s1.state

      and months_between(sysdate,s13.lastdate) <= 6
      and s13.lastamt >= 5 and s13.lastamt < 50

    ) l6_5_49,
    (select count(*) from customers s14 where s14.state = s1.state

      and months_between(sysdate,s14.lastdate) <= 6
      and s14.lastamt >= 50 and s14.lastamt < 100

    ) l6_50_99,
    (select count(*) from customers s15 where s15.state = s1.state

      and months_between(sysdate,s15.lastdate) <= 6
      and s15.lastamt >= 100

    ) l6_100p
    from states s1;

This returns all the data I need. I then base a single table report on the data.

Guess what happens? Report Builder dies with a Dr. Watson!! Any ideas/hints on what to do?

I did manage to get a report that worked. It required making correlated subqueries in Report Builder. So, I have one master query that generates the states, and then a whole bunch of queries linked on the state field -- one per column. I then made a master repeating group in the layout, and within it put the individual repeating groups for each of the linked queries. This actually does work, except that any position in the table with no count is blank instead of a zero. I haven't found the setting for that yet.

I really don't like this approach, because one report page takes 5 queries. I have 6 sets of these to do, so that would be 30 queries in one report! Yuck!!!

So any help will be greatly appreciated...

   Eric Raskin

• Original Message ----- From: Paul Dorsey <pdorsey_at_dulcian.com> Newsgroups: comp.databases.oracle.tools Sent: Saturday, December 04, 1999 10:52 PM Subject: Re: Help with Report Design

> You are correct in identifying the problem of null entries for a zip.
> You have to have a single query that brings back the zips
> and the data has to be done with look ups.
>
> I would create a function that would take a zip and a time period and
return
> your data.
> Then place the function in the DB and build a view that looks like what
you
> need.
> The you report becomes a simple single table report.
>
> If you don't have a zip table then your main view should be
>
> select distinct zip, p.f(zip,'last6') last6, p.f(zip, 'last6_5to49')
> last6_5to49, ...
> from customer
>
> By placing the function in the DB you can eliminate the awful network
> traffic.
>
>
> Paul Dorsey
> Dulcian, Inc.
> (212) 595-7223
> web address: http://www.dulcian.com
> email: pdorsey_at_dulcian.com
>
Received on Mon Dec 06 1999 - 14:55:51 CET