Re: (Summary) Reports in new code

[Chris Travers <..hidden..>]

Hi Erik;

On Mon, Apr 23, 2012 at 1:40 PM, Erik Huelsmann <..hidden..> wrote:
>
> The other day, I tried to implement a summary report on numbers of parts in
> open orders in new code. I knew the basic query I needed, so I thought I had
> the basics for an SPROC ready to go. However, I hit a snag:
>
> There were several possible levels of aggregation I'd like to add to this
> query, just like we do in many "old code" reports: one can add columns to
> the output simply by checking the checkmarks in the selection screen. In the
> same way, I wanted to allow filters to be placed on customers, vendors, time
> frames, etc.

Typically, for adding columns the stored procedure should return all
possible columns, and the columns can be enabled or disabled in
output.  However for aggregation that's a different question.  I see a
couple of options here.  The first is you can generate different
reports for different levels of aggregation, perhaps backed by a view.
 The second is you can do aggregation in Perl.
>
> All in all, the number of variations began to grow quite rapidly. My problem
> is that the number of of parameters to the sproc seems to explode. Is that
> how we want to solve this? See:

Yeah, don't address multiple levels of aggregation in the same sproc.
That's asking for needless complexity.

Thinking about this, though, there may be an exception.

Depending on what version of PostgreSQL you might be able to put in a
group-by parameter that could be used to build a group-by list.  That
still seems difficult and hard to maintain to me though.,

If it were me I would probably create a view for the main query and
then use different stored procs to aggregate on different levels.  The
advantage here is that each aggregation level would largely be simple
copy/paste (filter on set criteria, aggregate), but most bugs would be
fixed centrally.

Best Wishes,
Chris Travers

> My idea is to create a single query which uses NULL or some other default
> value for parameters which should not be included into detail. The consumer
> of the data should know that and simply not consume those columns. That way,
> with a lot of CASE statements in the SELECT and GROUP BY parts of that
> query, I can achieve the required result.
>
> Question is: is that how we want to do it, or are there better options? Is
> this the "new code" way of creating summary tables?
>
>
> What are your comments?
>
>
> Bye,
>
> Erik.
>
> ------------------------------------------------------------------------------
> For Developers, A Lot Can Happen In A Second.
> Boundary is the first to Know...and Tell You.
> Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
> http://p.sf.net/sfu/Boundary-d2dvs2
>
> _______________________________________________
> Ledger-smb-devel mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
>