Ms reporting services pdf

Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful? Please rate your experience Yes No. Any additional feedback? Note HTML report printouts may contain only the content on the first page if you are using the browser's Print command.

Note You can create and modify paginated report definition. In this article. You need to have a Database Engine available to store the report server database. Select Next to install the report server only.

For more information, see Report Server Configuration Manager. You need to create a report server database to complete the initial configuration of Reporting Services. If you are creating the report server database on a database server on a different machine, you need to change the service account for the report server to a credential that is recognized on the database server.

By default, the report server uses the virtual service account. If you try to create a database on a different server, you may receive the following error on the Applying connection rights step. SqlException 0x : Windows NT user or group ' null ' not found. Check the name again. To work around the error, you can change the service account to either Network Service or a domain account. Changing the service account to Network Service applies rights in the context of the machine account for the report server.

For more information, see Configure the report server service account. A Windows service is created as part of the installation.

The Delivery Analysis Mobile Report. Package Weight by Planet Mobile Report. Mobile Reports with Drillthrough. Employee Hours by Hub Mobile Reports. Mobile Report Publisher Report Settings. Layout Grid Resolution. Settings Tab. Time to Be Served. The Web Portal. Deploying Reports Using the Report Designer. Uploading Reports Using the Web Portal. Modifying Reports from the Report Server.

Managing Items in Folders. Moving Items Between Folders. Deleting a Folder. Renaming a Folder. Searching for a Report. Finding Text Within a Report. Report Output Formats. Exporting Reports in Other Formats. The Atom Data Feed. Printing from the Web Portal. Managing Reports on the Report Server. Linked Reports. Creating a Linked Report. Creating a Document Library. Deploying a Report to a Document Library.

Managing Reports. Security Roles. Data Alerts. Delivering the Goods. Chapter 12 Delivering the Goods: Report Delivery. Shared Datasets. Report Parts. Caching In. Report Caching. Enabling Report Caching. Report Snapshots.

Enabling Report Snapshots. Cache Refresh Options. Cache Refresh Plans. Report History. Enabling Report History. Managing Report History Snapshots.

Standard Subscriptions. Managing Your Subscriptions. Data-Driven Subscriptions. Site Settings. The General Site Settings Page. Branding Site Settings Page. Managing Brand Packages. Additional Settings. Building On. URL Access. Web Service Access. The Report Viewer Control. SharePoint Web Parts. Reporting Services Utilities.

Best Foot Forward. Chapter 14 Well Begun: Best Practices. Use Report Templates. Use Shared Data Sources. Use Views and Stored Procedures. Use Navigation Features. Report Deployment Practices.

Create a Backup of the Encryption Key. Review Reports Before Deploying. Use Linked Reports. Use Folders and Descriptions to Organize Reports. Assign Security at the Folder Level. Assign Security to Domain Groups. Assign Only the Rights Needed. Hide Items. Deploy Supporting Items to the Report Server. Use Caching and Snapshots. Where Do We Go from Here? In many ways, each new engagement presented a new set of challenges: a new organization, a new group of people, a new industry, a new business problem.

Clients tended to prefer web-based solutions, and ASP. NET became my tool of choice. They needed a way to store and manage their data, and SQL Server became my tool of choice.

And almost without exception, they needed to generate reports from their data and deliver them to the right people to act on them. There, SQL Server Reporting Services became my tool of choice and served me—and my clients—well in countless projects. A number of resources helped me learn this powerful tool, including Microsoft SQL Server Reporting Services by Brian Larson, an earlier edition of the book you now hold.

A decade later, my fascination with Reporting Services had carried me into a role at Microsoft guiding the direction of that very product. Brian is uniquely qualified to write about Reporting Services, having worked with the product since its introduction over a decade ago, and even collaborated with Microsoft to contribute code to its first version.

This book, now in its fifth edition, is a worthy successor, a remarkably comprehensive guide to a powerful product with so much to cover. The remainder of the book stays true to the tutorial style of previous editions, expertly guiding you step by step through everything from installing Reporting Services to xvii FM.

Be that as it may, it is possible to identify the first step in this whole process. A coworker of mine at Superior Consulting Services, Marty Voegele, was between assignments, on the bench, in consultant-speak. Marty was bored, so he decided to take matters into his own hands. He made a few phone calls, and before long, Marty was again consulting to Microsoft, this time creating something called Rosetta.

