Reports integration with ASP.NET and WPF applications using Visual Studio and Report Builder 3.0

In this post I would like to show how to integrate very powerful reporting engine with your .NET application.
As Database I will use SQL Server 2008R2, ReportBuilder 3(free tool by MS), and Visual Studio 2010
I also will download Adventure Works sample database from codeplex.com

1. Create blank report in Report Builder, change title of the report

2. From left pane select Data Sources and add new Adventure Works Source





3. From the left pane create new Data Set
Change name of data set to SalesDataSet
select Data Source you have created
Then run Query designer to build Select script which will return Data Table


4. Select
Production.WorkOrder.OrderQty
Production.WorkOrder.DueDate
Production.Product.ProductID
Production.Product.Name

Click Ok,
in Parameter from Left pane add @Year as a parameter
then modify Query so it will include Year filter

SELECT
  SUM(Production.WorkOrder.OrderQty) AS Sum_OrderQty
  ,Production.Product.Name
  ,Month(Production.WorkOrder.DueDate) as 
FROM
  Production.Product
  INNER JOIN Production.WorkOrder
    ON Production.Product.ProductID = Production.WorkOrder.ProductID
Where YEAR(Production.WorkOrder.DueDate) = @Year
GROUP BY
  Production.Product.Name
  ,Month(Production.WorkOrder.DueDate)



5.Open Insert Tab and click Chart/Insert Chart
Select the first one 

6. Select Chart, then drag and drop Qty column to Chart , Month to Category Group

7. Click Run on the ribbon
type 2001 in year box and run report
Now you have a report which can be printed and exported
Save it on disk

8.Now lets integrate integrate this report with our .NET application
Unfortunately you cannot send this report to Visual Studio 2010 as it is not compatible with ReportBuilder 3.0
the only way is to publish the to to Report Server and then integrate it with your application

Visual Studio has a Report Wizard which allows you to create report directly in you .NET solution

9. Click on Save As  on ribbon menu
Click on Recent Sites or Server, if it will be empty select your Report Server URLwhich is like http:///reports by default, reports also can be accessed by http:///reportserver


name your report something like MyFirstReport
and Save it. Sure you should have the appropriate permissions to publish report.

10. Open report server using your browser and check if your report was published
11. And YES , there are some problems with different browsers
Full functionality you will have with IE, with Firefox you will not be able to open report settings, with Chrome the will not be opened at all. Here is the result for the report in Firefox


12. There is ReportViewer control in Visual Studion using which you can consume a report from server
This control is accessible for Windows Forms and ASP.NET applicaitons
to use it with WPF you first should insert WinformHost control to which ReportViewer will be added

Here I will show integration with ASP.NET

13. Create ASPX page and insert ReportViewer control
Drop it into your WebPage

14. Open properties of the control

Change Processing Mode and Report Path 

15. now you can run web application to check the report


Comments

Popular posts from this blog

How to poll database using WCF-SQL adapter

SQL query timeout from application but works fast from SSMS