Integrating SQL Server Reporting Services with ASP.NET 3.5

Today, I am going to discuss Basic and Simple demo on Integration of SSRS with ASP.NET 3.5, I am assuming that readers have already have installed Visual Studio 2008, SQL Server 2008 [with Reporting Features] and Report Builder 2.0

Just to have idea those who are new to SSRS, SQL Server Reporting Services 2008 are in a way unique due to their architecture, Now we have a separate block of Tablix, Tablix is combination of Table + Matrix [In short, Table + Matrix = Tablix].

Since Microsoft have great association with .NET Dundas visualization tool, so now you can implement Graphs, Gauges in your reports with ease and with much more Rich UI, which you can create using tools like Report Builder 2.0.

I am showing this example with Northwind Database. I assume you are comfortable with Report Builder 2.0 to design your report.

What I would like to discuss over here before we go for ASP.NET integration is how we can enhance performance of Report say for like getting 1 lac or more records, So We can Implement Cache for the same.

Step 1 : Cache the Report for better Performance

Start your Reporting Manager instance [ You can find URL of that in your Reporting Services Configuration], Once you add Report to your directory, Click on Report and locate “Properties” tab, Click on Properties and you will find Cache settings in “Execution” option on left hand side.

Note : You can only set Cache if and only if you provide valid credentials for “DataSources” option just above “Execution” option, else it will not allow you to implement Cache.

Once you apply Cache, Test report in Report Manager, Close the Report Manager instance and Start Visual Studio 2008

I tried from 60k to 1 lac records and it worked out very smoothly, so I personally find this feature as “Edge” over Crystal Reports.

Step 2: Integrate Report with ASP.NET 3.5 and Send parameters to Report from ASP.NET Web Application

You already have “MicrosoftReportViewer” Control in your Visual Studio 2008 under Reporting tab on Toolbox.

I am now taking one instance of ReportViewer with one TextBox to accept paramaters and one command button to submit that parameters to Report.

ASP.NET Code :

<div>
   <table>
       <tr>
           <td>Enter Customer ID: </td>
           <td><asp:TextBox Width="180" runat="server" ID="txtparam"/></td>
           <td><asp:Button Text="Show Report" ID="btnSubmit" runat="server" onclick="btnSubmit_Click" /></td>
       </tr>
   </table>  
   <rsweb:ReportViewer ID="rptvw" runat="server" Height="600px" Width="800px"></rsweb:ReportViewer>
   </div>

C# Code :

using Microsoft.Reporting.WebForms;

….

protected void btnSubmit_Click(object sender, EventArgs e)
    {
        rptvw.ProcessingMode = ProcessingMode.Remote;
        rptvw.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver");
        rptvw.ServerReport.ReportPath = "/Northwind/NewNorthwind";

        ReportParameter[] param = new ReportParameter[1];
        param[0] = new ReportParameter("CustomerID", txtparam.Text);
        rptvw.ServerReport.SetParameters(param);

        rptvw.ServerReport.Refresh();
    }

Explanation of Code :

Currently, I am sending only one parameter as “CustomerID” to my Report. SSRS basically supports two Processing Modes as “Remote” and “Local”, Since there is no support available for “Local” mode, Developers are suppose to fetch reports directly from Reporting Server, So I have set mode as “Remote” and have set those paths which you can see in code.

This is most simple way to integrate SSRS with ASP.NET, some other ways are using WebService [WSDL which RS exposes], but I personally find that time consuming and bit difficult compare to this one.

Hope this will give you idea about how you can enhance performance of your report with implementation of Cache and how easy is integration with ASP.NET 3.5 Web Application.

On ending note..

SSRS now give you power to export your reports in various formats like XML,PDF,CSV,TIFF and other Standard Office document formats, Also Report Builder 2.0 now implements most of the Office features like Zooming and Ribbon Controls for design and editing etc.

Vikram.

IIS 7 : Reports and Database Manager features.

