Another problem with filter using RANK in report

building reports desktopintelligence
1

Hi,
I have a problem with a complex report. To make it simple here I am providing a sample data to replicate my problem.
The report should look similar to the following


DIM	MEAS1		MEAS2
Val1	10		0.1
Val2	20		0.4
Val3	0		0
Val4	30		1.0
-----------------------------------------
TOTAL 50		0.25

Where,
MEAS2 = MEAS1 / MEAS3
Here I am giving sample values for MEAS3


DIM	MEAS3
Val1	100	
Val2	50	
Val3	20	
Val4	30
-------------------
TOTAL       200

The user wants to eliminate the 0’s in the report, so I had to rely on the Rank function (a neat trick given by Dwayne/Dave- here
I cannot use the filters as the Measures are all complex report variables across different data providers and BO do not allow aggregates in complex filters)

Coming to the problem, I have the report exactly the way I wanted; the only problem is that the TOTALS are not the same for MEAS2.

The result looks like this:


DIM	MEAS1		MEAS2
Val1	10		0.1
Val2	20		0.4
Val4	30		1.0
--------------------------------------------
TOTAL 50		0.277

The total for MEAS2 should be 0.25 (= 50/200) but the value I am getting is 0.277 (=50/180).
Obviously, it is eliminating the Value of DIM Val3. I need that value to be included in the calculation.
I tried to use the extended syntax, but that value is not coming into the table. I used NoFilter(MEAS2), IN(DIM), FOREACH(the report has sections) , FORALL(DIM), In Block; but no result!

So I want to know can we get the filtered value (eliminated using Rank function) into the calculation using extended syntax?

I appreciate any suggestions on resolving this problem.

Thanks

multicube :india: (BOB member since 2005-09-28)

2

I think NoFilter can solve you issue!

Please see the append doc file! For further question, please do hesitate ot inform of me!
Demo.rep (58.0 KB)

fatball :cn: (BOB member since 2004-06-27)

3

Thanks for the Suggestion. NoFilter() should work on my report. But for some reason I could not able to get the data. I am still investigating. As, this a report with 6 data providers and most of the variables uses calculation contexts I am finding it difficult to debug.
But thanks for the tip. I will attach the report if I could’nt find a solution today.

multicube :india: (BOB member since 2005-09-28)

4

I still could’nt figure out why it is not giving the result with NoFilter(). I wish I could upload the file, but the size(above 256kb) is not permitting me to upload even after deleting unnecessary tabs, formulas or even purging other data providers…

multicube :india: (BOB member since 2005-09-28)

5

Ok, I got the solution… :lol:
But could’nt understand what is happening… :roll_eyes:
The varaible to which I am using NoFilter() was defined like this :
=Sum(If InList (“AB”,“BC”,“CD”) then else 0)

I restricted the data at the query itself to change the formula as
=

And if I apply NoFilter() to that variable, it is working! :blue:

Thanks for the tip. (NoFilter())

multicube :india: (BOB member since 2005-09-28)