As work was added, I had the opportunity to take on part of this assignment as well. It was both challenging and exciting working on code that you knew would be part of a major product from a major software company. What was perhaps most exciting was that Rosetta seemed to be a tool that would fill several needs we had identified while developing custom applications for our own clients.

One of the sales representatives here at Superior Consulting Services, Mike Nelson, decided this would be a nice bit of marketing material to have as we trumpeted our involvement with Reporting Services.

This was where I grabbed the map and compass and decided on the next path. Because the magazine article came out fairly well, I decided to write a book on the topic. Marty informed me that writing a page book would probably make his fingers fall off, so I could take this next step on my own. So, here we are today, one book and four revisions later. All of this is a rather lengthy way of saying that I owe a big thank you to Marty and Mike. Without a shadow of a doubt, this book would not have happened without them.

He supported our efforts on Reporting Services and made it a focus area at Superior Consulting. The assistance, guidance, professionalism, and humor of the editorial staff have made this project much easier. The attention that McGraw-Hill Professional has given this project has been truly overwhelming. Last, but certainly not least, I want to thank my wife, Pam, for all her efforts and understanding. Not only did she agree to my taking personal time to write and revise this book, but she took it upon herself to proofread every page and work through every sample report.

You, as a reader, are greatly benefiting from her efforts. I also want to thank you, the reader, for purchasing this book. My hope is that it will provide you with an informative overview, steady guide, and quick reference as you use Reporting Services.

Best wishes, Brian Larson [email protected] FM. We have already seen a number of advances in the product, from the rearchitecting of the rendering engine to the addition of the tablix and mapping, and now mobile reporting. Never has there been a product with so much potential for sharing business information with such ease of use and at such a reasonable price. Anyone who has ever struggled to find a way to efficiently share database information across an enterprise will see a reason to be delighted with this product.

Now I will admit that I may not be unbiased when expressing this opinion. I did have the opportunity to create a small piece of what has now become Reporting Services. But my excitement goes beyond that. The main reason I get excited about Reporting Services is because I have been a database application developer for 27 years. I have fought with various reporting tools. I have struggled to find a way to efficiently share data between far-flung sales offices and the corporate headquarters.

I have researched enterprise-wide reporting systems and started salivating when I saw the features they offered, only to have my hopes dashed when I looked at the licensing fees. During the past nine years, my colleagues and I at Superior Consulting Services have had the opportunity to incorporate Reporting Services into custom database solutions. We have worked with a number of organizations, helping them get up to speed on the product. We have seen how quickly and easily Reporting Services can improve the data analysis and data distribution capabilities within an enterprise.

At one client, we began implementing Reporting Services on Monday morning. By Wednesday afternoon, reports were being e-mailed around the company. Information was being shared as never before. On Thursday morning, the president of the company emerged from his office to see what all the hoopla was about.

He seemed to be taking a long time going over the layout, so I assumed we did not have things quite right. This book is designed to help you and your organization achieve those same results. One note about the structure of the book: this book is meant to be a hands-on process. You should never be far from your Reporting Services development installation as you read through these chapters. The book is based on the philosophy that people understand more and remember longer when the learning takes place in an interactive environment.

Consequently, the majority of the book is based on business needs and the reports, code, and configurations you will create to fulfill those needs. The book is dedicated to offering examples demonstrating complete solutions. I have tried to stay away from code snippets as much as possible. Nothing is worse than seeing five lines of code and knowing they are exactly the solution you need, but being unable to implement them because you do not know what code is supposed to come before or after those five lines to make the whole thing work.

I have also tried to have a little fun in the book when appropriate. That is why the business scenarios are based on Galactic Delivery Services GDS , an interplanetary package delivery service.

You might call it the delivery service to the stars. Although GDS is a bit fanciful with its antimatter transports and robotic employees, the business needs discussed will ring true for most organizations.

I hope you find this book a worthwhile tool for getting up to speed on this exciting product. I hope you get a chuckle or two from its GDS examples. Most of all, I hope the book enables you to unlock the potential of Reporting Services for your organization. The Galactic Database and Other Supporting Materials All of the samples in this book are based on business scenarios for a fictional company called Galactic Delivery Services.

