Create a Formula to Represent Shift

I have 4 fields that I need to create a formula on that shows as Shift 1 and Shift 2 the fields are

Type: String

Shift1begins="06:45" Shift1Ends="19:15"

Shift2begins="18:45" Shift2Ends="07:15"

My intention is to create a formula for a report so that I can use the Shift1 and Shift 2 as parameter choices in my report. Can you help?

Thanks!Can you give alittle more information as to what you need the formulas to do? Do you want to pass the work "Shift1" and have Crystal know that it means between 06:45 and 19:15? Or do you want to pass a time and have Crystal tell you what shift it fall under? Or something totally different?|||My report is a productivity report on our Therapists. Some work from 6:45 am to 7:15P this is shift 1. Shift 2 works from 6:45 pm to 7:00 am.

There are 4 fields that represent the times that are in shift 1 and 2 the beginning time and the endtime. My report has a date parameter that allows me to pull a range of data for our Therapists but it doesn't pull the correct data for the night shift because there is no way to specify the shift times. The report consists of

Therapists Name
Dates Worked
Daily Productivity for each day
Productivity Average for each month.

Day shift works fine cause it doesn't span two days. Night shift is inaccurate because it only calculates the time for the one date entered and doesn't allow for the time that scales both days. So I figured that if I set up a parameter based on the shift that this would work. I added the table that has the field in it to the report (which is a canned report anyway) and tried to create a parameter but I am not doing it correctly and that is what I need help with. Thanks!|||Originally posted by Shiloh917
My report is a productivity report on our Therapists. Some work from 6:45 am to 7:15P this is shift 1. Shift 2 works from 6:45 pm to 7:00 am.

There are 4 fields that represent the times that are in shift 1 and 2 the beginning time and the endtime. My report has a date parameter that allows me to pull a range of data for our Therapists but it doesn't pull the correct data for the night shift because there is no way to specify the shift times. The report consists of

Therapists Name
Dates Worked
Daily Productivity for each day
Productivity Average for each month.

Day shift works fine cause it doesn't span two days. Night shift is inaccurate because it only calculates the time for the one date entered and doesn't allow for the time that scales both days. So I figured that if I set up a parameter based on the shift that this would work. I added the table that has the field in it to the report (which is a canned report anyway) and tried to create a parameter but I am not doing it correctly and that is what I need help with. Thanks!

Hi,

If your fields contains datatime datatype you can calculate enddatetime - startdatetime that returns the value of number of seconds difference.|||No that doesn't work. I have date parameters that I have tried changing to "datetime" datatype but it works fine for day shift but on night shift it will not calculate correctly. You see our Therapists on Night shift come in at 6:45 pm and they get off at 7:15 am so they may come in for example on 02/10/04 and their shift will end on 02/11/04. That is why I figured if I use the shift field which is already a pre-filled value at 6:45 pm and 7:15am to represent shift 2 (made up of of two fields which say Shift2Begins and shift2Ends) that I might be able to get the correct data. I guess there is just know way to do this then. So thank you anyway. I appreciate your efforts.

Shiloh|||Originally posted by Shiloh917
No that doesn't work. I have date parameters that I have tried changing to "datetime" datatype but it works fine for day shift but on night shift it will not calculate correctly. You see our Therapists on Night shift come in at 6:45 pm and they get off at 7:15 am so they may come in for example on 02/10/04 and their shift will end on 02/11/04. That is why I figured if I use the shift field which is already a pre-filled value at 6:45 pm and 7:15am to represent shift 2 (made up of of two fields which say Shift2Begins and shift2Ends) that I might be able to get the correct data. I guess there is just know way to do this then. So thank you anyway. I appreciate your efforts.

Shiloh

use this following functions in the crystal report formula field.

datediff ("h", DateTimeValue (2004,04,26,20,0,0),DateTimeValue (2004,04,27,1,20,0))

CR not evaluating NULL values in formula

I have a data set where I am looking for a person's name and title. In some cases though either a title or a name was not entered. When this happens CR does not evaluate it in a formula I have built. Is there a trick to getting CR to evaluate Nulls in a particular formula or am I missing somthing in my initial formula?

Thanks in advance.

Formula (finds the persons title but only if the title value is NOT NULL):
----
'**********************
'Psychiatry/Psychology
'**********************
If (InStr({Concurrence.ConcurrenceTitle}, "Psychol", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "psychia", 1) > 0) then
formula = "Psychiatry/Psychology"

'****************
'Radiology
'****************
ElseIf (InStr({Concurrence.ConcurrenceTitle}, "Radiol", 1) > 0) then
formula = "Radiology"

'****************
'Patient Safety
'****************
ElseIf (InStr({Concurrence.ConcurrenceTitle}, "Patient Safety", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "PSM", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "PSO", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "PSC", 1) > 0 _
or (InStr({Concurrence.ConcurrenceTitle}, "PAT", 1) > 0 _
and InStr({Concurrence.ConcurrenceTitle}, "SAFETY", 1) > 0) _
or (InStr({Concurrence.ConcurrenceTitle}, "PT", 1) > 0 _
and InStr({Concurrence.ConcurrenceTitle}, "SAFETY", 1) > 0) _
or (InStr({Concurrence.ConcurrenceTitle}, "PS", 0) > 0 _
and InStr({Concurrence.ConcurrenceTitle}, "MANAGER", 1) > 0)) then
formula = "Patient Safety"

