Present Aggregate Data Item of Last Period within Crosstab?

[torre2011]

The title of my thread seems simplistic, but I could not put everything in the title  :o

So here is my issue, I am building a dashboard within report studio, and i need to build a crosstab that carries the following items:

• Previous Quarter and Current Quarter for the columns
• Group Type for rows
• The measure is an aggregation of 2 data items (Numerator and Denominator).  Lets call this Score
• The last column must be a separate aggregation that takes the Current Quarter Score - rating from another query.  Lets call this data item Performance

So I am able to build the query for the crosstab to show the Score of both quarters, but I am unable to figure out how to JUST show the Performance for the Current Quarter rather than for both Quarters.

Below is some code to represent these key data items:

Score = ([Numerator]/[Denominator])*100
Performance = [Score] - caption([qryTargetResult].[Target Rating])

I have tried various methods to capture the value of the Current Quarter and then possibly use that within the aggregation for the Performance so that I can add that as a column to the crosstab..but to no avail!

Anyone have a suggestion for me...I am sure I am missing something here  ;D

-------------------------------------------------------------------------------------------------------------------------

I should add that this is an OLAP data source, so attempting to use a UNION was not successful.  From what I have been searching, I am trying to use 2 time periods within the same crosstab...the first periods are going to be both the current and previous quarter, and then the 2nd time period will be JUST the current quarter for the aggregated measure!

I hope I have not confused any of you :)

[navissar]

Try this:
Set up your crosstab as you would normally do without the performance. Then drag in a query calculation to the right of the last column with
total([Score] within set [currentPeriod]) - caption([qryTargetResult].[Target Rating])

[torre2011]

That will work!  Thanks.

I also was incorrect with my last reply, in that I could use a UNION to resolve this as well!

[torre2011]

Nimrod,

In your solution how would I go about only showing the last period, rather than the whole set, which in this case would be Quarter 1 and Quarter 2???