Prev: key is not unique in collection
Next: Scatter Graph
From: The Frog on 23 Jul 2010 10:20
Hi everyone,

This may seem like a dumb question, but I'm going to ask it anyway. I
have been requested to build a report in a db I am developing, and you
would think this is a pretty simple report to build - but I am stuck
as to how best to approach it. I would really appreciate some advice
before I commit myself to a particular path.

In short: The desired output is a series of product lists (in this
case 5 of them). The lists can all be pulled from one primary query
that contains the source data as well as the necessary columns to
allow filtering for each particular list. Each report would require
two filters (where clauses) to correctly identify the right rows to
return, and only a single column would be returned for each list - so
I am guessing 5 queries per report. The layout is to have the lists
side by side.

The structure of the filtering is pretty simple. One of the two
filters would remain the same between versions of the report -
effectively specifying which column on the report is being filled in -
in this case the sales channel. The other would change between reports
to specify which group of products (category to be specific) is to be
listed. The end result is a report that for each sales channel
(column) will be a list of the products that fall into the assigned
product category. Each column will end up with slighty different
lengths as they are not all evenly distributed.

I was thinking of making a single 'master' report that has the five
columns - one for each sales channel - and then simply altering the
reports filter property to select for the product group. When I try to
implement this it starts to get really messy very quickly. I figure
that I am doing something wrong and that there is probably a better /
easier way to do this.

How do you think it best to approach this scenario?

I am curious for your feedback

Cheers

The Frog
From: Salad on 23 Jul 2010 11:46
The Frog wrote:
> Hi everyone,
>
> This may seem like a dumb question, but I'm going to ask it anyway. I
> have been requested to build a report in a db I am developing, and you
> would think this is a pretty simple report to build - but I am stuck
> as to how best to approach it. I would really appreciate some advice
> before I commit myself to a particular path.
>
> In short: The desired output is a series of product lists (in this
> case 5 of them). The lists can all be pulled from one primary query
> that contains the source data as well as the necessary columns to
> allow filtering for each particular list. Each report would require
> two filters (where clauses) to correctly identify the right rows to
> return, and only a single column would be returned for each list - so
> I am guessing 5 queries per report. The layout is to have the lists
> side by side.
>
> The structure of the filtering is pretty simple. One of the two
> filters would remain the same between versions of the report -
> effectively specifying which column on the report is being filled in -
> in this case the sales channel. The other would change between reports
> to specify which group of products (category to be specific) is to be
> listed. The end result is a report that for each sales channel
> (column) will be a list of the products that fall into the assigned
> product category. Each column will end up with slighty different
> lengths as they are not all evenly distributed.
>
> I was thinking of making a single 'master' report that has the five
> columns - one for each sales channel - and then simply altering the
> reports filter property to select for the product group. When I try to
> implement this it starts to get really messy very quickly. I figure
> that I am doing something wrong and that there is probably a better /
> easier way to do this.
>
> How do you think it best to approach this scenario?
>
> I am curious for your feedback
>
> Cheers
>
> The Frog
Without much thought put into it I suppose a union clause might work to
select the records.
Select data, "" as col2, "" as col3... from query where...
UNION ALL
Select "" as Data, [Data] as col2, "" as col3 from query where...
UNION ALL
Select "" as Data, "" as col2, [data] as col3 from query where...
etc...

The issue I see is the sorting. Would the sorts be different per
column? If so, subreports might make sense.

The filters could ref a hidden control of the calling form.


From: David W. Fenton on 23 Jul 2010 15:37
The Frog <mr.frog.to.you(a)googlemail.com> wrote in
news:93061567-81e1-4f6b-b3d2-fad67b92161d(a)c10g2000yqi.googlegroups.co
m:

> In short: The desired output is a series of product lists (in this
> case 5 of them). The lists can all be pulled from one primary
> query that contains the source data as well as the necessary
> columns to allow filtering for each particular list. Each report
> would require two filters (where clauses) to correctly identify
> the right rows to return, and only a single column would be
> returned for each list - so I am guessing 5 queries per report.
> The layout is to have the lists side by side.

Be easy on yourself -- use a temp table and base the report on that.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
 | 
Pages: 1
Prev: key is not unique in collection
Next: Scatter Graph