Paul Turley - Professional Microsoft SQL Server 2012 Reporting Services

Part I

Getting Started

• Chapter 1: Introducing Reporting Services
• Chapter 2: Reporting Services Installation and Architecture
• Chapter 3: Configuring SharePoint Integration

Chapter 1

Introducing Reporting Services

What's in this chapter?

Understanding report designer roles and the tools used to design reports

Understanding dashboards, reports and applications

Examining Business Intelligence solutions

Discovering multidimensional and tabular semantic models

You're holding this book, trying to decide if it will help you solve a problem or teach you essential skills to create reports with Reporting Services. If you and I were having this conversation in person, I'd ask you to tell me what you need. I teach classes and travel to companies to create report and BI solutions, and at the beginning of every class or consulting engagement, I ask what the student or client needs. What are the requirements? What questions does your report need to answer? What's not working? What needs to be fixed, and what will it take to build a solution to help you reach your goals? So, I ask you, what do you need? Why are you reading a book about Reporting Services? Do you have a specific problem to resolve, or do you just need to develop some basic report design skills? Do you need to build an entire reporting solution? Who are the users of these reports? Are they department workers, business managers, or financial analysts? Maybe your user is the CEO of a major corporation or other business executives who need to know if the company is on the right track. Maybe you need to create reports for your own business to make sure it's profitable and achieving its goals. Whether you are creating an invoice to sell arts and crafts out of your garage or a BI dashboard to help manage a multinational corporation, the reports you will create are important. Therefore, you need to make sure they deliver accurate information and are designed correctly, using industry best practices. That's a big responsibility.

Whatever your needs, we'll cover all these bases and address each topic thoroughly. I've enlisted some of my most trusted associates to share their experiences.

This chapter is a high-level introduction to the concepts and capabilities of this powerful reporting tool and the data analysis platform of Microsoft SQL Server 2012. It introduces common reporting scenarios, beginning with the most basic and then moving to the more advanced. In subsequent chapters, you will explore these capabilities in depth and learn how to use them in your own reporting solutions.

SQL Server Reporting Services has grown to become the de facto industry standard reporting tool by which others are measured. It is a foundation upon which you can construct complete report, scorecard, and dashboard solutions for business users. Today, it does everything from simple ad hoc data reporting to delivering enterprise-ready integrated reporting into business portals and custom applications.

Not long ago, the information technology (IT) group for a large financial services company wanted to make sure that they were using the best reporting tool on the market. They decided to hire a consulting company to evaluate every major reporting product and give them an unbiased analysis. I was lucky to land this assignment. We worked with the client to identify about 50 points of evaluation criteria. Then I contacted all the major vendors, installed evaluation copies and explored features, and spoke with other customers and with those who specialized in using these various products. It really helped us see the industry from a broad perspective and was a valuable learning experience. There are some respectable products on the market, and all have their strengths, but I can honestly say that Microsoft has a unique and special platform. As a consultant, contractor to Microsoft, and Microsoft SQL Server MVP, I have had the opportunity to work alongside the Reporting Services product team for many years. They have a vision, and they're passionate about their product. I have a great deal of respect for the fine people who continue to develop and improve Reporting Services, version after version.

Business users fit into a few categories when we consider how they use reports. Some are report consumers only. They're happy to use reports that have been written and published for them. Others prefer to create their own reports using business tools they understand and use for other things, as Excel is used for planning and financial analysis. Maybe they just want to browse information to look for trends and to understand how the business is measuring up against their goals. Still other business users want to use more sophisticated tools to create powerful reports. A typical information technology group at most large organizations has three common roles: system administrators, application developers, and project managers. Usually everyone else in the department supports these roles. Where does the report designer fit in the organization? Good question. Honestly, I don't have a simple answer. The fact is that people who design business reports don't come from a common pool of IT professionals. In fact, many people who spend the majority of their time creating reports are part of the business community and are not your typical hard-core computer geeks.

Microsoft has a long history of building highly technical products that appeal to the technical community. In more recent years, Microsoft has begun to enhance its product culture for more suit-and-tie-wearing folks who talk about things such as business performance management strategy and market share rather than remote procedure calls and polymorphic object inheritance. If you're a business-type person, you probably don't care about integrating your reports into custom applications and web sites or writing complex programming logic to make them sing and dance. Some of us live for that. What you may care about is giving your savvy business user the ability to drag and drop report parts from the gallery to visualize important key metrics to see what products are performing well in their sales territory. However, to enable that experience, a certain degree of technical expertise is necessary.

Over the years, I've taken inventory of the people who consider themselves report designers. They generally fall into one of two camps business-focused or technology-focused. There has been a significant shift toward more accessible reporting tools for those who have less technical aptitude. The following roles represent the majority and describe some of the trends we're seeing as the industry continues to evolve.

People in this role have strong computer skills, but they don't spend their time writing code and using programming tools. Their primary interest is exploring information and finding answers, rather than designing complex reports. If you're an information worker (IW), you need easy-to-use tools to browse data and create simple reports quickly and with less technical expertise. IWs typically create a report to answer a specific question or address a particular need, and then they may discard the report or save it to a personal area for reuse. They tend to create a separate report for each task and may or may not share these reports with others who have similar needs. This is by far the largest and fastest-growing group of report tool users in the industry.

If you're a business manager, you're primarily interested in your own domain of the business. Managers need reports to support specific processes to address their analytical needs and to help them make informed decisions. Like information workers, they have little interest in the implementation details or technology used to make it work. As information workers, managers may create their own reports to analyze the productivity of their team or area of responsibility.