First of sorry for so much delay in putting blogpost, I am out of my hometown for some work so hardly get time to blog and Internet access is sporadic.

Today I am going to share with you the simply amazing tool of IIS7. It is add-on to IIS7 but it is very much handy especially for those who monitor performance and into Website management. Those people along with others now can have complete Drill-down reports from IIS7 which can be export to CSV,HTML,XML etc.

Also many of the developer find hectic to log on to SQL Server instance for firing queries and modifying Table data and Schema. Now IIS 7 provides handy add-on of Database Manager, let’s discuss it one by one.

For this you need Admin Report Kit for IIS7 (ARKIIS), find it here :

http://www.iis.net/downloads/default.aspx?tabid=34&g=6&i=1782

Download and install it, it won’t ask you for any other dependency, all you need is IIS7 installed in place.

IIS Reports :

Once you installed it, you will find it like this once you start IIS7

Click on IIS7 Report [Assumption : You have at least one website hosted on IIS7]

You will get “Get Report” option and “Filters” to view specific reports from two specific Date intervals.You will also find Export option over here, as we discuss above, you can export it in various formats, along with that you have Pie and Line as other options of Chart, Column is default.

As you can see above, This is how you will get report, drill down to date and categories like which page,script or resource had maximum hits etc. in proper grid as well as graph format. Beside this there are several options too which you can see in image below.

Other details of visit you can see in Grid Format just below Chart like this.

So, don’t wait, download it and start using, it is a great tool !

Database Manager : 

As we have discuss above, now Database Manager is built-in in IIS7, it is very similar to SQL management studio [Looks wise] and other hosting consoles which we get on hosting sites for Database.

Here you can create Tables with Schema, Stored procedures and you can play around with it, lots of general Data Operation functionalities are provided.

Hope this article will encourage you to move from IIS6 to IIS7 and make use of these great tools, I will back with loads of new stuff on ASP.NET and Silverlight in coming days, so I am back here again, do let me know your suggestions and feedback.

Vikram. 

Silverlight 2 : Playing Multiple Videos with One Media Element

You may think that Playlist is always good option, though it is, sometimes it takes too much time to develop and test, so for small amount of video files, you can do play multiple videos by making use of Media_Ended() event.

I found many new things while doing demo of this, First surprise was that I was not able to declare “var” globally, I am researching on it why it had happened, Hashtable and ArrayList are seems to be not allowed in Silverlight though they are normal collections with .net framework, a surprise for me !

Code goes here :

public partial class Media : UserControl
    {

        int i = 1,j;
        Dictionary<int, string> temp = null;

        public Media()
        {
            InitializeComponent();

            temp = new Dictionary<int, string>();

            temp.Add(1, "http://localhost/Video1.wmv");
            temp.Add(2, "http://localhost/Video2.wmv");
            temp.Add(3, “http://localhost/Video3.wmv”);

            j = temp.Count;

            this.Loaded += new RoutedEventHandler(Media_Loaded);
            MasterMedia.MediaEnded += new RoutedEventHandler(MasterMedia_MediaEnded);
        }

        void MasterMedia_MediaEnded(object sender, RoutedEventArgs e)
        {
            if (i == j)
            {
                MasterMedia.Stop();
            }
            else
            {
                i++;
                MasterMedia.Source = new Uri(temp[i], UriKind.Absolute);            
            }
        }

        void Media_Loaded(object sender, RoutedEventArgs e)
        {
            if(i == 1)
              MasterMedia.Source = new Uri(temp[i], UriKind.Absolute);            
        }
}

As I told you that Hashtable and Arraylist was not in position so I have declare a “Dictionary” over here globally. I am adding URLs of media in it along with a key as integer so as to recognize what needs to be play. That can be track using global variables i and j.

This is the simplest way to play media in loop and you will observe the great usage of Media_Ended() event over here, how every time its getting fired. Do let me know your feedback on this if there are any more smart ways to do so.

Vikram.