Re: Help with Report Design

[Quoted] Eric, why don't you just use Oracle Reports instead of all that creating of tables/views stuff?

[Quoted]
1. Just make one query that selects the state and zip.
2. Then create 4 Formula Columns in that same group.
3. For each Formula Column go to the property sheet and go to the PL/SQL Formula area. Declare a variable for your count, do your counts according to [Quoted] your 4 counts in your view but tie them back to the group record with "zipcode [Quoted] = :zipcode" in your where clause (or what ever you call the zipcode field on [Quoted] your layout).NOTE: Remember to "return(countvariable);" that will say what to [Quoted] put in your layout field for that record.
4. Then you can use a SIMPLE default tabular report layout. You definately [Quoted] don't want to have to create tables and views everytime you have a slightly [Quoted] complicated report. I think you are making it too hard.

Robin Q

Eric Raskin wrote:

> Paul:
>
> 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 - 19:20:36 CET