BO Crystal Reports 11.5 - customizing your link between tables 1

[mirogak]

Need to 'customize' my link between two source tables Table A and Table B

Situation:

Table A has column X, column X has values such as 200609 (representing 2006 September)
Table B has column Y, column Y has values in the form 2006 - Period 09

So I want to take the '2006' part and '09' part out of column Y, and then take that to join it with column X.

In Oracle, I can easily use the SUBSTR command and append feature to do this.

But how do I do it in BO CR. When I go to the Database Expert window, I dont find any place where I can customize the joining query.

HELP please !!!

Thanks,

Mirogak

 

[satinsilhouette]

If you are good at writing sql, write a script doing a subquery in the where clause or an inline view and then add this in cr as a sql command object. This will be viewed just like it is a table.

Thanks so much!
satinsilhouette

 

[mirogak]

Hi satinsilhouette,

Thanks for the reply. Ok, I will do that but how I will access that command to use it to join it with the other table in the Databae Expert. The "Database Expert" function, where you do your linking, doesn't allow you to access anything else (like a formula or command) other than the tables and the fields from your source.

 

[synapsevampire]

What satin is saying is that you should use ONLY SQL to generate the recordset.

Under your Oracle data source you'll see Add Command, select that and paste in the SQl to return the data that you need and the report will use that SQL as the data source, don't add in tables using the Crystal GUI.

-k

 

[mirogak]

Hey Synapse Vampire and Satin,

thanks for your help ... IT worked.... the Add Command did exactly what I wanted it to do.....

thats awesome

Furthermore, once I create my custom data set (like a view on the fly) using the Add Command, I can actually then go ahead and join it with any other source tables as well, even with the CR GUI.

Ciao,
Mirogak

 

[synapsevampire]

Yeah, but connecting it to the datasources in crystal means that Crystal does the joining, which nets bad performance, just add all of the tables and do the joins in the Add Command as well.

Unfortunately you can't readily change existing table based reports in this fashion.

-k