Professional microsoft sql server 2012 reporting services pdf


Request PDF on ResearchGate | Pro SQL Server Reporting Services | Pro SQL Server Reporting Services is Microsoft's enterprise-level reporting platform. What's new in Microsoft SQL Server Reporting Services 3. ChapteR 2 run Reporting Services in integrated mode, replace the instructions we pro- You should also export the report as a PDF file to check the rendering more. SQL Server. Integration Services. (SSIS) – Step by Step. Tutorial. A Free SSIS eBook from SQ Microsoft® SQL Server® Bible. 1, Pages··

Language:English, Spanish, Portuguese
Published (Last):24.08.2016
Distribution:Free* [*Register to download]
Uploaded by: OCIE

48219 downloads 123081 Views 32.44MB PDF Size Report

Professional Microsoft Sql Server 2012 Reporting Services Pdf

Microsoft SQL Server Reporting Services - Pearsoncmg Recipes for Designing Expert Reports, Professional SQL Server Reporting Services. Microsoft® SQL Server® Reporting. Services is a hosted service, Reporting Services will help you look and feel—enabling IT professionals and power. Summary: This book contains tutorials for SQL Server Reporting Services: Create a. Data-Driven Subscription, Create a Basic Table Report, Create a.

You'll learn effective report solution design based upon many years of experience with successful report solutions. Improve your own reports with advanced, best-practice design, usability, query design, and filtering techniques. Expert guidance provides insight into common report types and explains where each could be made more efficient, while providing step-by step instruction on Microsoft SQL Server New functionality, new capabilities, re-tooled processes, and changing support require a considerable update to existing knowledge. Whether you're starting from scratch or simply upgrading, this book is an essential guide to report design and business intelligence solutions. Understand BI fundamentals and Reporting Services architecture Learn the ingredients to a successful report design Get up to speed on Microsoft SQL Server Grasp the purpose behind common designs to optimize your reporting Microsoft SQL Server Reporting Services makes reporting faster, easier, and more powerful than ever in web, desktop and portal solutions. Compatibility with an extensive variety of data sources makes it a go-to solution for organizations across the globe.

Report snapshots serve three purposes: Report history: By creating a series of report snapshots, you can build a history of a report that shows how data changes over time. Consistency: Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.

Performance: By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours. What is Cached Report? Ans:A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports.

They have a mandatory expiration period, usually in minutes. Download Now! What are Click through Reports? Ans:A click through report is a report that displays related data from a report model when you click the interactive data contained within your model-based report.

Microsoft SQL Server 2012 Reporting Services - Pearsoncmg

These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a click through report is opened. These field settings cannot be changed in the report authoring tools.

Click through reports are auto-generated. However, you can create an alternative customized report to the model for interactive data items that is displayed instead.

The custom report is a standard Reporting Services report. What are Drilldown Reports? Ans:Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report. For reports with large amounts of data, consider drill through reports instead.

What are Drillthrough Reports? Ans:Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart.

The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report.

Drillthrough reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from subreports in that the report does not display within the original report, but opens separately. They differ from clickthrough reports in that they are not autogenerated from the data source, but are instead custom reports that are saved on the report server.

They differ from drilldown reports in that they retrieve the report data only for the specified parameters or for the dataset query. What is Subreport? Ans:A subreport is a report that displays another report inside the body of a main report.

Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report.

You can set up the parent report to pass parameters to the subreport. Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports. For reports with many instances of subreports, consider using drillthrough reports instead.

What is Data Set in report? 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. 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.

[PDF] Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports [Read]

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. 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.

Pro SQL Server Reporting Services | Brian McDonald | Apress

This effectively proves that. NET is a powerful arsenal of programming tools. However, it also makes the point that we have lacked a strong reporting solution to round out Microsoft's front-line development and database suite.

When Visual Studio. NET was released in , I was a little disappointed because the only integrated reporting component was a limited-use version of Crystal Reports. Now, before I get myself into too much trouble with folks who may be loyal to this product, I'll say that Crystal Reports is a capable reporting tool. However, it's neither a part of Microsoft's strategic direction nor does it behave like, or integrate tightly with other Microsoft products.

The version of Crystal Reports that installs with Visual 6 Getting Started with Reporting Services Studio is limited to five concurrent users and the term concurrent is subject to some serious interpretation.

