Calling a scalar valued function from within a reporting services data model report

Question

I'm working on a report using SQL Server Report Builder (version 3) against SQL Server 2008 with a custom data model.

Within the database that my data model is based on I have a couple of scalar valued functions that are used to calculate fiscal periods.

What I would like to be able to do is call into these scalar functions from my report so that I can use this data to filter the results I get back.

Obviously if I was developing a SQL based report then this would be a simple where clause. As the report is based on a data model however, I can't seem to figure out how I can achieve this.

I have thought about re-writing the database functions as custom code within the report, but I'm trying not to go down this route if at all possible as I don't want the code duplicated over a number of reports.

Answer 1

I am afraid you can't invoke non procedures (i.e. functions) without going through SQL select statements in reporting services.