What are some best practices for report writing? [closed]

Question

I need to give a presentation to a coworker on some of the best practices regarding reports. Right now I use jaspersoft's iReport to do my reports and I need to give some input to a coworker. I'm looking for some general knowledge and insight on the topic.

Answer 1

Unlike the votes to close, I do think this is an excellent, answerable question.

The single most important thing in reporting is to understand your data and your data model. I mean really understand it, you need to be able to look at the results you get as you are developing and be able to tell if they are the right results. You need to know when you need to have a left join vice an inner join for instance (do you know if every table in your query has or should have a matching record in every other table it is joined to?) You need to know where to find the information and how to aggregate it correctly and what where conditions might be needed that are not obvious.

Report queries are often extremely complex and return or consolidate the results of many records, as a result the SQL you write for them must be far more performant than most people are used to writing. This is especially true in a report that will present all the data over a period of time. This report will grow and grow and grow in number of records returned and will get slower and slower to execute. Techniques like correlated subqueries which seem fine when you only have one month's data or test data will fail miserably when running 3 years worth of data (use CTEs or derived tables instead). Don't use them. Anyone writing reports needs to read the chapter on queries in a performance tuning book for the database backend you are using. There are many techniques to avoid.

As far as the design of the report, usually you get told what filter items and what columns in the requirements. There is no excuse for sending a report to testing that doesn't mactch the requirements. Sadly people do all the time.

One technique I use in developing the query is to start the final select (or the original select if I don't have some complex preprocessing stuff) with the requirements list. So first I write

SELECT 
 [COLUMN 1 NAME]  
 ,[COLUMN 2 NAME] 
 ,[COLUMN 3 NAME] 
FROM

Then I comment out all the columns.

Then I start writing the joins and where conditions and add actual field names and calculations and uncomment them one at a time and check the data. So after the first field is working it might look like this:

SELECT 
  t1.field1 as[COLUMN 1 NAME]  
 --,[COLUMN 2 NAME] 
 --,[COLUMN 3 NAME] 
FROM table1 t1
WHERE t1.field3 = 'Yes'

Then it might look like this:

SELECT 
  t1.field1 as [COLUMN 1 NAME]  
 ,coalesce(t2.field4, t1.field6) as [COLUMN 2 NAME] 
 --,[COLUMN 3 NAME] 
FROM table1 t1
JOIN table2 t2 on t1.table1id = t2.table1id
WHERE t1.field3 = 'Yes'

And so on. If you do one chunk at a time and your final select starts with all the columns and their exact names per the requirement then you will find it easier both to build the report query and to debug it as you go. IN SQL server we also have the ability to use CTEs. I use them extensively in reporting so I can grab chunks of data and check that each chunk will have what I want before I put it all together. Derived tables are also critical to reporting if you don't have CTEs available.

If I am getting incorrect data, I may need to see the whole record of each table to figure out what is causing it to return incorrectly. SO I also tend to add a commented out select * that I can use to see everything if I need to. Sort of like:

SELECT 
  t1.field1 as [COLUMN 1 NAME]  
 ,coalesce(t2.field4, t1.field6) as [COLUMN 2 NAME] 
 --,[COLUMN 3 NAME] 
--SELECT *
FROM table1 t1
JOIN table2 t2 on t1.table1id = t2.table1id
WHERE t1.field3 = 'Yes'

To run the full query start highlighting from the second select on. Only do this on dev!