While a fair amount of the work that goes into creating, maintaining or optimizing databases is hidden from decision makers, the work that goes into creating reports is immediately visible to management and so, must be given a good deal of attention.
This post is written as a guide for creating SQL based reports. It helps ensure that high quality, valid, reliable reports are created by report writers.
After reading this post, you would have a formal process for creating SQL reports and a step by step guide for reporting on data contained in AS400, Oracle, SQL Server, Microsoft Access or some other database platform.
A Step By Step Guide For Creating SQL Reports
Here is a step by step process for creating SQL Reports …
Step One: Gather and Document the Requirements for your Report
Step Two: Find out where you will be pulling your data from (AS400, Oracle, SQL Server, etc)
Step Three: Write the SQL Queries for extracting your data from the database
Step Four: Present and format your SQL Results in a Reporting Tool
Step Five: Distribute your SQL Based Reports to the end users
Gather Or Document Requirements
Considering the complexity of reporting requirements, you may need to first document the requirements of your project before you start developing the reports.
Writing a requirements document would help you ensure that you understand your users, their reporting needs and the operational or business environment.
A requirements document will also help validate the robustness of your reporting solution upfront … before you start developing the reports.
How To Access Reporting Data From Heterogeneous Databases?
Many organizations store there data in more than one type of database which poses a challenge from a skills stand-point.
If your organization uses AS400, Oracle and SQL Server to store data and you’ve been asked to write a report that draws from all of them, you may find that challenging because each type of database uses a different flavor of SQL.
If this is your situation, then you need to start with the database that you’re the most familiar with (Microsoft Access, SQL Server, etc.) and see if you can connect to all the other databases from that.
Note that many database systems like SQL Server or MS Access offer you the ability to access the data contained in other types of databases easily.
How To Design Or Format Reports?
If you are asked to design a SQL based report that presents data in tables or charts with formatting of page titles, page numbering, column headers, sub-totals, totals, page breaks or more … here is what you can do
You can choose a reporting tool like Crystal Reports or SQL Server Reporting Services or Microsoft Access.
Out of these three, Microsoft Access Reports may be the easiest to learn or use in formatting or designing your reports.
You can develop or code your reports as a web-page. Note that coding or developing your report using a web-development language like PHP or ASP.NET is a flexible but skill intensive approach to creating reports.
Using a web development language like ASP.NET or PHP, you can execute SQL statements on a database and then return the results formatted into tables, columns, charts, etc, on your web-page.
You can also paginate the results or add advanced searching or filtering options. When you write all the code yourself, you have complete control over what you can do or create with your SQL reports.
How To Deliver Or Distribute Reports?
Depending on the organization you work for, you may have to distribute your reports based on the preferences of your users.
For example, while some business users expect their reports to be printed and placed on their desk, others may prefer that their reports be distributed by e-mail.
You can choose between the several possible types of report delivery options including PDF, Microsoft Excel or even deliver your report as a HTML web page.
How To Schedule Reports?
Finally, you also have to decide what reporting frequency will be most appropriate for your situation (daily, weekly, monthly, end of year), etc.
Some reports take a while to prepare or consume a fair amount of server resources, so they need to be done nightly or on weekends.
Others like tax or payroll reports may need to be prepared weekly or monthly.
The takeaway is that you need to be mindful of each type of report and verify when or how often you need to prepare it.
So, now you have a set of steps for preparing reports. Start by gathering your reporting requirements. Next, determine the sources of data that will be presented on the report. Then create the reports using SQL. Finally, format and distribute the reports to the appropriate users.