How to display a SQL Server Report in a .net webpart for SharePoint?

2
votes

Can anyone describe to me the exact steps in displaying a SQL Server Report hosted on the report server (Remote mode) in a customized webpart? I realize that there is an out of the box web part that comes with SSRS but I want to be able to have already configured webparts that display specific reports available for users to add in their edit site modes. Essentially, the goal is to have a report such as 'Assigned Hours' as a web part itself, and anyone interested in displaying Assigned Hours need only click on Site Actions, Edit Page, Add Web Parts, and choose the Assigned Hours Web Part. No need for configuring a report server url or report path. Also, for certain reasons we would like to continue running the report server in native mode so Integrated mode with SharePoint is not an option.

So, below is a summary of my situation and what I have already tried:

  1. Environment is WSS3.0, Microsoft SQL Server 2005
  2. I have created a .net webpart and am attempting to add an instance of the ReportViewer control to the webpart. I have set to run in remote processing mode and have extended the abstract IReportServerCredentials class to handle the authentication.
  3. I have made sure that the assembly is set to AllowPartiallyTrustedCallers
  4. The project compiles fine. I place the dll in the correct wss bin folder for webparts.
  5. When I try to view the webpart I get the site error message 'That assembly does not allow partially trusted callers.'

I've read things about registering my assembly with SQL Server, which I have tried with the CREATE ASSEMBLY SQL syntax, but that I am getting an sql query error saying that the there is already a reference to another assembly and that it can not find it in the same location.

Honestly, I don't really know enough about SQL Server and reporting services to know if I am close to getting it to work or if I have everything set up completely wrong in the first place. I have made custom webparts before and successfully deployed them to the site, so I know the problems lie in my ignorance of the SSRS and SQL Server. Has anyone else accomplished what I described early before? Thanks in advance!

1
sql-serversharepointreporting-servicesweb-parts

1 Answers

0
votes

I suggest using an ASP.NET ReportViewer control in your web part. These controls allow you to customize all parts you mentioned and are fairly easy to use and set-up. The one thing to watch for though with this control would be browser compatibility. Out of the box, it doesn't work too well with Firefox or Chrome. If you wanted though you could attached to the Reporting Services service which would give you the data you want back, then write a template-able control that used that structure to output the data. That option is much more complex, but allows for the format to be defined by the web part, rather than the report.