Link to home
Start Free TrialLog in
Avatar of TBSupport
TBSupport

asked on

Crystal: How to Show "01/01/1900" as Blank

Hello:

Attached is my Crystal report and below is the T-SQL view that the report is based on.

How do I make 01/01/1900 be blank instead of a date/datetime in the Crystal interface?

 Thanks!

 TBSupport

 SELECT DISTINCT 
                       ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], COALESCE (ME97705.DOCDATE, '') AS [DocumentDate], ME97708.ME_Work_Scope AS [Product], 
                       ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales], COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], 
                       CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt], 
                       /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN ME97708.ME_Job_Close_Date <> '1900-01-01 00:00:00.000' 
                       THEN ME97708.ME_Job_Close_Date ELSE NULL 
                       END AS [CloseDate], CASE WHEN ME97708.ME_Job_Close_Date = '1900-01-01 00:00:00.000' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
 FROM         ME97708 LEFT OUTER JOIN
                       ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                       GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX INNER JOIN
                       ME97702 ON ME97708.CUSTNMBR = ME97702.MEuserdefined3
 /*where ME97708.CUSTNMBR = 'BCFB'*/ UNION ALL
 SELECT DISTINCT 
                       ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], COALESCE (ME97704.DOCDATE, '') AS [DocumentDate], ME97707.ME_Work_Scope AS [Product], 
                       ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales], COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], 
                       CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt], 
                       /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN ME97707.ME_Job_Close_Date <> '1900-01-01 00:00:00.000' THEN ME97707.ME_Job_Close_Date ELSE NULL 
                       END AS [Close Date], CASE WHEN ME97707.ME_Job_Close_Date = '1900-01-01 00:00:00.000' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
 FROM         ME97707 LEFT OUTER JOIN
                       ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                       GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX INNER JOIN
                       ME97702 ON ME97707.CUSTNMBR = ME97702.MEuserdefined3

Open in new window

CARBON-COMMISSION.rpt
Avatar of James0628
James0628

What do you mean by "Crystal interface"?

 If you're talking about when you're selecting parameter values, I don't think you can (unless you change the parameters to strings, which you presumably don't want to do).

 James
Agree.

What version of Crystal?

mlmcc
Generic: Create a formula field (Crystal Syntax) with

if CStr({DateField}, "DD/MM/YYYY") = "01/01/1900" then
   ""
else
  CStr({DateField}, "<desired format>");

Open in new window

or
if (Day({DateField}) = 1) and (Month({DateField} = 1) and (Year({DateField) = 1900) then
   ""
else
  CStr({DateField}, "<desired format>");

Open in new window


(example code from memory, not tested, may have syntactic glitches ...)

Include result in report.
If you wish to avoid creating a separate formula for this,
right-click the field on the report canvas, Select Format Field, Common Tab, Display String and enter the expression like this:
IF {DateField} = #01/01/1900# then "" else  CStr({DateField})

Open in new window

You can also conditionally "Suppress" the field in the report.  

Right-click the date field in the report and select Format Field...
Select the "Common" tab.
Click on the blue "X+2" button to the right of the "Suppress" check box.
Enter the following formula: ToText({DateField},"yyyy/MM/dd")="1900/01/01"
Click the "Save and close" button.

The "Suppress" formula button should now be red.  The date field will be suppressed when the date field is 1/1/1900.
Avatar of TBSupport

ASKER

Hello:

01-01-1900 is not physically on the report.  So, suppressing that "field" does no good.

Also, I don't want a formula for 01-01-1900.  It needs to be changed in either the programming or the Select Expert and Parameter fields.

Below is my revised view and attached is my revised report:

SELECT DISTINCT
                      ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate], ME97708.ME_Work_Scope AS [Product],
                      ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales], COALESCE (GL00105.ACTNUMBR_1, '') AS [Account],
                      CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX INNER JOIN
                      ME97702 ON ME97708.MEuserdefined3 = ME97702.MEuserdefined3
