#UNAVAILABLE in WebI with BEx Query via BICS over ECC

[Former Member]

dear all

i have a very simple problem to explain.

when i try to use =Month([Flight date]) function, my key figure returns #UNAVAILABLE

that's it !

of course, without the function, the key figures are good, but i get every darn date, but that is not what i want. i want to sum up the amounts for each of the months.

when responding, please be mindful that there is no universe here, as it is via BICS.

thanks

g

[Former Member]

Did u changed Key figure Properties in Bex Level and checked.

Try changing the Aggregation and Calculations in your Bex Query.

Sometimes this will fix your issue.

Thanks,

Muthu.

[Former Member]

chaps, it has been a short while. since then, i have patched the gui and the bex bi7 add-on. i will take a new look at this soon.

[Former Member]

Is flightdate formatted as date? Try to put the todate function into the month function.

Another idea would be to use substring to extract the date and group by that.

Let us know how it went!

[Former Member]

hi sven

it is a date field, in fact it is a time dimension. so no problems there, but i tried this to test

=ToDate([Flight date];"dd/mm/yyyy")

this function works but does not help my scenario as i already have the full date and it did not group at all, but the interesting thing here, is that i lost my measure to #UNAVAILABLE.

i thought i would try

=ToDate([Flight date];"mm/yyyy")

which gave #ERROR in the field and still #UNAVAILABLE in the measure. i guess you must have the "d" in there somewhere.

i then tried to proove the [Flight date] is recognized as a date by doing

=IsDate([Flight date])

the records came back with 1, which proves they are recognized as a date. so i cannot use subst as it requires a string. but this is going down the wrong path anyway.

then i tried

=FormatDate([Flight date];"mmm/yyyy")

which does this Apr/2011 for example, goes near and will do what I want to achive, but yet again the measure is #UNAVAILABLE again.

it seems that it just does not work.

i am this close of logging an SAP support error

I can get my grouping to work if I create a Month field in the source, and outside WebI, but i don't really want to do that. I want the variable webi dimension to work as it should, or i believe it should

[Former Member]

Hi Glen,

I have the same problem when I use any date formules (Month, Year, Week, DayBetween....)

Do you solved this issue?

Appreciate your reply.

Best regards,

Sergio Gordon.

[Former Member]

I was facing similar issues and SAP Note 1603634 helped me to resolve the issue.

Regards,

Anu

[Former Member]

hello anu, i took a look at that note, but did not help me at all.

i created a detail dimension as suggested, hid the actual date, but aggregation was not occurring as i still had all the dates hidden. as soon as i took the dates out, the same #UNAVAILABLE occurred.

for me to fix this, i had to create a dimension in the source that took care of the calendar month, then all was well,