The data for these examples comes from the Galactic database. This download also includes the complete source code for all of the reports and. NET code seen in the book. Follow the instructions in the Zip file to install the Galactic database and the other sample code as needed. Some of these additions bring incremental growth to existing capabilities.

Some answer a desire voiced by report authors for many years. Some add whole new areas of functionality to the product. These are covered in Chapter 6.

Although this new feature is very straightforward, it got some of the biggest cheers when unveiled at the user conferences. We work through an example in Chapter 9. Chapter 10 is dedicated to mobile reports. It incorporates a more modern look and feel along with responsive web pages to work well on any size device. The web portal is covered in Chapter This means any modern browser can be used to interact with the web portal and to view reports. Although HTML5 compliance is not specifically discussed in detail, the web portal and report rendering in the browser are covered in Chapter Instead, when you print a report from the web portal, the report is rendered as a PDF document and the PDF viewer printing capabilities are utilized.

This provides much better compatibility across platforms. This is discussed in Chapter This is demonstrated in Chapter You can now output a report as a PowerPoint document. This is covered in Chapters 11 and This branding includes adding a custom logo to the web portal and customization of the web portal color scheme. Web portal branding is seen in Chapter These visualizations are updated on a scheduled basis so they remain current.

This pinning process is discussed in Chapter In an interconnected workplace, it makes sense that your reporting solution should offer companywide, nationwide, and even worldwide communication. Where almost every other aspect of the SQL Server release represented a completely new platform, Reporting Services simply added to the solid foundation provided by the earlier version to make a great product even better. The release provided an additional report-authoring environment, improved report-development features, and enhanced capabilities for distributing reports.

The report processing and rendering engine was completely rewritten. This allowed Reporting Services to be more robust, especially when rendering large reports. This overhaul also allowed for major changes to the report definition structure and a simplification of the inner workings of the report-rendering engine. Databars, sparklines, indicators, and maps were added to the array of tools available for presenting information on reports in a graphical format.

Individual report items could be saved in a report part gallery for reuse by other report authors. All that brings us to the latest release of Reporting Services, delivered as part of SQL Server —a release that, once again, brings exciting new features and architectural changes to the product.

Reporting Services was code-named Rosetta during its original development at Microsoft. This name comes from the Rosetta Stone, a stone slab found in that contains an inscription in both Egyptian hieroglyphics and Greek. This stone provided the key piece of information necessary to unlock the mystery of Egyptian hieroglyphics for the modern world.

Just as the Rosetta Stone brought key information across 1, years of history, Rosetta, or Reporting Services, is designed to bring key information across distances to unlock the mystery of success for your business. The Rosetta project, which I was privileged to play a very, very small part in, was originally conceived as a feature of SQL Server Reporting Services provides an environment for creating a number of different types of reports from a number of different data sources.

The reports are previewed and refined using one of several authoring tools. Once completed, the reports are deployed to a report server, which makes the reports available via the Internet in a structured, secure environment. Last, but not least, the report management and distribution portion of Reporting Services is free of charge when installed on a server already running SQL Server.

Why did this set of features generate so much excitement? When you put them all together, the result is a product that facilitates the creation, management, and timely use of business intelligence.

Sharing Business Intelligence Because you are reading this book, you are probably the keeper of some type of information that is important to your organization. You may have information on sales, finance, production, delivery, or one of a hundred other areas. The Need to Share In addition to maintaining this information, you have a need to share this information with others. See if any of these situations sound familiar.

Because of this, it is important that the production manager knows about these changes in the inventory level in a timely manner so he can adjust production accordingly. The production manager has asked you to provide him with an up-to-date inventory report. He would like this report to be available on his PC. He would also like to be able to view the report on his tablet or on his smartphone so he can get the latest information wherever he happens to be. This information is updated daily in the company database.

A report containing the credit information for all clients is printed weekly at corporate headquarters and mailed to each sales representative. The vice president of sales has requested that the credit information be made available to the sales staff in a timelier manner. He has asked that this report be accessible over the Internet from anywhere across the country.

The sales representatives will print the report when they have access to the Internet and then carry it with them for those times when they cannot get online. He has also asked that this online version of the report be as up to date as possible. She likes to participate in all facets of the decision-making process and, therefore, needs to stay well informed on all aspects of the company.

She wants this information presented on screen as key performance indicators, as well as available in a more detailed format for analysis. The CEO expects all this information to be available on her PC when she arrives for work each morning at a.

