Parameterized commands

[sheridan101]

I am using Crystal Reports.NET and am in need of making a single report that, depending on parameters passed, can query data from a main database table, or from a separate history database table with the same field setup as the main. That is, have one report be a report on main data or on history data, depending on how it is run. Unfortunately, the report definition wizard only allows you to tie the fields to one database. So, my current line of thinking is to create the query using the "Add Command" and pass it parameters for date range and database. Question... can CR.NET accept parameters in "Add Command" for ODBC? Is there a better way to do this?

I have a book for CR9 which shows a parameter section on the Add Command dialog, but the CR.NET Add Command dialog does not have this section. Can it reference main report parameters? If so, how????

If I can get this to work, then it will eliminate the need to duplicate several reports across several tables. This makes managing the reports a bit messy, not to mention the fact that maintaining them will be more difficult because you have to remember to make any changes to all of them, and I would rather not get into that can of worms!!!

 

[synapsevampire]

So these are different databases, or just different tables?

Ther are a few ways you might address this depending upon the database used.

A Stored Procedure sounds like a good fit, you might even be able to use a View which does a Union of both tables and uniquely identifies each table type, which you would then use to differentiate which table you'd like. It would also allow you to use both.

A view/sp on the database also simplifies maintenance.

-k

 

[sheridan101]

(for the third time... this stupid thing keeps giving me an error and loosing the text of my message... guess I'm going to have to copy/paste just before submitting/previewing to keep from loosing the message... anyway...)

The tables are identical, but in different databases. We have a main database where data is collected from the networked game systems on a nearly real time basis. Because the database can possibly amass more than 2 million records in 24 to 36 hours, we have a clean up process that moves non-current data from the main to a history database. Then, daily/weekly/monthly summarization is performed on the history regularly to keep that manageable.

My desire is to "steal" the reports written for the main database, and reuse them to report on information in the history tables, with little/no modification to the reports themselves. My current line of thinking is to modify my viewer program to perform the query ahead of calling the crystalreportsviewer to display the results. Then, assign the resulting dataset to the crystalreportsviewer object and have it use that dataset rather than performing it's own internal scan. However, I can't get the crystalreportsviewer object to use my dataset... it still insists on doing it's own internal scan. I am using...

<myreport>.SetDataSource(ds);

to pass my dataset to the crystalreportsviewer object. Is there something else I need to do???

Also, I assume that I should not close the connection when done. Instead, the crystalreportsviewer object will do that for me. Am I right?

 

[sheridan101]

It appears that trying to query the data in the program and pass it to the report is not going to work. It "appears" that, in the creation of a connection for the DataSet object that the program/report shares, it ties it too tightly to a specific server. I need to be able to connect to any server, preferrably via the ODBC DSN entry in the control panel. It appears that you can't set a DSN as a connection source when setting up a dataset shared between CR and .NET programs.

It seems I am running into one brick wall after another. These "wizards" are far too restrictive. I hate them!!! If anyone can help, I would appreciate it. This is becoming extremely frustrating. To summarize what I need:

1. table 'X' is defined in multiple databases (main, history, daily summary, ...) exactly the same way
2. I need to be able to define a single report that can report results from any one of these tables depending on the one chosen by the viewer program (as a result of options selected by the user)
3. I need to make as few changes to the existing report as possible to get it to work this way, but some changes are acceptible. I just don't want to do mass, wholesale changes.
4. I need to be able to connect transparently to any server since this is going to be used by our customers to manage their database files, and there is no telling what they will be naming their database servers. It needs to be flexible enough to allow them to call their server anything they want. It also needs to allow for the possibility of a single site having multiple servers. Ideally, I would like to be consistent with the rest of the system and use a standardized DSN definition to point to the database. What I envision here is having multiple DSN entries, one to each database (assuming multiple servers), then a special login pane in the program gives them access to a specific one.
5. the goal is to minimize the number of duplicate reports that will need to be managed and maintained.

Question: can this be done with Crystal Reports? I know it was being done in the old Microsoft XL version of the reports. If not, then this seems to be a severe limitation of CR!!

 

[synapsevampire]

It can be done in CR, but like the previous architect, it's the wron place to do so.

Create a Stored Procedure which queries that appropriate data source based on a parameter passed.

Now any application can use this data.

I'm not a CR for .net developer, in other versions you can pass a recordset to a report, so I'd think that you could do so with CR for .net.

Have you checked the sample apps?

http://support.businessobjects.com/fix/samplescr.asp?ref=default.asp_selectlist#02

-k

 

[Senseial]

Hello

Can someone help me Please !!!!

I just upgraded to v10 of Crystal Reports.
Suddenly now when I create a parameter field and refresh I get that windows only once. Second time if I refresh and say I want to enter new value for a parameter I get nothing. No window. I have to close CR open and only than window shows up. Again only once. Am I doing something wrong.

Thanks