Dynamic Default Start/End Date in Crystal Report Prompt

system · August 10, 2011, 6:16am

Hi All,

I am using Crystal XIR2 and my crystal reports use BVM for prompts/LOVs and Stored Procedure to pull the data based on value selected in prompts.

I also have two static type date prompts Start Date and End Date with default values (i.e. 1/1/2011 and 12/31/2011).

We need to manually change this default Date values every year in these crystal reports and now we need to automate this so that the default dates will be changed automatically based fiscal_year table in database.

I can think of creating couple of LOVs in BVM and use them in reports but not sure whether we really can have Dynamic type of Date prompt with Default date value which will be changed every year.

Any help/assistance in this, is highly appreciated.

Thanks,
-GJ.

Gaurav13 (BOB member since 2005-09-30)

system · August 10, 2011, 2:14pm

Hi,

Well let me understand your question. do you want a way to automatically update the fiscal year depending upon the current year?

Thanks,
KD

kd773 (BOB member since 2011-08-06)

system · August 10, 2011, 2:20pm

yeah I want start date prompt to be displaying 1/1/YYYY by default automatically every year and if user does not want this date (as a parameter) then they should be able to pick it up from calendar/Date Picker.

I have start_date field stored in my year table with active_flag.

Thansk,
-GJ.

Gaurav13 (BOB member since 2005-09-30)

system · August 10, 2011, 4:14pm

Hi,

Well providing the date value automatically in the parameter window would not be possible in crystal report.

In the past, I too had a similar problem, but i didn’t found any solution on this one.

Thanks,

KD

kd773 (BOB member since 2011-08-06)

system · August 10, 2011, 10:31pm

i called it autodating my reports… our issue was that we have a ton of reoccurring reports - monthly, quarterly and anyally and i hated having to schedule these things with the parameters. there would always be one that i forgot to get and they can;t be set to a reoccurring schedule because of the dyanmic dates, so i did the following… let me know if you have questions on it…

How to autodate a report.
Use: There were several old tax reports that were using date parameters for reports that ran on an annual basis. Since the parameters were needed, each report needed to be scheduled with the parameters provided and could not just be left to run rach year without providing the date range. Autodating is using the assumed run date (since these are always run on March 1 for the previous years data) and basing the date parameters off that assumed run date. The date criteria can then be used in the Selection Expert of Crystal Reports to get the desired data rather than a parmaeter in the SQL query. Below there are a number of different fields that were useful in using this type of date selection rather than a parameter. Basically if a report has a known schedule or known run dates, we can use formulas to create the date parameterd and use the created date parameters in Select Editor to select the correct records.

EndDate - Date To Use; This selects the end date, based on the month of the current month. The ‘00’ is a formating item, used with the ToText? to specify the length and also to pad with leading zeros if they are not there IE - 1 will be 01, 2 will be 02, etc.
// quarterly months
// JSF - 9/4/2009; Modified this report to remove parameters. Having the parameters made it necessary
// to schedule this report and supply the parameters rather than have a set schedule where the
// date parameters are based on the run date. this will allow the report to have a set schedule and
// be able to supply the date parameters itself without requiring the scheduler to supply these.

IF Month(CurrentDate) - 1 = 3
then ToText(31,‘00’)
else

IF Month(CurrentDate) - 1 = 6
then ToText(30,‘00’)
else

IF Month(CurrentDate) - 1 = 9
then ToText(30,‘00’)
else…
· EndDate - MDY; this concatenates the Month, date and year of the EndDate? to provide the total parameter that the date range is based on. The three fields are concatenated with the &"-"& string. Formatting (Including the zero padding) of the individual fields is done at the field level rather than in this concatenating field.

{@EndDate - YearToUse}&"-"&{@EndDate - Month}&"-"&{@EndDate - DateToUse}

· EndDate - Month; provides the end of Quarter month. Since this report runs on the 5th day of the month after the end of the quarter, the EndDate? - month is the current month minus 1 month…presuming that this report runs in April (for Jan-March), July (for April-June), October (For July-Sept.) and Jan (for Octover-December) this quarterly formula works.

ToText(month(CurrentDate)-1,‘00’)

· EndDate - YearToUse;

If Month (CurrentDate) = 1

then ToText(Year(CurrentDate)-1,’####’)

else ToText(Year(CurrentDate),’####’)

EndDate - Date To Use; This selects the end date, based on the month of the current month. The ‘00’ is a formating item, used with the ToText to specify the length and also to pad with leading zeros if they are not there IE - 1 will be 01, 2 will be 02, etc.

StartDate? formula fields

· StartDate - DateToUse?

ToText(01,‘00’)

· StartDate - MDY

({@StartDate - YearToUse})&"-"&({@StartDate - Month})&"-"&({@StartDate - DateToUse})

· StartDate - Month

ToText(month(CurrentDate)-3,‘00’)

· StartDate - YearToUse?

If Month (CurrentDate) = 1

then

ToText(Year(CurrentDate)-1,’####’)

else

ToText(Year(CurrentDate),’####’)

Once these fields are done, then what?

Remove the parameters from the command. The report will use the Selection Expert to do the date
comparison rather than the command. This will allow you to use the concatenated fields in the
selection and remove the parameters used in the command. Alter the command to feed the date

jfuhrman (BOB member since 2011-02-23)

system · August 11, 2011, 3:10am

Hi,

Thanks for this wonderful post. we will try this out and if this work, its surely gonna be very useful to everyone.

Thanks,

kd

kd773 (BOB member since 2011-08-06)

system · August 11, 2011, 6:01am

KD - let me if that works for you.

Gaurav13 (BOB member since 2005-09-30)

system · August 11, 2011, 1:30pm

i’ve been usingit in our production env for 2 years so far. It’s saved me alot of headache. I just place the reports on a reoccurring schedule and let em go. Now something that had to be touched once a month can be left alone and just work. if any of the parts or pieces need further elaboration or are unclear, let me know.

jfuhrman (BOB member since 2011-02-23)

system · August 11, 2011, 1:32pm

Thats cool - thank you so much - I will also try this option and update the forum accordingly.

Gaurav13 (BOB member since 2005-09-30)

system · June 19, 2012, 4:31am

Hi Crystal Report Experts,

I have a crystal report based on stored proc which has date parameters. Now as we all know, these SP parameters show up as prompts in Crystal report. I want to schedule this report in info-view without changing Stored proc parameters. Is there a way, I can pass values for today and yesterday to these date prompts for scheduling purposes?

Thanks in advance,
Raj

Rajasopa (BOB member since 2007-08-22)