How can appear the dates by sequence?

[former_member723394]

I use this formulas for the report review, but the report appears without the sequence. Attached screenshot. Thank you!

if {?Sub Sort} = 'Contract Ship Date' then ToText ({CUST_ORDER_LINE.DESIRED_SHIP_DATE})else if {?Sub Sort} = 'Part ID' then ToText({CUST_ORDER_LINE.PART_ID}) else {CUSTOMER_ORDER.CUSTOMER_PO_REF}

[DellSC · Accepted solution]

Since I don't have access to your data, at this point you're going to have to play with your data and figure out which of the filter criteria are filtering them out and why. I might start with a clean report that has the same tables and just put the fields that you're grouping/sorting on and the ones that you're using in filters on the report - including the SQL Expression. Then try building the selection formula one piece at a time and see what gets filtered out of the report. This should help you figure out what's happening and allow you to tweak your selection formula to get what you need.

-Dell

[DellSC]

Hi Molly,

I think the best way to get the contract ship date is going to be to use a SQL Expression. This will push the function down to the database in the report's "Select" statement and will allow you to return a single value instead of having to include both fields. SQL Expressions can only be used if you're not using a Command for the report - but, based on the field names, it doesn't look like you are. The syntax for this will follow your database's syntax - NOT Crystal syntax! - and will probably look something like this:

Case
  When "CUSTOMER_ORDER"."PROMISE_DATE" IS NULL Then "CUST_ORDER_LINE"."PROMISE_DATE"
  Else "CUSTOMER_ORDER"."PROMISE_DATE"
End

If you do this, you won't need a formula. Instead, you'll use the SQL Expression on the report. If you need to use it in a formula, SQL Expressions are in the format {%MyExpression}.

-Dell

[former_member723394]

thank you Dell for help!

Now, the new request is we want to appear the contract ship date (one of the record selections in parameter field) with specific timeframe. how to add this additional parameter into the existing parameter?

thanks again!

[DellSC]

If I understand correctly, you need to filter your data by Customer Ship Date. This is where using a SQL Expression makes things run faster. If you use a Crystal formula to define the contract ship date, Crystal will pull the unfiltered data into memory and filter it there. If you use a SQL Expression, the filter gets pushed to the database for processing and fewer records will be returned to the report.

You cannot add this parameter to the existing parameter. Instead, you'll need to add two new parameters to filter the date - {?Start Date} and {?End Date}. These will be either Date or DateTime data type, depending on the data type of your PROMISE_DATE fields. Make these optional parameters.

Then, in the Select Expert, edit the selection formula to include something like this:

(

  {?Sub Sort} <> "Contract Ship Date" or
    ({%ContractShipDate} >= {?Start Date} and
     {%ContractShipDate} <= {?End Date})
)

Note the use of parentheses - they are essential for getting this to work correctly!

-Dell

[former_member723394]

Hi Dell,

Thank you for your help. Please see the screenshots below. this is the current parameters we use for now. the first screenshot is the formulas under selection formula-->record. Please help check the 3rd screenshot whether the setting is correct.

Please advise what I would do to put your formula into the current settings.

Thank you.

[former_member723394]

trying to add the formula into SQL expression. here I got the error..

[DellSC]

You cannot use parameter in a SQL Expression. If you're familiar with writing SQL Select statements, SQL Expressions become part of the Select clause and the filter needs to be in the Where clause. So, this filter needs to go in the Select Expert formula instead. The fact that it uses a SQL Expression makes it work more efficiently in the SQL that Crystal builds for the report.

-Dell

[former_member723394]

thanks Dell for clarifying.

should I add the new formula in the select expert? if so, I got this error message.

And this is formula for "sub sort" as your reference:

if {?Sub Sort} = 'Contract Ship Date' then ToText(if IsNull ({CUST_ORDER_LINE.DESIRED_SHIP_DATE}) then {CUSTOMER_ORDER.DESIRED_SHIP_DATE} else {CUST_ORDER_LINE.DESIRED_SHIP_DATE}, "yyyyMMdd") else if {?Sub Sort} = 'Part ID' then ToText({CUST_ORDER_LINE.PART_ID}) else {CUSTOMER_ORDER.CUSTOMER_PO_REF}

[DellSC]