Professional SQL Server Reporting Services

Now that Crystal Reports has changed hands once again recently acquired by Business Objects , it will be interesting to see how this affects the direction of this well-known product. Notably, the most remarkable change in the industry over the past few years has been the opportunity and need to exchange information over the Internet.

Previous technologies simply don't provide the means to access application components across the Internet. Connecting business trading partners and even regional sites was often cost prohibitive and logistically infeasible. Few options existed for reporting over the web. At best, a list or table filled with data could be viewed in custom-built, server-side web page solutions using ASP or CGI.

Each page had to be carefully designed and scripted at the cost of dozens, or sometimes hundreds of programming hours. With the recent maturity of the web, a new generation of mobile devices is evolving that can connect users to company resources, email, documents, and databases.

These laptop, hand-held, palm-top, and wrist-worn devices open new doors of opportunity and present new challenges for data presentation.

Perhaps, it will soon be common for people to stagger around the streets, talking to themselves and staring blindly into space in a zombie-like trance as they are connected to the world through webenabled cerebral implants! We can only hope! To gain access to useful and readable information, data must be accessible over available communication channels such as corporate networks and the Internet , easy to access, secure, and available in a variety or formats so that it may be viewed using available document readers or browsers — all compatible with different devices.

Did I mention the need to support different Operating Systems OS , applications, and perhaps, without the installation of any custom software on the client device?

This is the challenge. SQL Server Reporting Services is a server-side reporting solution that meets all of these requirements and more. It can obtain its data from a variety of data sources that you can access using modern programming tools. That data may be grouped, sorted, aggregated, and presented in dynamic and meaningful ways. The structure of the data and the presentation elements may be transmitted across practically any communication medium, using an industry standard format, to just about any type of client or server computer or device.

The resulting content may then be displayed in many standard formats using browsers and document readers. Further, the data itself may be consumed by standard and custom applications to be further parsed, imported, manipulated, and consumed. It's a truly remarkable innovation with incredible possibilities. Since Reporting Services is based on.

NET, it offers the advantage of integrating tightly with the Windows platform and benefits from the performance, scalability, and security inherent to the. NET Framework. When used in concert with BackOffice products like Share Point Portal, it can provide a comprehensive enterprise solution with little programming effort.

Reporting Services can be used with ASP. NET and other. NET programming tools to produce highly customized, special-purpose solutions. Reports may be rendered in program code or they may be accessed through a simple web address — like any other web page. Reports may be rendered in several formats. These include different flavors of HTML to provide compatibility with different browsers and devices, the Adobe Acrobat Portable Document Format PDF for uniform presentation and printing, as a graphic file, and in Microsoft Excel so users can slice, dice, pivot, and re-analyze the data.

Business Intelligence Solutions Traditionally, BI solutions have been very costly and only accessible to large businesses that could afford them. However, they require costly deployment, training, and maintenance. By contrast, this is the part I like the best Reporting Services is available at no additional cost if you install it on a computer with a licensed instance of SQL Server. In a single server installation, you don't need an additional license and you can use it royalty free — so long as your database, and server products are appropriately licensed.

For additional information regarding licensing and deployment options, please refer to Chapter Comparatively speaking, collecting data is the easy part.

Most companies have been doing this for decades, but how they utilize all of this data is often another story. There is no doubt that effectively collecting data may not be so easy but it's something businesses have been doing for quite some time.

Most companies have untold mega-, giga-, or even peta-bytes of "important" archived data residing in documents, spreadsheets, and various databases on backup tapes, disks and folders throughout their enterprise — with no hope of fully utilizing and gaining significant value from it all.

It's about measuring performance, discovering patterns and trends; and measurable forecasting through statistical analysis. It ties together applications, documents, and data sources in a manner that lets people collaborate and communicate effectively. BI systems are no longer a luxury but a necessity in many business environments.

Today, having access to timely information can make the difference between having a competitive edge and being left in the dust behind competitors. Who Uses Reports and Why? In almost any organization, there is a universal condition that people in different roles and at different levels have different perspectives on information.

This is typically most apparent in large corporations, where executive leaders who make financial and market-direction decisions have less exposure to the daily processes of the company than the line-level workers.

