Calculation referencing crosstab cells values

[fcapell]

Hi everyone!

Please forgive me if my question is too basic, but I'm a newbie.

I have a Report Studio report which access a cube on TM1 database. It's a Cognos Express 10.1 installation. I'm attaching a screenshot so you can see the page layout.



I need to put a calculation at the bottom of the crosstab, right bellow the "Total" line (exactly where the '=Act/Bud' text is). Calculation is: Total MTD Actuals / Total MTD Budget.

Since the crosstab has the values I want to use in my calculation, is there a way of creating this calculation referencing those crosstab cells values? I don't know, maybe referencing the crosstab values through their cells coordinates: is this possible?

Any suggestions would be gratefully appreciated  :)

Best regards,

Fausto

[MFGF]

Hi,

Whatever calculation you add within the crosstab will need to be either a new row or a new column. You could reference the appropriate intersections using tuple() functions but the value would display throughout the entire row or column Is this what you want?

Another option is to add a singleton outside the crosstab and use the same approach - this time it will display a single value wherever the singleton is located.

Cheers!

MF.

[fcapell]

Hi,

Whatever calculation you add within the crosstab will need to be either a new row or a new column. You could reference the appropriate intersections using tuple() functions but the value would display throughout the entire row or column Is this what you want?

Another option is to add a singleton outside the crosstab and use the same approach - this time it will display a single value wherever the singleton is located.

Cheers!

MF.

Hi MF! Thanks for the reply!

I have wondered something like putting a tuple inside a singleton but I'm afraid I can't create a tuple. Tuple function asks for dimension members and this "Total" whose value I need is a data item of type "Dimensional Edge Summary" rather than a dimension element.



Is there a way of creating such a tuple?

Best regards!

Fausto

[MFGF]

Hi,

Is that just the way you have written the report, though? Does the dimensional edge summary equate to the same value that would be returned if you used the "All" member of the dimension?

If it's not that easy, then you can use an aggregate() function instead of a tuple. For whatever set of members you are using in your dimensional edge, you can say aggregate([your chosen measure] within set [your dimensional edge set of members])

Cheers!

MF.

[fcapell]

Hi,

Actually this Total is not a dimension consolidation member like "All members". So I kinda followed your aggregate function suggestion.  But I used total() function instead, putting it inside a singleton.

Take a look at the output:



Here are the steps I did:

[1] Duplicate the query.
[2] At the crosstab cell where I want to place the calculation, change "Define Contents" property to "Yes".
[3] Drag and drop a Singleton to that crosstab cell.
[4] At the Singleton, change "Query" property to the duplicated query.
[5] At the duplicated query, create a Data Item using the following expression:

Code Select Expand


  (  total ([Measure_Amount] within set [MemberSet_Hotels], [Rooms Revenue],  [DataItem_MTD], [Actuals])
   / total ([Measure_Amount] within set [MemberSet_Hotels], [Occupied Rooms], [DataItem_MTD], [Actuals])
  )
/ (  total ([Measure_Amount] within set [MemberSet_Hotels], [Rooms Revenue],  [DataItem_MTD], [Budget] )
   / total ([Measure_Amount] within set [MemberSet_Hotels], [Occupied Rooms], [DataItem_MTD], [Budget] )
  )


I forgot to tell you that "ADR (Average daily rate)" is also a calculation, which is [Rooms Revenue]/[Occupied Rooms].

[6] Drag and drop the data item created on step [5] into the singleton.

Please fell free to suggest a more elegant solution :)

I wish I could create a data item to get crosstab cells values using "Row Coordinate" and "Column Coordinate" properties. It that even possible?

Thank you!

Fausto