/*where ME97708.CUSTNMBR = 'BCFB'*/ UNION ALL
SELECT DISTINCT
                      ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate], ME97707.ME_Work_Scope AS [Product],
                      ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales], COALESCE (GL00105.ACTNUMBR_1, '') AS [Account],
                      CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX INNER JOIN
                      ME97702 ON ME97707.MEuserdefined3 = ME97702.MEuserdefined3
CARBON-COMMISSION.rpt
Are you trying to filter on the close date/document date when they are NULL?

If so use IsNull({CloseDate})

To be able to do that try it this way
If({?CloseDate} = Date(1900,1,1)) then
    IsNull({Closed Date})
Else
    {Closed Date} = {?CloseDate}

mlmcc
Hi mlmcc:

Where do I put this formula, and will it be possible for the parameter fields of close date and dates (document date) to reference the formula?  That's what's key.

TBSupport
Sorry.  I must have misunderstood your question then.  

When you said "How do I make 01/01/1900 be blank instead of a date/datetime in the Crystal interface?" can you give an example of what you want?  Is ME97708.ME_Job_Close_Date a DateTime column?  

Are you trying to change the type of the column that is provided to the Crystal report?
I noticed in your Select Expert expression you have the following in the formula:

ToText({CARBON.CloseDate}, "dd-MMM-yyyy") = '1/1/1900'

The format string is not correct for comparing the date specified.  I would suggest changing that to

ToText({CARBON.CloseDate}, "dd-MM-yyyy") = '01/01/1900'

According to the Crystal help document:
"MMM" returns a month as a three letter abbreviation (e.g. MAY or JUN)
"MM" returns a month with leading zero for single digit (e.g. 05 or 06)
Actually, larryh has a good point.  I hadn't noticed that before, but your format in the record selection formula is wrong.  "dd-MMM-yyyy" will give you "01-Jan-1900", which, of course, won't match "1/1/1900".  But his suggestion won't work either, because he used "-"s in the format, but "/"s in the string.  If you change those to be consistent, his test should work (assuming that the date is actually 01/01/1900, and not null).

 But, now that I think about it, it might be simpler to just do a date comparison (rather than converting the date to a string and then comparing strings):

Date ({CARBON.CloseDate}) = Date (1900, 1, 1)


 Getting back to your question, I think it's pretty clear that we're still not sure what you're asking.  Going back to your first post
How do I make 01/01/1900 be blank instead of a date/datetime in the Crystal interface?
and my first question:

 What do you mean by "Crystal interface"?

 Are you saying that when the users are entering values for the parameter, you want a "blank" option that equates to 1/1/1900; or you want a selection for 1/1/1900 that somehow shows as "blank" somewhere; or are you talking about something else entirely?

 As I understand it, you're using CR 10.  Some later versions allow "optional" parameters, where the user doesn't have to enter a value for that parameter, and you can test for that in the report.  Is that maybe the kind of thing you're looking for?

 James
The SQL converts 1/1/1900 to NULL so the value is NOT in the result set used by Crystal.

YOu use my formula as part of your selection/filter

mlmcc
Hello All:

I apologize, for not asking my question well enough.

I have read your replies, and I'm wanting what James mentioned, as follows:

Are you saying that when the users are entering values for the parameter, you want a "blank" option that equates to 1/1/1900;

Please tell me, if you would, how to accomplish this.

Thanks!

TBSupport
Thanks for the clarification.  We could also use information on which parameter are you wanting to convert a blank string to 1/1/1900.  There is a {?Dates} parameter and a {?Close Date} parameter.  Is this conversion for one or both of these parameters?

So, if a given date parameter is left blank the default assumption will be 1/1/1900?

I would create a formula that converts a string parameter to a date:
if Length(Trim({?Dates})) = 0 then Date(1900,1,1) else Date({?Dates})

Use this formula value instead of the parameter value in your Record Selection Formula
Hi larryh:

Yes, it's both the Dates and the Close Date parameter fields that I want to work like this.

I used your formula as part of Select Expert, but Crystal gave me an error message saying that "a string is required here".

Below is my revised Select Expert formula:

