Friday, February 12, 2010

Querying Data for Report Parameters

Often there's a need to dynamically get data to populate the list of options for a report parameter. There are a couple of ways to go about this. One I often see is to query a transaction table to get a distinct list of items to appear in a drop down parameter. Something along the lines of


In my opinion this is a bad idea for two reasons:

  1. It places unneccessary load on the database. Querying thousands, maybe even millions, of rows to return a handful of rows might not be the best idea if there is a reference table (perhaps just listing cities or even listing all customers - with city information as part of the customer's address). The situation is even worse if the column being queried from the transaction table isn't included in a suitable index.
  2. Looking at a transaction table for this information will only return a resultset for those items which have been involved in transactions. If it's important for the report user to be able to query against any city then there's a problem!

So, whereever possible I advocate querying small reference tables to populate report parameters. Look at the data model as a whole to try and find suitable tables and ask questions of key super-users, DBAs, application developers if need be - you might be suprised to find there's a table you can use - it just may be that it was named in a way that didn't make its purpose obvious.

No comments:

Post a Comment