Filtering on linenumber() function

[MikeKeane]

Hi,
I need to apply a filter to a crosstab based on the first 5 rows of data in the section. I cannot seem to get this working as it will not let me apply a filter on it. Also when i create a dimension and set a flag to say its "5 and below" based on the linenumber, this does not allow me to filter either.

Any help would be great.

Thanks

 

[promero]

Try applying a ranking. Rankings allow to show only the first N values of a dimension sorted by an associated measure. For more information see "Rankings" in the "Business Objects User Manual".

 

[blom0344]

Hello Mike,
To explain why your filter doesn't work:
BusObj makes a clear distinction between measures that come directly from the dataset and measures that are based on local variables. The first ones you can sort AND apply filter on, the second ones only allow you to do sorting.
I think linenumber is a local measure (as output of function)
Ranking is a powerful and often forgotten tool ...... T. Blom
Information analyst
tbl@shimano-eu.com

 

[MikeKeane]

Hi,
sorry i wasnt clear earlier.
Basically i am trying to achieve a minimum of 5 rows per table. I have created a union which appends 5 blank rows to the dataset that i am extracting, this works fine.
So sometimes with the data i pull back i only get one populated row of data plus the 5 blank rows.
If there are more than five rows of real data then i need to show them all, if not, e.g only one row, then i need to show that row plus 4 blank ones.
This is all to create a nice uniform bar chart and stop the creation of one big fat bar within the chart.
That is why i cannot use rankings because if u set it to 5 then the charts with more than 5 rows of real data will suffer.
I was hoping to use the linenumber with check to see if the data is blank and linenumber < 6 and filter that way but not successful.

Hope i am being clear this time.
Thanks for your responses, this site is a great help.

 

[blom0344]

Hello Mike,

Not exactly what you want, perhaps (tried it with ORACLE):

SELECT ROWNUM, X FROM T1
WHERE ............
UNION
SELECT ROWNUM*-1 , 'DUMMY' FROM T2
WHERE ........... AND
ROWNUM < 6 --- to get 5 dummy records ---

Put a calculated filter on the table on ROWNUM, such as
=<ROWNUM> > -4

and you get 5 records if you had 2 real records and 3 dummy to show.

To get this, but then fully automatically applied I use a second query to retrieve record count:

SELECT COUNT(*) FROM T1
WHERE ................. --- same where clause ---

Then I build a filter on ROWNUM like:

= If (<COUNT(*)> > 5) THEN (<ROWNUM> > 1) ELSE (<ROWNUM> >= (<COUNT(*)>-5))

This filter DOES select the right values of ROWNUM, but does not seem to work in the table.
Perhaps in newer versions than 5.1.3 this bug is fixed, otherwise it might give you inspiration......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[MikeKeane]

Thanks,
been going along the same route but to no avail.
will keep trying.
Bit annoyed really cus its only for cosmetic purposes to make the charts look better! But then again ive found that most problems and report writing time with BO are to make the reports look good, data is not usually the problem.

Will keep trying tho.
Thanks very much