PDA

View Full Version : Formulas in meta data



colorfool
12-11-2007, 11:32 AM
Hi there,

I want users to use WAQR to create some reports. Many of my tables include a date column, only the format of the column is a bit unlucky. It is an integer value which does not even contain a normal timestamp value but a day counter since Dec 31 1971.

I am trying to create a new column in the meta data which is of type date and calculates its value by using a formula.

There is not much documentation on the behaviour of this formula thing, though, so I keep generating errors ;)

My most successful try up to now was to set Field Type to "other" and Is this formula exact? to "yes" and put SQL into the formula field: "convert(char(12),dateadd(day,del_date,'Dec 31 1971'),104)"
(this is a Sybase database and "del_date" is the integer column)

That way if I use WAQR I actually get a report with the correct result, the BI server at the same time generates an error message, though, saying "Failed to parse Formula".
How to get rid of that error? I am sure that my way is very unclean, anyways, because as soon as I would create a join in WAQR where both tables have a field called "del_date" I would have to specify the table for del_date in the formula.

Is it possible to do this without SQL? There is a DATE function described in the Wiki but it seems like it cannot take a timestamp argument? A timestamp would be easy to calculate from my integer...

I would be glad for any advice,

cheers,

Axel