{CARBON.DocumentDate} = {?Dates} and
{CARBON.Project} = {?Job} or
{CARBON.Editor} = {?Editor} or
{CARBON.Status} = {?Status} or
if Length(Trim({?Close Date})) = 0 then Date(1900,1,1) else Date({?Close Date})

If I can get this to work for the Close Date parameter, I will revise this to work for the Dates parameter.

Thanks, for the quick response!  I really appreciate it!

TBSupport
First, create the formula named DocDateRequested and use the formula I suggested.  
Second, create a formula named CloseDateRequested using the same formula but substituting {?Close Date} for
{?Dates}.  
Third, in your Select Expert I would change the criteria to the following:

Date({CARBON.DocumentDate}) = {@DocDateRequested} and
 {CARBON.Project} = {?Job} or
 {CARBON.Editor} = {?Editor} or
 {CARBON.Status} = {?Status} or
 Date({CARBON.CloseDate}) = {@CloseDateRequested}

I included the "Date()" function since the database fields are a DateTime and this function converts them to a plain Date format to match the format of the formulae.
Hi larryh:

I'm getting all kinds of errors in the Formula Editor for either one of the two formulas that you suggest I created.  I'm either getting "a string is required here" or "The ')' is missing".

Please help.

Thanks!

TBSupport
Please post what you have typed into the editor.  Perhaps include the latest version of your RPT file.
Hi larryh:

I just took your formula, as follows:

if Length(Trim({?Dates})) = 0 then Date(1900,1,1) else Date({?Dates})

TBSupport
Here is the latest copy of the report.

TBSupport
CARBON-COMMISSION.rpt
I see now that the parameters are DateTime values.  I assumed they were string values since you wanted to enter a blank.  I would suggest changing the types of the two parameters to a String.  At that point the formula should work.  You were getting an error because the Trim function was passed a DateTime value instead of a String.
No, the parameters are Date values---not DateTime values.  How does that change your answer?

TBSupport
Are you wanting the user to enter nothing for the date to get the open ones or aenter a date when they want to see the closed for that date?

If so set the parameter up as optional and use the HASVALUE function

If HasValue({?ClosedDate}) then
     {ClosedDate} = {?ClosedDate}
Else
    {ClosedDate} = Date(1900,1,1)

mlmcc
I wasn't aware of the HasValue function that mlmcc mentioned.  I am not sure where to use the code he mentioned, maybe set up a formula like I mentioned using the HasValue function instead of converting the parameter to a string and using the Length and Trim functions.
OK.  Based on that, below is my revised Select Expert formula.  And, Crystal gives me an error saying a number, currency amount, boolean, date, time, date-time, or string is expected here.

I want the user to not have to enter 01/01/1900 for the Dates parameter to get the open ones and to not have to enter a 01/01/1900 date when they want to see the close for that date (i.e. the Close Date parameter).

{CARBON.DocumentDate} = {?Dates} and
{CARBON.Project} = {?Job} or
{CARBON.Editor} = {?Editor} or
{CARBON.Status} = {?Status} or
If HasValue({?Close Date}) then
      {CARBON.CloseDate} = {?Close Date}
 Else
     {CARBON.CloseDate} = Date(1900,1,1)
When I drop that into your report I get no errors.  Where are you adding it?

User generated image
mlmcc
Could some of the dates be NULL?

If so you will have to test for that first

 {CARBON.DocumentDate} = {?Dates} and
 {CARBON.Project} = {?Job} or
 {CARBON.Editor} = {?Editor} or
 {CARBON.Status} = {?Status} or
(
 If HasValue({?Close Date}) then
 (
    Not IsNull({CARBON.CloseDate})
      AND
    {CARBON.CloseDate} = {?Close Date}
  )
  Else
  (
    IsNull({CARBON.CloseDate})
      OR
    {CARBON.CloseDate} = Date(1900,1,1) 
   )
)

Open in new window


mlmcc
I'm adding it where you are.  And, Crystal brings up this error and highlights "HasValue" at the same time.

TBSupport
...and, I get that same error on the second formula that you suggested regarding "NULLs".

TBSupport
Here's something that has messed me up.  For your two Date parameters you have the option "Allow multiple values" set to true.  I would suggest changing that to false for both of your parameters.

