Search results matching tag 'SQL Server Reporting Services'

Installing & Configuring the Integration between Reporting Services and SharePoint

Mike Glaser — Tue, 06 Nov 2007 21:29:45 GMT

More and more people are migrating their Report Server environment to SharePoint. This migration will give you the ability to open your reports in SharePoint instead of just another UI. For a complete explanation on this migration check a previous blog of my at SQL Server 2005 Service Pack 2 (Integration between Reporting Services and SharePoint 2007). Before you can integrate between Reporting Services and SharePoint you have to decide if you'll setup a separate Application Tier. Almost all my customers have a similar setup as pointed out below.

SERVER 1 - Data Tier

.NET Framework 2.0

SQL Server 2005 SP2 (Database)

SERVER 2 - Application Tier

Internet Information Server 6.0

.NET Framework 2.0 & 3.0

Windows SharePoint Services 3.0

SQL Server 2005 SP2 (Reporting Services)

Reporting Services Add-in for Microsoft SharePoint Technologies

It's also possible to install all these products on one machine, but it isn't advisable. Reporting Services installs some Web Services and a Web manager (will be replaced by the Reporting Services Add-in for Microsoft SharePoint Technologies). After installation you can find all files at the Program Files\SQL Server\MSSQL.#\Reporting Services\ReportServer directory. If you've installed al of the above products and updated it with the latest service packs and updates you can start the Reporting Services Configuration Manager. This tool can be started for the Start menu at All Programs - SQL Server 2005 - Configuration Tools - Reporting Services Configuration. Depending on the choice of installation you will see a similar screen like this.

If some of the above sections displays a red cross mark fulfill these steps.

The Report Server virtual Directory should point to the web site where you installed and configured SharePoint.
The Report Manager Virtual Directory will become obsolete, so it isn't necessary to implement this step.
The Windows Service Identity is used under which the Report Server Windows service runs. The Report Server Windows service performs initialization, reversible encryption, database maintenance tasks, and all scheduling and delivery. The service runs in the background. It performs end-to-end processing for reports that run on a schedule (specifically, it creates report snapshots and runs subscription reports).
Because it performs all encryption operations, the Report Server Windows service must be running whenever you specify or use encrypted values. Specifying stored credentials, running a report that uses stored credentials, and publishing a report to a report server (data source information is encrypted) are all operations that require the Report Server Windows service.
The Web Service Identity creates or uses a application pool under which the identity runs in IIS.

Now all steps are completed and Reporting Services is initialized you can start completing the last step to complete SharePoint Integration. Click on the Database Setup section and Change the Server Mode from Native to SharePoint Integration. After hitting the change button fill out the following screen and make sure the Create the report server database in SharePoint Integrated mode is selected.

Type in new Database Name (e.g ReportServerWSS). Note. Don't start to create a database which starts with WSS because in some cases the connection will fail. After creating the database the SharePoint integration is completed.

Summary
Although it isn't hard to install and configure the add-in, it will give you a lot of steps to fulfill. First of all you have to make a decision about your server topology. Secondly you must setup Reporting Services to use you're data tier (remote or local). Third upgrade your database, so all content will be added to SharePoint and the rendering and caching stays in SSRS. In my next blog I'll explain how Activating SharePoint for Reporting Services works.

Technorati tags: WSS, MOSS, SharePoint, SSRS, SQL Server Reporting Services

Extra, Extra read all about, Mike is back

Mike Glaser — Tue, 06 Nov 2007 20:02:39 GMT

I wasn't blogging for a long time, but now I'm back with a strong rhyme.
Look, near the camera, snap my picture. I'll sign my name on it, then I get richer.
Like LL said don't call it a comeback and face the fact Jack I'm all that.

The last couple of months I spend a lot of time developing and giving courses. Like a few of my colleagues already posted on their blogs, I spent a lot of time on our Summer Classes . Check Update summer classes 2007 at http://blogs.class-a.nl/blogs/anko/archive/2007/04/18/update-summer-classes-2007.aspx and Summer Classes 2007 at http://bloggingabout.net/blogs/dennis/archive/2007/06/08/summer-classes-2007.aspx for the details. My part in these classes was the developing an teaching the BI part. I had lots of fun with all my students and they appreciated my experiences as well. By the way these classes will be held again in January or February of 2008. See Class-A Summer Classes become Winter Classes at http://bloggingabout.net/blogs/dennis/archive/2007/10/04/class-a-summer-classes-become-winter-classes.aspx for full details.

