SQL Server Reporting Services 2008 -Log - Parameters / Dataset / Some rendering of report

We're currently using Telerik Reporting mechanism in a WinForms environment but have recently started taking a look at Reporting Services, since we are running on a SQL Server 2008 platform that seems to make sense;)

I'm pretty 'light' on reporting services knowledge at the moment (busy wading my way through a couple of Brian Larson's books as we speak!) but there are things I need to find out as soon as.

We have the following requirements: -

- Log all report parameters + user executing report etc.
- Log dataset used to generate report
- Log some rendering of the report generated, i.e. some export
- Possible integration into an existing WinForms application

What I need to ask is are these things possible? Best approaches etc.?

Happy to provide further clarification...

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL Server ConsultantCommented:
You can find the report name and user (Windows identity) and parameter list in the report execution log. This is available as a view, dbo.ExecutionLog2 (or dbo.ExecutionLog) in the ReportServer database. You may need to grant permissions on the view for your application.
Execution logging is enabled by default but could be turned off by connecting to the report server using SSMS and changing the settings (connect, right-click the server name, select 'Properties', and click 'Logging'. You can also control how long records remain in the log. The default is 60 days.
I'm not clear yet what you want to log about the dataset or rendering. Is this something you want to happen automatically, or something that your application would do explicitly? If you call code in a custom assembly from a dataset or from somewhere in the report then you can gain control to do "extra" things, provided that you give the assembly sufficient permissions to do what it needs to do.
There are WebForms and WinForms versions of the Visual Studio report viewer control.
inthegardenAuthor Commented:
"I'm not clear yet what you want to log about the dataset or rendering. Is this something you want to happen automatically, or something that your application would do explicitly?"

I would like to log the dataset returned, if I could log a copy of the report output (in some form) that'd be great. Automatically would be good but it could happen under application control. As I'm sure you've guessed by now I have pretty heavy audit requirements.

"If you call code in a custom assembly from a dataset or from somewhere in the report then you can gain control to do "extra" things, provided that you give the assembly sufficient permissions to do what it needs to do."

Are you saying that I could possibly see the dataset from within some custom calling code? In terms of permissions, what sort of issues am I likely to encounter? Any pointers to examples of this sort thing would be fantastic!

With regard to the viewers, are there any differences in the way the function, winforms vs webforms?

Thank you for taking the time out to respond, appreciate the questions are pretty vague, just trying to wrap my head round this technology!

Megan BrooksSQL Server ConsultantCommented:
You can have complete control over the data source if you provide a custom one. A custom data source resides in its own assembly that you create, and connects with the report server through the RS config file. It communicates with the server by passing a DataReader object.

A custom data source could establish its own database connection, execute a query, and pass the data reader to RS. That way it would "know" exactly what was in the data. You configure it with whatever permissions it needs.

If you want to also intercept the rendered output you can do that, but then I think it would be up to you to create your own report viewer control. There is a "render" method that will return the report's HTML to you rather than displaying it. If there are no interactive parameters (or other features such as export to Excel) then having to display it yourself might not be a problem.

I am going from memory and these are both features that I have not used for several years, but I can point you to more information if this sounds like what you need.

Custom code (as I mentioned before) allows you to take control when the report code initializes, when a dataset is created (prior to executing the query--it can supply the text of the query, or part of it), or during formatting of individual fields. Sometimes this is useful for providing other customization through side effects, but often is not because control passes at the wrong time. By default it runs with very low privileges, but you can change that by modifying the RS config file. Right now it sounds as though a custom data source is more what you would be looking for.

There are significant differences between the WinForms and WebForms viewers. Also, 2008 RS has new viewer code, which may introduce even more differences (I know it fixes some major issues). I haven't worked with the WinForms viewer other than the instances used in Visual Studio for preview, but I can again point you to more information.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

inthegardenAuthor Commented:
Anything you can point me at would be great! Just trying to build up a critical mass of information;)

Megan BrooksSQL Server ConsultantCommented:
To have complete control over the data source you can implement a Custom Data Extension. A CDE accepts a query from a dataset and returns a .NET DataReader object containing the results. In your case you could either pass the query through, having your CDE connect to SQL Server and execute it, or you could have predefined queries built into the CDE for greater security. Most of the work in the CDE I created (to use a web service API as a data source) was around interpreting the query. Predefined queries might also simplify things.

Once a CDE has been installed and configured, it appears in the list of data sources in the Report Designer dialog

The CDE overview is in Books Online, or you can find it at http://msdn.microsoft.com/en-us/library/ms152816.aspx

The other thing you mentioned was access to the report output. As I mentioned earlier, the Render method of either the ServerReport or LocalReport  object in the ReportViewer properties will return the rendered report directly to the caller. You can specify the renderer to use, so you can get the data back as HTML or in Excel or other formats. The data is returned as an array of bytes.

I am not sure exactly how to do it, but it seems like when a user requested a report you could, in your application code, first call the "Render" method to capture (for the log) the content of the report and then let the report viewer control render the data for the user. I don't know if all of the events you would need to do this are available or not.

ServerReport.Render is described at http://msdn.microsoft.com/en-us/library/microsoft.reporting.webforms.serverreport.render%28VS.80%29.aspx

LocalReport.Render is described at http://msdn.microsoft.com/en-us/library/microsoft.reporting.webforms.localreport.render%28VS.80%29.aspx

The Execution Log that I also mentioned earlier is a view. You can find it in the ReportServer database using SSMS, and there is more information at http://msdn.microsoft.com/en-us/library/ms159110.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
inthegardenAuthor Commented:
Absolutely fantastic responses, many thanks rscowden!

Time for me to do some reading!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.