Formula CloseDateRequested:
If HasValue({?Close Date}) then {?Close Date} else Date(1900,1,1)

Formula DocDateRequested:
If HasValue({?Dates}) then {?Dates} else Date(1900,1,1)

Here is what I have for your selection expression:
{CARBON.DocumentDate} = {@DocDateRequested} and
{CARBON.Project} = {?Job} or
{CARBON.Editor} = {?Editor} or
{CARBON.Status} = {?Status} or
{CARBON.CloseDate} = {@CloseDateRequested}
What version of Crystal do you have?

I suspect it is pre-CR 2008?
Optional parameters were added in CR2008

mlmcc
I get the same "...number, currency amount...is expected here" error that I got from mlmcc's suggestions, on the formula for DocDateRequested.

TBSupport
The Crystal version is 10.0.0.533.

TBSupport
I apologize to you both, mlmcc and larryh, if I seemed a little abrupt about the formula error that Crystal keeps giving me.  :)  You guys are great, and I appreciate your help!

TBSupport
MY idea won't work.  CR2008 is V12.

For that you will have to either use a string for the date and convert it or give it a default value of 1/1/1900 so the users don't have to enter it.

mlmcc
I am running V12 (CR2008) as well.

If HasValue is not a valid function then I would go back to the suggestion of making the parameters String values and using the Length(Trim()) functions instead.  

E.g.
if Length(Trim({?Dates})) = 0 then Date(1900,1,1) else Date({?Dates})
Hi larryh:

The only problem with changing the Dates and Close Date parameters from Date to String is that Crystal gives me an error on my record selection formula, afterward, for the {?Dates} parameter field saying that "A date range is required here.":

{CARBON.DocumentDate} = {?Dates} and
{CARBON.Project} = {?Job} or
{CARBON.Editor} = {?Editor} or
{CARBON.Status} = {?Status} or
{CARBON.CloseDate} = {?Close Date} OR ToText({CARBON.CloseDate}, "dd-MMM-yyyy") = '1/1/1900'

TBSupport
Basically, what they said.  :-)

 As I mentioned earlier, the HasValue function allows the use of "optional" parameters, but that was added after CR 10.

 You could change the date/datetime parameters to be strings, but:

 1) You won't be able to use the datetime fields to set the default values for the parameters.  Maybe you could change the view to convert the fields to strings, and use those to set the default values.  I don't know.

 2) Users won't be able to use the built-in calendar to select dates for those parameters (since the parameters are not dates/datetimes).

 3) If you allow the users to type in the dates/datetimes, the format can vary, and they can type in invalid values.

 James
Delete the Selection Criteria and then change the parameter types.  After that is done you can paste in the criteria I mentioned earlier.
First of all, as was mentioned earlier, ToText ({CARBON.CloseDate}, "dd-MMM-yyyy") = '1/1/1900' is never going to work.  "dd-MMM-yyyy" would give you "01-Jan-1900", not "1/1/1900".  If you want to see if CloseDate is 1/1/1900, I'd just use

Date ({CARBON.CloseDate}) = Date (1900, 1, 1)

 At this point I don't know if you actually need that test, but the one you have is pointless, since it's never going to work.

... Crystal gives me an error on my record selection formula, afterward, for the {?Dates} parameter field saying that "A date range is required here."
Yeah, that's another problem with changing the date/datetime parameters to strings.  larryh suggested just using something like Date ({?Dates}) to convert the parameter to a date, but that won't work, because those parameters allow multiple discrete and range values.  CR treats multi-value parameters like arrays, and you can't use the Date function on an array, or on a range.  Try using Date ({?Dates}) in a formula, and CR will give you an error.

 I assume that you need the ranges, but do you really need those parameters to allow multiple values?  If not, you could theoretically replace each range parameter with separate start and end parameters (eg. ?Start_Date and ?End_Date), which only allow a single value.

 Otherwise, the simplest solution to the error that you mentioned would be to convert the datetime fields to strings, so that they could be compared to the strings in the parameters.  But then the format of the strings becomes critical.  What is the format of the parameter strings?  And if the users are allowed to type in values (not just select things from the default value list), then the format can change.  For example, "08/12/2015" <> "8/12/2015" <> "2015/08/12".

 The other choice is to basically go through every string value in the {?Dates} and {?Close Date} parameters and check their format, so that you can compare those strings with your fields.

 James
