?
Solved

Use both string and integer in field

Posted on 2011-05-02
7
Medium Priority
?
629 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:scifo_dk
  • 3
  • 2
  • 2
7 Comments
 
LVL 4

Assisted Solution

by:musalman
musalman earned 800 total points
ID: 35503820
Just after Quick View: Use NULL instead of "N/a"
0
 
LVL 6

Author Comment

by:scifo_dk
ID: 35503884
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
 
LVL 4

Expert Comment

by:musalman
ID: 35504079
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 101

Expert Comment

by:mlmcc
ID: 35504654
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
 
LVL 6

Author Comment

by:scifo_dk
ID: 35504732
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 1200 total points
ID: 35504764
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
 
LVL 6

Author Comment

by:scifo_dk
ID: 35506744
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question