Report Studio Problem Convrting Varchar to date

[As120379]

Hi- I am having a problem in converting a varchar to date in report studio. What is happening is I have a cube as a datasource to the report studio. When I try to perform any > or = etc operations between the cube element and the day say current date it says "UDA-SQL-0460 A general exception has occurred during local processing. UDA-EE-0094 The operation "equal" is invalid for the following combination of data types: "varchar" and "date2" RSV-SRV-0042". Now when I use Cast function and say "cast([Cube - AP_AR].[Date].[Date].[Day].[Day - Long Name],Date)=current_date" on data Item I get the following error message when I run the report "UDA-SQL-0144 An arithmetic exception was detected. UDA-EE-0029 A date/time value is invalid. UDA-SQL-0460 A general exception has occurred during local processing. UDA-CUR-0000 Unable to fetch the row." Interestingly I don't get any errors when I validate the report. Please advice.

Thanks in Advance

 

[As120379]

For more info.. Initially I was not able to see the folder called "Vendor Specific Functions" in the function tab but somehow I was able to get it now but those functions are not working either. Please advice How could I achieve this

 

[blom0344]

The Vendor specific functions are targeted at the various relational databases, but in your case the datasource is a cube. Mixing SQL functions with an OLAP source is not the best approach.
To be honest I have no idea if there is an equivalent to cast in MDX (SQL for OLAP sources)
One way to solve this is by modifying the cube sources and adding a dimension that stores the varchar as date.

Bye the way, the report validation does not validate the the query generated as this is compiled and executed at run-time.

Ties Blom