[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
(Summary) Reports in new code
- Subject: (Summary) Reports in new code
- From: Erik Huelsmann <..hidden..>
- Date: Mon, 23 Apr 2012 22:40:38 +0200
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
-- aggregation parameters
in_agg_date bool, -- does not aggregate order dates away
) 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?