Reports on SC with Crystal reports.

[jayjayv]

We are running SC on P4 and using Crystal reports 9.

Does anyone have any suggestions on how to join two files in a single report ... e.g. Retreive parts of data from Probsummary and activity file to get the summary of activities for an IM ticket?

I tried the different Joins like L/R outer/inner and full joins but could not get it to work.

Is this a limitation of the P4 database?


Thanx in Advance
Jay

 

[Jasonic]

This is a limitation of the sc client which if you are using the P4 driver for crystal reports you are by proxy using.
P4 is a non relational database system therefore you cant do relational joins in SC.

There are ways round this you can:

1) Use a subreport in Crystal thereby only having one dbdict in each report and joining the two. However this will only pull in information from the dbdict used in the subreport you cant do selects on more than one dbdict with this approach.

2) Make the dbdicts relational in SC using erddef and joindef functions (see manual) this makes them relational for sc and your reports. It may have a knock on effect in system performance though.

I hope you find this helpful.

Jason Thomas
AssetCenter Consultant
Jason Thomas Consultancy Limited

 

[cmarchw]

I am having problems with the odbc driver for Service Center 5 when trying to access the database with Crystal Reports. If I pull in all the records of the database I can run querys and select expert on the data fine. But if I try to refresh the data after I design the report (add formulas, sort, defeine selection critera) I get the error "Failure to open Row Set" and none of the records import. I search the Service Center webite for new drivers to no avail.

 

[chrishaw]

The ODBC driver is not released separately from the client. In order to get a newer version of it, you'd have to install a newer client version (eg, if you're on 5.1.2, try 5.1.4). What version of the client/driver are you using?

 

[Shazza]

I was getting this error message when I hadn't completed the join between the two tables in the report - in Service Centre itself.

As you need to set up the relationship in Service Centre, before trying to join the tables in Crystal. ie. If you have the probsummary and incidents joined in Crystal using incidentid, then this join must be in Service Centre.

To see whether the join exists you need to do a search, open the database manager and search for joindef. Then search for the join, normally named table1-table2.

I am using SC5 & CR9.

Hope this helps.

Cheers