cancel
Showing results for 
Search instead for 
Did you mean: 

Need to lookup boolean value in table

Former Member
0 Kudos

I have a situation where I need to determine if the date is a working day or not and if not, find the next earliest working date.

I'm starting with the date from this statement:

//due date - production time

datevar startday: = datevalue(dateadd("d",{-invt.prod_time},{due_date.due_date})

I have a table (calendar) with 2 columns, date and is_working, where the date is a calendar date is_working is "Y" or "N"

the tables calendar and due_date are linked through {due_date.due_date} and {calendar.date}.  There was not other link I could create in the report with the table calendar.

My questions are:

1. Should calendar be linked at all or a stand alone table?

2. How do I lookup {calendar.is_working} using startday?

3. How do I adjust startday to find the next earliest "Y" in {calendar.is_working}?  There will not be more than 3 consecutive "N" values.

I was thinking of:

startday = startday-1

then keep testing until I get a "Y" for the {calendar.is_working} test

I appreciate any assistance.

Paul

Accepted Solutions (0)

Answers (5)

Answers (5)

DellSC
Active Contributor
0 Kudos

You could also use a SQL Expression something like this to get the last date prior to a weekend or holiday date:

Select max(date) from Calendar

where is_working 'Y' and

  date <= "due_date.due_date"

This should give you the correct date to use in your filter or on the report.

-Dell

Former Member
0 Kudos

Dell,

I've been trying to use your solution since it seems much simpler.

I'm having a bit of an issue with the formula.  I've got:

datevar start:

datevalue (dateAdd ("d", -({inventor.prod_ltime},{due_dts.due_dts}));

//new code from Dell

select maximium ({calendar.date}) from calendar

{calendar.is_wkday} = true and

{calendar.date}<{@Start Date};

start

I'm getting an error "the remaining text does not appear to part of the formula starting at:

"from calendar..."

Can you help me with the syntax here.  I've tried lots of options, but I can't figure it out.

Thanks,

Paul

abhilash_kumar
Active Contributor
0 Kudos

Hi Paul,

Dell's suggestion is to use a 'Sql Expression'. Go to the Field Explorer > Right-click 'SQL Expression Fields' > New > and use the SQL like so:

(

Select max(date) from Calendar

where is_working 'Y' and

  date <= "due_date.due_date"

)

The parenthesis need to be around the sql statement.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

I am not familiar with the syntax for the SQL query in crystal, so I apologize for being so dense.  My exact statement is:

(

Select maximum ({calendar.calendar.date})

{calendar.is_working} = true and

{calendar.date} <= {due_date.due_date}

)

I've tried this and several variations and keep getting the same error message:

"The ) is missing."

Can you help with this syntax error?

Thanks,

Paul

DellSC
Active Contributor
0 Kudos

SQL Expressions generally use the same SQL syntax as your database.  They don't use brackets or Crystal functions.

This looks like you 've done it as a formula instead of a SQL Expression.  The SQL Expression will look something like this (assuming two tables - Calendar and Due_Date and that Due_Date is already included in your report):

Select max("calendar"."date") as maxdate

from "calendar"

where "calendar"."is_working" = 'Y'

and "calendar"."date" <= "due_date"."due_date"

Most databases do not have Boolean field types (true/false).  Depending on who set up the tables, they may be "bit" types where the value is either 1 (true) or 0 (false), they may be single characters where the value is 'Y' or 'N' (or 'T' or 'F'), or they may contain the full words 'Yes', 'No', 'True' or 'False'.  You need to take a look at what type of data is in the calendar.is_working field to determine how you need to use it in the where clause above.

-Dell

Former Member
0 Kudos

After several days of trying to figure this out, I finally found out that with multiple database connections the SQL expressions option is not available, therefore the  SQL query is not an option.

Is there another method that can achieve the query?

ido_millet
Active Contributor
0 Kudos

As a solution for your scenario, one of the 3rd-party Crystal Reports User Function Libraries (UFLs) listed at http://kenhamady.com/bookmarks.html allows you to call SQL against any ODBC or OLE DB data source from within Crystal formulas regardless of how many data sources are used by the report.

By the way, a while back I brought this same exact issue (can't do SQL Expression when rpt uses multiple data sources) to the attention of SAP's tech support. They agreed that it can lead to wasted time by report developers who are not aware of this limitation.  I was told they plan to change the GUI to make it clear SQL Expressions are not available when the rpt uses multiple data sources. I still think the GUI could do a better job at clarifying this limitation.

Former Member
0 Kudos

Yes, it does.  Weekends and holidays are in the calendar table marked as "N"

abhilash_kumar
Active Contributor
0 Kudos

Ok.

Modify the formula inside the Subreport as:

Shared stringvar holidays;

If Not(DayOfWeek({Date_field}) IN [1,7]) AND {is_working} = 'N' then

     holidays := holidays & totext({Date_field}, 'yyyy,MM,dd') & If Not(OnLastRecord) then ">";

Next, modify the formula on the Main Report to:

datevar curr_date := {Database_date_field};

shared stringvar holidays;

numbervar i;

For i := 1 to 7 do

(

    If Not(dayofweek(curr_date) IN [1,7] OR Instr(holidays, totext(curr_date, 'yyyy,MM,dd')) > 0) then

    (

        curr_date := curr_date;

    );

    If dayofweek(curr_date) = 1 OR Instr(holidays, totext(curr_date, 'yyyy,MM,dd')) > 0 then

        curr_date := curr_date + 1

    else if dayofweek(curr_date) = 7 then

        curr_date := curr_date + 2;

);

curr_date;

Let me know how this goes.

-Abhilash

Former Member
0 Kudos

There is no table for holidays.  Only the calendar table which identifies non-work days (weekend and holidays) in the two columns, "date" and "is_working".  The date column is every date of the year for several years.  The is_working column is just a "Y" or "N".  When I calculate the start date for the production job, I need to make sure it's a working day.

How can I look up the start date for the production job from the calendar table and if "N" (not a working day), search back to the first "Y"?

Paul

abhilash_kumar
Active Contributor
0 Kudos

Does the table also have weekends listed and marked as 'N'?

-Abhilash

Former Member
0 Kudos

Thanks for your response Abhilash but neither will work.  I'm working with fixed tables out of an ERP package and the holidays are kept in the Calendar table, are different year to year and for each implemented company.  I need to be able to draw the data from the existing tables.  I am able to determine weekend already based on day of week, but I'm missing the holidays.

Paul

abhilash_kumar
Active Contributor
0 Kudos

If there exists a table for the holidays, you could try this:

1) Insert a Subreport pointing to the Holidays table and place it on the Report Header.

2) Inside the Subreport, create a formula with this code and place it on the Details Section:

Shared DateVar holidays;

Redim Preserve holidays[recordnumber];

holidays[recordnumber] := {Date_field} //The array stores the list of holidays

"";

3) Suppress all sections of this Subreport

