record selection formula and query

[dsangel]

I'm calling a crystal report from VB. The report is being passed a parameter used for record selection. The Report runs an SQL query, then runs the record selection query. I only want the record selection query to run b/c the report query has no criteria and takes 10 minutes to execute.
I would like the same functionality as using a parameter, but I don't want the user to see a parameter prompt. Is this possible?

When I run a profile on the program, I see that this query runs
select...from.... **[no critera]
The data returned is not used in the report.
Then I see the query from the record selection.
QUERY =
Select.....From...where field_from_table = UserId
record selection formula =
field_from_Table = @formula(UserId passed from VB)

 

[ScottGS]

dsangel,

What mechanism are you using to call the report? Shelling to a compiled report; report viewer Active-X control (Report Engine Automation Server); or the Report Designer Component?

If it is a compiled report, then you can pass option info as in a file which is one of the report data sources. This is not very elegant unless you are just tacking extra fields to an exported data file for the rest of the report.

If you are using the Automation Server, then you can set Parameter fields in VB using this sort of syntax:
report.ParameterFields("UserID&quot.SetCurrentValue(strUserID)

If you set a parameter field in code, then the user will not be prompted for it.

Alternatively, you can use a formula. In your report, define a formula @UserID = "" (just to define it). Then use this VB code to replace it with the user ID you want passed:
Report.FormulaFields.GetItemByName("UserID&quot.Text = strUserID

All of these methods have examples, but sometimes it is tricky (at least for me) to wade through them.

You can do all this and more (with different syntax, of course) with the Report Designer Component. Using that, you can manipulate just about everything in the report, including labels.

Scott

 

[dsangel]

Thank you for answering my question. I was trying not to add any library references to our VB program, but there isn't a way around this problem. Most likely we're going to use the Automation Server solution setcurrentvalue, or something similar. Now that we're going to add a Crystal Report reference library, I'll be free to play around with the different solutions you suggested. Thank you again.

 

[ScottGS]

dsangel,

One other idea...
If you want to avoid adding references (which suggests that you are currently shelling to a compiled report) then you might consider using your VB program to do the record selection to arrive at a smaller dataset. The report would then look at that. As for the location of the data, that is up to you. For example, lets say you are using an Access db with a huge table of user info. The report currently gets the whole table and then does a record selection within based on the passed (or entered) userid.
A faster method would be to have VB execute a query to create a temporary table with just the records of interest, and point the datasource to that table.
Similarly, you could have VB create a query in Access (or view in SQL server) which limits the records, and use that as the report datasource. These approaches would not require adding any Crystal references to your project.

The new functionallity of Crystal/VB is pretty cool though so it may be worth exploring anyway.

Scott