inputcontrol value in webi

[Former Member]

Hi,

We have 2 tables in webi report. Table 1 is with objects Region, measure1, measure2; Table 2 is with objects Category, measure1, measure2.

We have 2 input controls region and Category. Above two tables have dependencies on these two tables.

We have below scenario:

When user select any value from Region input control (other than 'All values') then measure2 display zero in table2. And

When user select any value from Category input control (other than 'All values') then measure2 display zero in table1.

Appreciate your help,

Regards,

[Former Member]

Hi,

is it a real time requirement or your experiment?

Got Solution? Pls Update.

[Former Member]

Yes, It was real requirement when we don't have data for all measures for all categories.

Anyway it was not solved. And now we have data for all categories so we don't have this requirement now.. 🙂

Thank you for all your support as I achieved some results with your suggestions.. 🙂

[Former Member]

Thanks for the Update

[Former Member]

Hi Prem,

Update the discussion if you got the answer.

Thanks.

[Former Member]

Hi Parthibhan,

Still no luck..

When we select 'All values' in Category then below are the results

Count1	Count2	Measure2	Measure2
Region1	3	3	Condition True so it will display measure2
Region2	1	1	Condition True so it will display measure2
Region3	3	3	Condition True so it will display measure2

When we select 'Cat1' in Category then below are the results

Count1	Count2	Measure2	Measure2
Region1	1	3	Condition True so it will display measure2
Region2	1	1	Condition True so it will display measure2
Region3	1	3	Condition True so it will display measure2

When we select 'Cat2' in Category then below are the results

Count1	Count2	Measure2	Measure2
Region1	1	3	Condition True so it will display measure2
Region2			Condition True so it will display measure2
Region3	1	3	Condition True so it will display measure2

I am expecting measure2 should be 0 if we select any category using Category input control (combo box)

Thanks again,

Regards,

Prem

[Former Member]

Hi Prem

1. Are you getting 'Measure2' values when 'Cat2' is selected?

2. Kindly provide more information on your requirement. Why 'Measure2' should be 0 when any one of the category is selected?

Think, its not possible to achieve the requirement due to the input control behaviour of having all the categories irrespective of Region, but I will try.

Thanks.

[Former Member]

Hi Prem,

Please update the discussion if the above solution works for you.

Thanks.

[Former Member]

Hi Prathibhan,

Thanks again for your reply. Still i am not achieving expected result with above formulas:

We have the below data for Region and Category:

Region	Category
Region1	Cat1
	Cat2
	Cat3
Region2	Cat1
Region3	Cat1
	Cat2
	Cat3

so if we use Category input control and select Cat1 then count will display as below:

	Count([Category])	Nofilter(Count([Category]))
Region1	1	3
Region2	1	1
Region3	1	3

If we select Cat2 then counts are as below

	Count([Category])	Nofilter(Count([Category]))
Region1	1	3
Region2		1
Region3	1	3

if you do not select any Category then counts are as below:

	Count([Category])	Nofilter(Count([Category]))
Region1	3	3
Region2	1	1
Region3	3	3

I need the below result (Measure2 is Zero) if you select any category:

Region	Measure1	Measure2
Region1	1	0
Region2	2	0

Thanks again for your time..

Regards,

Prem

[Former Member]

Hi Prem,

What i understand from your reply is, if Cat1 is selected, then Region2 is showing the Measure2 instead you are expecting 0. You are expecting Region2 should display Measure2 if All values is selected in the input control. Am i right?

Kindly note, Region2 has only one category Cat1, hence, selecting Cat1 or All values gives same sense. So, you can use the formula. If you expect 0 , then please explain more on your Scenario.

Thanks,

Parthiban

[Former Member]

Hi Prathibhan,

Now we both are on same page. 🙂

Region2 has one category Cat1 only.. But Category input control will display all three Categories (Cat1, Cat2, Cat3).

So Measure2 display 0 for all regions if we select any category (other than 'All values).

Hope i explained issue clearly now..

Thank you and Regards,

Prem

[Former Member]

Hi Prem

Kindly try the below formula:

=If(Count([Category])<>1 Or Count([Category])in([Region])=1) Then([Measure2]) Else(0)

Update the discussion if it works.

Thanks,

Parthiban

[Former Member]

Hi Parthiban,

Thank you again for your reply. It is not displaying correct result:

When we select 'All values' in Category then below are the results

	Count	Count in Region	Measure2
Region1	3	3	Condition True so it will display measure2
Region2	1	1	Condition True so it will display measure2
Region3	3	3	Condition True so it will display measure2

When we select 'Cat1' in Category then below are the results

	Count	Count in Region
Region1	1	1	Condition True so it will display measure2
Region2	1	1	Condition True so it will display measure2
Region3	1	1	Condition True so it will display measure2

When we select 'Cat2' in Category then below are the results

	Count	Count in Region
Region1	1	1	Condition True so it will display measure2
Region2			Condition True so it will display measure2
Region3	1	1	Condition True so it will display measure2

As I mentioned I am expecting measure2 should be 0 if we select any category using Category input control (combo box)

Please help me any other work around.

Thank you in advance for your time,

Cheers,

Prem.

[Former Member]

Hi Prem

Please try this modified formula:

=If(Count([Category])<>1 Or NoFilter(Count([Category]);All)in([Region])=1) Then([Measure2]) Else(0)

I can't able to produce the same scenario. hence, above formula is just a guess. Try this and update.

Thanks,

Parthiban

Message was edited by: Parthiban Ganesh

[Former Member]

Hi Prem

Use the below formulas:

=if(Count([Region])<>1) Then([Measure2]) Else(0)

=if(Count([Category])<>1) Then([Measure2]) Else(0)

PFB Screenshots:

Please note: Last Column of Second table has Count([Quarter]), please dont consider the Name of that column. PFB Screenshot:

Hope it helps!!!

THanks,

Parthiban

Message was edited by: Parthiban Ganesh

[Former Member]

Hi Prathibhan,

Thank you for your reply.

Above formula will work if we have more than 1 Category per Region. But in one scenario we have 1 category only per 1 region. So it is always false with below formula and displaying Zero for that Region.

=if(Count([Category])<>1) Then([Measure2]) Else(0)

Can you please suggest?

Thanks again,

[Former Member]

Hi Prem

Use the below formula:

=If(Count([Category])<>1 Or NoFilter(Count([Category]);All)=1) Then([Measure2]) Else(0)

PFB Screenshots:

The above formula will display [Measure2], if [Category] has only single value as LOV or All values is selected in the input control. Kindly check and confirm.

Thanks,

Parthiban