Code works in Management Studio but not in the Dataset of an .rdl report

Question

Why does this only work in management studio and not in the dataset of a rdl report?

I don't have access to the live database so I cannot add this function directly into the database.

if OBJECT_ID (N'dbo.PreviousFriday', N'FN') is not null
    drop function dbo.PreviousFriday;
go
create function dbo.PreviousFriday (@Date datetime)
returns datetime
with execute as caller
as
begin
    declare @PreviousFriday datetime;

    set @PreviousFriday = DATEADD(DAY, (case DATEPART(DW, @Date)
        when 1 then -2
        when 2 then -3
        when 3 then -4
        when 4 then -5
        when 5 then -6
        when 6 then 0
        when 7 then -1
    end), @Date)
    return(@PreviousFriday);
end
go

if OBJECT_ID (N'dbo.NextFriday', N'FN') is not null
    drop function dbo.NextFriday;
go
create function dbo.NextFriday (@Date datetime)
returns datetime
with execute as caller
as
begin
    declare @NextFriday datetime;

    set @NextFriday = DATEADD(DAY, (case DATEPART(DW, @Date)
        when 1 then 5
        when 2 then 4
        when 3 then 3
        when 4 then 2
        when 5 then 1
        when 6 then 0
        when 7 then 6
    end), @Date)
    return(@NextFriday);
end
go

Here is the error I am getting:

Answer 1

Because the query in SSRS needs to be DML (Data Manipulation Language)

ie

 select * from ...

or

 exec dbo.prcGetMyData ...

That sort of thing...


But you are trying to enter DDL (Data Definition Language) ie you're attempting to change the underlying database, which would be somewhat of a security issue if this were permissible.

you could just create a dataset called LastFriday with your script inside it. You could then use the value of the dataset inside your textbox or where-ever...

SELECT    DATEADD(DAY, (CASE DATEPART(DW, @Date) 
                         WHEN 1 THEN - 2 
                         WHEN 2 THEN - 3 
                         WHEN 3 THEN - 4 
                         WHEN 4 THEN - 5 
                         WHEN 5 THEN - 6 
                         WHEN 6 THEN 0 
                         WHEN 7 THEN - 1 END), @Date) AS LastFriday

If you want to share that among multiple reports, create a shared dataset. (you'll need to do the same for NextFriday!)

OR you could avoid TSQL entirely and just create the function as code inside your report.