As you search for solutions to this one, remember no budget is allocated for this project—and, of course, your job is on the line. Possible Solutions These situations, and a thousand others just like them, confront businesses each day.

In our world of massive connectivity, these types of requests are not unreasonable; even if that is the case, it does not mean these requests are easy to fulfill. You could use one of a number of tools for creating data-driven HTML pages.

However, they all take time and a certain level of programming knowledge. With deadlines looming, you may not have the time to create custom web applications to solve each of these problems. If you are used to manipulating data with Crystal Reports or other reporting environments, you may not be ready to jump into full-blown application development, and you may not have a desire to do so at any time in the near future.

Even if you did create an application for each of these scenarios, one important requirement of the vice president of sales is this: the information must be printable.

HTML screens can look great in a browser window, but they can cause problems when printed. The content can be too wide to fit on the page, and there can be problems with page breaks. These types of formatting issues could make the output difficult for the sales representatives and the production manager to read.

You can control where the page breaks occur and make sure everything looks great. However, several issues need to be overcome with PDF files. First of all, you need some type of utility to produce output in a PDF format.

Once this has been obtained, a document must be created that contains the desired database information. This is usually a report created with a reporting tool or development software. After this document is created, it is converted into a PDF document using an export function or a special printer driver.

Once the PDF document has been created, it can be copied to a website or online file repository for access through the Internet. However, as soon as the PDF document is created, it becomes a static entity. It does not requery the database each time it is requested from the website. To remain up to date, the PDF document must be re-created each time the source data is changed. Perhaps there is a better way. A Third-Party Reporting Environment Reporting environments from other companies certainly overcome the limitations of our first two options.

These third-party products allow reports to be built without requiring large amounts of programming. They can also dynamically generate output in a format such as Adobe PDF that will perform well onscreen and in print. Some products can run into tens or hundreds of thousands of dollars.

This can be enough to break the budget—if indeed there is a budget—for reporting projects such as the ones discussed previously. Microsoft Reporting Services Now you can begin to see why companies get so excited about Reporting Services. It provides an elegant solution for all three of your demanding users—the production manager, the vice president of sales, and the chief executive officer.

Reporting Services does not have the drawbacks—cost, complexity, lack of flexibility—inherent in the possible solutions considered previously. No Programming Required Reporting Services provides a simple drag-and-drop approach to creating reports from database information. You can use a number of different tools to author reports. You do not need to be a programmer to create Reporting Services reports.

Note, however, these expressions are not necessary to create useful reports. They are also simple enough that even those who are totally new to Visual Basic. NET can master them with ease. There is no ActiveX control to install, no Java applet to download. Any browser that supports HTML 5 can view reports. Plays Well with Printers In addition to presenting reports in your browser using dynamic HTML, Reporting Services can render a report in a number of additional formats. All these formats look great onscreen when they are viewed, or on paper when they are printed.

Even when being output in the PDF or TIFF format for printing, a report can be configured to requery the database every time it is accessed. This ensures the report is always up to date. Special Delivery Reporting Services provides several different ways to deliver reports to end users. Using either the Reporting Services web portal or SharePoint, users can access reports via an intranet or the Internet.

Reporting Services also includes security features, which ensure that users access only the reports they should. Users can also subscribe to reports they would like to receive on a regular basis. Reporting Services will store a copy of the rendered report in a Windows file share or SharePoint document library, or send out a copy of the report as an e-mail attachment to each subscriber on a regularly scheduled basis.

Alternatively, a Reporting Services administrator can send out a copy of the report as an e-mail attachment to a number of recipients on a mailing list. NET applications. As long as the report server is installed on the same ch With this single server architecture, it will not cost you one additional penny to share your reports with others using Reporting Services. More about that as each of those features is discussed. How can you use the features of Reporting Services to fulfill the requests made by each of them?

The production manager wants a report showing the current inventory. It is certainly not a problem to query the inventory data from the database and put it into a report. He can request a report with up-to-the-second data wherever he is.

The solution for the vice president of sales is even more straightforward. He wants a report with credit information for each client. No problem there.

Next, he wants the report available to his sales staff, accessible via the Internet. To achieve this, you can publish the report on the Reporting Services web portal. You can even set up security so only sales representatives with the appropriate user name and password can access the report. In addition, the vice president of sales wants the report to look good when printed. This is achieved with no additional work on the development side. When the sales representatives retrieve the report from the website, it is displayed as HTML.

