How to reference a column value in a particular block.

[Former Member]

I am having problem in referencing a column in a particular Block in WEBi report.

Scenario: I have only one query in the query panel and i am creating a sample report having different blocks.

               Now there are four blocks in my report,

               1st Block: It it a table with two columns [country name], [Turn over]. Now this is converted into pie chart.

               2nd Block: It is the same table as above but it is linked with the block 1 so that whenever i clicked on a particular company name

                               in the pie-chart then this block will be showing only the company name and its turn over for that particular company.

               3rd Block: It is also the same table as above having same columns but it is showing top five company names and their turn over value.

                               I used ranking function through a variable for impelmenting this.

               4th Block: It is a table having columns [Company Code], [Company Name], [Turn over], [Company Address].

Problem:  Now i want to show hide 4th block when the [company name] value in 2nd block lies in the list of top 5 companies in 3rd block or else 4th block

               will show the table having complete info of the particular company which has been clicked in the pie-chart and 2nd block will be showing the

                comapny name and its turn over value.

Please help me with this problem as soon as possible, i am learning WEBi tool and now i am stuck with this use case.

[Former Member · Accepted solution]

Hi Saurabh,

Select 4th block, right click select "Hide" --> "Hide When" -->Select the check box "Hide when the following formula is true:" . In the formula editor specify the condition that you wold like to give, which is a ranking condition similar to the information that you would like to display.

~Manoj

[Former Member · Accepted solution]

Thnx for reply Bala Manoj...

But the problem is , what is the formula that i have to apply for this situation....

Let me give you an example what i want to do....

1st block: Let's say i clicked on country name- ITALY in the pie chart.

2nd block: Now the second block will be showing ITALY in column [country name] and its turnover in                     [Turn Over] column

3rd block: This block is showing the names of top 5 countries in column [country name]  on the basis of their turnover and their corresponding turnover in column [Turn Over].

Now I want that if ITALY is not present in the [country name] column values inside 3rd block then i will show the 4th block in which i am mentioning the [country code] [country name] [Turn Over] [Continent] etc. relevant information corresponding to ITALY.

please help me out with that formula, because i don't know how i can reference the value present in the [country name] column in 2nd block and then match it with the list of values present in [country name] column in 3rd block within a formula.

Your help is appreciated....

[Former Member · Accepted solution]

Hello Surabh,

Thank you for providing an example, from the example try the steps from 3^rd block to reach your requirement

1st block: Let's say i clicked on country name- ITALY in the pie chart.

2nd block: Now the second block will be showing ITALY in column [country name] and its turnover in [Turn Over] column

3rd block: This block is showing the names of top 5 countries in column [country name]  on the basis of their turnover and their corresponding turnover in column [Turn Over].

Here on 3rd block you have to place  logic for ranking

1. Create a report level variable of type Dimension with name "Ranking"

Ex:

=NoFilter((Rank([Turn Over];[Country name];Top)))

2. Add a block filter as:[Ranking]Less than or equal to 5 , to show top 5 countries

3. Create a new Dimension Variable with name(As you like) "Show" with the below definition

=If([Ranking]>5;1;0)

4. Add the Variable Dimensions created [Ranking], [Show] objects to2nd block

* Right click on [Show] column and choose "Hide" ->"Hide dimension", this will hide [Show] column from the table.

* Select 2nd block right click, select 'Format Table'->Select the check box "Hide when following formula is true" and define a condition as

=([Show]=1)

5. On 4th block add Variable dimension objects created [Ranking], [Show]

* Right click on [Show] column and choose "Hide" ->"Hide dimension", this will hide [Show] column from the table.

* Select 2nd block right click, select 'Format Table'->Select the check box "Hide when following formula is true" and define a condition as

=([Show]=0)

With this when rank of a country greater than 5 then block 3 will be displayed, otherwise block 4 will be displayed.

Hope this answers your requirement

~Manoj

[Former Member · Accepted solution]

Thnx for the reply Bala Manoj,

This really helps alot...thank you for your help...

Can you tell me one more thing that..

If i want to compare the value in column [country name] of block 2 with the values in column [country name] of block 3...then what do i have to do?

Because in this case we made a variable for ranking , and then on the basis of that we have done calculations, but let's say there is no limiting condition for block 3 and then we have to compare the values. So, what will be the procedure.

FYI...the variable "Ranking" as mentioned by you in your answer can't be made as of Dimension type...WEBi is automatically taking it as a measure, and when i am trying to change its type then it is throwing the error.

ERROR:  The qualification of the variable Ranking cannot be changed. (IES 10083) (WIS 10083)

your help is appreciated...

[Former Member · Accepted solution]

If i want to compare the value in column [country name] of block 2 with the values in column [country name] of block 3...then what do i have to do?

Right click on the table block1, select  linking ->Add element linking

In “Define Input control” pane, selection the option “Single object”

Select the object that you would like to compare, click Next, click Next.

Select other report block (block2) that you would like to compare/filter and click finish.

With this based on your dimension value selection on block1, block2 will be filtered.

Use webi rich client/Java report panel tool to create report level dimensions, it will work