(Summary) Reports in new code

[Erik Huelsmann <..hidden..>]

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.

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:

CREATE FUNCTION orders__summary_report(

   -- filter parameters

   in_eca_customer int,

   in_eca_vendor int,

   in_part_id int,

   in_fromdate date,

   in_enddate date,

   in_include_open_orders boolean,

   in_include_closed_orders boolean,

   in_part_status int,

   in_employee int,

   -- aggregation parameters

   in_agg_date bool, -- does not aggregate order dates away

   in_agg_customer bool,

   in_agg_vendor bool,

   in_agg_parts bool,

   in_agg_order_status bool,

   in_agg_order_number bool,

   in_agg_part_status bool,

   in_agg_employee bool,

   in_agg_<etc>

) RETURN SETOF order__summary_line;

And the number of parameters may grow when the query grows more complex, of course. (Expecting exponentially.)

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.