InetSoft Webinar: Tip For Constructing SQL Queries

This is the continuation of the transcript of a Webinar hosted by InetSoft on the topic of "Best Practices for Reporting." The speaker is Mark Flaherty, CMO at InetSoft.

As a general tip for constructing SQL queries, you should always carefully evaluate whether you use Select query, the SELECT DISTINCT query clause or not. Some report developers will automatically add this clause to every Select statement even when it's not necessary. This is actually a bad habit that should be reduced. Let me see here. Let me think about some of the other things we are talking about here. So we talked about the DISTINCT, the DISTINCT basically causes a lot of extra work on the database so we just talked about that in conjunction with UNION.

Another thing that you should probably be careful of using is a Select All. For example, this is the best way to phrase it. If you know, for instance, that you don’t need every single column in that table, why should you select all from that table. So if you know that you are only looking for let’s say two out of 10 columns in that table, it's always going to be better to select those columns explicitly as opposed to getting all the table columns out. So, a Select All can also be expensive when done unnecessarily.

And you know what’s interesting is that sometimes what happens is you will select all, you will select all from a table, but what you could probably do is maybe use, for instance, the top operator. This is one thing that I have seen used in databases, and this could limit the number of rows that are returned even though the user doesn’t enter any criteria, for instance, to direct the number of rows returned. So using the top statement will bring back the top records, let’s say the top 100 or top 50 records in that table or in that dataset and perhaps let you to work a little bit more efficiently.

So again, these are some areas here that can help you with SQL. I also wrote here to consider the use of temp tables. That may be something that you discuss perhaps with your data team because obviously those have to be provisioned in the database. But there are certain tuning techniques that will explore the use of views or temp tables as well. And again, that execution plan is going to be very helpful in understanding where the full table scans occur and all of that other stuff.

We have a couple more slides, and then we will get to the demo. I just wanted to speak to some best practices here in information design. Basically these following basic design principles will make the report easy to read and use. Keep the report short as possible. It goes without saying. If you have a table that appears in your report, make the tables scrollable.

Now I don’t know if this is like a hard number to live by but what I feel is it shows a maximum about fifteen items at a time. So, if you are in a table, make it scrollable, try to keep the display limited to fifteen items or less and when the table gets printed considering showing the top fifteen items in the main report. These should be the most important items, and then provide an optional appendix for the full data.

If you are going to use online charts, keep them concise and easy to read. If users need more detail then allow them to click the graphic to open up a larger version. So that would be like a popup or a drilldown. Include a report title at the top of the report. I mean that goes without saying, but I think some standard data points in the title should be author, the branding, the date range of the data, your data source, and maybe data source could be optional, but you could always maybe state that logically.

Read what InetSoft customers and partners have said about their selection of Style Scope for their solution for dashboard reporting.

For example, this is the sales database or the orders database. Display the date the report was produced and total number of pages. We said this earlier, but the executive summary should be the second item in the report right after the title. There should be a paging mechanism at the top and bottom of each page. Chunks of information should be grouped together if they relate to each other, and try to minimize or eliminate horizontal scrolling in your report under standard conditions. This all goes back to page sizing and layout on a page. And regarding color coding, if necessary, it should be implemented, but there should be an option for all text or text for the sake of those color blind or visual impaired users.