Skip to Content
avatar image
Former Member

Create parameter month and year, count total records

Hi,

I'm a newbie in crystal report. I need to create a summary and detail report.

Question 1: For the detail report, the user wants to enter Month and Year in the parameter and display records within that month and year. How can I do that?

Question 2: For the summary report, I try to create cross-tab as attached. I want to display count of user id by monthly in data field. The problem is how I can get data in month year instead display as date in the cross-tab and also how can I add calculation of grand total? The user need to enter year parameter and data display like below pictures.

Please help.

Thanks.

capture.png (9.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 25 at 07:53 AM

    Hi,

    1. Create a 'Date Range' Parameter. Go to the Field Explorer > Right-click Parameter fields > New.

    Select the data type as 'Date' > Scroll down and set the option 'Allow range values' to 'True'

    2. Go to the Report menu > Selection Formulas > Record. Add a selection formula to restrict records within the parameter range:

    {Date field} IN {?DateRangePrompt}

    3. Add a Crosstab on the Report Header. Use the Date field as the Column and set it's group options to print 'For Each Month'.

    Use the Company and Application fields as the two Row Fields

    Use the measure field as the Summarized fields.

    4. To format the date field in the crosstab, go to the Crosstab Expert > Highlight the date field > Select Group Options > Options tab. Check 'Customize Group Name field' > Select 'Use a formula as Group Name' and click the formula button beside. Use this code:

    ToText({Date field}, "MMM yyyy")

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks for your help Abhi. It's works. Cool :)

      If I do not want to set parameter using date range, how can I set parameter for month and year? For example, when user enter year - 2018 and month - Mac, then the cross-tab display only the data for this selection.