Hi Erik; On Mon, Apr 23, 2012 at 1:40 PM, Erik Huelsmann <[email protected]> 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 > [email protected] > https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel > ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Ledger-smb-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
