Date Parameter in selection criteria assistance plz 4

[markajem]

Crystal V8.5

This is what I would like to do.

We have a report that is used by many different people. Most of the time the user will want to go back 14 months in history and sometimes will want to enter their own date to go back in history. I would like to make the date parameter to DEFAULT to 14 months from the day they run the report and also give them the ability to change that default date to whatever date they want.

I also need this date to be brought into a subreport but I can handle that part once I figure out the default/user date formula.

How would I go about this please.

Thanks

Mark


Mark
Email: markanas333@hotmail.com

 

[bdreed35]

We do this alot on our current project.

We set up 2 parameters.
One is the "Schedule Type". The options would be "Recurring" or "On Demand".
The second parameter would be "Data Date". This could be either the Begin Date of the Period or the End Date of the period, its up to you.

In your Record Selection Criteria, you would want to set up something like this: (this assumes that your Data_Date parameter is the End_Date)

{table.date} in
switch
(
    {?Schedule_Type} = "Recurring", DateAdd("m",-4,CurrentDate) to CurrentDate,
    {?Schedule_Type} = "On Demand", DateAdd("m",-14,{?Data_Date}) to {?Data_Date}
)

~Brian

 

[vidru]

Create a parameter: {?date}

Then use this in your record selection formula:
if {?date} = currentdate then
// assume they didn't change the date
{Table.Date} >= DateAdd ("m", -14, {?date})
else
{Table.Date} >= {?date}

I think that's what you're looking for.

-dave

 

[markajem]

Thanks a bunch. Will try it out.

Mark

Mark
Email: markanas333@hotmail.com

 

[rhinok]

Brian and I are on the same project ;-)

If a user came to me with this requirement, I would ask a few more questions. For example:[ol][li]Do you really want 14 exact months from the Date value or do you actually need data for the 14 whole months up to the date value selected (Current or parameter-driven)? Most of the time, the user will want data for whole months.[/li]

[li]If you want whole months worth of data, do you want the previous 14 months plus the current (or selected) month-to-date or do you want the previous 13 months with the 14th month being the current (or selected) month-to-date?[/li][/ol]Try the following formula, which is a variation of Brian's:

{table.date} In
Switch
(
{?Schedule_Type} = "Default",
Date(DateAdd("m",-13,Date(Year(CurrentDate),Month(CurrentDate),1))) To CurrentDate,
{?Schedule_Type} = "On Demand",
Date(DateAdd("m",-13,Date(Year({?Through_Date}),Month({?Through_Date}),1))) To {?Through_Date}
)

In this example, I assumed that the user wanted 14 months of data including the current month-to-date. The SQL generated by my sample report using this example was:

WHERE
table."date" >= {ts '2002-10-01 00:00:00.00'} AND
table.&quot;date&quot; <= {ts '2003-11-19 23:59:59.00'}

This returns 13 previous whole months of data plus the current month-to-date (the 14th month).

Whether you need Data for whole months or for partial months based on the exact date, you need to encapsulate the DateAdd function in a Date() formula or else it won't be passed to the database for processing (which is the whole point of using the Switch statement). Check out the following example:

Date(DateAdd(&quot;m&quot;,-13,Date(Year(CurrentDate),Month(CurrentDate),1))) To CurrentDate

Vs.

DateAdd(&quot;m&quot;,-13,Date(Year(CurrentDate),Month(CurrentDate),1)) To CurrentDate

~Kurt

 

[markajem]

Kurt - Thanks for asking those questions specifically. In our line of business and what the users will need when running this report would be 14 months back from the date of running the report. It would be from the day the report runs back 14 months to the day. Using the other; if the report is run today then only 20 days would be in this month and then using the past 13 months. This is not how we calculate our numbers here for what they use the information for. But thank you again for asking the questions. I am also not using the scheduler to run the reports.

