Limiting number of rows in result

[M39Sthlm]

Using Crystal Reports XI

I have a report with a crosstab and a couple of charts.

I need top N functionality to limit my result to last 10 rows (both in my crosstab and my charts).

My SQL query is a simple select * from table

In my crosstab I only have Columns and Summarized fields. Since I don't have any rows in my crosstab I guess i can't use the built in top N feature in Crystal? The group sort expert choice is greyed out.

I guess there are alternative ways to get a top 10 result?? How?

How to fix a top 10 result in my crystal report? Help would be greatly appreciated.

(DBMS is Sybase Anywhere 8).

 

[lbass]

Add the field as a row instead of a column, and then you can have the topN functionality.

-LB

 

[M39Sthlm]

lbass: Hmmm but if i do this it gives my crosstab a totally different look which I don't want?

In my crosstab the columns are "reported date" and the summarized fields the values (using no rows).

This gives the crosstab the look I want if I also surpress row grand totals.

 

[lbass]

I guess I'm not sure why you are even using a crosstab or what the top 10 relates to--the 10 most recent records? So you want a row of dates with a second row with a value for each date?

-LB

 

[M39Sthlm]

lbass:

Yes, top 10 relates to 10 most recent records.

My crosstab looks like this: I have colums with different dates and rows with different data (all rows in my crosstab are different table columns in my database table).

Would it have been easier not using a crosstab for this? (at least for the top 10 effect I guess)

 

[lbass]

Do you mean you are adding a separate summary for each field? Earlier you said there were no rows entered in the crosstab. Can you show a sample of what the results look like, using several fields?

-LB

 

[M39Sthlm]

I do have rows but I'm using the summarized fields as rows. Not the actual "rows" section in the cross tab expert (if you understand what I mean).

In the cross tab expert I'm using reported dates as columns and the values (different table columns) in the summarized fields section). But I have nothing in the "rows" section.

Summarized fields are set to vertical and showing labels.

I'm surpressing row grand totals (not showing any sums only values).

 

[lbass]

I guess you could create a separate formula for each field:

if {table.date} >= nthlargest(10,{table.date}) then {table.field}

Add each formula as a summary field in the crosstab.

Add a group selection formula (report->selection formula->GROUP):

{table.date} >= nthlargest(10,{table.date})

This only works if each instance of the date field is unique.

-LB

 

[M39Sthlm]

lbass: Thanks but I never tried your formulas. Solved this by using the command feature instead. (A more convenient solution for me in this report since I have many objects using the same query).

http://www.tek-tips.com/viewthread.cfm?qid=1586378&page=1