Avatar of pmeharry
pmeharry
 asked on

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
Microsoft DevelopmentMicrosoft SQL Server 2008SSRS

Avatar of undefined
Last Comment
pmeharry

8/22/2022 - Mon
itcouple

Hi

Code cannot talk to neither data sources nor data sets.

What are you actually trying to achieve?

Regards
Emil
pmeharry

ASKER
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.
itcouple

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
pmeharry

ASKER
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
pmeharry

ASKER
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.
SOLUTION
itcouple

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
pmeharry

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
pmeharry

ASKER
Basically there is not solution.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.