Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2006
    Posts
    4

    Unanswered: Pass Stored Procedure Parameter to sub reports base on same sproc

    I have a report that is based on a MS SQL stored procuedure. The report has to be grouped and displayed in three different ways. So, what I did was to create a main report that has the groupings. Then, I created a subreport of each of the groupings, These subreports are based on the same stored procedure, but are limited to just one of the three groups.

    Basically my data looks like this

    Report Group , Date, Rest of Data
    1, Date 1, xxxxxx
    2, Date 1, xxxxxx
    3, Date 1, xxxxxx

    So....

    Main Report
    Top level Group1
    subreport linked to group 1
    Top level Group2
    subreport linked to group 2
    Top level Group3
    subreport linked to group 3

    The report works great, except that when I change parameters for the main report, entering new parameters for the sproc, I have to enter the exact same parameters for the each of the subreports as well.

    Param1 (main report)
    Param2 (main report)
    Param3 (main report)
    Param1 (subreport1)
    Param2 (subreport1)
    Param3 (subreport1)
    Param1 (subreport2)
    Param2 (subreport2)
    Param3 (subreport2)
    Param1 (subreport3)
    Param2 (subreport3)
    Param3 (subreport3)

    I know this report is inefficient, but it is a one time knock off for one big time customer (or so sales say.). My question is how to enter the parameters just one time (for the main report) and have the main report pass the parameters to the subreports runing the exact same stored procedure?

  2. #2
    Join Date
    Jul 2006
    Posts
    4
    Oh I also should mention that, I've tried to place everything in just one report. My problem is that group 2 is different from groups 1 and 3. On group to I need to order and group the data based on price and supplypt point. On groups 1 and 3. I dont need to group by supplypt. It should look like this:

    Group 1 is a fixed price product 1 based on a fixed supplypt lowest to highest.
    price 1
    price 2
    price 3

    Group 2
    SupplyPt1 product 1 but from lowest price at each supplypt.
    price 1
    price 2
    price 3
    SupplyPt 2
    price 1, etc.

    Group 3 same as group 1 but the lowest price of product 2, 3, 4,&5 of all supplypts.

    I can get them sorted out into the individual groups, but I can't get rid of sort without the supplypt for groups 1 and 3. that is why i went to the three subreports in the first place.

  3. #3
    Join Date
    May 2009
    Posts
    1
    I am having the same problem............having to answer the same parameter for subreports based on the same stored procedure. Please let me know if you found a solution.
    Thanks,
    Roger Self

  4. #4
    Join Date
    May 2009
    Posts
    5

    Thumbs up

    u can set a default value as null for parameter in the stored procedure

  5. #5
    Join Date
    Nov 2009
    Posts
    9

    Question Answer Was A Little Cryptic - Need Further Explanation

    Quote Originally Posted by Srinvb View Post
    u can set a default value as null for parameter in the stored procedure
    How does your solution impact how CR prompts for parameter values? Ideally we are trying to link the parameter values in the main report to the identical parameters in the subreport which feed the stored procedure.

    Do I need to remove the SP from the subreport; assign default values for the parameters in the SP; and the re-import the SP into the subreport? Is that how the subreport will stop prompting for parameter values when the CR is run?

  6. #6
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    When a Report (Main or Sub) is based on a stored procedure Crystal automatically creates report parameters from the sproc parameters.

    When setting up the Link to the SubReport, make sure you are not creating (default) new parameters but are passing the Main Report's parameter to the SubReport's Sproc Parameter. There should be no need to create extra parameters in the Main Report specifically for the subreport.

    Unfortunately the Crystal interface is not very intuitive. In the screenshot provided the Top graphic is what happens when you select and move the parameter from the Main report to the "Field(s) To Link To" ListBox. Note the "SubReport Parameter Field to Use" dropdown displays a default parameter name. If you click okay at this point a new parameter in the SubReport will be created.

    What you need to do is click the DropDown and scroll the list down until you see the appropriate existing parameter in the subreport - as per the Bottom screenshot in the graphic.
    Attached Thumbnails Attached Thumbnails linksetup.JPG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •