I have a crosstab report that displays performance measures by fiscal year. The x-axis displays the measure name and the y-axis lists the months of the selected fiscal year. I need to add a calculated column that displays the last performance measure of the fiscal year. For example if a certain measure name only has performance measures up to March, then I want to show the value for March in my calculated column. How can I do this?
Just to clarify - you have months layed out in columns, and each PM (row) may have one or more rightmost month columns which are not populated (null or zero), and you want to add a column to the right which contains the rightmost (latest) populated fact value? Only one column to the right for the latest year, or one for each year reported (i.e. do prior years also have potentially missing later values and you want to report the latest for each year)?
The short answer, assuming a single added column to the right for latest year and not one for each year: change your query basis to a UNION tabular set and add a second tabular model which returns each PM with its latest fact value. Include a dummy year '9999' for grouping so it always sorts highest (so you may need to add another data item to display on the report).
If you need more details on unioning or on formulating your latest-fact subquery post back and I'll elaborate.
Thank you for the response. I don't think I explained clearly the first time. Below is how I want my report to look. As you can see, the YTD column shows the last PM value for each Measure Name. Performance measures may be missing for certain months, but the YTD should be the last value.
Measure Name | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | YTD |
Performance | 75% | | 75% | | 78% | 85% | 65% | 75% | 81% | 83% | | | 83% |
Call Rating | 85% | 84% | | 86% | 88% | 85% | 85% | | | | | | 85% |
How would I go about changing my query basis to a union tabular set?
OK, here goes...I'm assuming that under the covers you are grouping on a data item which includes the year, and are reporting on fiscal year, since you are displaying months out of calendar sequence.
For purposes of this discussion let's assume you currently have a tabular model containing these data items:
measurename
yearmonth (YYYYMM)
monthname
value
In report studio, go to your query.
Select the tabular model and Cut.
Drag in a Tabular Set from the Insertable Objects Toolbox tab.
Doubleclick the Tabular set you just added and Paste. This will put your current tabular model in the left box.
Drag a tabular model from the toobox into the right box.
Now, add data items to the new tabular model so that it returns the same number and type of data items as your original, in the same order, but containing:
measurename (from database)
yearmonth='999999'
monthname='YTD'
value (last-populated-value-for-year from database)
Post once more if you're not sure how to return those values in the second model - hint: use a child tabular model beneath your new tabular model to return the measure names and their maximum time periods.
What version of ReportStudio are you using? I am using 8.2 and do not see any option for tabular set. When I go to my Query Explorer and click on my query, the only Insertable objects are: 'Data Item, Filter, and Slicer Member Set'.
Sorry, I'm running Reportnet, not Cognos 8 (there is a separate forum here for 8). The good news is, the method still applies - the bad news is I can't tell you what the equivalent concepts are in your environment. You still want a union, however that's implemented, and in place of "child tabular model" I think based on what I've read you can just create a separate query and join to it (though I'm not sure if "Query" is the correct terminology).
Good luck...
Thanks for your help. I'll post in the correct forum :)