Besides the BI part, I also developed a couple of our SharePoint modules. SharePoint is the reason why I was unable to post blogs for a long time. Our SharePoint courses SHAREPOINT 2007 DEVELOPMENT UNLEASHED, SHAREPOINT 2007 BI and SHARPOINT INSTALLING & CONFIGURING are almost every week sold out. I'm not surprised because SharePoint is HOT and other course provides can't deliver a training because the MOC (Microsoft Official Curriculum) material isn't available yet.

Here is some info about my next posts. More and more people are migrating their Report Server environment to SharePoint. I already received a couple of emails how to setup such an environment, but I never had the time to create a post. Today I received another mail requesting me to explain this. So my next posts will be about this subject. I'll divide this subject in three post.

Post: Installing & Configuring the Integration between Reporting Services and SharePoint
Post: Activating SharePoint for Reporting Services
Post: Developing Reports for Reporting Services under SharePoint

I'll keep you posted.

Technorati tags: WSS, MOSS, SharePoint, SSRS, SQL Server Reporting Services

What can we expect from SQL Server Reporting Services "Katmai"?

Mike Glaser — Thu, 10 May 2007 11:54:00 GMT

Microsoft is planning to release the next version of its SQL Server database, which is codenamed "Katmai," in 2008. Everybody is waiting anxiously what this version will bring us. Like everybody I've a personal wish list for SSRS and it seems MS is working hard to fulfill this list.

Rich Text support
RTF/DOC/PPT rendering
Shared Styles (Styles sheets)
Templates
Font embedding in PDF
Many Chart enhancement (Dundas)
Edit in Preview

At the Microsoft Business Intelligence (BI) Conference Jeff Raikes announced that Microsoft likes to acquire OfficeWriter from SoftArtisans. OfficeWriter is the only product available today that works with Reporting Services to preserve 100% of the features in your sophisticated spreadsheets and documents. Reporting Services currently does not support MS Word and has limited support for MS Excel. This is a huge win for folks who want to render reports to Word.

Another thing which is quite remarkable is the Report Designer will get a new look and feel. This could mean that MS is integrating the original Report Designer which is a part of BIDS and Report Builder to one product or the Report Designer is leaving the Visual Studio environment. Here is screen dump of the new Report Designer.

New Report Designer

Furthermore Microsoft added a new Katmai informational site Your Data, Any Place, Any Time. This site will inform you about the latest evolvements of "Katmai".

Summary
Unfortunately not all sessions from the Microsoft Business Intelligence (BI) Conference are and will be available on the net, so it's hard to understand where MS is heading to. Maybe the organizers could learn something from the MIX organizers because all sessions were available on the net within 24 hours after each live presentation. Great job guys! Besides this I hope you're are as enthusiastic as I'm about "Katmai" and I can hardly wait till the first CTP of "Katmai" will come out public.

Technorati tags: SQL Server, Katmai, SSRS, SQL Server Reporting Services

Automatic Logon in fully qualified domain name (FQDN) environment

Mike Glaser — Thu, 19 Apr 2007 12:15:47 GMT

This is one of my many posts about FQDN. Most people who followed the guides about setting up SSRS, WSS and TFS in FQDN environment, complained they have to log on each time when the connect to their sites. These sites are still intranet sites, but IE thinks otherwise. IE qualifies any FQDN as an Internet site. If you want to log on automatically to the Local intranet site, you can add the site to trusted sites of the Local intranet. Keep in mind if you changed the level or deselect the "Automatic logon only in Intranet zone" option this workaround won't work.

Open Intranet Options and select the Security Tab. After doing so select Local intranet and click the Sites button.

When the following display appears, select the Advanced button.

