BOFocus – Crystal Reports Tips – Tricks – Tutorials

Crystal Reports - How-To's

How to pass a date range to a subreport using a parameter

Displaying data from a subreport based on a date range can be a bit tricky.  Many people choose to simply hardcode the date range within the subreport.  This certainly will work just fine, but a more dynamic approach would be to create a parameter that passes the required date range to the subreport.

In my example I will be using the SAP extreme samples database, utilizing the Bill and Bill_Payment tables.

I created the following report based on the Bill table and will use the Bill_Payment table for my subreport.

1. Open your report you wish to add your

2. From the Field Explorer, right click on Parameter Fields and click New.

3. Make the following changes to your parameter:
Name: Date Range
Type: Date
Under Value Options, Allow range values: True

4. Click OK.

5. Create a sub report or insert an existing report one into your report (Do not link the sub report at this time).

6. Right click on your sub report and click Edit Subreport.

7. Create the following  new formula named ‘datelink’.

date({Bill_Payment.Date})

Replace the {Bill_Payment.Date}  field with your date/time field  that you wish to filter your subreport data on.

8. Return to the design tab.

9. Right click on your subreport and click on ‘Change Subreport Links…’

10. From the Subreport Links windows, select your parameter from the list under Available Fields and place it into the ‘Field(s) to link to:’ list.

11. Under ‘Select data in subreport based on field’ ensure that the datalink formula that you created within your subreport is selected and click OK.

12. Right click on your sub report and click Edit Subreport.

13. Open up the Select Expert.  From the toolbar, click on ‘Report’ > Select Export > Record.

14. Make the following changes to the select statement:

{@datelink} in {?Pm-?Date Range} to {?Pm-?Date Range}

15. Click OK and return to the design tab.

16. Press F5 to refresh your report, populating your date range parameter when prompted.

17. Your subreport data should not be filtering on the data range parameter from the main report.

Any questions, just ask below…..

Tagged , , , ,

8 thoughts on “How to pass a date range to a subreport using a parameter

  1. Ren

    hi…thanks for the info.. but it did not work for me. i have two conditions on my report filtering ({OSLP.SlpName} = {?Pm-OSLP.SlpName} and
    {@datelink} in {?Pm-?Date Range} to {?Pm-?Date Range}).

    Any suggestion on this..thanks in advance

    Reply
  2. Efren

    Hi,

    I tried that and it did not work. it seems that crystal is ignoring the passing of date range parameter and the sub report layout was blank. thanks for the info anyway.

    any idea is welcome.

    cheers
    em

    Reply
  3. Efren

    When running the subreport seperately, it will promt the date range parameter (the link between the two reports) and will return data based on what the date range entered. But when running from the main report it will not return any data from subreport regardless of what date range entered. cheers :)

    Reply
  4. bkuipers Post author

    Sorry for the delay.. Sounds as if the date being passed from the main to the sub does not match. Can you send me a copy of the report? I’ll look over the above steps I have listed.

    Reply
  5. Sanad

    I have a main report in Crystal reports of visual studio 2010.
    It is group with RecipeDate and inside its header there is a subreport.
    In subreport there is a IssueDate.
    I want to show all the issuance in the subreport issued right after/on RecipeDate but before the next RecipeDate.
    For Example:
    If I have a Recipe prepared on 18/11/2013
    and started issuing from the next day i.e 19/11/2013
    and the next Recipe is prepared on 25/11/2013.
    so I want to show all the issuance between 18/11/2013 till 25/11/2013
    How is it possible?

    Reply
  6. Teja

    Facing the same issue..When running the subreport seperately, it will promt the date range parameter (the link between the two reports) and will return data based on what the date range entered. But when running from the main report it will not return any data from subreport regardless of what date range entered.

    looks like data conversion issue. Kindly help

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

GET SOCIAL

QUICK TIP

When in the formula editor, Start out by typing the first charter of the function, press Ctrl-Space. The keyword auto complete will than show a list of the functions available beginning with the charter you typed in.

ADVERTISMENT