#UNAVAILABLE Error in Webi report Based on Bex Query

[Former Member]

Hi,

We have BO reports which are based on BeX Query's .We are implementing Currency Conversion for some of the BO WEBI reports .

we have a  Currency Conversion Filter in Selection Screen for this i wrote User response function to show the cloumn values Dynamically in the report But

By Deafualt values are showing in the Report column  "Currency-Key" but when i run for any currency (Ex: USD) values are not changing . For this i Created a variable called Technical1=UserResponse("Currency Conversion").

Then i directly written a formula on Clolumn Currency - Key "=If(UserResponse("Currency Conversion")="";[Currency - Key];[Technical1])" then am getting # UNAVAILABLE error for the only highlighted columns

Currently our BO Version : SAP BI 4.0 SP6.1

I tried creating a variable and placing that variable beside the columns thoug ham getting the same error.

Kindly help me in this regard to get rid og #UNAVALIABLE error.

Thanks & Regards,

Kumar

[Former Member · Accepted solution]

Hi Kumar,

What is your version of BW?

I faced the similar issue on two of my existing reports. The report worked fine on BW 7.0 SP10. After upgrading the BW server to SP13, I could see my reports having #UNAVAILABLE in several columns. Some of them could be resolved using the SUM() on the variables/formulas. But some couldn't be fixed.

It was noticed that the aggregation of the keyfigure that was Sum before the upgrade was changed to Database delegated(Hover your mouse on the particular keyfigure and on the mouse tip, you can see the Type and Aggregation).

We have raised a ticket with SAP Support to look into this issue and find a workaround for this. SAP is working on this now.

I shall let you know when we receive a workaround for this.

[Former Member · Accepted solution]

Hi Sarah,

Please find below DetailsBW Version whcih we are using

Thanks & Regards,

Kumar

[Former Member · Accepted solution]

Hi Kumar,

The version is different from my version of BW. But as per the SAP note-1874212, this is an issue in the BW 7.30 SP04 as well.

I'd suggest that you log a ticket with the SAP Support along with pursuing this discussion.

[Former Member · Accepted solution]

Hi Sarah,

what i understood here is till now SAP hadn't provide any solution for this and kindly confirm this issue will achieve in further pack up gradation if yes may i know the details.

Thanks for your time.

Thanks & Regards,

Kumar V

[former_member188911 · Accepted solution]

What you are experiencing is normal behavior, WebI can't resolve the filter because the aggregation is at database level.  This is explained in the guide Using functions, formulas, calculations...

and also in KBA 1606502

Thanks

SImone

[Former Member · Accepted solution]

Hi All,

Finally i resolved the issue in Webi Report itself .

I just changed the formula in variable and i changed the variable qualification as Detail and then  i associated  Dimension to it from avilable objects which is working as expected.

Thanks to one and all for your valuable inputs reagarding to this issue.

Thanks & Regards,

Kumar V

[Former Member]

I hope by now you are able to get results from Userresponse, but you are struggling with #unavailable.

Have you tried your luck with alerters/conditional formatting on column where you are getting #unavailable error.

[Former Member]

Hi Amit,

Tried that as well placing the formula in Conditional formatting but didn't worked.

Thanks & Regards,

Kumar V

[former_member188911]

What you are experiencing is normal behavior, WebI can't resolve the filter because the aggregation is at database level.  This is explained in the guide Using functions, formulas, calculations...

and also in KBA 1606502

Thanks

SImone

[Former Member]

Try using this formula:

=If(UserResponse("Currency Conversion")=" ");[Currency - Key];[Technical]

Once i solved the #Unavailable error by changing formula

[Former Member]

Hi Arifa,

The formula is not working it's throughing error some argument missing.

Thanks & Regards,

Kumar V

[Former Member]

Please paste the full formula so that it can be checked for errors

[Former Member]

Hi Arifa,

Here is the formula i implemented

