Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
Without having any context to the kinds of reports your business/environment needs, it's too localized; without knowing what specific details about reports you're looking to make more clear, it's too broad. Care to be more detailed with regards to what you're envisioning the outline to entail? –  Brad Christie Feb 17 '12 at 20:41
1  
This would be a great question if it had more details, sorry have to vote to close as is –  Tom Squires Feb 17 '12 at 21:00
    
Ithink it i highly answerable and a great question and please do not close it –  HLGEM Feb 17 '12 at 21:15
add comment

closed as not a real question by Michael K, kevin cline, Tom Squires, Jonathan Khoo, ChrisF Feb 19 '12 at 0:18

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 9 down vote accepted

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!

share|improve this answer
    
Awesome! This is exactly what I was looking for. Thanks for the input. –  precose Feb 17 '12 at 21:44
1  
I vote to reopen. –  JeffO Apr 19 '12 at 19:48
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.