Use both string and integer in field

Hi Experts.

I'm working on a BI report in Business Object report panel. In there I have a field that looks on another field, and depending on what the other field is, sets and integer value. However if the field I look at, does not contain one of the values from my IF-statement, it should be "N/a" as any "default" number like 0 would ruin the calcutation process.

I apoligize for the long question, but it's nesseary to get the complete overview on how the fields interact. I've translated fieldname ect. to english, for better understanding.

Hope you will take the time to look at it anyway.

Your help is greatly appreciated.

My fields:
Service std. type:
=If(Venstre([Service std].[Jobkode];1) = "U") Then If([Service std].[Prioritetskode] InList("A1"; "A2"; "A3"; "A4"; "A5"; "C1"; "C2")) Then "Afsluttet" Else "N/a" ElseIf(Venstre([Service std].[Jobkode];1) = "F") Then If([Service std].[Prioritetskode] InList("A1"; "A2"; "A3"; "A4"; "A5")) Then "Påbegyndt" Else "N/a" Else "N/a" 

Faktisk tid i timer:
=If([Service std. type]="Påbegyndt") Then [Timer fra OPR til påbegyndt] ElseIf ([Service std. type] InList("Afsluttet")) Then ([Dage fra OPR. til UDF. slut] *24) Else "N/a"

Timer fra OPR til påbegyndt:
=If(FormatDate([Meddelelse påbegyndt dato]; "dd-MM-yyyy") <> "") Then ((DaysBetween([Service std].[Meddelelse oprettet dato];[Meddelelse påbegyndt dato])*24*60)+((ToNumber(FormatDate([Meddelelse påbegyndt dato];"HH"))*60)-(ToNumber(FormatDate([Service std].[Meddelelse oprettet dato];"HH"))*60))+(ToNumber(FormatDate([Meddelelse påbegyndt dato];"mm"))-(ToNumber(FormatDate([Service std].[Meddelelse oprettet dato];"mm"))))+((ToNumber(FormatDate([Meddelelse påbegyndt dato];"ss"))/60)-((ToNumber(FormatDate([Service std].[Meddelelse oprettet dato];"ss"))/60))))/60 Else 0

Service std. overholdt:
=If([Service std. type] = "Afsluttet") Then If(IsString([Faktisk tid i timer])) Then "N/a" ElseIf ([Faktisk tid i timer]<[Service standard Mål]) Then "Ja" 	Else "Nej" ElseIf([Service std. type] = "Påbegyndt") Then 	If(IsString([Timer fra OPR til påbegyndt])) Then "N/a" 	ElseIf ([Timer fra OPR til påbegyndt]<[Service standard Mål]) Then "Ja" 	Else "Nej" Else "N/a"

Service standard mål:
=If(Venstre([Service std].[Jobkode];1) = "U") Then If([Service std].[Prioritetskode] InList("A1"; "A2"; "A3"; "A4"; "A5")) Then 24 ElseIf ([Service std].[Prioritetskode] InList("C1")) Then 168 ElseIf ([Service std].[Prioritetskode] InList("C2")) Then 336 Else "N/a"ElseIf(Venstre([Service std].[Jobkode];1) = "F") Then If([Service std].[Prioritetskode] InList("A1"; "A2")) Then 2 ElseIf([Service std].[Prioritetskode] InList("A3")) Then 4 ElseIf([Service std].[Prioritetskode] InList("A4")) Then 6 ElseIf([Service std].[Prioritetskode] InList("A5")) Then 8 Else "N/a" Else "N/a"


Converted to english for better understanding:
My fields:
Service std. type:
=If(Left([Service std].[Jobcode];1) = "U") Then If([Service std].[Prioritycode] InList("A1"; "A2"; "A3"; "A4"; "A5"; "C1"; "C2")) Then "Ended" Else "N/a" ElseIf(Left([Service std].[Jobcode];1) = "F") Then If([Service std].[Prioritycode] InList("A1"; "A2"; "A3"; "A4"; "A5")) Then "Started" Else "N/a" Else "N/a" 

