I could think of no other way to title this. Thanks for checking it out and hopefully, you will be able to assist me. Attached is a sample which will help you understand my problem.
I created a report which has sections by Department Number. Below each section is a rotated table which “stacks” the objects; making the report easier to read. It is a YTD report which expands left to right as the year progresses
If a department has no activity during a particular month, the YYYY-MM column for that month does not display. For example
If a department had activity in January and March with no activity in February, the columns for that department would display data for 200901 and 200903 and would not display 200902.
As it is currently displayed, it is difficult for report recipients to read. I would like to display an empty column or a column with “0” to ensure all columns align.
If we can determine a solution, I have been asked to make it work for a weekly version of the same report. So, all suggestions will be appreciated!
You have a couple options. If the date range is static or full year, you can make the report cells static (not a true crosstab or results table) and use cell formulas (= Where ( = 1), = Where (=2) and so on.)
This is probably not what you want, so what you are going to need is a reference date table to union in your main query or link via separate query in your report. If you can get your DBA to create and load a calendar table then you can create objects off this table. I often find that getting a new calendar table created in a database is an uphill battle, so another solution is with a derived table in Designer. Create a derived table using a series of unions against sys.dual or a similar table depending on your dbms. For example, I have a derived table called Calendar defined as:
SELECT TO_DATE(‘01-01-2009’)-1 AS MONTHEND_DT FROM SYS.DUAL UNION ALL
SELECT TO_DATE(‘01-02-2009’)-1 AS MONTHEND_DT FROM SYS.DUAL UNION ALL
SELECT TO_DATE(‘01-03-2009’)-1 AS MONTHEND_DT FROM SYS.DUAL UNION ALL
…etc
Either way you get a selectable table, view, or derived table with reference dates, you’ll then need objects based off them. I also normally add report formatting objects to every universe I do to help with union queries and other formatting, so I have classes ob objects defined as blank, zero, and dates that can be used to match the columns in a union query.
End result is you will bring all your real results back in one part of the union query or data provider and a date back for every month in the second part of the union query or linked data provider. Then your report can show zeros for the months with no activity.
Thank you for your very descriptive solution. I shall discuss this with my dba.
I been asked to also investigate the possiblility of using an .xls spreadsheet as an external data source so solve this. I have had some success with that solution but still trying to perfect it.
(BOB member since 2004-11-29)
(BOB member since 2002-08-19)