SQL Reporting Services 2008 FAQs

Q:What is SQL Reporting Services?
A: SQL Server Reporting Services is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports. An integrated part of the Microsoft business intelligence framework, Reporting Services combines the data management capabilities of SQL Server and Microsoft Windows Server™ with familiar and powerful Microsoft Office System applications to deliver real-time information to support daily operations and drive decisions. (text from http://www.microsoft.com/sql/reporting/productinfo/overview.asp)

Q:How do I create a report?
A:There are 2 common ways of creating a report for use by SQL Reporting Services. Reports in SQL Reporting Services use the Report Description Language (RDL) which is an XML based standard for defining reports. Since it is simply XML, a report can, theoretically, be written using any text editor. This method, however, is not an approach favored by most since implementing RDL by hand can be laborious in addition to having a steep learning curve.

By far, the most common way of creating a report is by using the SQL Report Designer which is available to those owning licenses for both SQL Server and Visual Studio.NET 2008. The Report Designer is and Visual Studio.NET 2008 add-in packaged with SQL Reporting Services and, when installed, makes available a Report project type in Visual Studio.NET 2008.

Also on the horizon are numerous third party tools that are able to generate RDL. At the time of this writing, many of the available tools are still in beta testing but there are a handful that offer full products for consumer purchase and/or evaluation.

Q: Is the ReportViewer control included in SQL Server 2005/2008?
A: No. Report Server is a component of SQL Server 2005/2008, but the ReportViewer control is not. ReportViewer is only included in Visual Studio 2005/2008.

Q : Does SQL Server have to be installed in order to use the ReportViewer control?
A: No. The only prerequisite of the ReportViewer control is .NET Framework 2.0.

Q : Is a SQL Server license required to use the ReportViewer control?
A: No. The ReportViewer control is freely redistributable. It can work independently of SQL Server ('local mode') and a SQL Server license is not required. In remote mode the ReportViewer control talks to a Report Server. The Report Server does require a SQL Server license.

Q : Does the data for the report have to come from a SQL Server database?
A: No. Data can come from any source. It is the host application's responsibility to collect data from whatever source it needs to come from, and supply it to the control in the form of ADO.NET DataTables or a collection of business objects. The ReportViewer control does not know or care where the data is coming from.

Q: Can the ReportViewer view reports hosted on a SQL Server 2000 Report Server?
A: No. In remote mode the ReportViewer control can only view reports hosted on a SQL Server 2005 Report Server. Note that this has nothing to do with where the data for the report can come from. Data for the report can come from any source, including SQL Server 2000, Oracle, DB2 and non-database datasources.

Q: Is the ReportViewer control part of the .NET Framework?
A: No. The ReportViewer control is distributed with Visual Studio 2008, but it is not part of the .NET Framework.

Q: What editions of Visual Studio 2008 include ReportViewer?
A: ReportViewer is included in Standard and up. (i.e., all editions except Express.) An add-in is available for Visual Web Developer Express. (See below.)

Q8: What is the difference between RDL and RDLC formats?
A: RDL files are created by the SQL Server 2005 version of Report Designer. RDLC files are created by the Visual Studio 2008 version of Report Designer.
RDL and RDLC formats have the same XML schema. However, in RDLC files, some values (such as query text) are allowed to be empty, which means that they are not immediately ready to be published to a Report Server. The missing values can be entered by opening the RDLC file using the SQL Server 2005 version of Report Designer. (You have to rename .rdlc to .rdl first.)
RDL files are fully compatible with the ReportViewer control runtime. However, RDL files do not contain some information that the design-time of the ReportViewer control depends on for automatically generating data-binding code. By manually binding data, RDL files can be used in the ReportViewer control.
Note that the ReportViewer control does not contain any logic for connecting to databases or executing queries. By separating out such logic, the ReportViewer has been made compatible with all data sources, including non-database data sources. However this means that when an RDL file is used by the ReportViewer control, the SQL related information in the RDL file is simply ignored by the control. It is the host application's responsibility to connect to databases, execute queries and supply data to the ReportViewer control in the form of ADO.NET DataTables.

Q: What does the C in RDLC stand for?
A: The C stands for Client-side processing. RDL stands for Report Definition Language.

Q10: Why doesn't the ReportViewer control support parameter prompting in local mode?
A: The ReportViewer control does not prompt for parameters when in local mode. It prompts for parameters when it is connected to a Report Server.
In local mode it does not make sense for ReportViewer to prompt for parameters. The rationale is as follows: The most common use of report parameters is to pass to queries as values of query parameters. But unlike the Report Server, the ReportViewer control does not execute queries itself. Rather, queries are executed by the host application, and the result is passed to the ReportViewer control. So the ReportViewer control does not have the opportunity to set query parameters. Applications should take advantage of the parameterization features of Visual Studio data wizards instead.

Q: I installed Visual Studio 2008 and started Report Designer. I can't find the Preview tab. Where is the Preview tab that existed in SQL Server 2000 Report Designer?
A: There are two editions of Report Designer. One comes in the Visual Studio 2008 box. The other comes in the SQL Server 2005 box. If you are using the Report Designer included in SQL Server 2005, the Preview tab is still available. If you are using the Report Designer included in Visual Studio 2008, you have to run your application in order to preview the report.
Why is the Preview feature missing in the VS 2008 edition of Report Designer? Because the ReportViewer control does not contain any logic for connecting to databases or executing queries. Since it is your application's responsibility to supply data—and your application can supply data that originates anywhere, including objects that are internal to your application—data for previewing the report cannot be obtained without running your application.

Q: Why can't I just hand an RDL file to the report control and have it do everything and show me the report?
A: When the ReportViewer control is used in remote processing mode you can simply supply the url of the Report Server and the path to the report, and ReportViewer control will do the rest.
In local processing mode, the ReportViewer control is designed to integrate well with the host application. That includes being able to generate reports based on business objects that are internal to your application.
The ReportViewer control does not contain any logic for connecting to databases or executing queries. By separating out such logic, the ReportViewer has been made compatible with all kinds of data sources, including non-database data sources.
Rather than building in the logic to connect to databases and execute queries, ReportViewer is designed to integrate well with Visual Studio data wizards and the classes they generate, such as the TableAdapter class that knows how to connect to data sources, execute queries and fetch data.

Q: I dragged and dropped a field from the Data Sources window into the report. When I run the application the report only shows one row of data. How can I display all rows?
A: To see all rows, first drag and drop a Table or a List to the report, then drag and drop the field into the Table or List.

Q14: I am using the WebForms ReportViewer control. ReportViewer displays the message "Report is being generated" but the report is not displayed when this progress message goes away.
A: If you have set ReportViewer's height to a percentage, then in the .aspx file delete the line that sets doctype to xhtml. Why?

Q15: Does xls export require Excel to be installed?
A: No. The xls export feature is independent of Excel. Excel is only required to view the xls file.

Q16: Does PDF export require an Adobe license?
A: No. The ReportViewer control does not use any Adobe code to generate PDF files. PDF is an open format, and anyone can create PDF files without requiring a special license.

Q: What are the limitations of ReportViewer control?
A: Unlike the Report Server the ReportViewer control does not connect to databases or execute queries. Also, in local mode the only export formats available are Excel and PDF. (In remote mode all formats supported by the Report Server are available.) The ReportViewer control cannot be extended by adding custom renderers or custom report items.
Besides additional export formats, the Report Server offers better scalability, central storage, management and access control of reports, caching, subscriptions, etc.

Q: How can I learn more about Reporting Services?
A: Plenty of books and training are available.


Reference: http://www.gotreportviewer.com/

How to Install CRM Adapter for BizTalk Server 2009

Installing CRM for BizTalk Server 2006 is quite straight forward. The CRM adapter for BizTalk requires that BizTalk 2006 to be installed. But when I tried installing CRM adapter on BizTalk Server 2009, it fails.

What I realized while installing CRM adapter for BizTalk 2009, it is just checking if Biztalk 2006 installed on the system. So I started digging about and discovered that CRM installation is simply looking for the registry key that says BizTalk Server 2006 is installed. So here is a short step in order to get the adapter installed on BizTalk Server 2009.

Action items before installing CRM adapter for BizTalk 2009:

Before installing the CRM Biztalk adapter, take a backup of the registry (good practice) and then modify the following key (mentioned below), then install the adapter. After the adapter is installed, set the key back to its original value.

For 64 bit machine:

Start run >> type regedit then hit enter, navigate to

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\BizTalk Server\3.0

change ProductName = "Microsoft BizTalk Server 2009" to ProductName = "Microsoft BizTalk Server 2006"

For 32 bit machine:

Start run >> type regedit then hit enter, navigate to

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0

change ProductName = "Microsoft BizTalk Server 2009" to ProductName = "Microsoft BizTalk Server 2006"

You are done, now install CRM adapter, once installation is done successfully make sure you revert back the registry product name to its original value.

Stay Hungry Stay Foolish …. I think this speech just changed my life

If you know you can love work, you're in the home stretch, and if you know what work you love, you're practically there.

People just listen this speech it has changed my entire life infact the most inspiring speech i ever heard.

Steve: "Stay Hungry. Stay Foolish" ...When I was young, there was an amazing publication called The Whole Earth Catalog, which was one of the bibles of my generation...It was sort of like Google in paperback form, 35 years before Google came along: it was idealistic, and overflowing with neat tools and great notions...(publisher) Stewart (Brand) and his team put out several issues of The Whole Earth Catalog, and then when it had run its course, they put out a final issue. It was the mid-1970s, and I was your age. On the back cover of their final issue was a photograph of an early morning country road, the kind you might find yourself hitchhiking on if you were so adventurous. Beneath it were the words: "Stay Hungry. Stay Foolish." It was their farewell message as they signed off...And I have always wished that for myself. And now, as you graduate to begin anew, I wish that for you. Stay Hungry. Stay Foolish."

SQL Server Reporting Service (SSRS) – 401 Unauthorized Access Issue in Production Environment

Let me share with you my experience on one of the issue i had with sql server reporting services in production environment.

Problem Description:
I am using sql server 2008 reporting services for reports in a .Net 3.5 application.

Production Environment:
Application Server: IM-APP1 (this is IIS machine and application is deployed here) has following windows server 2003, .Net 3.5, IIS 6.0

Database and Reporting Server: IM-SQL1 (This server has the database and sql reporting services), windows server 2003, .Net 3.5, SQL Server 2008, SQL 2008 Reporting Services

When i am browsing the application from desktop client, application works fine it fetches the data from database server, but when i navigate to a page, enter the report criteria and hit the button Generate Report then application throws 401-unauthorized access exception.

Now when i host database, reporting services and application on the same server (IM-APP1) then everything works fine.

below is the stack trace of the exception:

Exception Message : The request failed with HTTP status 401: Unauthorized.
Stack Trace : at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.SetConnectionSSLForMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)
at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.SetParameters(IEnumerable`1 parameters)
at Pages_Reports_LogFormReports.DisplayReport(ReportViewer reportViewer, String reportName, ReportParameter[] parmList)

DisplayReport() is the method name which is responsible for generating the report and display it in report viewer object

I reviewed the DisplayReport() method and found that report server user credentials are not passed to the report server, so here what Application server does is when it calls the reports and if the report is hosted on the same server it doesn't need any credential for generating report so the report is working fine, now in other scenario where reports are hosted on different server then Application Server should pass report server user credential to generate the report.

Now the question is how to pass report server user credential from the application.

Here is the solution:

First We need to Create one sealed class to perform this action. This class need to be inherited from IReportServerCredential interface.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Reporting.WebForms;
using Microsoft.ReportingServices;
using System.Security.Principal;
using RA.FRIMS.Settings;

/*****************************************************************************
* Created By : Nizam
* Created Date : Aug 17, 2009
* Description: This is sealed and serializable class and implementing IReportServerCredentials interface.
* This class Provides Network credentials to be used to connect to the report server.
* *****************************************************************************/

[Serializable]
public sealed class ReportServerNetworkCredentials : IReportServerCredentials
{
#region IReportServerCredentials Members
public bool GetFormsCredentials(out System.Net.Cookie authCookie, out string userName,
out string password, out string authority)
{
authCookie = null;
userName = null;
password = null;
authority = null;

return false;
}

// Specifies the user to impersonate when connecting to a report server.
//A WindowsIdentity object representing the user to impersonate.
public WindowsIdentity ImpersonationUser
{
get
{
return null;
}
}

// Returns network credentials to be used for authentication with the report server.
//A NetworkCredentials object.
public System.Net.ICredentials NetworkCredentials
{
get
{
//you can place below settings in configuration xml file
string userName = "ReportServerUserName";
string domainName = "ReportServerUserInDomainName";
string password = "ReportServerUserPassword";

return new System.Net.NetworkCredential(userName, password, domainName);
}
}

#endregion

}


Now all what you have to do in your display report method is:

//This method display reports in the report viewer object
private void DisplayReport(ReportViewer reportViewer, string reportName, ReportParameter[] parmList)
{
try
{
//For Reports with some input parameters.
reportViewer.ShowCredentialPrompts = false;
reportViewer.ShowParameterPrompts = false;
reportViewer.ShowDocumentMapButton = false;
reportViewer.ShowPageNavigationControls = true;
reportViewer.ShowFindControls = false;
reportViewer.ShowPrintButton = true;

reportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

// Here we are going to pass the ReportServerCredentials to the Report Viewer.
reportViewer.ServerReport.ReportServerCredentials = new ReportServerNetworkCredentials();


reportViewer.ServerReport.ReportServerUrl = "http:port//ReportServerMachineName/reportserver";
reportViewer.ServerReport.ReportPath = "ReportFolderName" + reportName;

reportViewer.ServerReport.SetParameters(parmList);
reportViewer.ServerReport.Refresh();

}
catch (Exception ex)
{
//handle exception
}
}

That's all you are done!!! Now run the report.

Please feel free to comment on this post. Critics are highly appreciated. :-)