Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.
SQL Server Report Custom Code - Use Datasource
Is it possible to use the datasource of an ssrs report inside of the custom code section. I want to utilize the same connection as the report to return other data from the same connection, but a different query.
Ex:
Report datasource name = ds
function test() as string
test = ds.connectionstring
end function
Ex:
Report datasource name = ds
function test() as string
test = ds.connectionstring
end function
Asked:
-
5
3
2 Solutions
Due to reasons outside my control I cannot create temp tables or temp functions. I want to write a query like:
select apptid, time, place, whoid
where there may be several whoid's.
so I would normally write
select apptid, time, place, getwho(apptid)
which would return something like
1001 , "3/7/2011 11:53 am", "city", "sally, bob, john"
By capturing the datasource / connection string I want to create a VB function that will look up the who's.
select apptid, time, place, whoid
where there may be several whoid's.
so I would normally write
select apptid, time, place, getwho(apptid)
which would return something like
1001 , "3/7/2011 11:53 am", "city", "sally, bob, john"
By capturing the datasource / connection string I want to create a VB function that will look up the who's.
Hi
Not sure if I understand... when you say look up who's. What exactly would you do?
Are you using 2008 or 2008 R2?
I'm getting an impression that you might have other options but I need to understand the look up bit.
Regards
Emil
Not sure if I understand... when you say look up who's. What exactly would you do?
Are you using 2008 or 2008 R2?
I'm getting an impression that you might have other options but I need to understand the look up bit.
Regards
Emil
CREATE FUNCTION #GetWho(@apptid int)
RETURNS VarChar(300)
AS
BEGIN
DECLARE @Names varchar(1000)
DECLARE @Name varchar(400)
DECLARE c CURSOR for
select name
from table t
where t.id = @apptid
OPEN c
Fetch Next FROM c into @Name
While @@FETCH_STATUS = 0
Begin
If @Names is null
set @Names = @Name
else
set @Names = @Names & ', ' & @Name
End
Close c
Deallocate c
set @Names = substring(@names, 1, 300)
RETURN @Names
END
RETURNS VarChar(300)
AS
BEGIN
DECLARE @Names varchar(1000)
DECLARE @Name varchar(400)
DECLARE c CURSOR for
select name
from table t
where t.id = @apptid
OPEN c
Fetch Next FROM c into @Name
While @@FETCH_STATUS = 0
Begin
If @Names is null
set @Names = @Name
else
set @Names = @Names & ', ' & @Name
End
Close c
Deallocate c
set @Names = substring(@names, 1, 300)
RETURN @Names
END
Forgot my context. This is a TSQL function. The VB Code would
have to make a connection using the connnection from the report first.
THen implement a function somewhat similar to this one.
have to make a connection using the connnection from the report first.
THen implement a function somewhat similar to this one.
I see your problem. I don't have definitive answer apart from that the answer to your original question is no, you cannot do that.
My approach would be to join tables (ID to get name) which will result in multiple rows (with different name). then use code to combine them (comma separate values). So you should be able to replicate what you did in a function. I'm don't use SSRS code in this way as I don't have sql server restrictions :). I suggest posting this specific question I'm sure there are some experts who use SSRS Code this way.
Hope that helps
Emil
My approach would be to join tables (ID to get name) which will result in multiple rows (with different name). then use code to combine them (comma separate values). So you should be able to replicate what you did in a function. I'm don't use SSRS code in this way as I don't have sql server restrictions :). I suggest posting this specific question I'm sure there are some experts who use SSRS Code this way.
Hope that helps
Emil
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Code cannot talk to neither data sources nor data sets.
What are you actually trying to achieve?
Regards
Emil