We’re building WebI reports on top of BPC10, using BEx queries and OLAP connections.
Within the BEx queries, we’ve got a variable built on top of a Function Module designed to display the 7 days of the previous calendar week.
The dates, when pulled into WebI, are a character string and I’ve tried all sorts of ToDate and FormatDate combinations to try and convert them to a date in order to sort them correctly, but to no avail - it either gives me a blank cell, or else #ERROR or #SYNTAX. I thought it would simply be a case of =ToDate([DAY_TIME];dd/MM/yyyy) but that isnt working.
Our dates are always in the format dd/MM/yyyy (ie. 01/02/2012).
Could it be because we’re using BEx variables? If so, do any of the experts have a way of formatting the dates in this case?
Thanks for your swift response, but unfortunately it’s still just emptying my columns.
With no code formatting applied, it shows my 5/6/7 day columns (data dependent) and the data in the body, but when code is applied all data seems to be emptied from the pivot table and it shrinks to one, empty column.
Does anybody have any ideas? Has anybody come across this before?
When you mean pivot atbe, do you mean cross tab? If so, what happens when you apply the format to a regular table? Maybe, it would help if you posted an image of the “pivot” table, before formatting?
Here are the first couple of rows. It is a crosstable, the column headers are formatted in an extended row of their own.
I’ve tried creating a new report in both horizontal and vertical axes and both simply depopulate when a ToDate function is used.
As I say, our data is coming from BPC10, which does not have any kind of 0calday object (or similar) - each type of date is listed as master data in the same attribute (ie. day, week, month, quarter etc.) and brought through as a character string. Is this what could be causing the problem? In WebI, only days in the format dd/MM/yyyy are shown, but in the back end there are all sorts of different date formats for different time periods.
Thanks,
Chris
EDIT: Don’t be fooled by the dates being in order in that screenshot - the ordering seems to come and go depending on the user…
Can you give some examples of these?
Are you sure that there is no padding on either end of the returned string? You can test this by using the length() function?
Are these days, e.g. 20120131 formatted to dd/MM/yyyy, character, in the Bex query?
Its really strange that the length function does not work, do any functions work?
Are you using a supported version of Java on your workstation?
What happens when you build the formulas using the server side Java Interactive Viewer?
Are you getting this for all your Webi docs based on Bex queries?
Are you using a universe to connect or are you using BICS?
You should downgrade the Java then, that version will not be supported… What version of BI 4.0 are you using? Have a look at the PAM (Product Availability Matrix) for that version. I would consider this especially as most variables don’t work!!
Nope. I meant the interactive Viewer. You can set this in your preferences, then do a view on a report. This will invoke the Java on the server itself, see if you have any luck using this?
And they work OK?
So, you are using BICS (Business Intelligence Cube Service) to manage the connectivity to your Bex query, yes?
Hey Chris,
Not sure if you still need this, but I found that when I reversed the commands, it did work. For some reason the two digit month and day just don’t work with the “ToDate” command.
B!
(BOB member since 2005-01-06)
(BOB member since 2005-01-06)