How can I limit the number of records (for testing)?

[kubu]

Sorry, but this is probably a real "noob" question. I'm new to CR (using CRXI with a BOXI server). I'm accustomed to writing my own report SQL as stored procedures, but this is not an option for my current project.

I have an issue similar to the one posted by ddnh; that is, my reports involve massive recordsets, and any modification to the data fields locks CR for eons while it reprocesses the records. I've tried using a Record Selection Formula to limit the number of records, but the nature of the underlying query is such that this method is not proving to be effective.

What I really need is a way to return just the top n records. I've tried (unsuccessfully) to incorporate a SELECT statement in a SQL Expression to do this. The only workaround I've found is so far is to create a Command Object that uses the FETCH FIRST n ROWS ONLY clause (DB2 datasource) and link it to the main query. But, this is not very elegant. Plus, this linking nixes the use of server-side grouping and SQL Expressions — iow, it just ain't gonna fly.

Oh, and a test database containing a limited number of records is not an option. In fact, I'm actually forced to develop against production data, and I've been warned not to hose the network. Scary, huh?

Suggestions?

Thanks!
Mark

 

[lbass]

How about explaining:

I've tried using a Record Selection Formula to limit the number of records, but the nature of the underlying query is such that this method is not proving to be effective.

Why can't you limit the report to 10 unique IDs or something like that?

-LB

 

[kubu]

Well, that's a valid question. Part of the challenge in this project is that I'm working with very unfamiliar data, no data dictionary, etc. As I gain a better understanding of how the data is related, this may be an option. So far, though, it appears that the tables use concatenated keys (three and more fields!). The reports are predicated on dates, but each date can have hundreds of thousands of records.

I'm hoping to find a quick-and-dirty way to limit records so that I can just start learning my way around CR without fully understanding the data. Now, you may wonder how I can create reports without knowing the data. It's complicated... I'm migrating reports that exist on another platform, and I have access to the base queries and the finished reports. However, I don't know how the current solution massages the resultsets.

So, maybe what I'm hoping for can't be done in CR. If so, I'll just have to invest more time querying the database directly to get a feel for unique values, and then leverage CR's Selection feature.

Thanks for the reply,
Mark

 

[synapsevampire]

I think that your question is more about advanced DB2 than Crystal, and there aren't many experts around here, try posting in the DB2 forum:

forum178

Limiting rows is VERY simploe in Crystal, place something in the record selection to do so based on a date or some such.

I just did a quick search on this and found a GROUP by with the FETCH FIRST clause here:

http://www.devx.com/getHelpOn/10MinuteSolution/16573/0/page/2

example:

SELECT CLIENT, SUM(SALEVOL) AS TOTALVOL
FROM MYSALES
GROUP BY CLIENT
ORDER BY SUM(SALEVOL) DESC
FETCH FIRST 2 ROWS ONLY

Not sure why thsi won';t work in a Command Object for you.

If you're simply trying to lim it the rows being returned to a manageable number, using a TOP N is likely slower, just use an additional WHERE clause against a date or some such to limit the rows.

-k

 

[kubu]

Thanks for your response. I understand the concept of limiting records in Crystal — e.g. using a record selection formula to limit by date. Again, the problem is that this is a very high-volume db. Even if I put something like {table.datefield} >= CurrentDate in the record selection, I'll still get back 100k+ rows.

Maybe I can simplify my question: Is there a way other than a SQL command to dynamically insert the FETCH FIRST n ROWS ONLY clause? Can this be done either through the record selection formula or a SQL Expression?

Thanks,
Mark

 

[lbass]

You would have to do it in a command. Still don't see why you can't select IDs that are unique to a row or that would return only a few rows each, and use them in your record selection formula. I work with a database that has millions of records, and I do this all the time.

-LB

 

[synapsevampire]

No, if it doesn't work in a Add Command, it won't work.

As LB suggests, further qualify the result set by limiting something else.

So if you're returning just one date, also add in that some string starts with the letter A or some such, or if you know the IDs, limit it that way.

I think that you're trying to over complicate this by using the FETCH FIRST.

-k

 

[Turkbear]

Hi,
I'll chime in and agree ( as I usually do) with synapse..
Find a field that has a string anduse that to limit the records by adding a where clause using a rare letter combo..I used to use "where last_name starts with 'X'" to limit employee data but as our workforce got more diverse, it is not so limiting anymore...I now use "first_name startswith 'Y'" instead..
Interesting note: Some previous versions of Crystal Reports allowed you to 'sample' a fixed number of records if you wished - it was one of the options you could set.








To Paraphrase:"The Help you get is proportional to the Help you give.."