Trying to change from pull to push but can't get SetDataSource to work 1

[cmers]

We have a server running Crystal 8 and are delivering reports through ASP pages. The datasource is an Access database on the same server. I design the reports in Crystal 8.5.

For the reports I've designed so far, I've used a System DSN for the Access database (pointing Crystal at the System DSN under ODBC datasources), and all the reports pull all data into the report and do filtering there. That is getting slow, and I want to try doing some of the filtering based on parameters supplied in the ASP page, then pusing the filtered recordset to Crystal.

I found some examples online of doing this. I have a test report I made based on one table, using a TTX file as the datasource. Then I tried modifying the working ASP code to pass the recordset. But when I try to use SetDataSource, I get this error in the web browser:

Microsoft VBScript runtime error '800a01bd'
Object doesn't support this action

It always references the line with SetDataSource.

I've tried a couple ways:
session("oRpt&quot.Database.SetDataSource rs
session("oRpt&quot.Database.Tables.Item(1).SetDataSource rs

The examples I found for this were all VB examples. Does this not work in an ASP page?

I have tried iterating through the recordset before doing SetDataSource and have verified that the code to build the recordset from the Access table is working ok (i.e., I get the table data displayed before I get the VBScript error above). I just don't know where to look for the problem!

Any help is much appreciated!
-Clint

 

[synapsevampire]

This:

"and all the reports pull all data into the report and do filtering there"

is only true if you design reports poorly.

As you're adding in the record selection formula, check the Database->Show SQL Query so that you see what gets passed to the database for filtering.

Since you decided to change your architecture based on bad analysis, I suggest you rethink this.

I have a FAQ here which addresses this:

faq767-3825

-k

 

[FVTrainer]

I tend to agree with sv's analysis here. Although as a general rule (at least with SQL Server) and when the application coder and report developer as the same person (or on the same team) I prefer ADO over just about any other means of generating a report, primarily because it moves your connectivity issues to your code, which I find much easier to control. Anyway, if you're looking for sample asp code to "push" the data into the report, here is some:

'Now we open get the ado recordset

Dim cnADO
Dim rsADO

Set cnADO = Server.CreateObject("ADODB.Connection&quot
cnADO.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=green;Password=password;Initial Catalog=Northwind;Data Source=itsc-dell2400-a"
cnADO.Open()

'Now let's define a recordset
Set rsADO = Server.CreateObject("ADODB.Recordset&quot
rsADO.ActiveConnection = cnADO

'And open it with the records that will be used to populate the first
'drop down box

rsADO.Open strSQL

If NOT rsADO.BOF then
'If there are records...

Dim Table
Dim Database

'We assign the rs to a session variable (Crystal requires this)
Set session("oRs&quot = rsADO

'We then get the table object from the database tables collection
'(there should only be one table in the tables collection of an ADO report)
Set Database = session("oRpt&quot.Database
Set Table = Database.Tables.Item(1)

'We then pass the data to the report.
Table.SetDataSource session("oRs&quot, 3

'Now we do the code from MoreRequiredSteps:
'Read records into the report
session("oRpt&quot.ReadRecords

If Err.Number <> 0 Then
Response.Write &quot;Error Occurred Reading Records: &quot; & Err.Description
Set Session(&quot;oRpt&quot = nothing
Set Session(&quot;oApp&quot = nothing
'This next line isn't good if you're asp app uses session variables other than the Crystal ones
Session.Abandon
Response.End
Else
If IsObject(session(&quot;oPageEngine&quot) Then
set session(&quot;oPageEngine&quot = nothing
End If
set session(&quot;oPageEngine&quot = session(&quot;oRpt&quot.PageEngine
End if

 

[cmers]

Thanks to both of you for your responses.

SV, your faq was a bit of an eye opener. I previously had no luck getting Crystal to push the record selection to the database, and I think I gave up last year with the assumption that it was a limitation of using ODBC and Access -- and the Crystal documentation was unhelpful on the subject. As a result, I put most of my selection criteria into formulas because I found them easier to reuse. Looks like that was a mistake. Your faq really helps fill in the blanks, although it's still somewhat mysterious why some code translates to SQL and other code doesn't.

For example, one report of mine has left outer joins from a main table to two subtables, both with one-to-many relationships. To limit the records returned, I want to only return records where related fields in each of the subtables match. But that comparison ({subtable1.field1} = {subtable2.field2}) does not show up in the Crystal SQL statement as a where clause.

SV, this confused me in your faq: &quot;-If when using a Crystal function it fails to pass the SQL, write you own functionality.&quot; What did you mean? Edit the SQL statement directly? Or write your own function to do the same thing as the Crystal function?

Elsewhere in the faq, you mention the possibility of writing the record selection in basic syntax. My 8.5 edition disables the syntax drop-down for the record selection criteria. Is there another way to change that?

Also, a side-note, the link to the Crystal doc from your faq includes the ending angle bracket, so doesn't work without editing the URL. Minor irritation.

I'll experiment with the SQL pass through and see if I can push at least some of the parameters up to the database. All I was going to do with ADO was try to filter on 2 or 3 of the incoming 20+ parameters to pass a reduced recordset to Crystal for processing. But it sounds like I should be able to accomplish the same result with careful editing of the record selection by getting some of the filering passed to the database.

FVTrainer, thanks much for your code. If I can get SV's technique to work, I may not need it. But I scanned it and seeing where you handle the RS as a session variable caught my attention -- I wasn't doing that, and perhaps that was the trouble. If I continue with the ADO approach, I think your code example will be very helpful. Thanks!

-Clint

 

[IdoMillet]

To solve your two 1-to-many (record inflation) issue, the easiest approach would be to create 1 or two queries that return the summary info from each &quot;subtable&quot;. Then, in Crystal, join from the main table to the qury (or queries) avoiding at least one of the 1-to-many joins (since the query has only 1 (summary) record for each record in the main table.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[cmers]

Eureka!

FVTrainer, the session variable issue was the problem with my code. It is now working ok. Thanks much for your help!

Synapsevampire, I give up with Crystal's SQL pass through! I've been trying to convert some of my existing record selection formulas into versions that Crystal could translate into pass through SQL, and the bottom line is that I can't reproduce my current level of functionality with pass through code. I can get a few statements to work alone, but as soon as I try combining them together with decision making (If or Select) it drops them off the SQL statement. It doesn't behave as described in the Crystal doc, either -- that is, a clause alone works ok, but when I combine it with a couple other non-SQL AND clauses, NONE of the clauses appear in the SQL statement -- but the doc says it should be keeping the first AND clause which works and then processing the other statements within Crystal! Here is an example:

(
({CASE.PLACED} >= {?Start Date} And {CASE.PLACED} <= {?End Date})
Or
({CASE.STSCHG} >= {?Start Date} And {CASE.PLACED} <= {?End Date})
Or
({CASE.CRTDATE} >= {?Start Date} And {CASE.PLACED} <= {?End Date})
Or
({CASE.POLDTE} >= {?Start Date} And {CASE.PLACED} <= {?End Date})
Or
({CASE.STATUS} In [&quot;A&quot;, &quot;B&quot;, &quot;E&quot;, &quot;H&quot;, &quot;J&quot;, &quot;K&quot;, &quot;L&quot;, &quot;S&quot;, &quot;Y&quot;])
)

And
//Only get records where comm payments are for this agent (rather than all payments per caseid)
(
(IsNull({HCB_EstimatedCommByAgent.EntityID}))
Or
({HCB_EstimatedCommByAgent.EntityID} = {AGPCOM.AGTNO})
)
And
//Cases with appropriate case status and dates
({@InSelectedStatus})
And
({@InDateRange})
And
//Cases this user is permitted to view
({@UserPermittedToView})
And
//Matches user-specified filter criteria
({@MatchesFilter})

If I use just the first AND clause alone, it gets passed to SQL. The effect would be to limit the records to only those that might possibly pass the @InDateRange formula. But when I add any of the additional non-SQL clauses, I stop getting any pass through WHERE statements.

While the pass through tips would be great with simple parameters and filtering, they wouldn't work for me without significantly stripping functionality out of my reports. ADO seems like a better approach because I could have complete control over the SQL statement, then I can still use the Crystal formulas to fine-tune the record selection. I am going to try it, anyway, and see if the performance boost is there.

Thanks all for your help!