on 03-19-2008 4:12 AM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.