Triplicate Quarterly data into Monthly data

[karthik.cognos]

Friends,

I have 2 crosstabs in my RS report. The 1st crosstab has data at a monthly level and the 2nd crosstab has data at a quarterly level...

The requirement is to display both the crosstabs at monthly level, so the 1st crosstab is straight forward...but for the 2nd crosstab, I have data for 03/31, 06/30, 09/30, 12/31...My customer asked me to display the 2nd crosstab at a monthly level too and he said I could triplicate the data, as in I could use March data to populate the measure for Jan and Feb (so all the 3 months would have the same number), use June data to display data for April and May and so on...

Any ideas, how to achieve this?

I reached up to a point where, I did a left outer join to my date dimension and was able to display all the months in the 2nd crosstab and now the challenge is to populate the data for the months Jan, Feb, Apr, May, Jul, Aug......I do have a date prompt on my prompt page with which the user will be able to select the Begin Year & Month and End Year & Month.

Thanks in advance for your help!

[pricter]

I do not have a report studio on front me but I think that the follow will work

Create a calculation to find for each date the quarter for example for the date  03/31 you will have 1.

Create a second query that will have months and quarters
for example
Month Quarter
1          1
2          1
3          1
4          2
and so on

Inner join these queries on quarters data items.

If you have more than one year you should also include the year in your quarter for example first quarter of 2011 201101.

[karthik.cognos]

Hi pricter, I created a query as you suggested but could not figure out how to proceed further...Could you please shed some light?

[pricter]

Did you add a data item that calculate the quarter from the "original" query

A good approach to find the quarter from a date is the following


ceiling((([Date]/12)*4))


So in your originally query you will have
Date Quarter Measure

You join the above query with the new query that you created on quarter data items

And on the third query ( that is created by the join of the two queries) and the
Month from the new query and Measure from the original query

[karthik.cognos]

It worked. Thank you very much!