Even after I change the Dates and Close Date parameters to string, Crystal gives me an error in the DocDateRequested formula in the Formula Editor window saying that a string is required here, and it pointing to the Dates parameter field.

Strange.

Is the problem the version of Crystal that I'm using?

Even so, based on comments made earlier, it sounds like using String for these two parameter fields may not be a good idea.

Is there a consensus, as to which is the best way to get this to work?  Or, am I hampered by the version of Crystal?

Thanks!

TBSupport
Did you do anything about the "Allow multiple values" options for each parameter?  That sounds like the error I got when multiple values were allowed.
What is that formula?

mlmcc
Even after I change the Dates and Close Date parameters to string, Crystal gives me an error in the DocDateRequested formula in the Formula Editor window saying that a string is required here, and it pointing to the Dates parameter field.
Yeah, it would.  I talked about this in my last post, but maybe I didn't really explain the problem.

 You're trying to compare a string parameter (eg. {?Dates}) with what I assume is still a datetime field (eg. CARBON.DocumentDate).  CR won't do that.  They have to be converted to comparable data types.

 If you have a string parameter and a date/datetime field, you can either convert the field to a string, or convert the strings in the parameter to dates/datetimes.

 If you convert the field to a string, it has to match the format of the values in the parameter exactly.  Like I said before, "08/12/2015" <> "8/12/2015" <> "2015/08/12".  So, you need to copy the format used for the values in the parameter.  But if the format for the parameter values is not consistent (eg. if the user is allowed to type in dates), then a simple string comparison won't work.

 OTOH, converting the strings in the parameter to dates/datetimes means going through every value in the parameter and converting each one individually, and comparing the results with your field.

 James
Thanks, All!

larryh:  I need "multiple values" so that I can have 01/01/1900 or blanks account for open documents in the Dates parameter or open dates in the Close Date parameter.

TBSupport
larryh:  I need "multiple values" so that I can have 01/01/1900 or blanks account for open documents in the Dates parameter or open dates in the Close Date parameter.

I could be wrong but I think you may be misunderstanding the definition of "multiple values".  This is supposed to allow you to enter more than one value at the same time.  For example, when you are prompted for parameters you could enter 5/1/2015 and 6/1/2015 at the same time in the same parameter. If that is not what you want then I would turn off the "multiple values".  

With it turned off you can still enter a different value in the parameter each time you run your report.  If you allow "multiple values" then you have to do something with those multiple values.  From what I've seen your queries are just looking to compare against a single date value for each parameter.  There is no handling of "multiple values".

When you run your report and it prompts for parameter values do you have the option to specify null for a parameter?  Can you post a screenshot of what your screen looks like with the parameter prompts?

As always when doing software it would be more helpful if you could give actual examples of what you want to be able to do so that someone else can better answer your question.
larryh is correct.  A multi-value parameter means that you can enter more than one value for that parameter, like if you want one report to include the records where DocumentDate is 07/01/2015, or 07/15/2015 or 08/01/2015.  Or, since you allow ranges too, you could ask for records where DocumentDate is 06/01/2015, or 06/15/2015 to 06/28/2015, or 07/07/2015 to 07/13/2015.

 Your last post doesn't really say anything about entering more than one value for a parameter.

 If maybe the idea is to be able to select one or more dates (Or date ranges?), and the "open" dates, how about adding separate parameters to select the "open" dates?  You could add separate Yes/No (or True/False) parameters for "open DocumentDate" and "open CloseDate", and then you wouldn't have to try to "hide" the 01/01/1900 value in the regular date/datetime parameters.

 James
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
FWIW, I don't see how the post you selected (by me) is a solution to your original question.  If you actually found a solution in the posts here, you can use the "Request attention" link to ask to have the question re-opened, and then accept one or more posts as the solution.

 James