Cannot SUM Database Delegate Measures in Webi

[Former Member]

Hi,

I am on BO3.1 SP5. We have a few COUNT(DISTINCT X) and Ratio ( SUM(A) / SUM(B) ) measures in our universe and we figured that we need to make them Database Delegate as they would otherwise result in incorrect results based on the dimension used in query vs the dimesions user drags into a report.

Now we have the right results, but when a user tries to use the Σ on those columsn in the Webi i get the #TOREFRESH and on hitting the Refresh Query I get an error as it generated a second query and since I have Start Date dimension used it says invalid date format. However my question is why we cannot use the SUM on the Database Delegate measures and what do I need to do to make it work after the #TOREFRESH message.

[Former Member]

Hi All,

I think I have take the above to another level -

Now I have the measure as Database Delegate set in the universe.

The users query has the Start  Date, and two other dimensions and this measure. On run query we get the results.

Then if the user tries to do the Σ ( Default Aggregation ) on this database delegated measure in the Webi, initially it gives the #TOREFRESH message and so in hitting refresh it gives the below error.

On looking at the SQL - i see that now there are two different SQL generated and since I have the Start Date object whihc is the Date in the database, it inserts the below on its own and since it is in the select the PRM does not map it and we are getting the above error.

The SQL now looks like -

SELECT

  0 AS GID,

  {d '1969-12-31'},  ---- This is added in the Select on its owns and the database is Netezza.

Is this any way to not add the default 1969 date ? How can I run the query successfully with the Start Date? As if I remove the Start Date is runs just fine. But the users need to see the Start Date?

Please any help is appreciated.

Thanks