'*************************
'All other entries
'*************************
ElseIf (IsNull({Concurrence.ConcurrenceTitle}) _
or Trim({Concurrence.ConcurrenceTitle}) = "") then
formula = "No entry"

Else
formula = "Other"

End IfTry putting the NULL test as the very first test. Crystal seems to be funny about this sometimes.

CR Formula

Hello

I have a little problem with formula in CR10!
I have a report designed on a table (ex: table1).
At runtime, i change the database connection to connect my report to another db and table (ex : table2), all in SQL.
Table 1 and table have exactly the same structure but not the same name.
My report have a formula refercing a db field (ex : {table1.Type}). When I want the preview the report with data from table2, the viewer send the following error : "This field name is not known"
I've tried to change the formula at runtime but no way, it doens't works ! :mad:
Can anybody help me ? thanks a lotYou need to place those columns in your report and delete old columns

cr 9, if ..

i need to use if statements in my formula like:
if a > b then
stringvar c := "abc";

if a > b then
numbervar i := 34;
In CR 9 :
how do i combine these two if statements into one
without getting error?.Have you tried:

if a > b then
stringvar c := "abc";
numbervar i := 34;

CR 8.5: Help With Formula

HI
I have 5 Fields in the DB:
CON_AFM.AFM_DIABETES, CON_AFM.AFM_TENSION, CON_AFM.AFM_STRESS, CON_AFM.AFM_HIV and CON_AFM.AFM_TIROIDE
every field can have the value 1 or 0. Now i want to look for every field that have the value 1 and change to a string.

if CON_AFM.AFM_DIABETES value is 1 then the string to show is "Diabetes"
if CON_AFM.AFM_TENSION value is 1 then the string to show is "Hipertension"
if CON_AFM.AFM_STRESS value is 1 then the string to show is "Stress"
if CON_AFM.AFM_HIV value is 1 then the string to show is "HIV"
if CON_AFM.AFM_TIROIDE value is 1 then the string to show is "Tiroides"

But if the value is 0 i dont show a string

Example:

DB Fields with Values

CON_AFM.AFM_DIABETES 1
CON_AFM.AFM_TENSION 1
CON_AFM.AFM_STRESS 0
CON_AFM.AFM_HIV 0
CON_AFM.AFM_TIROIDE 1

The formula must return a string like
"Diabetes, Hipertension, Tiroides"Would you prefer to do this server side or in the Crystal Report itself? If in the Server side, what DB are you using?|||Thanks blanch1122, but a friend send me this and now works perfect

stringVar STR;
if {CON_AFM.AFM_DIABETES} = 1 then STR = "Diabetes" else STR = "";
if {CON_AFM.AFM_TENSION} = 1 then STR := STR + ", Hipertensin" else STR := STR;
if {CON_AFM.AFM_STRESS} = 1 then STR := STR + ", Stress" else STR := STR;
if {CON_AFM.AFM_HIV} = 1 then STR := STR + ", HIV" else STR := STR;
if {CON_AFM.AFM_TIROIDE} = 1 then STR := STR + ", Tiroides" else STR := STR;
// Esto elimina la coma si el primero es falso
if left(STR,1) ="," then STR := MID(STR,2,LEN(STR)-1);

counting field totals as a formula

Hi,

I'm trying to count field login totals for users which updates another field
in another table. Which is the most efficient method?

I don't want to use a standard query as it will take too long if there are
1000 users per company each with 1000 plus logins.

I was thinking in terms of either a function, or a formula (using the built-
in formula field within mssql).

the query though (as its the only way which i'm familiar) is:

SELECT SUM(NumberOfLogons) AS TotalLogons
FROM EmployerProfileDB39
WHERE (CompanyName = x) AND (EmployerID = y)

how would i write this as a formula or as a function?James M via SQLMonster.com (forum@.SQLMonster.com) writes:
> I'm trying to count field login totals for users which updates another
> field in another table. Which is the most efficient method?
> I don't want to use a standard query as it will take too long if there are
> 1000 users per company each with 1000 plus logins.

Depends on what you mean with a "standard query", but if you mean one
that is ANSI-compliant you are likely to be right.

> I was thinking in terms of either a function, or a formula (using the
> built- in formula field within mssql).

Ehum, there is no "build-in formula field" in SQL Server. There are
computed columns, which may appear as "formula fields" in Enterprise
Manager.

Anyway, I don't think a computed column would be a good idea, since that
would have to be a scalar UDF, and they are not known for being
performance boosters. You could index the column, but there is some
hassle with this.

Anyway, this query should take you a long way:

UPDATE tbl
SET logintotals = e.TotalLogons
FROM tbl t
JOIN (SELECT CompanyName, EmployerID,
SUM(NumberOfLogons) AS TotalLogons
FROM EmployerProfileDB39
GROUP BY CompanyName, EmployerID) AS e
ON t.CompanyName = e.CompanyName
AND t.EmployeeID = e.EmployeeID

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp