cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to omit rows with null key figure values

Former Member
0 Kudos

Dear Experts,

I have a scenario here whereby i am unable to omit null key figure values in a query.

For example, my structure of the query consist of 2 characteristics as below:

Payment Terms, Vendor

*NOTE: For chracteristic payment terms, we have values such as 10 days from invoice date, 14 days from invoice date.

And the columns consist of 1 key figure:

Amount in Rupiah

Therefore, the report will look something like this as below:

Amount in Rupiah

10 days from invoice | a-Vendor| $XXXXX

| b-Vendor| $XXXXX

14 days from invoice | a-Vendor| $XXXXX

| b-Vendor| $XXXXX

Basically, the report is meant to display the total amount of all invoices that have not been settled for each vendor for each payment term.

The situation though is, there are certain cases where for 1 particular vendor, for a payment term, there are no invoices thus there is no amount in Rupiah.

E.g. For Payments Terms 10 days from Invoice & for Vendor IAmVendorA , there were no invoices that have not been settled thus the key figure column Amount in Rupiah should not have a value as depicted below:

Amount in Rupiah

10 days from invoice | a-Vendor | $XXXXX

| b-Vendor | $XXXXX

| IAmVendorA| (blank)

14 days from invoice | a-Vendor | $XXXXX

| b-Vendor | $XXXXX

This is true, and in the report, it displays the row with the above vendor and payment term and the key figure column is blank.

Unfortunately, we need to remove rows with blank key figures.

What we tried to do was, we created a formula with the following:

'Amount in Rupiah' / 1

This converted all blank entries into zeroes (e.g. 0.00)

Then we created a condition with reference to the Vendor characteristic and each Payment Term to only display rows whereby the new formula's value was greater then 0.

It works if we only have 1 condition but if we add another condition, it shows all 'Amount in Rupiah's as blank.

E.g. If we create a condition for payment term 10 days from invoice, it will show only vendors with non-blank Amunt in Rupiahs.

But if we add another condition for payment term 14 days from invoice, it will show only all vendors for that payment term, including the ones with blank or 0 'Amount in Rupiah'.

This being the case, we are unable to omit all rows with null key figure values.

Would really appreciate any feedback.

Thanks guys!

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Jonathan,

In your case as you said you want to ignore the rows with null values of the KF's. Try changing the properties of the KF.

Cheer's

HVR

Former Member
0 Kudos

Hi Reddy,

Thanks for the response.

Could you elaborate when you say "change the properties of the KF"?

Which properties would you suggest should be manipulated?

Also, which KF are you referring to? The one in beX or the actual infoobject from BW?

Btw, KF cannot never contian null values.

It's just that, in the query, its returning blank because the for the combination of characteristics, Payment Terms & Vendor, there is no valid KF thus it returns a blank entry.