Wednesday, December 21, 2011

Calendar Style Report in Report Studio

Create a Calendar Style Report in Cognos 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...

23 comments:

  1. IS it possible that you come add the XML of the report...
    It looks very interesting....
    Thanks

    ReplyDelete
  2. @CognosTech: I will be adding the code soon..

    ReplyDelete
  3. Hi Sriram:

    The second one is what I am lookin for? Is it possible that u can share theinformation of doin g one per day as well.

    Kavitha

    ReplyDelete
  4. @Kavitha,

    I will share the solution to both the reports. I am currently on the road will try to post the solution by the weekend.

    ReplyDelete
  5. Hi Sriram,

    The blog seems interesting, the business scenario which you have posted in this blog seems interesting, The way you explained the implementation section was quite good..

    Thanks,
    Sharath

    ReplyDelete
  6. Hey Sriram:

    Sorry for the late reply. Thanks for sharing the details, let me try it and will let you know if I am facing any issues. On a quick note, the xml file, I was not able to load it to the clipboard. Do you use this on ur local?

    ReplyDelete
  7. Also Sriram the Date is in the format mm-dd-yy which is OK right? or Date is extract(day current_date)

    ReplyDelete
  8. Hi Kavitha,

    The xml was copied from our live environment. I am not sure what you mean by "local".

    For the date format, I am not familiar with DB2 syntax, so, you have to test it before using it in the report.

    ReplyDelete
  9. Hey Sriram:

    I tried it out and works fantastic. I had to merge the code carefully for the table view though as we use the RS thru another server. I created two data items for the same field and then used the concat( client1, client2)..so far the result seems to be good. One thing is they are displaying as one field and I would like them to be separated by a comma..I have tried many options but don't seem to work.

    ReplyDelete
  10. Hi Kavitha,

    I am glad it works for you.

    I am not sure I understand the comma separation. Can't you include the comma in your concat statement, like this: concat( client1, ', ', client2)?

    Sriram.

    ReplyDelete
  11. Hi Sriram:

    I want to attach the table xml if u want to take a look. I enhanced it by adding a prompt to show current month data by default and user can choose prv month if interested to view. The problem is when Current Month is displayed few of the date fields are not appearing? When I totally delete the value prompt the calendar appears normally with all dates in the cells.

    ReplyDelete
  12. Kavitha,

    You can send me the xml at cognosonsteroids@hotmail.com

    If possible, send me screenshots of the value prompt and any expression you created as well.

    ReplyDelete
  13. Hi Sriram,

    This is nice. My boss asked me the same in our environment. Can you please share the code. I am not able to see it here. Can you add it here.

    Thanks,
    Bob

    ReplyDelete
  14. Can you please share the code to pingrkiran@gmail.com Can you add it here.
    Thanks in advance,
    R kiran

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Excellent article, Sriram. What needs to be done when there is no data for 'Customers'? I get 'No Data Available' and the calendar disappears once I add few Cell Data items to the calendar.

    Thank you
    Jay

    ReplyDelete
  17. I am not able to add the Table XML file into my report and am getting error doing same"This document does not contain a valid report specification". please help me..your quick response will be highly appreciated.

    ReplyDelete
  18. Hey Sriram,
    Can you share the report xml code at fpathan687@gmail.com
    I tried this scenario but was unable to achieve it.
    Your help will be highly appreciated.

    ReplyDelete
  19. Hi Sriram,

    Can you share the report xml at send4sai@gmail.com? What is [Created Date] query item in above example?

    ReplyDelete
  20. Hi, this looks really good, but what is the query item [Created Date]

    ReplyDelete
  21. I know this is an old post, but it's exactly what my customer asked me today.

    I can't find the link to download the XML. If possible, please share with bee-73@hotmail.com

    Many thanks

    ReplyDelete
    Replies
    1. Hi, Unfortunately, I no longer have access to a cognos environment and don't have the XML. But if you follow the steps laid out, you should be able to reproduce the result in 30 minutes.

      Delete