First  i created a variable called Technical1==UserResponse("Currency Conversion") to pull the Currency Conversion values from selection screen. then i directly written a formula on the Column itself as =If(UserResponse("Currency Conversion")="";[Currency - Key];[Technical1]).

Thanks & Regards,

Kumar V

[Former Member]

Hi,

Try the below Formula

=If((UserResponse("Currency Conversion")="");[Currency - Key];[Technical1])

Thanks,

Kirthi

[Former Member]

Hi Muthiah,

Thanks for your reply.

It's not working still the issue remains the same.

[Former Member]

Hi,

Is the variable Technical1 returning you the result?

Technical1=UserResponse("Currency Conversion")

What the object qualification you have used for the variable Technical1?

Thanks,

Kirthi

[Former Member]

Hi Muthiah,

The Variable Techincal1 giving the output for me. the Object Qualification is Dimension.

Thanks & Regards,

Kumar

[former_member182342]

Hi Kumar Vasantha,

Change your object qualification to detail. and check the output.It will resolved your issue.

Regards,

Anish

[Former Member]

Can youo try the below Formula and let me know if it is working.

=If(([Technical1]=" ");[Currency - Key];[Technical1])

Thanks,

Kirthi

[Former Member]

Hi Muthiah,

Many thanks for your immediate response and your time.

Tried the above formula but the issue remains the same.

[Former Member]

Hi Anish,

Tried this as well not working.

Thanks & Regards,

Kumar

[Former Member]

Can you try using Trim() as below

=If(Trim([Technical1])="");[Currency - Key];[Technical1]

[former_member182342]

Hi ,

Create new formula

Technical 2= =If(([Technical1]=" ");[Currency - Key];[Technical1])

and make Technical 2 variable as details and give Technical1 in reference of Detail qualification

and then refresh the report and let me know.

Regards,

Anish

[Former Member]

Hi Muthiah,

The formula is not working it's throughing error some argument missing.

Thanks & Regards,

Kumar V

[Former Member]

Hi Anish,

Tried the formula below mentioned but no luck.

Thanks & Regards,

Kumar V

[former_member207878]

Check below thread, it could be useful for you:

This error occurs, just because of database delegation. Change the property of measure from database delegation to Sum and check again. If it won't resolve your issue then you can check SAP Note 1603634

[Former Member]

Hi Sahil,

Already referred SAP Notes on this but none of the note solved my problem.

Kindly let me know is there

In case of Reports based on Bex queries the work around is not given in the SAP Note.

Is there any possible work around for Reports based on Bex Queries?

Thanks & regards,

Kumar

[former_member207878]

Have you checked it by changing measure's property from Database Delegate to Sum?

[Former Member]

hi,

the approach you have used is not correct i think,

firstly u made a user response based on Currency conversion

and checking your condition for currency conversion variable with "" spaces.the logic i think is not correct.

rather create an variable based on input control and based on this variable create an If condition to check data.

there are no issues with SAP note.

your version does not have problem i think. but rather the problem was with user response and that user response is "" .

please try this approach and let us know.

Regards,

sundar

[Former Member]

Hi,

Are you using Universe to connect Bex Query with BO report.

If so, Try refreshing the Universe Structure and then refresh the report.

Thanks,

Kirthi

[Former Member]

Hi,

Reports Created on BICS  Connection.

Thanks & Regards,

Kumar

[Former Member]

Hi Sundar,

I can't create Input Controls .

The Requirement is to implement Currency Conversion in BO reports we have a "Currency Conversion" Prompt in Selection Screen and we have a Column "Currency - Key" in the report when ever user runs the report it showing default currency with values when ever user change the currency to (Ex: USD) currency values are converting but in the Column "Currency - Key" showing Default Text values instead USD. For this reason i created User Response function.

Technical1 =UserResponse("Currency Conversion")

Then i applied a formula on "Currency - Key" Column as:

=If(UserResponse("Currency Conversion")="";[Currency - Key];[Technical1]) to show only Key values as(Ex: US Dollar to USD).

Thanks & Regards,

Kumar V