SQL User Defined Functions and SQL Reporting Services.

I am having problems using my user defined function iDate to convert
dates in SQL Server Reporting server. Normally I just write stored
procedures and use iDate to convert the dates to date data types in the
stored procedures and SQL Reporting never knows they are just numeric
dates but in this case the user wants to directly access the tables
without me writing a stored procedure.

What I have established so far is that:

1. When you type an SQL statement into data window in SQL Reporting
Service, the SQL is just passed through to the AS/400. In other words, I
have to use SQL syntax that is AS/400 specific.
2. I edit the OLE DB and give it a library list and I have confirmed
that Reporting Service is using the library list and my utilities
library EMUTIL is on the library list.
3. I can take the SQL statement to iSeries navigator and run it there
and it work fine.

But when I run the SQL statement, I get back a message saying that iDATE
is not found which makes no sense.

I tried the following but it returns garbage.

Select emutil.iDate(TRNDTE,'*CCYMD') From PCHIST

Has anyone tried to use a User Defined Function from SQL Reporting
Service and how did you get to work?

My solution at the moment is to create a view over each table that the
user wants to use and use iDate to convert the fields to date data type
but that means creating a view over every table the user want to access.


I have tried converting the dates manually using something like this but
it does not work because the AS/400 is expecting the date in a
CCYY-MM-DD format and I have CYYMMDD format dates so cannot convert into
CCYY-MM-DD formats and I don't see any keywords in OLE DB to specify the
date format.

Select ITNBR,
CRDT,
Date(Substring(Digits(CRDT),4,2) || '/' ||
Substring(Digits(CRDT),6,2) || '/' || Substring(Digits(CRDT),2,2)) As
ADateField
From ITMRVA

Thanks for the help.