CODEWITHSUNDEEP
sql-serverreporting-services
JustNod
JustNod

Reputation: 53

Can I use a SQL user defined function in an SSRS query?

I seem to be having problems building a dataset in SSRS when the query references a SQL user defined function. e.g.

SELECT ISNULL(Period, dbo.FnuPeriodFromDate(PerBegDt)) as Period
  FROM MyTab

Is it legal to do this? It rejects it as invalid syntax yet the query works in SSMS.

Upvotes: 1

Views: 2651

Answers (2)

JustNod
JustNod

Reputation: 53

Sonny's suggestion was the correct solution - i.e.

"Try giving yourself rights to execute the procedure by adding this to your query: GRANT EXECUTE ON [storedProcName] TO [userName]"

many thanks.

Upvotes: 0

Sonny Childs
Sonny Childs

Reputation: 598

There are many things that the query builder will reject, that actually work fine once you render the report. For example, one of my queries uses this:

WITH X AS 
(
  SELECT TOP (30) n = ROW_NUMBER() OVER (ORDER BY m1.number)-1
  FROM [master].dbo.spt_values AS m1
  CROSS JOIN [master].dbo.spt_values AS m2
)

It throws errors at me, but the report runs fine(SSRS doesn't like the OVER keyword). If you hit 'OK' on the dataset and your 'Report Data' tab displays field names, it's still working. Just use SSMS or run the query in Visual Studio with the BI tools and ensure that your data is how you want it to be.

You can view your report data by going to the menu bar -> 'View' -> 'Report Data'

Upvotes: 2

Related Questions