Quarter To Date Parameter

[Former Member]

I have a field in my report (Closed Date) formatted as m/d/yyyy. I also use this field as a parameter for selecting criteria. How do I create a parameter based on the Closed Date field that will allow the End User to enter Q108 and return all records closed in the first quarter of 2008?

[Former Member · Accepted solution]

I have CRXI, but this should work in 2008

Create your parameter (string)

if {?Param_String} = "Q108" then {Start Time} in Calendar1stQtr else.... ( whatever your default condtion is)

[Former Member · Accepted solution]

For all of our reports that have date parameters, I use a string parameter that allows a date or mnemonics such as CQ (current quarter), PQ (prior quarter), PM (prior month), etc. followed by an optional + or - and a number to add or subtract a number of quarters, months, etc. (What's added or subtracted depends on the mnemonics; PQ-1 is two quarters ago; PW-4 is 5 weeks ago.) I then have Crystal and database functions that translate the value entered to the appropriate date (one for starting dates, one for ending dates). Once the functions are written, it's trivial to add this functionality to a new (or existing) report. This allows the same report to be scheduled to run automatically with weekly values, monthly values, quarterly values, etc.

HTH,

Carl

[Former Member · Accepted solution]

Let me re-phrase the question. I have a Parameter field called Closed Date. When selecting criteria for the report the End User can enter the month/date/year and the results will show all records closed within the timeframe specified by the End User.

In addition to entering the month/date/year as the Close Date, I want the End User to be able to enter any given quarter (example Q108, or Q204) and get all records closed within Q108, Q204, or any random quarter and year. How do I do this?

[Former Member · Accepted solution]

You will need to write a formula that converts the text strings entered into your parameter to the desired date range. If you're only concerned about quarters, try (basic syntax):

dim pyear as string

pyear = cstr(2000 + val(mid({?quarter parameter}, 3, 2)), "0000")

select mid({?quarter parameter},1,2)
case "Q1"
  formula = cdate("01/01/" + pyear) to cdate("03/31/" + pyear)
case "Q2"
  formula = cdate("04/01/" + pyear) to cdate("06/30/" + pyear)
case "Q3"
  formula = cdate("07/01/" + pyear) to cdate("09/30/" + pyear)
case "Q4"
  formula = cdate("10/01/" + pyear) to cdate("12/31/" + pyear)
end select

HTH,

Carl