You need to change {?Sub Sort} to the name of the parameter where you're selecting the sort type for the report. I can't see it in the image you posted. Also, I assumed that you had set up the SQL Expression with the name "ContractShipDate" - if you used a different name, you'll need to change that as well. Finally, you'll add this formula to the END of the existing selection formula in the Select Expert, putting "and" in front of it so that it adds to the existing selection criteria.

-Dell

[former_member723394]

Hi Dell,

Please see the error message in the screenshots. I also attach the settings in the field explorer.

Thank you for all your helps.!

[DellSC]

Put "and" before the first parenthesis in the code that I gave you.

-Dell

[DellSC]

When you run into this type of issue, you can delete that piece of the formula, open the "Report Fields" in the middle panel above the formula, and double-click on the parameter/field/SQL Expression there to add it back into the formula. That will work if you have issues with any of the items in that part of the formula.

-Dell

[former_member723394]

1. Since the date format is string...I have to set the formula like this. but it still says "A date-time is required here"...I follow the formula format from the previous one and it works. very odd..

2. For your reference.. this is the SQL expression for ContractShipDate. and it can be successfully saved.

Case When "CUSTOMER_ORDER"."DESIRED_SHIP_DATE" IS NULL Then "CUST_ORDER_LINE"."DESIRED_SHIP_DATE" Else "CUSTOMER_ORDER"."DESIRED_SHIP_DATE" End

[DellSC]

Change the data type of the date parameters to Date. That way users will have the ability to select the date from a calendar in addition to being able to enter it manually. If you do this, the formula will work as-is. If you decide not to do this, change "ToText..." to just the parameter.

-Dell

[former_member723394]

Hi Dell,

I set the date type as "Date". and the error message on the second screenshot states "missing )"...

[DellSC]

Go back and take a look at the formula I posted for this. In particular, look at the location of the parentheses on the line with {?End Date}.

-Dell

[former_member723394]

sorry..but got same error message..

[DellSC]

That's because you haven't completely fixed the problem. Take a closer look at the line with {?End Date} - it doesn't need a "(" at the beginning.

-Dell

[former_member723394]

Thank you. It works. is there any where i can modify the formula for blanking the dates if not putting any values. like what "customer name (blank for ALL) does.

[DellSC]

No, unfortunately, there isn't. What I would do is change the description of the start and end date parameters. Since they are now dates instead of strings, you don't need the formatting information that currently there. I would change it to something like "Required when 'Contract Ship Date' is the selected Sub Sort, otherwise leave blank." This will let your users know when they need to select dates for the parameters.

-Dell

[former_member723394]

Thank you so much Dell for the suggestion!!

sorry..i found some further issues here. the first screenshot is the old report with no start date & end date parameter. the second screenshot is the new report. i can see there are some missing dates in February, such as four lines of 2/12/21 and one line of 2/26/21.

not sure why...a bit frustrated...

[former_member723394]

Yeah! I get it! SQL expression for the circumstance would be:

Case When "CUST_ORDER_LINE"."DESIRED_SHIP_DATE" IS NULL Then "CUSTOMER_ORDER"."DESIRED_SHIP_DATE" Else "CUST_ORDER_LINE"."DESIRED_SHIP_DATE" End

Thank you Dell for all of your help by far!! Hope you have a great weekend!!

[former_member723394]

I just noticed under contract ship date, here is the formula to use ...

how to net-formula into the parameter (sub sort)?...

thank you!

if IsNull ({CUST_ORDER_LINE.PROMISE_DATE}) then {CUSTOMER_ORDER.PROMISE_DATE} else {CUST_ORDER_LINE.PROMISE_DATE}

[former_member723394]

I have this three elements for the "sub sort" parameter for your reference.

thanks!!

[former_member723394]

Thanks Abhilash. It works for the majority except first 5 lines.

Please advise.

Thank you!!

[former_member723394]

Here is another search for another customer and it is non-sequential...

Please advise...

[abhilash_kumar]

Try changing the formula to:

if {?Sub Sort} = 'Contract Ship Date' 
then ToText({CUST_ORDER_LINE.DESIRED_SHIP_DATE}, "yyyyMMdd")
else if {?Sub Sort} = 'Part ID' then ToText({CUST_ORDER_LINE.PART_ID}) 
else {CUSTOMER_ORDER.CUSTOMER_PO_REF}

-Abhilash