• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2650
  • Last Modified:

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



0
Lucia
Asked:
Lucia
  • 3
  • 2
1 Solution
 
ebolekCommented:
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
ebolek
0
 
mlmccCommented:
Try this

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
0
 
LuciaAuthor 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


0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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

mlmcc
0
 
ebolekCommented:
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
0
 
ebolekCommented:
Glad to help
Emre
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now