Missing Dates in your Report? Create them in a Command Object

Gaps between date data can be a problem for report developers and application developers. If your data has gaps in the dates and you’re designing a report that requires that all dates be added to a report and be available for groups, charts, cross-tabs etc. then this blog post may help you.

Examples of reports that absolutely require a completed date range include time-sheets, calendar based reports, monthly summary reports, etc.

The best solution is always to create a ‘date lookup’ table in your database that contains all of the date values you will need for your report or application needs. You can also use a date lookup file outside your database (csv file, xls file, etc.) if you’re not allowed to add objects to your database. This will work okay if you’re not using your report against a large record set as there could be a significant performance loss. This is because the processing of the complete record set will not happen on your database server.

There are other techniques that my suit your situation, depending on your reporting needs:

1. If you simply just need to display the dates and you’re not using them for further analysis or on objects like charts or cross-tabs, then you can create a formula or Custom Function that fills in the missing gaps. For more information on that technique you can go here.
2. You can also create a set of formulae that ensure that every grouping displayed on your date-based report will always have a value. Think of a monthly report that needs at least 31 formulas or a weekly report that needs 7 formulas to ensure that each date value has a summary associated with it. For more information on that technique go here.
3. Create a Command object where your existing date field drives the query and sub-queries bring back the summary / measure data. This can work if you’re working with less-granular date groups (e.g. year or month) and if you’re not using a lot of sub-queries…otherwise the query performance may not be that great. For more information on that technique you can go here.
4. Create a stored procedure that generates the dates in the required range as well as returns the summary / measure data for those dates. If you have a database where your stored procedures support control structures or loops, then this may be a viable method.

The technique that this blog post covers is how to create all dates in a specified range using a Command object. Note that the syntax that is covered below is SAP HANA SQL. Your SQL may vary from this. If you need help for syntax for your database, please consult the online help or a forum dedicated to your database. I’ve also attached the report that is outlined in this technique. After you download the file below, extract the contents and change the .txt extension to .rpt.

We’ll use a simple example where an orders summary report has gaps in the data. Please go back to the screenshot at the top of this blog to see the original data. In the record set returned for this report, July 9, 2012 and other dates are missing. We want to build a monthly summary report where all dates for July 2012 and other months have the complete record set, even if there are zeroes (0) for the summaries / measures. For example, our report should look like below where the day highlighted in yellow is added to our original report record set.

Our original report with the missing dates runs a SQL query as below:

SELECT DISTINCT

“ORDERDETAILS”.”ORDERDATE”,

“ORDERDETAILS”.”ORDERID”,

“ORDERDETAILS”.”PRODUCTID”,

“ORDERDETAILS”.”QUANTITY”,

“ORDERDETAILS”.”NETSALES”,

“ORDERDETAILS”.”CUSTOMERID”
FROM   “STS”.”ORDERDETAILS” “ORDERDETAILS”

It’s a simple query off of a single table so we know that neither a filter nor a JOIN is causing the gaps in the data. However, we can take this existing query and combine it with several tables derived using a SELECT WITH directive and ensure that we’re getting all required dates. At this point, please do check to see that your database supports the SELECT WITH directive.

Our final query that we want to use is right below, and I’ll explain what it does later.

WITH

A AS
(
SELECT
DAYS_BETWEEN ({?StartDate}, {?EndDate})+1 AS DAYS,
ROW_NUMBER() OVER (PARTITION BY(SELECT 1 FROM DUMMY))-1 AS INCRDATE
FROM  STS.ORDERDETAILS
ORDER BY INCRDATE ASC
),

B AS
(
SELECT
INCRDATE,
DAYS,
ADD_DAYS ({?StartDate}, INCRDATE) AS MAINDATE
FROM A
WHERE INCRDATE < DAYS
ORDER BY INCRDATE ASC
),

C AS
(
SELECT DISTINCT
COUNT (DISTINCT CUSTOMERID) AS CUSTOMERS,
COUNT (DISTINCT ORDERID) AS ORDERS,
COUNT (DISTINCT PRODUCTID) AS PRODUCTMIX,
ORDERDATE,
SUM(QUANTITY) AS QUANTITY,
SUM(NETSALES) AS NETSALES
FROM  
STS.ORDERDETAILS
GROUP BY ORDERDATE
)

SELECT B.*, C.*
FROM
{oj B LEFT OUTER JOIN C ON B.MAINDATE = C.ORDERDATE}
ORDER BY MAINDATE

First off note that we are using 3 derived tables (A to C) and we’re beginning our query using the SELECT WITH directive.

There are two parameters / prompts in the Command object (StartDate & EndDate) and their type is Date.

The first table in the Command, A, is used to calculate the number of days (using the DAYS_BETWEEN function) between the start and end dates and this table is based on the same table that we’re going to be selecting records from, which is ORDERDETAILS. You can use any table from your database here as long as the count of records in that table is greater than the number of days between your start date and your end date.

Table A also uses a ROW_NUMBER window function to create a running total / incrementing integer (INCRDATE) beginning from 1. The syntax is structured in this way as we’re creating dummy data. You could just use a table that you know has ascending integers starting from 1 instead of generating this derived table using ROW_NUMBER, but this exercise assumes that you don’t have such a table.

SELECT

DAYS_BETWEEN ({?StartDate}, {?EndDate})+1 AS DAYS,

ROW_NUMBER() OVER (PARTITION BY(SELECT 1 FROM DUMMY))-1 AS INCRDATE

FROM  STS.ORDERDETAILS

ORDER BY INCRDATE ASC

Table B will create dates using an ADD_DAYS function, starting with your StartDate parameter value, adding the INCRDATE integer created in A and applying a filter / WHERE clause to the data based on that integer being less than DAYS which is created in A.

SELECT

INCRDATE,

DAYS,

ADD_DAYS ({?StartDate}, INCRDATE) AS MAINDATE

FROM A

WHERE INCRDATE < DAYS

ORDER BY INCRDATE ASC

Table C is the data that includes summaries of our orders detail information. After C, the query includes syntax for an outer join between the MAINDATE field in table B and our existing date in table C. Table B is the main table in the outer join and we’ll later use this MAINDATE as the date group in our report. Once again, this syntax is for SAP HANA and your Command syntax may vary.

SELECT B.*, C.*

FROM

{oj B LEFT OUTER JOIN C ON B.MAINDATE = C.ORDERDATE}

ORDER BY MAINDATE

Hopefully you can adapt this technique to your database by subsituting specific function syntax (DAYS_BETWEEN, ROW_NUMBER and ADD_DAYS) to that of your database and replacing references to the database table that I used (STS.ORDERDETAILS) in table A & table C.

Please note that this technique may not be suitable for massive datasets and reports containing a lot of records. In those cases it would be advisable to use a date lookup table as mentioned much earlier.