Need help with the Switch function

[mulema]

Hi All,
I am in the process of merging some reports that were designed at various times over the last two years.

These three reports use the same underlying view, so the columns are all the same.

I use CR version 8.5.

The first report pulls and groups Warehouse information by their "Transaction Date".
The second report was designed to pull and group by "Manufactured Date",
The third report pulls and groups the same records by "Julian Date".

My Task is to merge these 3 reports into one, and depending on my choice, will group accordingly.

Question: How can I make use of the "Switch" function in my record selection formula.

(I will mention only the 3 most important parameters giving me trouble here)

First Parameter. {?Order_Type} - This Parameter has a drop down List with 3 options.
( "By Transaction Date" -- DateTime field
"By Manufactured Date" -- Date field
"By Julian Date" )
Second Parameter. {?Date_Range} - The user will enter a Date Range depending on whether he chose By 'Transaction Date' or 'Manufactured Date'

Third Parameter. {?Julian_Date} - If the User Chose Option #3 "By Julian Date", he will enter the 3 Digit Juilan Date.

The first problem. How can I design my Record Selection to ignore for example
the {?Date_Range} parameter if the user wants to run by Julian Date.

Eg. I decide to run by Transaction Date, so I will enter the following parameters
1. {?Order_Type} = "By Transaction Date"
2. {?Date_Range} = '2004-1-15' to '2004-1-30'
3. {?Julian_Date} = '011' **( Can I have this parameter ignored )

Is there an easier and less complicated way to do this?

Thanks.

 

[bdreed35]

Can you list out the possibilites here?

If I chose:
{?Order_Type} = "By Transaction Date"
Should it use then {?Date_Range} against the Transaction Date field?

If I chose:
{?Order_Type} = "By Manufactured Date"
Should it then use {?Date_Range} against the Transaction Date field?

Now, what should happen if I choose:
{?Order_Type} = "By Julian Date"
What field will you compare the Julian Date against? Is the 3 digit Julian date representative to the number of days in the current year?

~Brian

 

[mulema]

OK Brian
These are the posibliites.
If you chose:
{?Order_Type} = "By Transaction Date"
then {?Date_Range} will = the Transaction Date field

If you chose:
{?Order_Type} = "By Manufactured Date"
then {?Date_Range} will = Manufactured Date field

If you Choose:
{?Order_Type} = "By Julian Date"
then my {?Julian_Date} will = Julian Date field (This column exists in my View )

Thanks,
Mulema

 

[bdreed35]

Ok, I think I have something for you.
This is structured to always return the Transaction Date, Manufactured Date, and the Julian Date in the WHERE clause. The key is that the range on each one that isn't selected will be large enough to return every possible record.

{table.Transaction_Date} IN
switch
(
    {?Order_Type} = "By Transaction Date",  {?Date Range},
    {?Order_Type} = "By Manufactured Date", CDate(1900,1,1) TO CDate(3000,12,31),
    {?Order_Type} = "By Julian Date",       CDate(1900,1,1) TO CDate(3000,12,31)
)
AND
{table.Manufactured_Date} IN
switch
(
    {?Order_Type} = "By Transaction Date",  CDate(1900,1,1) TO CDate(3000,12,31),
    {?Order_Type} = "By Manufactured Date", {?Date Range},
    {?Order_Type} = "By Julian Date",       CDate(1900,1,1) TO CDate(3000,12,31)
)
AND
{table.Julian_Date} IN
switch
(
    {?Order_Type} = "By Transaction Date",  1 to 365,
    {?Order_Type} = "By Manufactured Date", 1 to 365,
    {?Order_Type} = "By Julian Date",       {?Julian}
)

You may need to make some adjustments, specifically to the Julian Date section. I assumed that it being a 3 digit number that valid values would be from 1 to 365.

Here is a sample of what would be passed down to the WHERE clause we used you example from the first post.
1. {?Order_Type} = "By Transaction Date"
2. {?Date_Range} = '2004-1-15' to '2004-1-30'
3. {?Julian_Date} = '011' **( Can I have this parameter ignored )

WHERE
    table.`Transaction_Date` >= {ts '2004-01-15 00:00:00.00'} AND
    table.`Transaction_Date` < {ts '2004-01-31 00:00:00.00'} AND
    table.`Manufactured_Date` >= {ts '1900-01-01 00:00:00.00'} AND
    table.`Manufactured_Date` < {ts '3001-01-01 00:00:00.00'} AND
    table.`Julian_Date` >= 1 AND
    table.`Julian_Date` <= 365

~Brian