Count duplicates for the past 4 months and display it by month

[Web2CRM]

Hi,

I need some help about counting the number of duplicate data for the past 4 months and display the total counts by months and total sum.

                                                                                                                                                           2013
1.) Ex:                                                                                                                        4                5            6           7
     Column ID                      Column ID Name               Column Create Date            APR           MAY        JUN        JUL        TOTAL
     =====================================================================================
     ID001                             ID001_A                            April 05, 2013                       
     ID001                             ID001_A                            April 15, 2013
     ID001                             ID001_A                            May 01, 2013
     ID001                             ID001_B                            June 17, 2013 
     ID002                             ID001_C                            June 29, 2013
     ID002                             ID001_A                            July 12, 2013

2.) I group it by column ID and column create date to get the counts by ID and month, so the report would be look like this:

                                                                                                                                                         2013
                                                                                                                                  4                5            6           7
     Column ID                      Column ID Name               Column Create Date            APR           MAY        JUN        JUL        TOTAL
     =====================================================================================
     ID001                             ID001_A                            April 05, 2013                       2              1               1          0             4
     ID001                             ID001_A                            April 15, 2013
     ID001                             ID001_A                            May 01, 2013
     ID001                             ID001_B                            June 17, 2013 
     ID002                             ID001_C                            June 29, 2013                       0               0              1           1            2
     ID002                             ID001_A                            July 12, 2013

3.) If the current month is August, the column header for months will automatically adjust including the integer month equivalent on top of the months.

Any help will be much appreciated, Thanks!!!

[blom0344]

Can you point out to us what the problem is? The layout of the report, or  the proper dynamic filter to get the running 4 months?  Counting the number of duplicate data is a bit vague. Can you provide an example of the data you need to report on?

[Web2CRM]

I attached an actual view of the report that I need to generate.

This report is called a usage summary report, I need to count the number of times a report was used for the past four months and group them by months.  So I need to automatically change the month columns based on the current month including the integer value of month on top of the character months.

Thanks!!!

[Lynn]

I assume this is a relational source?

If so, then a filter will be needed to get the latest four months of data (current month and 3 previous months):

Code Select Expand


[ReportRunDate] between _first_of_month( _add_months(current_date, -3) ) and current_date


Next you will need to create a data item for the month name and another data item for the month number. I don't know if there is a cognos function for month name, but a case statement or a function native to your database would work. I'm using the DB2 monthname function.

Code Select Expand


monthname( [ReportRunDate] )


Code Select Expand


extract(month, [ReportRunDate])


Now your query will return all the report runs for the last 4 months (to date) and determine the appropriate month bucket that each run belongs in. You can arrange these elements in a crosstab layout along with the report ID, name and other report attributes on the rows and the month name and number on the columns.

The last missing piece is a query item that will display the counts at the intersection of each row and column. All you need is an expression that will return a value of 1 for every record returned by the query, and set the aggregate function to total. The month expressions and crosstab layout will handle summing all those figures and bucket them accordingly.

Attached is a sample of something similar from the great outdooors sample data. It counts up the number of days where returns of web orders occurred for a couple of product lines. It shows the detail data in a list and the summary in a cross tab.

[Web2CRM]

Lynn - Is this applicable to list report? I am using list report not crosstab report.  Appreciate if you can create a version of your instructions above that works perfectly in list report.  Thanks in advance...

[Lynn]

Why do you need a list rather than a crosstab?

If it must be a list then try adapting the techniques shown and post back with a more specific question. Hint: you will need 12 query items instead of the three I illustrated.

[MFGF]

Lynn - Is this applicable to list report? I am using list report not crosstab report.  Appreciate if you can create a version of your instructions above that works perfectly in list report.  Thanks in advance...

Hi,

The idea of the forum is that you give and receive advice and pointers to help folks overcome whatever issues they are facing. It sounds very much as though you are expecting Lynn to hand an answer to you without putting in any effort yourself. If you have Lynn's approach already, why not try to adapt it to suit the exact situation you are facing? We're all here to help if you find you are struggling, but you do need to try things too. That way you learn and are able to use similar techniques in other situations, so it's a winner all around.

Good luck!

MF.

[Web2CRM]

Hi Lynn,

I managed to apply your solution in list report, however, I'm having trouble applying calculated data item in list column name that will display the months name and number.  When I try to use Data Item value in column name it affects the results of the list making it incorrect.  Thanks!!!

[Lynn]

It is difficult to diagnose without understanding how you have implemented things or expressions you have used. As a total guess I'd suspect that there is a problem with the expressions you are using to derive the month names and numbers. It may help to review the SQL being generated by your query and also to look at the tabular data to see if you can spot where the bugs may be lurking.