Non-Linked table data not being displayed 2

[SteveChamberlain]

Hi all,

I have a report (CR 8.5) which used to use two tables that were stored as paradox files. These have been replaced with an Access database.

The two tables are totally unrelated and therefore there are no links between them. The report uses fields from both tables.

When I go to "preview", I see data in a the field objects from one table but not from the other (they are simply blank space)! If I go to the database menu and use "Show SQL Query" I can see that it only SELECTs fields from one of the tables. If I try to change this manually to select fields from both tables and then click OK, it seems that the SQL is not stored.

I am really confused as to why Crystal Reports will not do a select on the fields from both tables.

The strange thing is, if I use the "Browse Field Data..." menu item in the design view, it shows the correct data in the current Access tables.

Does anyone know how I can solve this?

Many thanks!

 

[satinsilhouette]

The two tables are not linked. CR in 8.5 does not do multilple sql statements in that version. Therefore you need to either figure how to link them or bring the second table in as a subreport and display the data that way.

CR 8.5 will only allow you to change the sql in the show sql box for that run of the data, it will discard it after that.

Thanks so much!
satinsilhouette

 

[SteveChamberlain]

Thank you very much satinsilhouette for the advice!

I have played around with it a bit and I have found a solution. I have introduced two "dummy" fields, one in each table called ID. In my program I simply pass these fields a NULL value.

I can now link the two tables in CR8.5 visual linking expert. After that, I can manually edit the SQL statement like so:

"SELECT
WindLoadsLong.`Type`,
WindLoadsLong.`Item`,
WindLoadsLong.`Force`,
WindLoadsLong.`VesselVCOP`,
WindLoadsLong.`VesselOverturningMoment`,
WindLoadsLong.`SupportHeight`,
WindLoadsLong.`CargoVCOP`,
WindLoadsLong.`CargoOM`
FROM
WindLoadsLong WindLoadsLong, WindLoadsSummary WindLoadsSummary"

I find this a very strange "feature" of CR8.5. I am in the process of updating our version to XI, do you know if XI supports multiple SQL statements?

 

[synapsevampire]

Technically CR 8.5 supported multiple SQL statements, however you would use a UNION ALL query to combine them into one recordset.

CR XI does the same thing.

But your solution is even mor odd. Create a query in Access which does a UNION ALL of the data and use the query as your record source.

Stating that you need both tables but they are not related doesn't make much sense from a database persons perspective, if you need to combine them in a report, then they're realted, they're just not designed properly.

-k

 

[SteveChamberlain]

Exactly. The thing is, I don't have much choice, I didn't design the system ;-)

We have a "main" database in Oracle. The Access database simply contains a data dump to be used for reporting.

 

[Turkbear]

Hi,
Is that to avoid ad-hoc reporting against the 'real' datasource?
If the data is in Oracle, why not use it from there?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

So did you conider the query idea within Access?

Makes better sense than trying to work around data issues in crystal, and Access will be MUCH faster.

-k

 

[SteveChamberlain]

synapsevampire, I have not tried to use an Access query simply because I have the reports working now and I have other project commitments, but I think it is a good idea if others have a similar situation and are looking for a solution.

As regards Oracle Turkbear, we did consider creating a temporary user/schema and storing the print data there. The trouble is that it could make the Oracle server too slow. I don't really know Oracle, I am not a DBA and so I cannot say whether this is true or not, but the decision was made within the team to go with Access.

 

[mirogak]

Hi Guys, I am new here. I have been asked to create some dashboards in CR with complex drilling ... Nevertheless, this is where I am stuck ....

In my report I have 4 tables as data sources (using Oracle 8i) I have linked 3 of the tables. I can't logically link the 4th tables, because it doesnt have any linking fields.

I want to use that 4th table as a standalone table from which I really need only one cell (one column out of one record ... sounds confusing .. I know) I'll probably use SQL expressions to do that ...

However, as soon as I pull something from that 4th tables, my other numbers in the report change ....

do i have to link all tables in your data sources ????
is there a way around this

 

[synapsevampire]

mirogak: Start a new post, this has nothing to do with the current thread.

Crystal expects a single recordset for the rpeort.

If you need one cell from an unrelated table, consider creating a subreport and returning the value in a shared variable.

And when you start your new post, include your software version.

-k

 

[mirogak]

Sorry that i am posting this here .. BUT how the heck do you start a new thread or a new post ... i have looked in every page ... help

 

[mirogak]

Found it .. now I know where to go to post new threads ... please disregard my previous post...

thanks,