Date range column in cognos report studio

[sameena]

Hi all,
             Below is my cross-tab output..my first column is a month column. I want that colum to be displayed a range column.

My present output
     
                           Rev          Prof
Month                Brand-A   Brand-B
12-3-2013          6754       9076
14-3 2013          1235       7654
29-3 2013          1275       7655
30-3 2013          1285       7656
Month total    10549     32041
01-4-2013          5674       8064
12-4-2013          3456       9084
15-4 2013          1235       7654
28-4 2013          1835       7654
Month total    12300     25568         


Expected out-put

                                                     Rev          Prof
Month                                        Brand-A   Brand-B
12-3-2013 - 30-3 2013              6754       9076
                                                  1235       7654
                                                  1275       7655
                                                  1285       7656
MonthTotal                            10549     32041            
01-4-2013 -   28-4 2013             5674      8064
                                                   3456       9084
                                                   1235       7654
                                                   1835       7655
Month total                             12300     25568       


Here I need to display the range of date, starting from the first till the last for a month.
Thanks in advance
Sam                         






       

[Satheesh]

Hi,


Use the Below code


cast(_first_of_month ([Date]),varchar(10)) +' - ' + cast(_last_of_month ([Date]),varchar(10))


in The above, [Date] is from date dimension.


You can find the result in attached screenshot also.


Thanks
Sateesh Chapparpu

[sameena]

Thanks satheesh..Will try it soon...

[sameena]

Hi, Actually this will display the whole moth range. but i dont have data for the full range..Some data is present for half the month..So it should display the range from 1 to 15..

Thanks
sam

[anjan]

Hi

Create a new query, which has data item min date – max date (12-3-2013 - 30-3 2013) for each month (for available data), join this query on year and month, Now you will be having a new query with date range field , date and other fields.

Now drag, new derived item , rev, prof.....


Hope this helps.

Regards,