Solved

SQL Server Report Custom Code - Use Datasource

Posted on 2011-03-07
8
862 Views
Last Modified: 2012-05-11
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
Comment
Question by:pmeharry
  • 5
  • 3
8 Comments
 
LVL 10

Expert Comment

by:itcouple
ID: 35055965
Hi

Code cannot talk to neither data sources nor data sets.

What are you actually trying to achieve?

Regards
Emil
0
 

Author Comment

by:pmeharry
ID: 35056238
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
 
LVL 10

Expert Comment

by:itcouple
ID: 35056762
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
 

Author Comment

by:pmeharry
ID: 35056843
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:pmeharry
ID: 35056860
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
 
LVL 10

Assisted Solution

by:itcouple
itcouple earned 500 total points
ID: 35057030
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
 

Accepted Solution

by:
pmeharry earned 0 total points
ID: 35057137
Thanks for your time & help!
0
 

Author Closing Comment

by:pmeharry
ID: 35120642
Basically there is not solution.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS - Generating separate reports for each department 2 57
SQL 2008 Conversion failed 7 20
Using CTE to insert records into a table 2 27
sql Total query 2 14
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now