Urgent Help! Filter problem~

[whloo]

Hi! I am a Front End user for Business Object application.
i have this 3 fields.
Name, Part-Time Salary %, Full Time Salary %

What i want to do is to ONLY display the result that its Part-Time salary % that is in the range of 40% to 60%.
I created a flag variable to check if the Part-Time Salary % is between 40 and 60%, so the flag will be "Y" and "N" if it is fall out of that range.
I manage to do that successfully.

But my problem is i want to display only all those records that has flag "Y" only. I tried many ways to exclude the "N", but seems like i can't find any way to do that
Please help~
Thanks!

 

[vaass]

A Question
The fields are created at the report level or they belong to database

 

[whloo]

it is at the report level.

 

[vaass]

Try this

IF (<Part-Time Salary%> > 40% and <Part-Time Salary%> < 60%) then 1 else 0

For percentage calculation use NoFilter function

Then rank the top 1 according to the flag variable

 

[whloo]

what do u meant by percentage calculation use no filter function?

anyway, it works partialy!!!
thanks!!! but it won't work if none of my data is not fall between 40 and 60%..... then it will show all data with &quot;0&quot;

 

[whloo]

so sorry....
it shld be....

it will not work if all of my data not fall between 40 and 60%. then it will show all data with 0 value

 

[whloo]

any other solution for it?
the ranking not really work because if all of my data is not in the range, then it won't work

 

[vaass]

You want to display the rows falling between 40% and 60%, if there is no record satisfying this condition then this will not return any rows. Hope this is what you want.
I didnt understand what r u asking for.
Lemme know

 

[whloo]

vass....

lets say,
name - Part-Time Salary% - Full-Time Salary% - flag
A - 45% - 55% - 1
B - 25% - 75% - 0
C - 55% - 45% - 1

so, when we apply the ranking based on the condition it will return:
A - 45% - 55% - 1
C - 55% - 45% - 1

which is correct.

but when the records are:
A - 35% - 65% - 0
B - 25% - 75% - 0
C - 85% - 15% - 0

when we apply the ranking, it will return us:
A - 35% - 65% - 0
B - 25% - 75% - 0
C - 85% - 15% - 0

which is wrong. it is because we always return the top 1 no matter what.
hope you get what i meant.
thanks

 

[blom0344]

There is a dirty way out of this by turning your query into a union query where you fetch just one record in the additional query that will ALWAYS return a flag 1 at the report level , call it Report_check or Dummy. You may need to experiment a little or add a dummy record to the database for just this sort of occasion

T. Blom
Information analyst
tbl@shimano-eu.com

 

[whloo]

the problem is that i can't touch the database record
btw, Part-Time Salary%, Full-Time Salary% and Flag are variable that created by me it is not directly an object from universe....

 

[blom0344]

I expected these to be report variables, but that does not matter if you are not doing an aggregation at report level.
If you have no suitable record in the database, you can always edit the SQL in order to modify for it to work (either use free-hand SQL or activate 'Do not generate SQL' option.
I admit this is a 'dirty' approach to your problem....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[whloo]

thanks bloom for the quick response.
too bad i am as a end user that don't have access right to do all those thing.
if not, i would have create a filter object to do all those
so, the conculsion is that my problem can't be solved at my level of access?

 

[blom0344]

Come on, you should be able to create a union query with query panel. Use the button that shows intersected circles and you can either select union,intersect and minus options. Union is the one that has the 'U' as decal, which gives you a second tab in query panel to play with. Set up conditions to ensure you get a proper record returned to turn up as a '1' in the report flagging.........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[whloo]

bloom....
it is not possible for mine at all.
it is because Part-Time Salary% and other variables is created after few complicated condition calculation. there are no direct object in my universe that able to let me do those thing in query.

 

[Sridharan]

Hi whloo,

Do as vaass told. But have two tables one below the other. One table with the required data for display and the other with just maybe heading. Once you do the ranking and you don't have any data for the percentage to be displayed hide the main table and show the other table.

Sri

 

[whloo]

Sridharan,
let me tell you a more details on my situation and you will know why i can't use query.

Lets say i have
Com ID, Component, Sales Revenue.
Com1, C1, $100
Com1, C2, $100
Com1, C3, $100
Com1, C4, $100
Com1, C5, $100
Com1, C6, $100
Com1, C7, $100
Com2, C1, $200
Com2, C2, $200
Com2, C3, $200
Com2, C4, $200
Com2, C5, $100
Com2, C6, $100
Com2, C7, $100

I have all these data.
Now, i need to group all Component into 2 grp, G1 and G2.
G1 = C1, C2, C3, C4
G2 = C5, C6, C7

Now after i created a variable for those 2 group. my data will be:
Com ID, Component, Sales Revenue, Group
Com1, C1, $100, G1
Com1, C2, $100, G1
Com1, C3, $100, G1
Com1, C4, $100, G1
Com1, C5, $100, G2
Com1, C6, $100, G2
Com1, C7, $100, G2
Com2, C1, $200, G1
Com2, C2, $200, G1
Com2, C3, $200, G1
Com2, C4, $200, G1
Com2, C5, $100, G2
Com2, C6, $100, G2
Com2, C7, $100, G2

Next, i want to know the percentage contribution of each group which is higher level than the Component.
so the result will be as below.

Com ID, Group, Sales Revenue,
Com1, G1, $400
Com1, G2, $300
Com2, G1, $800
Com2, G2, $300

Lets say now i want to see only company that its Group contribute more around 40-60% from total revenue.
Hence, i create another 2 variable to calculate each group percentage:
Com ID, Total Sales Revenue, G1%, G2%
Com1, $700, 57%, 47%
Com2, $1100, 72%, 28%

from here, we can see that we want to see Com1 only because it fall in the range of 40-60% contribution.
I created a flag 1 or 0 for that purpose and use ranking to do it.
Com ID, Total Sales Revenue, G1%, G2%, Flag
Com1, $700, 57%, 47%, 1
Com2, $1100, 72%, 28%, 0

Hope you understand now why i can't use the query.