Add your portal URL (e.g. http://www.mikeglaser.com, http://portal.mikeglaser.com or http://tfs.mikeglaser.com)

Summary
These simple steps help you to configure your Internet Explorer so you don't have to log on every time you want to access your portal.

Technorati tags: TFS, Team Foundation Server, WSS, Windows SharePoint Services, SSRS, SQL Server Reporting Services

Hosting Reporting Services on a fully qualified domain name (FQDN)

Mike Glaser — Fri, 26 Jan 2007 13:08:56 GMT

This blog describes how you can setup your Reporting Services environment using a FQDN. A FQDN is an unambiguous domain name that specifies the node's position in the DNS tree hierarchy absolutely. To distinguish an FQDN from a regular domain name, a trailing period is added. ex: somehost.mikeglaser.com. An FQDN differs from a regular domain name by its absoluteness; a suffix will not be added.

For example, given a device with a hostname of "myhost" and a domain name of "mikeglaser.com", the fully qualified domain name is "myhost.mikeglaser.com". It therefore uniquely defines the device — whilst there might be many hosts in the world called "myhost", there can only be one "myhost.mikeglaser.com".

Host name resolution is the process of resolving a host name to an IP address before the source host sends the initial IP packet.

Step 1 Local host name

The configured host name for the computer as displayed in the output of the Hostname tool. This name is compared to the destination host name.

Step 2 Hosts file

A local text file in the same format as the 4.3 Berkeley Software Distribution (BSD) UNIX \etc\hosts file. This file maps host names to IP addresses. For TCP/IP for Windows XP and Windows Server 2003, the contents of the Hosts file are loaded into the DNS client resolver cache. For more information, see "The DNS Client Resolver Cache" in this chapter.

Step 3 DNS server

A server that maintains a database of IP address-to-host name mappings and has the ability to query other DNS servers for mappings that it does not contain.\

For this example I will modify the host file to use www.mikeglaser.com as a FQDN. On Windows-based computers, this file can be found in the systemroot\System32\Drivers\Etc folder. As with all virtual directories, you can further customize the report server and Report Manager virtual directories through Microsoft Internet Information Services (IIS). Besides this I will also configure the Default Web Site, so it's only accessible through www.mikeglaser.com. This can be done by changing the default identification and adding the FQDN as a host header value.

After doing so, I open the both Reporting Services applications using the mine FQDN.

http://www.mikeglaser.com/ReportServer

http://www.mikeglaser.com/Reports

The first URL will still work, but second URL will give you the following error, "The request failed with HTTP status 400: Bad Request." as you can see in the next picture. This error will not occur if you didn't configured the IIS Web Site to work with a host header value.

To configure the report server or Report Manager to use a FQDN, you must also edit the configuration files.

Open RSReportServer.config in a text editor.
Append the port number to the UrlRoot setting in the rsreportserver.config file. For example, if Urlroot is set to http://server/reportserver server, set it to http://www.mikeglaser.com/reportserver instead.
Open RSWebApplication.config in a text editor.
Set ReportServerUrl to the same URL you specified in UrlRoot.
Delete the value (but not the tags) for ReportServerVirtualDirectory.
Save both files.

When you finished doing the previous steps, Reporting Services will function with a FQDN.

Summary
As you can see it's not that hard to use a FQDN with Reporting Services, but you gotta know how.

Technorati tags: SSRS, SQL Server Reporting Services

Multiple Reporting Services instances on one machine

Mike Glaser — Mon, 18 Dec 2006 10:23:45 GMT

Since the release of Reporting Services 2000 back in January of 2004, I've build lots of apps using this technology. On my newest assignment they requested me to implement two Reporting Services instances on the same machine. Their serverpark isn't that big, but they want to simulate a separated test en development environment. I immediately applied that this won't be a problem and since SQL Server 2005 contains Reporting Services Configuration it isn't that hard anymore. To begin this giant adventure you've install two instances of SQL Server 2005 Reporting Services (e.g. Test & Development) and choose the option to not configure Reporting Services. Finished this, you now have to create an additional Web site(s), so the separated Reporting Services instances are used independently.

To simulate two web servers on one machine, you need to install an extra IP address or use the same IP address in combination with a host header value. This time I choose the extra IP address because this satisfied the Helpdesk administrator the most ;-). To add an extra IP address open your Network Card properties, followed by opening the properties of the Internet Protocol (TCP/IP). Select the Advanced button and Add as many IP addresses as you want to have separate instances of Reporting Services.

IP Address configuring

When you've added the extra IP address, you can create an additional Web Site to your Web Server. Using the Web Site Creation Wizard, you can select one of the other IP addresses.

Web Site Creation Wizard