The sales representatives can then print the report using the print feature available within Reporting Services. Now they are ready to go knocking on doors! For the CEO, you can build KPIs in the web portal along with a report or, perhaps a series of reports, that reflects the state of her company. This will serve to keep her informed on all facets of her business.

When she wants to present this information to the corporate vice presidents, she exports a report to a PowerPoint presentation. The best part is that because you already have a SQL Server license, the Reporting Services solution costs the company nothing. You have earned a number of bonus points with the big boss, and she will make you the chief information officer before the end of the year! Report Authoring Architecture When it comes to sharing information in this day and age, we need to think beyond the printed report.

As demonstrated by our earlier user scenarios, data visualizations and analysis can be delivered on a number of platforms. We want to have the right tool ch To that end, Microsoft has created a number of reporting and data visualization tools.

In fact, as was already noted, there are a number of different tools under the SQL Server reporting umbrella. These are also the reports that are required to look good when printed. Although it might be tempting to think we can take paginated reports and just shrink them down or rearrange them to display properly in the smaller footprint of a tablet or phone, Microsoft begs to differ. They believe the most effective mobile reports are built from the ground up with mobile in mind.

The interactive reporting environment focuses on allowing the user to continuously interact with the data by slicing, filtering, and drilling down. This interactivity is provided at the expense of exact control over layout and format. Although Reporting Services supports filtering and drilldown, it does not focus on these aspects of data interaction in the same way that Power BI does.

Pivot tables and pivot charts provide robust reporting right within an Excel spreadsheet. This book will examine two of the four report types, paginated reports and mobile reports, and the tools used to create them. Paginated reports are created using either the Report Builder or the Report Designer. The Report Builder supports the ch It features a user interface similar to that of Microsoft Word or Microsoft Excel , so it should be familiar for users comfortable with those products.

This book can help you get the most from the incredibly rich report-authoring features available in Report Builder, the Report Designer, and the Mobile Publisher. These environments contain everything necessary to create a wide variety of reports. Everything you need to select information from data sources, create a report layout, and test your creation is right at your fingertips.

Report Structure for Reporting Services Paginated Reports Each Reporting Services paginated report contains two distinct sets of instructions that determine what the report will contain. The first is the data definition. The data definition controls where the data for the report will come from and what information will be selected from that data.

The second set of instructions is the report layout. The report layout controls how the information will be presented on the screen or on paper. Figure shows this paginated report structure in a little more detail. The data source is the database server or data file that provides the information for your report. Of course, the data source itself is not included in the report.

What is included is the set of instructions the report needs to gain access to that data source. Reporting Services will use this information to determine how to communicate with the data source. When the report is executing, it uses the data source instructions contained in the report to gain access to the data source.

It then extracts information from the data source into a new format that can be used by the report. This new format is called a dataset. The content of the dataset is defined using a tool called the Query Designer. The Query Designer helps you build a database query. The query provides instructions to the data source, telling it what data you want selected for your report.

The data selected by the query into the dataset consists of rows and columns. The rows correspond to the records the query selects from the data source. The columns correspond to the fields the query selects from the data source. MDX queries are flattened into a table of rows and columns.

Information on the fields to be selected into the dataset is stored as part of the definition of the dataset. Only the query and the information on what the resulting columns will be called and the type of data they will hold is stored in the definition of the dataset. The actual data is not stored in the definition of the dataset, but instead is selected from the data source when the report is run. Paginated Report Layout The data that the report has extracted into a dataset is not of much use to you unless you have some way of presenting it to the user.

You need to specify which fields go in ch You also need to add things such as titles, headings, and page numbers. All of this forms the paginated report layout. In most cases, your report layout will include a special area that interacts with the dataset.

This special area is known as a data region. A data region displays all the rows in the dataset by repeating a section of the report layout for each row. This includes the data source instructions, the query information that defines the dataset, and the report layout.

When you create a report in the Report Designer, it is saved in a file with an. Report Designer Figure shows the Report Designer. This is one of the tools you can use for creating and editing reports throughout this book. We will look at some features of the Report Designer now and discuss them in more detail in Chapters 5 through 9. Design Surface The design surface, in the center of Figure , is where you create your report layout.