Ask any executive and they will tell you that 8 Getting Started with Reporting Services the line-level worker doesn't have a broad perspective regarding the challenges and direction of the organization at a high level. Conversely, ask most of the line-level workers in the organization and they will tell you that the upper management and executives don't share their perspective of "real problems" and the daily pulse of the company.

To a point, this is the natural condition of a healthy organization. Bill Gates has spoken extensively about the information worker of the twentieth century. At all levels within an organization, people who have convenient access to accurate and appropriate information are empowered to take informed decisions that benefit the organization and the individual.

This is rapidly becoming the case throughout many industries today and continues to change the way people work and are managed. Although this paradigm shift may be occurring for many people, organizations often struggle to provide the resources necessary to support workers who are eager to use information to make a difference in their environments.

Executive Leadership Leaders simply must make informed decisions. They must fully understand their business environment and the competitive climate in which they operate. Access to market conditions, customer needs, and financial information can often make the difference between decisions that produce success or jeopardize the organization.

Decision support systems provide interfaces for executive leadership through dashboards called Executive Information Services EIS. Reporting Services installs them with a simple web interface and enhances integration with executive consoles through SharePoint Portal services and third party solution integration. Managers Inefficient business processes can no longer remain the status quo. Customers demand results and simply will not tolerate services or products that don't meet their expectations.

Customers have choices and will quickly switch to a competitor if their needs are not met.

Managers need the information necessary to drive customer satisfaction and make corrections, directing business processes and the effective use of people and other important resources. Information Workers In businesses today, workers are educated and given more freedom to solve problems and effect change. This category could be applied to workers at various levels within an organization, including the managers and higher level workers. Often, the customer service representative or service provider will be the only human interface a customer has with an organization.

That person must be empowered to collect and retrieve information quickly and accurately. They must also be empowered to make corrections to - and to work with, not against - unyielding business processes. In the past, workers simply had to accept the way information was presented to them, as well as the inefficiencies of most automated systems.

With greater demands on businesses, workers simply must have the means to acquire accurate and concise information that meets their needs — in order to work efficiently.

Customers who can get the information, services and assistance they need may not demand that someone help them when it's not warranted. By making regular services available through customer-friendly automation and information portals, you can afford to offer assistance to customers who really need special attention. Customers often need to look up account and transaction histories, order status, and shipping information. Making these services available through a web browser, email, or a mobile device can provide a greater degree of customer satisfaction.

Vendors and Partners Like customers, business vendors may need to interface with an organization to place orders, schedule service calls, and obtain status information. Making this information available in the most appropriate form will improve efficiency and ultimately business-vendor partnerships. Business vendors are often more accepting of special procedures and automated systems. Vendors can be trained to use more sophisticated systems to obtain product information, service orders, invoices, and other business-related information.

Systems may be designed to interface and automate the download or exchange of information that enable a partnering business to work cooperatively.

Reporting with Relational Data Transactional databases are designed to capture and manage real data as it is generated; for example, as products are purchased and as services are rendered. 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.

This helps preserve the integrity and accuracy of data at the detail level, but it presents challenges for deriving useful information from a large volume of transactional data. In order to obtain information with meaningful context, tables must be joined and values must be aggregated. Although relational database systems may support complex queries, reporting against these queries routinely could prove to be slow and inefficient.

Reporting for Decision Support Optimized data storage systems are for analysis and don't use normalized tables, and don't contain details at the transactional level. Tables typically have more columns and fewer rows and often contain descriptive values that would otherwise exist only in lookup tables. The purpose of a decision-support data store is to drive meaningful reports and analysis tools with a sampling of read-only, historical, data and not for keeping up-to-the minute details.

Data Warehouses The usual approach for maintaining a decision-support system is to copy only necessary data from relational, transaction-support databases to a separate data store at regular intervals. Depending on an organization's reporting needs, this data dump or import is performed daily, weekly, or even monthly.

Surrogate key values and codes have little use since the transformed data is readable in a more concise form. Data warehouses and smaller subsets of data analysis called data silos , can simply be implemented as relational data stores that have been designed for analysis.

There can also be special purpose data structures that store data in hierarchal or multi-dimensional structures. These specialized data storage structures are optimized for performing pivots and extensive calculations and aggregations against a large volume of decision-support data.

