How to replace blanks by zero in a Bex Query output for key figure values?
I have an issue with blank values in the query output. My requirement is to show these blanks as zero.
We are showing the flex time of employees in hours for each month and flex time is a calculated KF calculated as NODIM(flextime). Now if any employee is having flex time as zero for a particular month it is showing up as zero but if an employee is not existing in a month then it is coming into the report as blank and not as zero. Our report is built on a multi provider which is built on an infoset. If an employee is not there(not valid for a time period) then those records are not coming up in the infoset or multiprovider and this is causing blanks to appear in the report.
Suppose if we take 2 employees A and B and their flex time as below:
A joined in march 2010 and B is there from jan 2010 and their flex times are as below:
Jan Feb Mar Apr May Jun
A............................................... 2 0 1 2
B............................................... 1 1 3 1 0 1
In the above case the report(when ran for Jan to Jun 2010) shows zero for A for the month Apr'2010 and for B for the month May'2010. But the report shows blanks for Jan and Feb for employee A and these records not available in MP or IS.
I have searched in SDN and tried all the given solutions like creating another CKF and adding zero to flex time or creating flex time as RKF and then creating a CKF by adding zero to the RKF but they did not work. Finally decided to write a IF THEN ELSE formula but there I had a challenge to show blank as I didn't find option to keep single quotes to represent blank.
Can anybody help me how to create a IF THEN ELSE condition using the blank value or any other possible solution to show the blanks as zero.
Thank you very much.