Use data from an Access DB as a parameter 4 reports on another DB ?

[CmdrGravy]

Hi All

We have recently bought Crystal Reports 7.5 to report on our Aspect ACD telephone switch ( which uses an Oracle DB ) and I am trying to find ways of using the functionality of Crystal to make my life easier.

In our current setup we have a number of identical reports all reporting on the same data but filtered by Application Numbers, many reports will use the same Application Number(s) for the filter and the Application Numbers which they use is subject to change on a frequent basis. These numbers are currently hardcoded into the Selection Criteria in the report ( e.g. {Call.Detail.ApplicationNumber} in [1,34,90,11] ).

Because of this we currently have an administrative nightmare where every time the Application Numbers change for a particular group of reports we need to go into every report and change them, often we miss out or forget to update one report and gradually all the reports end up reporting on different things which gets us into a lot of trouble and takes ages to sort out. It would be much easier to keep one list of all the Application Numbers required for each group in of reports in a seperate database somewhere and then in our existing reports we could just use a parameter to say which group to use and the current application numbers would be automatically retreived by the report and used as the Record Selection in the report. That way we would just have to alter one database and all our reports would report on the same thing.

The question is, is it possible to do this using Crystal Reports actually in the report ( I am aware it would be possible using VB but I would rather find a way using the default capabilities of Report Designer if possible ). The kind of thing I am imagining is passing the report a paramater like "Application Group 1" and the report looking in my Access DB for Application Group 1 and retrieving the relevant list of applications ( as a comma delimited string, so just one record would be returned from this query ). It would then use this list as the Record Selection criteria for the "Call Detail.ApplicationNumber" field for the reports main query on the Oracle DB.

Can this be done, and if so how ? Please help becuase it would make my life so much easier!

 

[pastasalad]

Any chance of putting the maintainable codes into an Oracle table rather than Access? That would make the whole thing do-able in a single SQL parse.

If you must use Access then over to folks who are clever than I for further instructions...

Marc Kozlowski {}

 

[pastasalad]

Any chance of putting the maintainable codes into an Oracle table rather than Access? That would make the whole thing do-able in a single SQL statement.

If you must use Access then over to folks who are clever than I for further instructions...

Regards

Marc Kozlowski

 

[CmdrGravy]

Wow, that was a quick response !

Unfortunatley we do not have write access to the ACD's Oracle Database and there is no way we can add our own tables to it so that is not really an option.

I was thinking maybe of using 2 sub reports, 1 to get the data from my Access DB with the Applications in it and another to get the data from the Oracle DB. The problem there is whether or not it is possible to pass values from one Sub Report to another and synchronize their running so the one waits until it receieves the necessary parameters from the other.

Thanks, Joe.

 

[IdoMillet]

Have a look at 3rd-party Crystal tools at:

http://www.kenhamady.com/bookmarks.html

My DataLink Viewer is one such tool that provides
dynamic (and cascading) parameter functionality:

http://www.milletsoftware.com/DataLink_Viewer.htm

It would allow you to use your Access database as the data source for parameters. It would also allow you to display meaningful names (not just numbers) in the parameter dialog. Finally, it would allow you to let the user pick one or more application groups and then pop a "cascading" parameter that allows the user to select one or more applications within the selected groups.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[CmdrGravy]

Thanks for those links Ido, your product would certainly does what I am looking to do the only problem is ( as far as I can see from your documentation anyway ) that it requires you to run reports using your Crystal Viewer rather than the Info Desktop which looks like it means that I could not schedule reports using it.

I may have to implement a similar soloution myself using VB but I would rather avoid doing this if there was anyway to acheive my goals using standard Crystal Reports in the Info Desktop.

Cheers, Joe

 

[IdoMillet]

OK, let's try to think this over...

1. Create a report against the Access table to accept an Application Group as a parameter and return all the application codes. In the detail section, append (concatanate) these values into a String variable.

2. Establish the final value of that variable
by returning it in a formula inserted in the Report Footer.

3. Insert your original report as a subreport in the Report Footer of the report from step 1 above.

4. Use the formula in the report footer as a subrepoert link...

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[CmdrGravy]

Hi Ido,

I think that plan might do the trick however there is a further complication...

All our reports are already Sub Reports because of the way Aspect have installed Crystal for us. They use an intermediate Access database which creates 'views', essentially just SQL Pass Through queries to the Oracle DB as their datasource rather than querying Oracle Directly. I believe this is done so we don't have to manually work out all the numerous joins between the various different tables in Oracle and can just work with a simple query on the returned dataset.

In our main report we have a number of parameters which can be passed to it and in the detail section is a function called "SQLv82QueryClassStartQuery" ( I am not sure if this is a standard Seagate function or not ). Our actual report goes in the Report Footer and this somehow ( I am not sure how ) uses the dataset returned by "SQLv82QueryClassStartQuery"

Given this situation I am not sure whether it would be possible to implement your suggestion above unless I used a Subreport within a Subreport ( not sure that is allowed or not ).

Thanks for your help, I will do some experimenting and let you know how I get on.

Cheers, Joe.

 

[IdoMillet]

Crystal doesn't allow subreports within subreports.

However, you should be able to
* insert the Application Group subreport in the main report you already have.

* Establish the value of the string in a SHARED variable.

* Grab the value of the shared variable in a main report section BELOW the section where the subreport was inserted.

* continue with the plan as outlined above...

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[CmdrGravy]

Fantastic! I will give that a go. Thanks once again for your help.

 

[synapsevampire]

A simpler means might be to Link your Oracle tables to the Access database, create the query in Access, and base the Crystal Report on the Access database query.

-k

 

[michaelgray]

Help!

I am trying to use this solution, and have run in to the following difficulties.

I have setup the link directly to the Oracle tables, and have the Main report with a link to the Access Database.

The application numbers have been in to a string i.e. "501, 502, 211, 104," and I have linked this to a parameter in the Sub-Report.

What’s step 5?! As in how can this string now be utilized to
1) Select the records based on the values in the string and
2) Select the records on the SQL server, as opposed to selecting all records, and sorting locally.

 

[CmdrGravy]

I don't think you will get it to work passing the list of application numbers as a parameter - it just doesn't seem to work !

Instead I think you will need to set up some more tables in your Access DB alongside the linked tables to link the Application Numbers to a particular name e.g.

My App Number Table

App Number My App Group
1 Incident Team
2 Incident Team
3 Incident Team
4 Management
5 Management
10 Management
23 Management

You will then need choose this table in Crystal and join the App Numbers to the associated tables you are using from the Oracle DB.

I'm not sure how efficiently Access and Oracle will be able to work together to achieve this but you may find the reports take a long time to run.

However you will just need to pass the relevant string ( "Management", "Incident Team" etc ) to the report to get everything in your list of applications.

I decided not to use this method but to extract all the stuff I needed from Aspect into a MySQL database where I could pretty much do what I liked with it. Check out this thread thread808-599750