4) Back in the Main Report, right-click the Subreport > Format Subreport > Subreport tab > Check the 'Suppress Blank Subreport' option

5) Go to the Section Expert > Select the section that holds this Subreport and select 'Suppress Blank Section'

6) In the second formula from my previous post, add the 'shared' keyword before the datevar array variable:

Shared datevar array holidays;


-Abhilash

ido_millet
Active Contributor
0 Kudos

You could remove the table from your report and use an SQL expression that fetches the minimum date that is
(>= {Lookup_Date}) AND (is_working = "Y")

Sorry, for some odd reason the forum was showing this thread a few minutes ago without all the follow-up answers.  Please ignore.

abhilash_kumar
Active Contributor
0 Kudos

Hi Paul,

Wouldn't it be a lot easier if the 'Calendar' table had another column called 'next working day'? You could then simply Join the existing table to this Calendar table on the Date Field and easily get the right date.

If today is a working day then the 'next working day' column would have today's date and if today is Not a working day, then it would have the next working day.

Another way to do this, if you're OK with maintaining a Holiday List variable at the report level is:

1) Create a formula called '@holiday_list' with this code and place this on the Report Header:

DateVar array holidays := [cdate(2014,12,10), cdate(2014,12,15), cdate(2014,12,25)];

"";

Keep adding all holidays to this list

2) Create another formula called '@next_working_day' with this code and place this on the Details Section:

datevar curr_date := {Database_date_field};

datevar array holidays;

numbervar i;

For i := 1 to 7 do

(

    If Not(dayofweek(curr_date) IN [1,7] OR curr_date IN holidays) then

    (

        curr_date := curr_date;

    );

    If dayofweek(curr_date) = 1 OR curr_date IN Holidays then

        curr_date := curr_date + 1

    else if dayofweek(curr_date) = 7 then

        curr_date := curr_date + 2;

);

curr_date;


-Abhilash