Dates in BEx query coming into designer as a dimension with Char data type

[Former Member]

This must be an old issue with a well documented work around ... but alas i can find it !

When i build a universe over a BEx query with dates the universe designer builds them as Dimensions of CHAR type not DATE. Therefore all WebI date functionality fails .. eg sorting, automatic time hierarchies etc.

What is the workaround ?

Andrew Fox

[Former Member · Accepted solution]

Is the data source an InfoSet or an InfoCube? It appears the date type object has not been implemented yet for Infosets. Here's SAP Note 1370410:

Symptom 
Infoset date type objects (DATS type) are mapped as Character types in a universe based on a SAP BEX Query.
Prompts related to the Date field variables are coming as List of values instead of Calendar in WebIntelligence.
Reproducing the Issue 
Create a BusinessOjects universe on a Super Query which is based on a multiprovider containing an Infoset or
Create a BEx superquery directly on Infoset.
BusinessObjects Enterprise XI 3.1
SAP Integrated Solutions Kit XI 3.1
Cause 
This behaviour has been considered as an enhancement request and tracked under ADAPT01229917. 
Resolution 
This enhancement request is scheduled to be addressed in Service Pack 3 (SP3) for BusinessObjects Enterprise XI 3.1 due approximately April 2010. 

I did try a BEx query that uses an InfoCUBE as a source and the objects are coming into the universe correctly as dates.

[Former Member · Accepted solution]

Sadly it looks like this has not made SP3. The sap note has been updated...

"This enhancement request is scheduled to be addressed in Service Pack 4 (SP4) for BusinessObjects Enterprise XI 3.1 due approximately May 2011."

anyone got any cunning ways of getting around this. i need to do a between condition on date and with it being seen as a character its of course not playing ball

cheers!

jeremy.

-

---

Jeremy Braker

AgilityWorks.co.uk

[Former Member · Accepted solution]

Hi Jeremy,

Were you able to solve the Date issue with installation of SP4 ?

Please let me know if you had done any modifications with out installation of SP4 or work around to solve the issue.

Thank you

Leela

[Former Member]

Hi ,

Please use Todate function to convert that particular Infoobject(char) to date and we can do Sorting etc.

Thanks

[Former Member]

Thanks for taking the time to reply. When i try to use the ToDate function i get XML errors. Can oyu give me an example of the syntax ?

[Former Member]

Hi,

Which BOE version are you using?

We support DATE datatype for date characteristics but not for properties such as key.

You can't turn a CHAR into DATE because you will hit a driver error.

On the other hand, SAP does not support calculated expression that returns date or string: only numeric is supported.

By the way, TODATE is for relational only not for OLAP.

Regards,

Didier

[Former Member]

Didier,

I am using BOE XI3.0. Unfortuantely we will not be upgrading to further service packs until "defects" are identified that become compelling reason.

So as i understand it I cannot do any conversion in a universe to change DATS characteristics to a "date" data type in the universe to allow WebI to work as it would in against a relational data source.

So where i am getting columns from a BEx query formatted 092009, 102009 etc or AUG 09, SEPT 09 they will only sort alphanumerically not in a Date sequence.

What do I need to tell my Cube developer to do to give me dates in a format that can be interpretated by the Universe correctly and used in WebI ?

Cheers

Andrew

[Former Member]

Hi,

I don't know what is possible to do on the cubes or BEx queries layer (maybe SAP Exit)?

By the way, you can convert a string to date in WebI document ... but you have do it for each document.

Didier

[Former Member]

Yep, that is what i am now doing Didier. Doing date conversion report side but this carries an overhead and to be honest is user friendly.

Andrew