Calendar Style Report in Report Studio

Calendar Style Report in Report Studio

Business Case: Create a Cognos Report Studio report that displays data in a monthly calendar format.

             I came across this requirement from a fellow developer in IT Toolbox forums. This is a very interesting scenario, which I haven't faced before.  The user wants to display daily stats in a calendar format. Each day's information should be in the corresponding calendar box.

Environment: IBM Cognos 8.4.1 Report Studio, Oracle 11g.

Example 1:
The monthly sales order report should display number of sales orders per day in the form of a monthly calendar with each day containing the corresponding count.

When finished, the report would look like this:

Example 2:
This one is a similar report but with detailed information like list of customer visits or meetings etc. The output will look like this:

Implementation Logic:

• The solution I have come up with needs one row of data per day, so if you have multiple rows per day, they need to be aggregated (if it is a fact) or concatenated (if the data point is a string). 
• In example above, I used oracle function "wm_concat" to concatenate multiple customer names into one name per date.  
• If the data you want to display is a measure, then use the appropriate aggregate functions to display the correct value.  
• Depending on your data structure, you may have to create a separate query that gives you the date and the measure. You can use this query as a reference in the main query.
• In my case, I used a SQL Object to do the groupings.

Query:

• Since this is a report by day, you need to have only two items in the query, Date and whatever metric you want to display in the calendar. In the examples above, I had date and retailer count (or retailer list).
• The second step is to create a data item called "Shift". This data item will specify how many days the dates have to be shifted from the first day of the week (Sunday). This is done to place the dates in the correct weekdays (instead of 1st always falling on the first cell). This depends on which day of the week the first of month falls under. For example, if you take December 2011, Dec 1st falls on a Thursday, which is a shift of 4 days from Sunday. The expression for [Shift] is:

(_day_of_week (_first_of_month(current_date),7)-1)

• Next, you have to create 2 sets of 35 query items, two for each cell of the calendar (total of 70 query items).  I know.. This seems like a lot of query items, but is necessary for the calendar layout to work.
• One of these items is the date and this will be placed in top left corner of each cell starting from top left. In the screenshot above, this is for the numbers 1 through 31 displayed in the corner of each cell. I am calling these Cell 1 Date, Cell 2 Date .... Cell 35 Date. The expression for the "Cell 1 Date" is:

                                          Case When extract(day,[Created Date]) = 1-[Shift]

                                          Then extract(day,[Created Date])

                                          Else null

                                          End

• Change the number in the Case statement to match the cell number. For example, expression for "Cell 35 Date" will have "35-[Shift]" in the Case statement.
• The second item will be for the actual data in each cell. Example, the order count in the first example and list of customers in the second example. I am naming these as Cell 1 Data, Cell 2 Data ... Cell 35 Data. The expression for the "Cell 1 Data" is:

                                          Case When extract(day,[Created Date]) = 1-[Shift]

                                          Then [Customers]

                                          Else null

                                          End

• The data item [Customers] should either have an aggregated measure like customer count or a concatenated string like list of customers separated by commas.  This is very critical for this report to work. There can be only one row per date.
• Once all the data items are created, set the "Aggregate Function" and "Rollup Aggregate Function" properties to "Maximum". This is done to consolidate the data set into one row. Otherwise, you will see the entire calendar repeated multiple times.

Layout:

• Create a List report on the page. Hide the list titles.
• Insert a table with 6 Rows and 7 Columns. I went one step further and put another 3x3 table inside each table cell to get the small box in the top left corner (except for the first row which has the weekdays displayed as text.
• Set the cell heights and width to your needs (I used 100px x 100px in the example).
• You can download the XML for the table I created from here: Link to Table XML.
• Copy the contents of the file to clipboard and paste inside the list report. The table will appear inside the list.  Note that this is just the empty table. You will have to drag the data items individually into each cell.
• Once the table is in place, place items "Cell 1 Date" thru "Cell 35 date" into the top left corner cell of each day.
• Next, place items "Cell 1 Data" thru "Cell 35 data" into the middle cell of each day.
• When you are done, your report design page should like this:

Voilà.. You are done.. When you run the report, you should see the report like the second screenshot above.

The report may sound complex, but once I had the expressions figured out, the entire report took me about 2 hours to create. Go ahead and try it out and post your comments here...