Actual time in hours:
=If([Service std. type]="Started") Then [Timer fra OPR til påbegyndt] ElseIf ([Service std. type] InList("Afsluttet")) Then ([Dage fra OPR. til UDF. slut] *24) Else "N/a"

Hours from created to started:
=If(FormatDate([Case started date]; "dd-MM-yyyy") <> "") Then ((DaysBetween([Service std].[Case created date];[Case started date])*24*60)+((ToNumber(FormatDate([Case started date];"HH"))*60)-(ToNumber(FormatDate([Service std].[Case created date];"HH"))*60))+(ToNumber(FormatDate([C];"mm"))-(ToNumber(FormatDate([Service std].[Case created date];"mm"))))+((ToNumber(FormatDate([Case started date];"ss"))/60)-((ToNumber(FormatDate([Service std].[Case created date];"ss"))/60))))/60 Else 0

Service std. respected:
=If([Service std. type] = "Ended") Then If(IsString([Actual time in hours])) Then "N/a" ElseIf ([Actual time in hours]<[Service standard Mål]) Then "Ja" 	Else "Nej" ElseIf([Service std. type] = "Started") Then If(IsString([Hours from created to started])) Then "N/a" ElseIf ([Hours from created to started]<[Service standard Mål]) Then "Ja" 	Else "Nej" Else "N/a"

Service standard goal:
=If(Left([Service std].[Jobcode];1) = "U") Then If([Service std].[Prioritycode] InList("A1"; "A2"; "A3"; "A4"; "A5")) Then 24 ElseIf ([Service std].[Prioritycode] InList("C1")) Then 168 ElseIf ([Service std].[Prioritycode] InList("C2")) Then 336 Else "N/a" ElseIf(Left([Service std].[Jobcode];1) = "F") Then If([Service std].[Prioritycode] InList("A1"; "A2")) Then 2 ElseIf([Service std].[Prioritycode] InList("A3")) Then 4 ElseIf([Service std].[Prioritycode] InList("A4")) Then 6 ElseIf([Service std].[Prioritycode] InList("A5")) Then 8 Else "N/a" Else "N/a"

Open in new window


Thanks alot in advance.
LVL 6
scifo_dkAsked:
Who is Participating?
 
mlmccCommented:
I don't know if that can be sone in the BO tools.  I use Crystal and we have the same issue there.

Crystal has an IsNull but I don't know of a way to set a value NULL.

Is 0 a valid value?
If not then you could test for 0 when you want the calculation and ignore it.

mlmcc
0
 
musalmanERP ConsultantCommented:
Just after Quick View: Use NULL instead of "N/a"
0
 
scifo_dkAuthor Commented:
If I change "N/a" to NULL in Actual time in hours like this:
Actual time in hours:
=If([Service std. type]="Started") Then [Timer fra OPR til påbegyndt] ElseIf ([Service std. type] InList("Afsluttet")) Then ([Dage fra OPR. til UDF. slut] *24) Else NULL

Open in new window


I get the following error, which translates to:
Void id 'NULL' at the position 167. (WIS 10022)
 Error
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
musalmanERP ConsultantCommented:
One more Hit n Try you can do here :

Use "N/a" and for Numbers Use ToText(11111)

In formula where you require to use these values , use  ToNumber(11111)

It will allow you to handle both Number and Text at a time...
0
 
mlmccCommented:
The problem is a variable must have a type.  A formula can only return 1 type so if you need it to be numeric or N/A then it wont work unless you have it return a string.  You then can't use it in a calculation.

Why does a 0 cause isues?
If it is because you are using summaries to do ome calculations then you will have to do those calculations through formulas where you test for 0's and ignore them.

mlmcc
0
 
scifo_dkAuthor Commented:
mlmcc:

Hmm, good idea. If I use 0 as default and test for it, it should work.

Is there a way to say set a field as NULL or empty? Like:
If ([Field]="MyValue") then "Count" else NULL 

Open in new window

0
 
scifo_dkAuthor Commented:
0 IS a valid value, and I can use it as a fix, as the service level can't be 0.

Thanks for you help, it works :)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.