Microsoft Reporting Services is the component of Microsoft SQL Server that . Optional: Microsoft Visual Studio Standard or Professional with either the online reports into a single report for the purpose of producing a PDF. With Microsoft SQL Server Reporting Services (referred to as SSRS .. Portable Document Format (PDF): Format used to produce print-ready reports. Pro. SQL Server Reporting Services. Rodney Landrum, Shawn Format (PDF): Format used to produce print-ready reports using.

Pro Sql Server 2008 Reporting Services Pdf

Language:English, Dutch, Hindi
Published (Last):14.05.2015
ePub File Size:28.34 MB
PDF File Size:14.39 MB
Distribution:Free* [*Sign up for free]
Uploaded by: TAMMIE

When designing Microsoft SQL Server Reporting Services, I started you save this report to a PDF file and then choose the customer sales. Topic: SQL Server. Professional Microsoft SQL Server Reporting Services ( ) cover image Table of Contents (PDF) · Index (PDF). Source code for 'Pro SQL Server Reporting Services' by Rodney Landrum, Walter Voytek, and Shawn McGehee

SalesAmount , FIS. The completed report, with some basic formatting, should look as shown below. One of those features was "On-Demand" report processing, which is a very nice performance improvement especially for large reports. With this feature, when the report is viewed in Report Manager, SSRS displays estimated number of pages with a question mark "?

Often times users mistake this to be a bug, but this is actually the result of a feature.

And hence, there are requests to display the total number of pages and remove the question mark. Let's see how we can achieve this. To address this issue, we need to use the "TotalPages" Built-in Field either in the report header or footer. Con: When viewed in the browser the report would not look like it does when printed. The preview process would likely consume very large resources resulting in poor performance and is likely to end up with out-of-memory exception.

Combining low resolution and high resolution images in one report Pro: Putting both low resolution and high resolution images into one report covers most of the typical scenarios. Con: This approach requires additional effort when designing individual reports and additional reusable development.

High and Low Resolution Solution Approach This solution uses two images of different resolution in a single report, a 96 dpi image and high resolution images, dpi in this case. The out-of-the-box ReportViewer functionality was also customized so that the correctly processed image is chosen in different scenarios, such as browsing and printing. The challenge of poor print quality with a low resolution image see challenge A above was solved by using two different versions of images for each page in two different resolutions and by implementing a special logic to switch between these images during the browsing and printing.

The second challenge with properly viewing the report on screen at high resolution image see challenge B above was solved by not using high resolution images for browsing and print preview.

We customized the preview by displaying low resolution image in WinForms report viewer and by using PDF preview when the printing is activated from a web page. This allows us to significantly reduce the impact on performance and the memory pressure. The third challenge with the HTML renderer not supporting item overlap see challenge C above can be eliminated by not using HTML renderer and instead using the built-in JPEG renderer to render a jpeg image of a single page of the report at a time and building a custom report viewer to navigate between individual pages of the report.

Thus, we created the two customized ReportViewers: one for Windows applications based on WinForms ReportViewer control, the other one for web scenarios that is based on a custom aspx page that leverages SSRS functionality through web requests and also internally uses WebForm ReportViewer control. Each time the report is rendered on screen, 96 dpi images are used while when the report in printed or exported the high resolution images are used.

Since the report code itself cannot determine in which context it is used, we added a boolean parameter named ForPrint to the report. The parameter is set by our custom code to False when the report is viewed on screen and True when it is printed or exported. Our solution is depicted on Figure 1 for Windows applications and on Figure 2 for web scenarios. Figure 1 Solution Overview for Windows applications Figure 2 Solution Overview for web scenarios Implementation Notes As mentioned, one of the challenges challenge C above that we solved was related to image distortion in web browser: the default SSRS HTML renderer slightly moved the designed positions of textbox and checkmark controls relative to the background image.

Coordinates of these cells are rounded by various browsers in a different way for instance Internet Explorer 7 appears to be rounding it up to a next integer , and with each next row and column the displacement increases. Different browsers add different kinds of distortion to the final image, but none of them is able to produce a pixel perfect representation of the report. A possible solution would be to develop a custom HTML renderer see this MSDN article for more information on custom renderers that would render reporting items that are typically used by fixed layout reports, such as text boxes, images, tablix etc.

A custom renderer was out of the scope of our sample and we decided to use a simpler solution: a custom aspx page that could be used instead of the default ReportViewer.

For browsing such reports, this custom page invokes SSRS through web requests by crafting appropriate urls to get individual pages of the report in JPEG format rendered using images in 96 dpi. For navigating between pages of the report and for invoking printing and exporting, we added custom buttons at the top of the page.

The printing is based on behind-the-scenes export to PDF so it is the generated PDF document that is actually sent to the printer. Our solution focused on addressing the fundamental issues with generating reports that overlay data on form images and we demonstrated the viability of this approach. Some individual reports may require additional custom code. For instance some forms have fields that span a few rows and the length on each row can be different, which make it difficult to handle by standard reporting items.

Similarly, some forms have certain fields, such as a SSN composed of individual cells. These additional requirements could be addressed by implementing Custom Report Items with the design-time behavior that would allow defining exact dimension of individual lines and exact location of individual characters for strings "splitted" into cells, and the runtime behavior that would take into the account the dimensions and "spacing" defined in the design-time.

More information about Custom Report Items can be found here, but we decided that implementing them is out of scope of this work.

Implementation Details Our solution consists of 3 parts, each represented by a Visual Studio project: A sample multi-page fixed layout report that overlay data on form images A sample generic solution for browsing, printing and exporting this kind of reports from Windows applications A sample generic web based solution for browsing, printing and exporting this kind of reports Requirements To open and run the samples it is required to have Visual Studio , SQL Server and the correct integration between these two products.

The easiest way to achieve it is to have both products installed locally. Creating a SSRS report that overlay data on form images for the C-2 Form Below are the step-by-step instructions for creating a sample multi-page fixed layout report that overlay data on form images using SQL Server Reporting Services. Creating a single page report can follow the same steps as below.

The complete sample report is available and can be viewed by opening the "Fixed Layout Image Overlay Report.

To make the data source schema and sample data for this report available, open "C2SampleData. I was taken back by a handful of non-developers who complained that they wanted to use Visual Studio to create reports. In Chapter 11 you will see how designing reports isn't restricted to the Visual Studio. NET design environment. There will likely be other design tools for building reports in the market soon.

The fact is that designing reports is easy.


If you have used other report design tools, I'm sure you will agree. One nice thing about using the Visual Studio report designer is that it feels like the other Microsoft products you already know how to use. If you are a Microsoft developer, you'll love it. If you're not a developer, you'll love it when you realize how easy it is to design, deploy, and manage very powerful reporting solutions with it. Agility Imagine that you are sitting in a presentation meeting at the corporate office of a key customer.

You are a senior sales representative for a company that sells high volume data backup systems, and the solution they decide on will be implemented in several regional data centers around the world.

Your team has been preparing for this meeting for months. Your success depends on your ability to demonstrate your competence to the customer and a clear understanding of their needs. Your team has done their homework, and you know the customer has a history of scanning printed medical records and storing them as image files.

Based on this information, you are certain that a particular product will adequately provide the file backup facilities for their moderate volume of image files.

You have made it a point to familiarize yourself with the capabilities of the system that appears to be the best fit. Now they need a backup system that can handle large file capacities. They are prepared to make an investment that is substantially larger than what you had anticipated for a capable backup solution. Your company began to offer a large-scale solution just a couple of weeks ago but you aren't very familiar with its capabilities. You've spent so much time preparing to sell the smaller system that you haven't had time to learn more about this new product.

Your associate is doing introductions, and it will be your turn in about 15 minutes. You login to your company's secure report server, select the product catalog report; choose the product category and then drill-down to the new product. The report has a drill-through option that lets you quickly view a detailed specification report for the new, high-volume backup system.

After noting the pertinent specifications, you save this report to a PDF file and then choose the customer sales inquiry history report. Looking up this customer, you learn that someone named Julie made an inquiry about two months ago regarding video media backups from this very company.

Looking around the room, you find a name card with her name on it. You explore the details of this call, and you find that she had asked if you offer a solution comparable to a very expensive product from a competitor. Checking the competition's web site, you discover that the competing product Julie had mentioned uses older technology, has a smaller capacity than the new system, and it costs considerably more. You save a report with all of the pertinent specifications to your memory card, hand the card to the administrative assistant sitting next to you, and ask that he make printed copies of the PDF file it contains.

Your colleague finishes her presentation and then introduces you. Taking another quick glance at the new product specs, you begin your introduction. You explain that one of your team's greatest strengths is your real experience and understanding of how business can change day-to-day.

In order to be responsive and competitive, it's necessary to adapt to these changes. You show the brochure for the midscale product and explain that this product would be an excellent solution for a company that just scans documents. But for digital video, a more capable solution is required. You share the product specification and qualify the product to your customer's needs.

During your presentation, the administrative assistant returns with the printed specification report. Not missing a beat, you distribute these to everyone and conclude.

Bibliographic Information

Making brief eye contact with your colleague, he raises an eyebrow just before your customer's chief decision maker, Julie, aggressively shakes your hand, and thanks you profusely for your time and effort. The Way We Were In many business applications, reports were an afterthought. This lack of planning often forced developers to build ad-hoc reports with little opportunity for significant planning and design.

Queries became complicated and difficult to support. Reports ran slowly and were prone to errors. To avoid these difficulties, you really need a plan. In a perfect world, you would architect the database and application around your reporting needs, and would completely understand your users' requirements before designing the system. In the real world, you may understand some of the users' needs ahead of time but chances are that new reports will be requested long after the other features are in place.

According to Frederick P. Brooks' The Mythical Man-Month, it's usually a good idea to learn from and throw away your first few attempts at almost any design. I typically try to develop reports in stages 3 Chapter 1 realizing that the first attempt will be a prototype. My experience has been that when you gather the initial requirements, users will ask for a handful of different reports based on some specific criteria.

After the solution is deployed and people begin to use it, others will almost inevitably realize that they too would like reports to help make their jobs as easy as their associates. As users realize what kinds of information they can get, they will find new and exciting ways to sort, filter, group, pivot, and slice and dice their data — in ways they never thought possible.

Professional SQL Server Reporting Services

That is, until you show them the possibilities. That Was Then, This Is Now Static, printed reports may be an acceptable format for a list of products and prices or for a company, but not for the majority of the information people use to make important decisions today. Business decision makers need pertinent information, and they need to view it in a manner that applies to that person's role or responsibility.

Since most users deal with information in a slightly different manner, you can create hundreds of reports, each designed for a specific need. Alternatively, you can create flexible reports that serve a broader range of user needs. For example, a sales summary report could be grouped or filtered by the sales person's region, by customer type, and include information for the week, month, quarter or year, or for a specific product category.

To produce individual reports for each of these needs would be time-consuming and cost prohibitive. Besides, computer users are savvier than they were a few years ago and need to have tools that help them take informed decisions, not just look at the numbers. I recall working at Hewlett-Packard several years ago in a manufacturing site IS group. Every Thursday a report cart would come around. There were several regularly scheduled reports that the mainframe system produced on a weekly and monthly basis.

Users, typically department managers, would subscribe to these reports that were then printed in another building and delivered by hand to each subscriber.

Paginated reports

Many of these reports were little more than a huge list of numbers and text printed on continuous, fan-fed paper — some as large as pages. I watched inquisitively as managers would meticulously scan through the pages, highlighting and circling figures of interest. Some would bind them into large books and give them to their administrative assistants to go through with a ten-key calculator and add up all of the figures they had highlighted. At the end of the month dumpsters full of these reports were hauled off to landfills and recycling centers as their usefulness quickly came to an end.

I spent nearly two years developing a reporting application for this group using Microsoft Access. We originally planned for eight to ten reports in this application. But as time went on, and users began to rely on the reports to perform their jobs, they would ask for the same reports with different sorting, grouping, and selection criteria. In the end, we deployed some reports, most of which were variations on the few original reports.

He says, "I think one of the greatest challenges to providing BI Solutions is to educate the customer as to the extent of the long-range problems and the associated business costs caused by disjointed attempts to derive information from corporate data.

Closely related to that is to correct the normal tendency to apply band aids. Foresight and planning with a BI Strategy is the most effective means of halting the creation of stove-pipe data analysis systems. Once management perceives the benefits and downloads into the process, a 'master plan' strategy can be formulated, that will guide the 4 Getting Started with Reporting Services process of developing the solution.

Integration of existing systems, new tools, or BI Platform migration can then be tackled based on priority and available resources. They reason that good data should lead to good decisions, and good decisions mean good business. This makes sense, right? A very common scenario today is that businesses trying to get that edge will invest in expensive ERP systems that effectively gather and store mountains of customer, product, and sales information.

Mission accomplished? These days, the time between data entry and consumption is very short, almost instant. More effective data-gathering mechanisms result in data silos and data warehouses populated to the gills with all kinds of facts.

The new generation of business workers are informed and empowered to make decisions.

They need tools to get useful information and respond to changes. Having data available is useless unless it has business value and can be used to effectively take informed decisions. A fundamental fact in business is that the people who gather and collect data are often not the people who use that data or need access to the information that the data represents. Business executives, managers, and analysts make strategic decisions everyday that may affect many people, the direction of their organizations, and ultimately, the way people and organizations will go about conducting business in the industry.

These decisions are largely driven by the relative height of a bar displayed in a chart or a few numbers printed on a piece of paper. Having capable reporting tools doesn't necessarily solve this problem. Most businesses don't know how to effectively use the products they own. A reporting tool is of little value if it's complicated and difficult to use. This presents some fundamental challenges such as collecting comprehensive, accurate and meaningful information, storing it in a form so it continues to represent the facts, and presenting the information in a concise and unbiased form.

On the surface, it seems like a simple task. Automation to the Rescue — A Scenario I'll share an example of this kind of challenge. Several years ago, I spent a few months developing a reporting system for the operations group at a paper mill in the Pacific Northwest.

Pro SQL Server 2012 Reporting Services

The old mill is located in a small, remote town and many of the people operating the mill have been working there all of their lives. As is common in the pulp and paper industry, the mill has changed ownership a few times and is currently operated by a very large paper and office supply company.

As time went by and technology changed, several different computer systems were incorporated into the operation of this mill; an IBM and an AS system were used to manage customer orders and production history records. The original inventory management system is still in place. It's a very old, special-purpose computer that stores most of its data in a single, flat text file. All of its components are redundant and it hardly ever needs significant maintenance.

Shortly before I arrived, a Windows server box was installed with a SQL Server database and an application that would replicate production and inventory data from the existing database systems. Management within the parent company believed that they didn't have a handle on the rates of material consumption and product quality. They wanted a reporting system that would give them the figures they needed to make adjustments to their ordering and pulp production processes.

The system would calculate quantities of ingredients to produce a batch — typically to fulfill an order for a customer.

The order would be sent to the production floor where workers had newly installed controls used to assure the accurate delivery of pulp ingredients. Different batches of product continued to be produced with varying degrees of quality and their ability to track the consumption of these materials didn't significantly improve.

Management continued to invest in reporting solutions. They bought and developed software to look for trends and perform statistical analysis but to no avail. After several months and hundreds of thousands of dollars invested, the product quality didn't really improve much. Finally, one of the IT managers put on a hard hat and walked down to the production floor to observe the process. What he learned was a simple lesson: when the orders arrived on their computer workstations, workers were printing the orders and then putting them aside.

They had overridden the automated controls and were using the same manual techniques to make paper that earlier generations had been using for decades.

It was a matter of tradition and pride, and they weren't about to let some computer do their job for them. The initial reporting solution was elegant and technically capable.

The calculations were accurate and the report presentation was appropriate. However, the solution didn't fully support the process. This cultural hurdle was eventually overcome workers were instructed to use the automated systems if they wanted to keep their jobs and the product and process improved. A report is only as good as the data it presents, and the data is only as good as the information used for collection.

The information is only as good as the process that it represents. Challenges of Existing Reporting Solutions For over ten years, Microsoft has offered only one product with substantial reporting capabilities. Designed to run as a single-user or a small workgroup, desktop application, Microsoft Access is a capable database and reporting solution.

In Access , Access Data Projects were added. In Visual Studio 6, an integrated reporting tool was offered for Visual Basic 6 but its capabilities were meager at best. Developers at that time thought this was a glimpse of things to come in subsequent versions of Visual Studio.

Due to the lack of a unified, consistent approach for reporting, many developers have had to revert to creating their own custom solutions. One case in point is the reports starter kit project available on the ASP.

Pro SQL Server 2012 Reporting Services

NET development support site www. The developers did a bang-up job creating a web-based reporting solution using ASP. NET datagrids and datalist controls.

They even made their own pie charts using line drawing objects.Brian was born and raised in Cincinnati, Ohio but he currently lives in Jacksonville, Florida with his beautiful wife and his two little ninja's; Bailey and Kylie.

AR Reconciliation. Relational databases are designed according to the rules of normal form and typically have many tables, each containing fragments of data rather than comprehensive information or business facts.

NET and other. Go back.

LORINDA from Scottsdale
I enjoy reading novels correctly . Feel free to read my other posts. I have always been a very creative person and find it relaxing to indulge in powerbocking.