How to Show null values in Report Output

[Former Member]

Hi Friends ,

                  We have a Requirement in Bex . Query have  a keyfigure with Zero's, Null values. In query output have to hide Zero's and need to show null values.

The below is the example

Material      Quantity                       

   A                0

   B                0

  C                     

   D             

   E                20

   F                50       C and D having null values . it should be display in Output.             

Out put should be

Material      Quantity                       

   C                     

   D             

   E                20

   F                50

Please  Advice how to Achieve this.

[Loed · Accepted solution]

Hi Ramesh,

Check the blog of Yasemin..It will solve your scenario..

Regards,

Loed

[Former Member · Accepted solution]

Hi loed ,

                 Thanks for your  help and also Thanks to Yasemin to provide this solution.

Regards,

Ramesh

[yasemin_kilinc · Accepted solution]

Hi Ramesh,

You and another post I saw in this week motivated me to write that blog

Thanks for the inspiration

Regards

Yasemin...

[swati_gawade]

Hi Ramesh,

What is the type of your key figure?

Typically, for any KF the NULL value means 0. Please share your configuration to store the NULL values in system.

As per my understanding, in normal circumstances, the NULL value will be stored as 0 only. hence there will not be any distinguishing factor between NULL and 0 Values.

Hope this helps.

-swati.

[Former Member]

Hi Swati,

                   The Requirement is User dont want see Mat-type X with QTY=0 in out put.

In the below Example  i need to remove Material A and B in query out put.

Material	Mat-type	QTY	RKF1	RKF2
A	X	0	0
B	X	0	0
C	X	10	10
D	Y	30		30
E	Z	0		0
D	K	60		60

RKF1 = Restrict with Mat-type X with QTY

RKF2 = Restrict with other than Mat-type X with QTY

In RKF1  only the Zero's should be remove .

I need to create one more Field that field should be display like below

Material	Mat-type	QTY	RKF1	RKF2	Output filed
C	X	10	10		10
D	Y	30		30	30
E	Z	0		0	0
D	K	60		60	60

I.e user don’t want see the Mat-type X with QTY = 0, And he want see other than Mat-types X with QTY=0.

I am following this processor but i am not getting desire Output.

Can you pls suggest How to  Achieve this requirement.

Regards,

Ramesh.

[anshu_lilhori]

Try this:

Make a new formula say F1 and write if else condition like this:

(rkf1==0)*(-1)+(rkf1<>0)*rkf1

Now against 0 with material type x you will get -1.

Next step is to make a new formula and add (rkf2 +F1)

Finally apply a condition on this new formula--show values >=0.

Hope this solves your issue.

Regards,

AL

[Former Member]

Hi Anshu,

                  already I tried what you suggested and it gives the formula F1 is not correctly defined error message . because of  this (-1)  value in Formula.

One more thing the IF we do like this the values will fill with null values also and when we do this (rkf2 +F1)  values will be Wrong.

Regards

Ramesh.

[yasemin_kilinc]

Hi Ramesh,

To make a discrimination between null values and zero values, you need to add a new characteristic to your infoprovider. You can check from the datasource if the value coming is null or zero and if the material type is X you can assign '1' to this new characteristic. Then in the query you can use this characteristic filtering the material type X with qty = 0.

Hope it gives an idea

Regards

Yasemin...

[swati_gawade]

Hi Ramesh,

Try this.

RKF1 = Quantity restricted with Mat type = X

RKF2 = Quantity restricted with Mat type <> X

(rkf1==0)*(RKF2)+(rkf1<>0)*(rkf1_RKF2)

Hope this helps.

-Swati.

[Loed]

Hi Ramesh,

Try this:

Create a formula, let's call this ZFORM_ADD and add the two (2) selections above:

RKF1 + RKF2

HIDE the two (2) selections (RKF1 and RKF2) so that you will only have one (1) column for your keyfigure, the ZFORM_ADD..

Create a CONDITION for RKF1 > 0 so that values equal to 0 for material type X will not be shown..

Regards,

Loed

[former_member183012]

Hi Ramesh,

I am agree with cornelia..then also can you please try one thing in your query...apply condition on keyfigure <>0 check whether it will consider space value as 0..it should be then also try once.

Regards,

Ganesh Bothe

[Former Member]

Hi ganesh ,

                    I tried condition ,  when  i apply condition Material C and D also not displaying in Report Output.

Can you pls give some alternative to get the desire output .  like through create dummy CKF or RKF

[former_member183012]

Hi Ramesh,

Is there any other field is different in case of null value's material so that we can identify these material.,

[cornelia_lezoch]

Hi Ramesh,

you will need to have a counter key figure - i.e. from a dso or you need to add it to your data records.

In case A/ B and E/F the counter needs to bring something > 0 and in cas C and D there is a 0.

Then you have something to put a condition on top.

regards

Cornelia

[cornelia_lezoch]

maybe you can also create the counter within your query.

do an exception aggregation on the quantity and let the system count the occurences. It should also count the 0 but not the NULL

[Former Member]

Hi Cornelia  pls see the below clear Requirement  and Comment on that

[Former Member]

Hi Ganesh,

                        pls see the below clear Requirement  and Comment on that.

Regards,

Ramesh

[cornelia_lezoch]

Hi Ramesh,

with the data you show this will not be possible.

you can either hide the 0-lines and only E and F will remain, or you show the material master data then all lines from A - F will be shown.

regards

Cornelia