Two questions: PL/SQL inside Crystal / Subreports.
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=271
Printed Date: 03 May 2025 at 12:13am
Topic: Two questions: PL/SQL inside Crystal / Subreports.
Posted By: Ajit
Subject: Two questions: PL/SQL inside Crystal / Subreports.
Date Posted: 02 Mar 2007 at 2:57pm
|
Hi I am new to Crystal Reports and this is a great site. I have just joined this forum because I have a problem that I have not been able to find an answer to. Hopefully someone will be able to help me here, I am very grateful for all help.
I am using Crystal Reports XI.
1.) In the Database Expert I know that a SQL command can be used to select records from the database using the 'Add Command' under the datasource name. However is it possible to use a PL/SQL command here? I have an anonymous PL/SQL block that works in RapidSQL/TOAD but I only get errors about receiving "" instead of something else that is expected. Is there a way this can be done?
2.) I need to form a report by using parameters from another report. I know how to enter a set of parameters from a report to a subreport but I have a report where each record relies on two pieces of information from each record from another report. That is, I don't just have a pair of parameters to pass, I have a pair of parameter for EACH record to pass. Any ideas how to do this? I can only figure out how to send a single pair of parameters (and that works).
Again, all help is appreciated. :-)
|
Replies:
Posted By: BrianBischof
Date Posted: 02 Mar 2007 at 3:10pm
|
Glad you like the site! Re your questions, I've never used your database so I can't answer specifically for it. However, I know that some databases don't allow command objects with the native drivers. But if you use the ODBC connection then that will work. But your problem might be a SQL syntax problem. I would first pass a very simple SQL statement to the database and see if that works (e.b. SELECT COUNT(*) FROM table). If that works, build from there to see what you can and can't do with SQL and your database. Re question two, I'm a bit confused as to what the question is. You are passing a pair of parameters for each record to a subreport? Can't you create two parameters and link them both to the subreport? But maybe you mean you want to use two fields in the main report instead of parameter fields? You can still do that with the linking expert. Confused. 
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: Ajit
Date Posted: 02 Mar 2007 at 3:30pm
|
Hi thanks for welcoming me , you have helped me (and I'm sure many other people) with your work. I for one am very appreciative, thank you!
With regards to the questions, yes a regular SQL command works fine and I am using an ODBC driver. It is PL/SQL blocks that seem to cause errors (but the blocks are not incorrect since they work fine in the Rapid SQL and TOAD tools).
For now however, that^ is not important if I can figure out how to do what I want with these subreports. I can try to clarify what I am trying to do again.
I have one report that runs perfectly well (record selection done using SQL command). Now I want to create another report that uses a different datasource from the first report but uses two columns from the first report (the field data in the details section) to use as part of the selection criteria in the second report (same column/table type).
This is possibly better understood using an example:
Suppose I have a report where one of the fields in the details section (a column of data) returned is a date. Each record can have a different date. Now I want to make another report suing a different datasource that will select records using that data from each record in the first report as selection criteria in the new report because the table in the different datasource has a column which is of date type and I can set the date type in that colum to >= to the date in each record from the first report to select data.
^ This is suprisingly difficult to explain!
Anyway, I'm not sure how to make this happen.
|
Posted By: BrianBischof
Date Posted: 02 Mar 2007 at 3:50pm
|
Hmmm.. It seems like these are two completely different questions now. The way you rephrased it sounds too simple now (so I'm concerned that I still don't understand). You keep mentioning "pairs of parameters" and "two columns from the first report". But then in the last paragraph you say that you just want to do a join on the date field. This sounds contradictory. Anyway, the fact that the main report and subreport use two different datasources isn't important. When you create the subreport, just choose the other data source for it. Crystal Reports doesn't care what the data source is. Just be careful when linking on date fields b/c different databases can store dates in different formats and cause you problems. Might be best to use a formula to convert them to strings... Now, as far as the parameters go, in the Link tab for the subreport, you can choose two fields from the main report as the linking fields. Then for each field you can specify which field in the subreport they match up with. Thus, you can pick two fields as your pair of parameters from the main report. Ok, this probably wasn't any help, but I'm doing the best I can...  ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: Ajit
Date Posted: 02 Mar 2007 at 4:12pm
|
I think I need to do more playing around on Crystal because perhaps I'm not exactly sure how I want to do this. The databases are both Oracle. I suppose what is most important for me is to know whether this is possible:
The new report is such that at the end the columns should be like this:
a1 b1 x1
a2 b2 x2
a3 b3 x3
a4 b4 x4
...(and so on)
where x1, x2,... etc. is selected depending on each (a1,b1) pair of data (it's not important what the relationship actually is). However, each of the a's and b's are depending on p's and q's from the orginal report:
p1 q1
p2 q2
p3 q3
p4 q4
e.g. a1 >=p1, a2>=p2, b1 >=q1, b2>=q2
So really, is this even possible to do somehow in Crystal Reports. Don't worry about it though :-) I know this not the best explanation.
|