Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

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
0
pmeharry
Asked:
pmeharry
  • 5
  • 3
2 Solutions
 
itcoupleCommented:
Hi

Code cannot talk to neither data sources nor data sets.

What are you actually trying to achieve?

Regards
Emil
0
 
pmeharryAuthor Commented:
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.
0
 
itcoupleCommented:
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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
pmeharryAuthor Commented:
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
0
 
pmeharryAuthor Commented:
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.
0
 
itcoupleCommented:
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

0
 
pmeharryAuthor Commented:
Thanks for your time & help!
0
 
pmeharryAuthor Commented:
Basically there is not solution.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now