Skip to Content
avatar image
Former Member

Crystal Reports: Getting Date Paramaters to transition to the new year when January hits

Greetings,

I have a Crystal report that automatically runs on the 1st of the month for my customers. The goal of my report is that each month it presents them with a running monthly summary of the current calendar year. The logic below works beautifully until you get to January of the new calendar year.

UNDERLYING LOGIC (SQL)

Record_Date Between ?StartDate and ?EndDate

PARAMETERS:

?StartSTART DATE = YB

?EndDate = ME-1

So... this works great until January, which translates into an impossible range (1/1/2018 - 12/31/2017)

Thus, I am looking for code that will be able to carry these parameters across the year when January arrives.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 25 at 07:49 AM

    Hi Dave,

    Why do you have a Date Range prompt for a report that is supposed to return data for "year to date"?

    What database are you reporting against?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 25 at 04:32 PM

    Thank you for responding and your willingness to consider this..

    This Crystal Report is mounted in a proprietary 3rd party application which is used by end users to actually view the report in a PDF format. The 3rd party app also presents the Crystal parameters to the end users (they have the option to change parameters on-the-fly if they want, but in reality few are actually do this). Since this report is scheduled to run for end users on the 1st day of each month, it basically serves a summary to them automatically which they simply view on the 1st of each month. In this case, the monthly summary is configured to show data from the beginning of the current year through the end of the most recent month (The date parameter by default are set to "DATE between YB AND MB-1"... logic works great each month except in January... Ha!).

    I am not sure if there is a solution using parameters like I am here that will also transition the year. Maybe what I need to do in this case is hard code my parameters in SQL (i.e. When Month = January then... else ...) so that it can transition the year ? (this would take parameter control away from users I am thinking... but maybe there is no other way). Anyway... that's why I thought someone with more experience might have an idea. Would love to allow them to have some parameter control but maybe I simply cannot in this case?

    THANKS!

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank you for responding and your willingness to consider this..

      This Crystal Report is mounted in a proprietary 3rd party application which is used by end users to actually view the report in a PDF format. The 3rd party app also presents the Crystal parameters to the end users (they have the option to change parameters on-the-fly if they want, but in reality few are actually do this). Since this report is scheduled to run for end users on the 1st day of each month, it basically serves a summary to them automatically which they simply view on the 1st of each month. In this case, the monthly summary is configured to show data from the beginning of the current year through the end of the most recent month (The date parameter by default are set to "DATE between YB AND MB-1"... logic works great each month except in January... Ha!).

      I am not sure if there is a solution using parameters like I am here that will also transition the year. Maybe what I need to do in this case is hard code my parameters in SQL (i.e. When Month = January then... else ...) so that it can transition the year ? (this would take parameter control away from users I am thinking... but maybe there is no other way). Anyway... that's why I thought someone with more experience might have an idea. Would love to allow them to have some parameter control but maybe I simply cannot in this case?

      THANKS!