My on demand reports actually works like a menu for the users. Click on button on the main report they get open sales orders for the item. Click another button they get open purchase orders fot that item and so on and so on. This is why i use on deman reports. This way it only generates the reports that user requires. Not all users will use all the on deman subreports.

Now Brain, I think the formula that you provided will give me exactly what I need.

One thing is I basically understand the logic in your formula but there is no need for selection criteria in the main report. I will only require the dates in the subreports. The way I originally set up the parameter dates in the main report was placing in the record selector as: {?TO DATE} = {?TO DATE} just so the report will ask the user for the date in the prompt. Then this date can be brought into the subreports.

How would I alter your formula so that it does not have to look for table information but instead just prompt the user for a date and then I can set that date equal to a variable and carry it thru to the subreports.

Setting up a subreport so it will read a date from the main report was a trick to begin with. I know that is a complicated thing and I get lucky sometimes when I end up doing it. Kind of surprise myself.

Thanks


Mark
Email: markanas333@hotmail.com

 

[rhinok]

Mark,

Thank you for explaining your requirements. If you only need to filter dates in the subreport, then you can place the original parameter in the subreport. There's no reason to pass it from the main report. Instead, the link(s) from the main report to the subreport should be based on another value such as the Item Number.

Also, even if you don't use a scheduler, you still need to allow the user an option to select the type of date to use (default or custom). You can call the parameter {?Date_Type} instead of {?Schedule_Type}. The parameter is required in order for the switch statement to intelligently pass the SQL to the DB for processing.

You will need to place the formula in the record selection criteria of your subreport (in addition to the linked fields, which also are in the record selection criteria). Combined with your linked criteria, the total record selection criteria in the subreport should look something like this:

//This criteria was created by the Subreport link
{table.item} = {?Pm-table.item} And
//This is the specific criteria used to evaluate the required date range at the subreport level
{table.date} In
Switch
(
{?Date_Type} = &quot;Default&quot;, Date(DateAdd(&quot;m&quot;,-14,CurrentDate)) to CurrentDate,
{?Date_Type} = &quot;Custom&quot;, Date(DateAdd(&quot;m&quot;,-14,{?Through_Date})) to {?Through_Date}
)

~Kurt

 

[markajem]

Okay, was able to get exactly what I wanted thanks to you guys.

Kurt, although I am sure your formula would have worked it was a little bit of a challenge for me to follow.

Dave - your formula was easier for me to interpret and follow the logic plus I was able to alter it a bit for other needs too.

Thanks again I do appreciate your times.



Mark
Email: markanas333@hotmail.com

 

[rhinok]

Mark,

Crystal attempts to pass off as much of your record selection criteria (the Where clause of the SQL statement in Database|Show SQL Query) to the database as possible so that the majority of the processing occurs on the server instead of the client.

As a general rule-of-thumb, Crystal Functions, variables and If-Then-Else statements are not passed to the database, which means that all records will be passed down to the client before Crystal can filter them out. There are a number of methods, however, where Functions and If-Then-Else statements can be passed to the DB, mostly involving Boolean logic.

Switch, IIF and Choose are some of the native functions that actually can be passed to the database if they're used properly. I used Switch in my example, because it seemed the most appropriate. The Switch function provides boolean logic through the Use of True conditions. False is not accounted for, which is ok in this example because there are only two possible values for the {?Date_Type} parameter ('Default' and 'Custom').

Switch(Boolean Expression[1],Return Value[1],Boolean Expression[2],Return Value[2], etc...)

The function I recommended basically states:

Select records where the date is in the range returned by the following formula:

(
If
{?Date_Type} = 'Default'
Then
calculate a date range beginning 14 months from the Current Date
Else If
{?Date_Type} = 'Custom'
Then
calculate a date range beginning 14 months from the Custom Date (entered as a parameter)
)

You could recreate the record selection criteria using straight If-Then-Else formulas with the logic above, but it wouldn't be processed on the server, which would be more efficient.

~Kurt