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

Posted on 2005-04-11
Last Modified: 2010-07-27

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,

Question by:Lucia
    LVL 10

    Accepted Solution

    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
    LVL 100

    Expert Comment

    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;


    Author Comment

    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

    LVL 100

    Expert Comment

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

    LVL 10

    Expert Comment

    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

    LVL 10

    Expert Comment

    Glad to help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now