A data warehouse is typically a large, central store of decision-support data whereas smaller, more specialized data marts, effectively divide analysis data into business unit and divisional data warehouses. Data warehouse systems often use special query expressions that have capabilities beyond that of SQL. The Multidimensional Expressions MDX language supports pivoting and slicing data cubes to derive informational facts for comparative analysis.

Eventually most businesses that generate reports from live data will experience a common anomaly. As the data in a transactional database is ever growing and changing, reports will reflect these subtle changes and show up on the bottom line. Different users may produce similar reports in a short period of relative time and will notice that totals and summary values slightly vary. With no other explanation, users question the accuracy of report data and assume that there is a problem with their data.

Believe it or not, the technical term for this condition is known as "twinkling data". As this phrase suggests, the totals and data points aren't typically way out of line. They just seem to fluctuate slightly. Reporting Services helps this situation by "freezing" report data through caching and report snapshots. You might not perceive this to be a problem but consider what might happen if a user had made data entry mistakes and in an effort to correct the errors, entered duplicate, corrected data and then deleted the old records.

Or, they deleted the erroneous records and then re-entered corrected information. If a report were produced at the wrong time, summary values could be skewed significantly. Statistically speaking, the chances of this condition causing a crisis may not be significant, but over time, they may increase.

Data analysis should therefore be performed on unchanging values that are updated at regular, predictable intervals. This problem is often addressed by building separate OLAP data stores used only for analysis and reporting against snapshot data imported from transactional databases at regular intervals.

The Reporting Lifecycle Chapter 2 will discuss the reporting lifecycle in greater detail with the architecture that supports this process. Briefly, creating a functional reporting solution requires an understanding of user and business requirements. Existing data sources must be considered and new data stores must be designed to meet reporting needs.

This interface is used to create data sources, queries and datasets, and the report definition. Reports can be delivered in the form of a web page, document, file, or even via email. Report Delivery Application Types In the past, reporting solutions were typically delivered through a desktop application of some kind. Data was queried in real time, and of course the application had to be connected to the data source.

Users also had limited opportunity to save reports for later viewing and usually printed them on paper. Now we have many opportunities to view and interact with reports in environments where it may not be possible or feasible to connect to data stores.

Reports may also be presented in different forms that offer multiple capabilities and compatibility with various devices and software. Web Browser Web browser-based solutions have become popular for a number of reasons. User accessibility takes on a whole new definition when special software isn't required on the client computer.

Of course, a web browser makes information available for viewing over the World Wide Web, but browser-based solutions are also a compelling means to deliver information in a controlled business enterprise environment.

Whether users access resources within their corporate intranet environment or over the web, the browser paradigm has significantly changed the approach to application delivery.

Some of the traditional challenges with browser-solutions are the lack of consistent support for clientside script and components. These issues have largely been resolved with server-side rendering mechanisms that output product-independent HTML content. For viewing offline content, HTML documents require links to external files, such as images, sounds, and video. Although not supported in all browsers, this format is a viable means to deliver extensible report content for live and off-line viewing.

HTML 4. Office Applications Microsoft Office brings together a tremendous assortment of capabilities to assist report users at all levels. Microsoft Excel has been the mainstay tool for data collection and analysis. By rendering a report into Excel, the data may easily be reformatted, modified, or analyzed using formulas and calculations. This capability has been around for several years but it required writing custom code to use the Excel object model from Access or Visual Basic to produce report data in Excel; in addition, this process was tedious at best.

Now, pushing complex report data into a useful and well-formatted Excel document is simple. Data tracking and management solutions can be created with minimal cost and effort. Access and Excel both provide the Office Web Components that may be used to view pivot tables and charts. These components duplicate the functionality of the Matrix and Report Services chart items but might give users a more convenient option for analyzing data.

Programmability The possibilities for incorporating report features in your own applications are impressive. All of the features of the Report Manager can be duplicated in many cases and can be extended through program code. Reports may be viewed in place within an application by using an external web browser window, integrated browser control, or a custom report viewer component.

Report content may be rendered to a file for persistent storage to directly into a viewer or browser. Subscriptions Subscriptions allow users to receive or gain access to reports on a regular schedule.

Reports are delivered by email or saved to files where they may be viewed offline at the users' convenience. Report subscriptions may be setup for an individual user or large groups of users using data-driven subscriptions.