Crystal Rpts: Assigning a value from a shared variable to a SQL parameter


I have a vendor front end app that runs Crystal Reports.  I create the crystal report.
For this report:  The main report is fed a report id which goes out to get the data and the subreports are linked to the main report by a unque record number.

The problem is that every subreport receives an unfiltered dataset from SQL server which is then narrowed down by the link (unique record number) from the main report.  

What I want to do is feed the report ID from the main report to the subreport so the dataset coming back is only for 1 record.

What I have done:

is create a shared variable in main

In Main Report:
Shared numbervar rptid
rptid = {?rptid}  - latter is received from the front end app

In SubReport:(created a formula holding)
Shared numbervar rptid

I can see the formula holding the report id in the subreport but how do I assign it to the Sql parameter, report id in the subreport.  You see I can only see in the report selection formula editor ...fields that are part of the database but I can't see the formula fields.

Hope you get what I saying,

Who is Participating?
ebolekConnect With a Mentor Commented:
If I understand it right, you want to send the record id from the main to the sub report than get some data according to that report id  right

Do this

Create  a parameter in the sub report called subreportid
Right clikc on the subreport
select change subreport links
select record id field from the drop down click >
select subreportid from the small drop down and click OK.

If you cant see the subreportid scrolldown. It is evry small and hidden but it is there.

then open the subreport and write
in the record selection criteria ?subreportid = <dbfieldname>

That should work
Try this

In Main Report: Header
Shared numbervar rptid;
rptid = {?rptid};  - latter is received from the front end app

In SubReport:(created a formula holding)
Shared numbervar rptid;

BrockAuthor Commented:
I am sorry but I don't think I explained myself properly:

1. Main report in procedure has a parameter called report_id. (value is received from front end app)
2. Subreport did not have a parameter but a link to the main report..
3. When the whole report runs all records are pulled from the sql server into the subreport ...however one only sees the appropriately linked record.
4. So I created a stored procedure for the subreport that takes a parameter called report id. on the sql server side.
5. Then I thought I could use this shared variable to feed the value to the parameter so the user does not have to type a value into the subreport stored parameter.
6. Unfortunately the shared variable can only be used WHILEPRINTING RECORDS.
7. You see the user won't know what the rpt id is and also I have like 60 subreports in this report.
8. The problem was when we did a trace you could see that it is already taking >3 seconds on each subreport query.
9. I guess what I am looking to do is to automatically populate a parameter created from the sql server side.

We are currently looking a creating a trigger to manipulate the subreport data on the server side.  

Thanks and sorry for the confusion. Lucy

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I believe you can link the subreport to the main report using the parameter.

you are right. Using subreport is bad for the performance. however, linking the subreport with a parameter is a valid solution. If you link the subreport by using a parameter than I dont understand why it will be such a bad performance issue. maybe I am understanding your problem wrong.

Whatever helps you is good though. Try triggers then

Glad to help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.