The next step is configure your Reporting Services using Reporting Services Configuration. For extra info about configuring check MSDN.

Internet Information Services (IIS) Manager

When everything is configured using this Tool, it still doesn't work the way you like too. The second instance is still connecting to your default instance because the default behavior of a Reporting Services instance is redirecting its requests to the localhost. To change this behavior you have to change some settings in the config files (RSReportServer.config, RSWebApplication.config) of Reporting Services.

For example in: C:\Program Files\Microsoft SQL Server\MSSQL.#\Reporting Services\ReportServer open

RSReportServer.config

old value:
<UrlRoot>http://<servername>/reportserver</UrlRoot>

new value:
<UrlRoot>http://10.10.10.11/reportserver</UrlRoot>

For example in: C:\Program Files\Microsoft SQL Server\MSSQL.#\Reporting Services\ReportManager open

RSWebApplication.config

old value:
<ReportServerUrl></ReportServerUrl>
<ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>

new value:
<ReportServerUrl>http://10.10.10.11/reportserver</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Note: It's very important that only one of the two values is filled, else an error will occur.

Run iisreset /noforce and you're now using multiple instance of Reporting Services on the same machine.

Check the following URL's:

Summary
The SQL Team did a great job for letting you choose how many instances you want to use on one machine. They're fully configurable and administrable and you're able to create a real test and development environment on one machine. Great job guys.

Technorati tags: SQL Server, SSRS, Report Services

SQL Server 2005 SP2 Improvement #1 (SSRS - Select All)

Mike Glaser — Tue, 12 Dec 2006 22:08:55 GMT

This is the first blog of a series about discussing the improvements of installing SQL Server SP2.

If you've applied Service Pack 2 CTP2 to your SQL Server 2005 Reporting Services installation, you might have noticed the appearance again of "Select All" from the drop-down list for multi-valued parameters. If you would like to get an official patch before SP2 is released, you can contact Support Services and ask for Knowledge Base Article #919478.

SQL Server 2005 SQL Server 2005 SP1 SQL Server 2005 SP2

Via Brian Welcker

Here's the story behind this disappearance.

With SQL Server 2005, we introduced multi-valued parameters, which allow for you to specify a set of values for a single report parameter. If you think about how multi-valued parameters work against a SQL source, you build a SELECT statement with an IN clause and the values get substituted before it is sent to the server, e.g.

SELECT name FROM customer WHERE country IN (@country)

becomes:

SELECT name FROM customer WHERE customer.country IN ('United States', 'Canada', 'Mexico')

The problem comes from the addition of "Select All" to the drop-down list. In summary, the functionality of the "Select All" checkbox is simply, "check all of the values in the list unless they are already all checked. In this case, then uncheck them all." While this functionality works fine with a small set of values, having several hundred values in an IN clause is not a very efficient SQL statement.

The performance implications for Analysis Services is even more significant; you can select all of the individual members of a dimension as well as the "All" member of a dimension. If your parameter contains the entire member hierarchy (not just a single level), selecting all of the items in the list can cause major performance issues.

A more efficient "Select All" function would actually modify the statement to exclude the clause entirely. Selecting "all countries" in the above statement would result in:

SELECT name FROM customer

This is quite a bit trickier than the simple parameter substitution logic as you need to maintain the integrity of the statement (What if there is more than one search condition?) Also, we would need to write query parsing routines for each language we supported (T-SQL, MDX, PL-SQL, etc.)

We thought a long time about how to address the performance issues of this feature in SP1. Ideally, you would be able to add a flag to the report definition that would say whether or not to add the "Select All" checkbox. However, as a policy, we don't change the report definition in service packs due to the upgrade and compatibility issues. We also thought about auto-detecting whether a report is against Analysis Services but couldn't really do this without changing the execution SOAP API. We thought about making it a flag in the configuration file but this would force people to decide one way or another for all reports. Finally, we thought about putting "Select All" at the end of the list instead of the beginning.

Summary
It’s good to know why the MS team removed the Select All in the first place. It had nothing to do with a bug or so, but it was all about performance. Since we are self-employed and responsible for our own queries it's nice to have back again. I know several people made all kind of solutions to fulfill the same needs, but nothing came really close to the current UI and behavior.

Technorati tags: SQL Server, SSRS, SQL Server Reporting Services