Rich Client Xi 3.1 - How do you reference a cell?

building reports webintelligence
1

I’ve built a fairly large and comprehensive report for something I’m doing, and through large amounts of jiggery pokery I’ve managed to get Business Objects to do everything I need it to, with the exception of one thing…

I have a summary tab, say, TAB1, and in it I have a table which contains various free text entries, but also contains a number of cells which I need to contain the same values as outputted by variables on other tabs, such as TAB2 and TAB3.

The values from the cells in question in those other tabs are calculated using variables on data in the adjoining block.

Trouble is, if I drop the variable object from one of the source tabs anywhere else other than in it’s parent block, it returns a null value.

In Excel terms, all I would need to do would be to reference the cell co-ordinates in the relevant Tab.

I have thought about Block Names, Report Names, Row Indexes etc, but I am just stabbing in the dark.

Can anyone shed any light on my dilemma?

Thanks in anticipation.

Steve Chapman :uk: (BOB member since 2007-06-28)

2

Steve,

It sounds like you need to familiarise yourself with ForEach functionality.

Have a read through the help topic within the application for it.

ForEach is used in calculation contexts to force the inclusion of objects in calculations.

3

Hi Mark,

Thanks for the prompt reply.

I’ve had a go at understanding the ForEach operand, and have managed to get my cell to go from returning a Null, to returning a #Multivalue, so I guess I’m getting somewhere. Trouble is, I’m trying to return a Maximum value of a Dimension based on a Measure with a conditional greater-than or less-than criteria.

I think I need further help. . . .

Steve Chapman :uk: (BOB member since 2007-06-28)

4

I know you can’t do it in Webi and I haven’t got rich client available at the moment but know you can do it in Deski. See if you can define your formula in TAB2 within the context that you have it.

#Multivalue simply means that there is more than one possible value to go in the cell, so putting Max() around your formula/variable may be appropriate.

5

Still trying to figure this out.

Just to elaborate, I have the Dimension, I have a measure variable next to it which displays the Percent Rank of the Dimension used in the formulaeic route of calculating Percent Rank (seeing as BO doesn’t have an in-built ability to do this - unlike Excel), and I have a measure for Max Rank as well.

Perhaps variables on variables on variables is where I’m coming unstuck?

The only examples I can find of the ForEach command in a similar sort of situation to where I am use all Dimensions, with no Measures.

Thought about maybe detailing what I was after exactly…

Ref(Dim) Value(Dim) PercentRank InvertedPercentRank
0001a 5.00% 0.000 100.00%
0002f 6.18% 0.004 99.64%
0003h 6.72% 0.007 99.28%
[…]

And so it goes on.

Reference is a dimension reference number for the id of the property.

Value Dim is the dimension variable that I want to display.

PercentRank is the measure percentrank of the value displayed as a spread of all values in the block (in excel it’s just “percentrank”, but in BO I have calculated it as : -
(MaxRank of Value Dim) - (Rank of Value Dim)
---------------------------------------------------
(Max Rank of Value Dim) - 1

Inverted Percent Rank is the inverse of the PercentRank.

I then need to apply 4 bandings to the block in a separate section which is defined by their percent rank. i.e. the Maximum ValueDim for the 0.005 percentrank band, then the maximum for the next band of percentage rank, then the next, then the next.

It’s quite complicated to explain.

It works fine if I make a cell within the body of the block in question, but I try to refer to this from outside of the block, it goes wrong.

Steve Chapman :uk: (BOB member since 2007-06-28)