Repeated Parameter Requests

[dspitzle]

I've got a slightly messy situation I'm trying to debug. I'm using CR XI to generate reports which are displayed by another application which runs on a server. I do the development on my local machine, using a VPN to connect to the server for data access. The data is broken into two separate SQL Server databases, one containing document data, and the other containing student data. I need to find the last document prior to a user-selected date, and use that to filter data from the student database. To do that, I'm using a Command to find the Max(date) for each student and then return the corresponding document ID with the Student ID, and then within CR's normal interface pull the student data corresponding to that document.

Now, when I run this report on my local machine, it works reasonably well. I enter the date and a district code, the system grinds through the thousands of records over a couple of mintues, and kicks out a dozen pages or so. However, when I run the report on the server hosting the application, the date parameter is asked for on every page (but not the district code). Is there some setting or versioning issue that causes repeated parameter requests on every page of a report? Any suggestions? Help!

 

[lbass]

You didn't mention using subreports, but the report is behaving the way it would if you had a subreport that was executing repeatedly and the subreport parameter was not linked to the parameter in the main report.

Do the two SQL databases use the same driver? If so, you could potentially use a command that references both databases. Can you tell us a little bit more about how the report is set up? Are the two databases linked in any way?

-LB

 

[dspitzle]

Thanks for the reply. I'm not using a subreport, but given the somewhat screwy ways (to my eye at least) that Crystal Reports handles SQL, I wouldn't be surprised if it uses the same process. As I said, though, this only happens on the server, not while running it locally in CR XI.

The two databases are established as separate SQL entities, and have to be linked as two separate data sources in CR XI, so I don't appear to be able to create a command which uses both, as commands are associated with a single data source in the user interface.

Actually coming back to your "the subreport parameter was not linked to the parameter in the main report" idea, is that done anywhere other than in the properties of the subreport object? Because when I created the parameter in the command, it appeared in the general Parameters menu for the report, and I was able to use it in the overall report filter without the system complaining, so I'm assuming they're linked. If there's some mechanism for linking the command parameter and the general one, I don't know where it is.

 

[lbass]

I do think that this is the issue, but I'm not sure what the solution is, as I'm not familiar with integrating with other applications. You said the reports were displayed using another application on the server--is there the capacity to create a parameter within that and then link to the CR parameter? (Here my own ignorance is on display.)

So you are saying that the two SQL databases work only with different drivers? If you could use the same driver, you could potentially create a command with a union all, using nulls to plug unrelated fields--that was my thought.

-LB

 

[dspitzle]

I hesitate in saying they work with different "drivers", as they're both linked via ODBC using the SQL Server driver, but they have separate names on the server, and as such must be linked separately for OCBC purposes.

 

[lbass]

I'm not talking about linking them, but instead, using a union all statement, as in:

select 'Doc' "database", "doc"."docID", "doc"."studentID", "doc"."otherfield", null "date"
from "owner1"."doc" "doc"
union all
select 'Student' "database", null, "student"."studentID","student"."otherfield","student"."date"
from "owner2"."student" "student"
where "student"."date" =
(
select max(A."date")
from Student A
where A."studentID" = "student"."studentID" and
A."date" < {?Date}
)

Then you would use this as your entire datasource. Might not work for you, and I'm sorry if I'm mistakenly leading you down the garden path. However, if this does work, it would be much faster I think than your current approach and I think it would eliminate the repeated parameter request.

-LB

 

[dspitzle]

Sorry, the basic problem is that it seems like I can't create a Command that includes both "owner1"."doc" and "owner2"."student". The Commands are themselves associated explicitly with "owner1" or "owner2" in the user interface. If anybody can point me to documentation on the Command feature that's more detailed than what comes with CR (man, do those docs suck), I'd be interested in being proved wrong.

 

[lbass]

That would seem to indicate that different drivers are required for each. Otherwise, you should be able to write a command referencing both owners in one of the two places. Did you try a simple union all? I'm likely wrong about the ability to do this. My own experience is in referencing two databases (different owners) that fall under the same driver. Maybe somebody more knowledgable in this area will jump into the thread.

-LB

 

[dspitzle]

I'll take a look. Perhaps it will work after all, though I don't need a Union as such.