"Browse" does not pick up all the data

[Rob32]

Hi

I am using Crystal Reports 8.5, to query a "view" (this view was created by one of our DBA's) of an SQL database. When I use the browse option to look at the data in a field, it only shows, say, 20 entries. However, when I query the same field using Crystal SQL Designer, there are in fact about 80 entries in it. I can't figure out why the browse option doesn't show all the entries. It's proving really difficult to create a report that I have any confidence in, because I'm having to manually enter data to select on, create parameter fields etc. I have tried database -> verify database, and it tells me the database is up to date. Any help greatly appreciated.

Cheers, Rob

 

[chelseatech]

"Browse" is only a sample of the data. Imagine if it was going to return all the entries in a table of hundreds of thousands of records.

So depending on the CR version, it is a sample of the first 200, 300 or 500 records in the DB. You can also set an option for "Select Distinct Values for Browsing" in some of the later versions, so you get individual values rather than individual records.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[dgillz]

This is s registry setting, which as Chelseatech mentioned can be affected somewhat by the "select distinct values" option.

To change the registry, go to regedit, HKEY_CURRENT_USER, software, seagate software, crystal reports, database server, MaxNBrowseValues. Right click this key and select modify, and change the value form the default of 500 to whatever you need it to be.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Rob32]

Thanks for the replies DGillz & Chelseatech, however:

I found the "Select Distinct Values for Browsing" which was already ticked, so I unticked it and nothing changed, so I re-ticked it & nothing changed. I can't edit the registry because I'm on a work machine & don't have access.

You both talk about a default number of values in browse - the real problem for me is that I'm not getting anywhere near even the lowest value you mentioned of 200. I'm getting about 20 out of a possible 100 approx (+- 20).

I spoke to the DBA about it, & asked him whether it could be a problem with the design of the view he created. He seemed to think that because he could read all the values using an SQL query then Crystal should also be able to pick them up. I would tend to agree, but is there some layer in a view that an SQL query might be able to see through that Crystal can't?

Thanks again, Rob

 

[chelseatech]

I've also found a timer issue. The browse seems to only wait for a short time and displays the list as it's got it.

Is your Db a bit slow?

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Rob32]

Chelseatech

I've tested it using the same data in both Crystal SQL designer & Crystal Reports - works in SQL Designer, not in Crystal Reports. Maybe Crystal Reports is slower to query the DB? I noticed a time delay of only 4-5 seconds when browsing in CR. I've never noticed it as an issue before - my suspicions are raised that it's happening when I query a view rather than a table directly. I'll keep plugging away - any further advice greatly appreciated.

Thanks, Rob

 

[dgillz]

Rob,

Did you change the registry value as I suggested? If so what happened?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Rob32]

Dgillz

I can't access the registry, as I am on a work computer & they have us locked down pretty tightly.

Cheers, Rob

 

[dgillz]

Show your IT manager this thread and ask him to change it for you.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[redlynne]

might help...just to be sure, are you actually browsing all data on the "view"? Or are you using the view to query directly or via stored procedure? If you're passing any parameter to CR/stored proc,data might have been filtered due to this.
Check Database/Verify database and/or refresh report data(preview) and check for parameters being passed.