Crystal Rpts: Assigning a value from a shared variable to a SQL parameter
Hi:
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,
Lucy
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,
Lucy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
I believe you can link the subreport to the main report using the parameter.
mlmcc
mlmcc
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
Emre
Whatever helps you is good though. Try triggers then
Emre
Glad to help
Emre
Emre
In Main Report: Header
WhileReadingRecords;
Shared numbervar rptid;
rptid = {?rptid}; - latter is received from the front end app
In SubReport:(created a formula holding)
WhileReadingRecords;
Shared numbervar rptid;
rptid
mlmcc