Physical Database Not Found With Subreport

[jtrapat1]

I'm getting this error message when trying to add a subreport to my main query.

I read through the rest of similar messages here but nothing specifically mentioned subreports.

I replaced my crw32.exe witrh an older version but this didn't fix my problem.

Maybe something is wrong here:

I'm using an sql query as my recordsource and would like to use fields in this same query to get the child records for my parent fields.

Can't this be done in Crystal?

Thanks in advance.

 

[kenhamady]

Can you create and run the subreport as a standalone report?
How exactly are you adding it? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[jtrapat1]

Ken,

Thanks for getting back to me about my problem.
I read your posts a lot and check out your homepage often.
Here's what I did:
I'm fairly new to CR8 since I've been working with Access Reports against an SQL Server database.
I'd like to drop the Access Reports and move to Crystal but I'm trying some of the same routines that worked in Access hoping that they'll work in Crystal.
I created my query in Crystal Designer and saved this as a .QRY file.
It's kind of a complex query with four LEFT OUTER JOINS (lots of one-to-many relations)which will ultimately have three subreports off the main report.
I brought the query into Crystal Reports, lined up the fields for the main report and all went smooth.
I get back about 8000 records and everything displays correctly.
Now I'm attempting to add the first subreport and I'm getting the error that I told you about.

Can you just tell me-
Is this the most efficient way to do this?
I mean, have the recordsource for my main/subreports based upon this one query and then set the LINK fields for the parent/child tables to get the data for the subreports.

I've seen you mention stored procedures from SQL Server as the most efficient way to report.

If so, how would I start this?

Can I use a stored procedure already saved in SQL Server?
Will CR8 understand the syntax of this query?

Would I have to pass parameters to this sp or, in the stored procedure, could I just use my SELECT statement?

Can you recommend the best book for Crystal Reporting with SQL Server?

Thanks in Advance
Reply When You Can.

John

 

[kenhamady]

Lots of info, but I am afraid that you didn't answer the 2 questions that I asked. Lets start there. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[jtrapat1]

Ken,
Thanks for the tips.

I was trying to add the subreport from Crystal Reports like this:
From inside the main report, Insert Subreport, then select the fields I need to show up on the report, and link the proper fields. But, I wasn't getting any info.

I did what you suggested and it worked; I created the subreport as a standalone, did a print preview, and I am getting back the correct info (the many of my one-to-many relation).
I saved this report, went back to the main report, added the subreport, but I'm having trouble getting the links set up correctly.
I think I have the links right but when I go to the main report, hit Print Preview, I get nothing back for info for my subreport.

Is there anything I should be aware of when linking a main report and a subreport?

Also, I tried selecting "On-demand subreport" since it's supposed to be better for performance.
Is this OK?


Thanks in advance.
John

 

[kenhamady]

On demand is better for performance because it doesn't run any subreports; at least not until you double click on them. Try adding a non-ondemand SR and see if that helps.

Otherwise your link may be bad. Go to the subreport's design tab and open Report - Edit Selection Formula - Record. Copy the formula and paste it here. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[jtrapat1]

Ken,

You're not going to like this but...
I don't have a selection formula in the Selection Formula window.

I based the entire recordsource for the main report from an sql query that I created from the Query Designer.
From File>New, I found the DSN, Table, etc. to the SQL Server database in the drop down, pulled the tables in, created the joins, and then saved this as a .QRY file.

Then, I used this recordsource for my main report.

From what you're saying, I guess this can't be done this way. Can you give me a direction to go to now?

I know I'll have to start over but can you just give me a hint here.

Like I said, I'm trying to apply the techniques I used from Access with Crystal and obviously it won't work.

Thanks

John

 

[kenhamady]

If you don't link the main report and the subreport, just insert the subreport in the appropriate section of the container report, what happens? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[jtrapat1]

Ken,

I tried what you suggested.
Both reports run fine on their own but when I inserted the subreport into the main report (unlinked), it was as if it overrode the main report.
The child report printed in it's entirety but the main report printed one record and then left blanks for the remainder of the report.

I appreciate all you've done with trying to help me but I'll have to go back to the beginning and learn the version 8 IDE.
I understand table relationships and SQL pretty well, but I'll have to learn the different ways SQL Query Analyzer and Crystal interpret the SQL, along with the syntax differences.

I have the crystal decisions version 8.5 book that came with crystal so I'll start with that.
But I couldn't find a great book that was database specific;
I mean, we need it for an SQL Server database and from what you've said before, stored procedures are the fastest ways to get data.

I guess you can't use a view in crystal but can I use a stored procedure that stores my entire SELECT statement needed for my report and then just run queries against this view?

Also, if you know of any good books that deal mainly with SQL Server and Crystal, let me know.

Thanks again
John

 

[kenhamady]

You can use views also. My guess is that the Subreport is using the same SP, which is forcing the SP to rerun.

What if you put the subreport in the Report Footer of the main report. Do they both run? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[jtrapat1]

Ken,
I tried to insert the subreport into the footer of the main report but it didn't run.

I got the main report but there must have been a conflict because I didn't get any output at all from the subreport.
I tried it in all of the other sections, too.

Thanks for the help and for the suggestions.

I think my problem was in using the Crystal SQL Designer.

If I stay within the Crystal Reports IDE, I think I may have better luck.

Now that you say that I can use a view, this may be a better approach for me.

I have a few views defined in SQL Server already where I have defined my whole select statement.

Can you just let me know if this is the way to get at my stored procedures or views:

File>New>Blank Report>More Data Sources>Microsoft SQL Server> (then choose the view or sp?)

Thanks for the help.

John

 

[kenhamady]

No, they should be listed like tables, once you tell CR to look for them.
Go into "File-Options" and go to the SQL tab. Check off Views, etc. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.