Saving a report In Reporting Services, you save

Managing reports Remember that managed reports are reports stored on the report server, where they can be stored in folders within the Report Manager. You can add reports to folders, move reports between folders, create new folders, rename old ones, and create folders within other folders, much the same way you d work with folders for your computer s file system in something like Windows Explorer. You publish your data sources to the report server when you deploy your report project; these data sources can be shared among many reports. You control access to such data by using data source connection properties to set the access permissions of a data source. Within Report Manager, you can move shared data sources into their own folder if you so desire. You can also save images used in your reports as resources in your folders, thus providing for easy access and maintenance. Report Manager also allows the administrator to control which users have access to which specific folders. Report Manager also provides access to various report properties that govern how reports will execute (such as scheduled runs or execution frequency), how reports should use the memory cache (cache temporary copy when run, when to expire the cache), how parameters are used (default parameter values, parameter interactivity), report-delivery techniques, and how users will be able to interact with the report content. You can use Report Manager to create snapshots of the report, and you can save report histories so that you can review how the data has changed over a period of time. All these properties provide great control over your managed reporting environment. I describe the capabilities in more detail in Chapter 9. Securing a report Reporting Services has a robust security model. It makes use of a role-based, user-based, and task-based security model to ensure proper permissions are established for access to critical business information. The security model can also be extended, and Microsoft will provide the bits for security model extensions to support proprietary authentication schemes. A role is a way to categorize users into groups based on the way they need to interact with the system and its resources. Users can belong to many roles like content manager, publisher, or browser (of information). User-based security is specific to a specific user in unique aspects of how they interact with the system. Tasks are different functions that a given user can perform. 22 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Saving a report In Reporting Services, you save

Saving a report In Reporting Services, you save any report you create by using Report Definition Language (RDL). RDL, an XML-based schema for report content that allows report definitions to be exchanged between different systems or different reporting tools, is an open specification that Microsoft has published. Some leading reporting tool vendors have announced that they will support this standard. This is a great development because it ensures that in the near future, you ll be able to move reports between different reporting tools without losing formatting or data. The properties of the project dictate where you save the report when the project is actually built and deployed. For example, you can build the report and deploy it to a folder in the Report Server. This enhances your ability to navigate to the report you need when you need it. Displaying a report You can preview your report layout in the Preview window of whichever design tool Report Builder, Report Designer, or some third-party (non-Microsoft) tool you choose to work with. After you re happy with the layout, you can print from the Design tool. This is one way to see the results of your work. After you save your report to the server, you can use the Report Manager to access and view the report. Another way to access your report is to use a technique called URL access, which allows you to type in a URL directly in a standard Web browser to render the report in your browser. With this method, you have the option of displaying the report without the headers and other toolbar and control features that the Report Manager provides. I discuss this further in Chapter 9. After you actually produce a report, you can render it in a variety of formats, including TIFF, JPEG, PDF, HTML, XML, Excel, and Word. You cal also use Open API (application program interface) formats, which can be utilized to render in many more output formats. Third-party vendors have also developed rendering extensions for Word, Excel, and even bar codes. With these rendering approaches, Reporting Services can serve information to a variety of devices such as PDAs, Web sites, wireless desktops, and any computer on a corporate intranet. Chapter 1: Getting Familiar with Reporting Services 21

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

to a report item with a basic drag-and-drop

Document Map is a great idea for documents that work like catalogs. You can also specify whether clicking a cell will jump to another report or to a URL location on the Internet. Using this capability can produce some interesting drill-down analysis capabilities or hyperlinking to get more information from another Web service or Web page about a particular subject. You can also control the visibility of each component of the report so that it can be initially visible (or not) but turn invisible (or visible) based on another report item, an action, or a conditional function. This feature allows for interesting drill-down effects within a single report, where you can initially hide a detail section and toggle between showing or hiding that section with a control that appears on the report. Formatting the content Reporting Services provides you with a variety of devices to control simple text formatting. They include toolbar controls for font and style of your text, menu controls which allow you to change foreground or background color, and text properties that allow you to adjust the format of numbers or general text for display. Text and numbers are sure to make up the greater part of your reports, but you re sure to face situations in which you ll want to add images to your reports. Maybe you want to provide some concrete visual information, or you just want to spice things up a bit. With Reporting Services, you can add images to your report as part of the header, display a picture of a catalog item, or even add a background image to every page of your report. And when it comes to how images get attached to your report, you have the option of embedding images directly in the report, referencing an image on the Web via its Web address (or URL), or storing the image as a field in a database. (Talk about flexibility!) You can set project properties to control the location of the report server as well as the name of the folder that the report will be created in when saved and deployed. Report properties can reference custom assemblies or code that can be run to handle special display formats for numbers in a field. You can also control XML data output options. Many charting types are supported (22 to be exact) with smoothing and 3-D effects that can be added. You can also make use of templates and styles to create interesting reports. This works similar to how you use templates in Excel or Word, where many aspects of the formats are available as predefined settings to save you time in getting to your final destination layout format. 20 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

to a report item with a basic drag-and-drop

to a report item with a basic drag-and-drop maneuver. Then, groupings of report items can be created with an Insert Group command on a table report item. Expressions can be used for the Group On value and Sort value. A report table can be sorted on any field in the table. This allows for robust control over your reports so that a single report with parameters the user can specify could replace a whole book of reports, which are hard-coded or preprogrammed for only certain combinations of parameters. You can create reports with interactive sorting capabilities, which enables the user to sort the data within the report. Reporting Services supports group sorts and various techniques to control the scope of the sort. The Interactive Sort control appears in the Report view as a control that shows the direction of the current sort and highlights which columns can be sorted interactively. The advantage of using this technique is that it interacts with the data that has already been retrieved from the server into the report. All the sorting is performed on the client application without having to query the server again. This provides another option for controlling the overall reporting solution performance. Defining parameters Parameters are variables that determine the filtering for the data presented in reports. You can use parameters in queries to specify report content. All you do is specify your parameter values in a report prompt within the report; they are then passed through to your query to generate the specified report. Parameter values can also be provided by another report so that reports can be linked to create a guided analysis across multiple reports. Report parameters are passed into the report, either through a parameter prompt or directly from another report. Query parameters are specified in the query of the data source that generates corresponding report parameters. You can select one or multiple values in the parameter value list to determine what parameter values are reported for a parameter-based report. Navigating and fact-seeking Being able to find something quickly in a long report can be a real plus, as anyone who s scrolled through pages of reports looking for an elusive fact can testify. You can use Reporting Services to control whether your report displays with a Document Map; it also lets you control what aspect of the report a Document Map is based on. Document Map allows the user to click a control in the report to jump to the bookmark represented by that value of the report variable. If you set up your Document Map wisely, you can then use the feature to quickly find the info you want. Chapter 1: Getting Familiar with Reporting Services 19

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Third-party tools from Microsoft partners are also available

One key feature of Reporting Services is that it allows multiple data sources to be used within a single report, a unique capability that allows for truly robust reports. Creating the layout Regardless of the design surface (Visual Studio or Report Builder) that you use, you have many report items in the toolbox of report controls available to you for building reports. Some of these items are independent items meaning that they re not associated with a particular data source. Examples of these report controls include the Textbox, Line, Rectangle, and Image report items. Rectangles can be used to group other report items or to add page breaks. Other report controls organize data for presentation and are known as data regions. Examples of these report controls include the List, Table, Matrix, Chart, and Subreport controls. Tables are used for tabular displays; matrix report items are excellent at creating cross tab reports; subreports act as containers for other reports; and charts create graphical content. You can also add headers and footers at the report, page table, or group level within a report. The heart of creating reports is knowing the ins and outs of working with the various report items as objects in your reports. I talk about these capabilities more in several chapters of this book, beginning in Chapter 3. Using expressions and formulas Inserting expressions into reports can sometimes be tricky, so it s good to know that Reporting Services has a robust Expression Editor to help in this task. You can create custom fields from columns you return from your data source and then create expressions in a textbox for descriptive text based on elements from your data sources for the report. When it comes to formulas, you can make use of aggregating functions such as SUM and AVG or COUNT and even set up conditional formatting so that various intersections of your report stand out because of distinctive back color, fonts, or other properties. Finally, you can create your own functions and utilize them with a report. Filtering, sorting, and grouping Think of filtering your report data as a way of providing an appropriate amount of data to meet what is known in the business as your information delivery requirements. Not everybody needs every bit of information, so go ahead and set up parameters in your query to reduce the amount of data returned. (See more on parameters in the next section.) Alternatively, you can add fields 18 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Third-party tools from Microsoft partners are also available

Third-party tools from Microsoft partners are also available for creating reports using Reporting Services. See Chapter 23 for a list of supported third-party products. Continuing the tour of Reporting Services features and/or terminology brings us to the Report Manager, an ASP.NET application built into the Web service to enable you to manage the reports that you create. This tool organizes reports into easily maintained folders of reports, data sources, and report resources. It allows an administrator to control access, security, and extended usage and also provides an interface for end users to access and view reports easily. Report Manager has features that allow you to create Report History snapshots for point-in-time information. You can also define subscriptions that allow reports to be delivered to users via e-mail or through Web sites. Report Manager also controls server functionality, such as the report cache, and other features that influence overall report distribution performance. I ll be saying lots more about the Report Manager in later sections, where I concentrate on managing, securing, and distributing reports. Another tool available from the Report Manager is Report Builder, which is a report authoring tool that complements Report Designer. It is a click once Windows application and is run from a full Windows application running on the report server. Whereas Report Designer can create very flexible and complex reports, Report Builder is designed for the business end user who needs to create an ad hoc report. Business users can drag and drop key information elements without having to know anything about the databases they come from. As such, Report Builder supports a more intuitive style of building reports. When I talk about Report Builder, I m talking about a thin application meaning that it is a small separate application that starts up quickly and runs within Reporting Services that is downloaded to your machine when you access it. It provides a rich development environment displaying more information about all the relationships and hierarchies (such as models) in the data sources that you use to create the report. A Model Builder utility for maintaining these relationships and hierarchies is accessible through Visual Studio. Connecting to a data source In order to create reports, you must first connect to a source of data. Reporting Services allows access to any data source with an ODBC (Open Database Connectivity) driver. It also supports OLE DB (Object Linking and Embedding database) connectivity to a wide variety of data sources, including some legacy data sources and most other relational databases such as Oracle, DB2, and Informix. You can also access flat-file data sources (text files) and hierarchical data sources if an ODBC driver supports it. This provides flexibility in sourcing the information for further data processing and any resulting analyses. Applications can be written to connect to SQL and even OLAP data sources as well as OLE DB-compliant data sources. Chapter 1: Getting Familiar with Reporting Services 17

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Investigating Business Data Many company managers and analysts

a few simple illustrations of Reporting Services features and capabilities will provide you with a perspective on how to work with the tool. Don t worry if you don t catch on during the first reading of this section. I ll backtrack and cover all these areas in greater detail in subsequent chapters of the book. This tour will enable you to appreciate the power at your fingertips with this new reporting service. It s a Web service Reporting Services gets its name from the fact that the report server is a Web service. A Web service is a software component that runs independently on a server and can be accessed by other applications running on the World Wide Web. The Web service runs on the report server. The Web-based reporting functionality of the reporting service provides a single platform for producing Web-based reports for all types of data. The reporting service enables you to embed reports within any Web application and make them available to users with any Web interface. It also allows you to save or view report information in a number of different formats, ranging from images to Excel workbooks. Any application that can make use of Web services can present information through Reporting Services. One particularly useful set of applications enables you to manage many aspects of report processing as part of the report service retrieving data, transforming the report layout into a device-specific format (rendering), delivering reports to specific formats, and securing the access to reports, to name just a few. Working with the design surface The design surface is the user interface used to create reports for Reporting Services. The primary design interface provided by Microsoft for creating reports is Visual Studio. Visual Studio hosts Report Designer, which is the application that allows the user to select a data source, build a query, lay out the report elements, preview it with sample data, and finally publish the report to the report server. Report Designer is a powerful tool for developing flexible and complex reports. It offers maximum programmability of the report to provide reports that are structured or free-form, static or interactive, parameter-based or hyperlinked, graphical, tabular, or matrix. It saves developers from the hassle of having to know the gory details of Structured Query Language (SQL) or eXtensible Markup Language (XML) or Multidimensional eXpressions (MDX) if you re accessing data cubes, the OLAP data stores that allow fast reporting of summary information. When you use Visual Studio to develop your reports, they are actually built within a Report Project. Project properties let you control where the reports are saved and where they are viewable when the report is published. 16 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Investigating Business Data Many company managers and analysts

Investigating Business Data Many company managers and analysts today are preoccupied with investigating business data. For companies that still have legacy systems systems installed in the 1980s or 90s that were developed on mainframe platforms or other platforms that new systems no longer use accessing the data for reporting and analysis can prove a challenge. Many business people have turned to spreadsheets for rekeying legacy data and creating some analysis based on the data. Databases such as Microsoft SQL Server and Oracle are becoming the information storage system of choice. Databases allow for easyaccess to information in tables that can be queried with reporting tools. Applying good reporting principles to the information in these databases can provide valuable insight into business operations. Being able to drill down into information for more detail or charting information to see trends in the information is business intelligence. Business intelligence (BI) solutions integrate information from multiple data sources and realign the data into structures that are ideal for reporting, drilling down, and trending. These hybrid data models provide greater value for users, including more insightful information content in reports. Smart users who know the tools can then explore information effectively to gain insight into the underlying information. In a well-executed BI solution, users have self-service access to reporting tools like Reporting Services. Self-service reporting for everyone is a nirvana state of business intelligence. Business people need to be able to freely explore appropriate information with interactive reports or view relevant performance data through dashboards. Dashboards are a common user interface for executives who typically have little training or knowledge in computers and therefore require very intuitive controls for interacting with the information. These varied reports become a truly analytical support system that can provide the perspectives necessary to increase the agility of your organization. Reporting Services is one tool that can be leveraged to provide this type of analytical framework. Uncovering the Major Features of Reporting Services Earlier sections in this chapter spell out in general the many principles related to creating, distributing, and managing those reports supported by Reporting Services. Now it s time to do a bit of our own drilling down by examining in greater detail the basic capabilities of Reporting Services proper. Hopefully, Chapter 1: Getting Familiar with Reporting Services 15

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Presenting to the right audience If you re like

By using Reporting Services, you have many content design approaches that you can put to use because of the many elements in the report toolbox that you can use in any report. In addition, you might want to provide for ad hoc query and analysis. The ad hoc nature of asking questions, getting answers, and integrating these answers together to gain an understanding of what s really going on is what most business analysts and their managers need to do. They need the capability to ask a variety of questions and filter the report dynamically or drill down to see more detail within the underlying data. Perhaps they want to be able to jump from one view to another, with a parameter passed to the second report that shows a different view for that aspect of the initial report. Reporting Services was designed to provide these kinds of capabilities. The design surface or user interface used to create reports for Reporting Services includes a wide variety of tools that can provide just the right content to clarify the information so it s clear to your audience. For example, tabular or list reporting might be appropriate for financial analysis, but subreports could provide other views that clarify the meaning of the information reported. Charts provide for graphical views that allow the user to easily visualize trends and anomalies. Free-form reporting tools such as lists and rectangles (containers used for placing other controls that allow more free-form reporting) allow much more flexibility than ordinary banded-style (columnar reporting with little flexibility to change column spacing and subtotaling formats) reporting tools most of us have had to settle with. Providing the right security Information provided to the wrong people can lead to some huge problems. For example, if personal information from Human Resources (like your employees Social Security numbers) was available to everyone in your company as well as to your customers and prospects, this could lead to an identity theft situation and some disgruntled employees. If you provide access to information that is not appropriate for some class of business users, you could open up your company to unwanted lawsuits and other nuisance issues that could result in your own trip to your HR department. Reporting Services allows you to properly secure the information by requiring users to submit both a user ID and a password if they want to access key information sources. Proper credentials must be provided to the server for any user to gain access to any major feature of the product. Reporting Services can also limit the access and distribution based on the access group defined in Windows-based security by your network administrators. Reports with sensitive information won t appear to users who don t have proper permission. 14 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Presenting to the right audience If you re like

Presenting to the right audience If you re like me, you need to create reports and share them with other people in the company so that they understand something about the data and then hopefully have them interpret or explain the business context to everyone else. Depending on the functional role of the person with whom you ll be sharing the results, the style of the report is sure to be different. Some people react better to a pure numbers presentation. Others respond better to a graphical view of the information, perhaps showing a trend over time or a comparative chart of performance compared with other like aspects. Reporting Services allows you to customize your information to suit the preferences of a wide variety of consumers of information. Reporting Services allows you to create traditional reports as well as free-form reports. It also allows you to create interactive reports that can drill into more detail by linking directly to another report. If you re making a presentation to the board of directors or perhaps creating a list of To Do s for yourself, knowing your audience helps you define the best style and format for your report. Offering the right content and design When somebody walks up to me at the copy machine and asks me, What is this report that Accounting sent me last week supposed to be telling me? , I usually wonder about two things. First, is this guy testing me with something that I m supposed to be able to pick up quickly? Then, after rapidly scanning the information and concluding that it is not a test, I m usually shocked by how poorly organized the information in the report actually is. Finally, I have to shrug my shoulders and confess that I am just as confused as he is. The moral of this story is that many bad reports are out there! Reporting can be improved by taking advantage of features that present the right information clearly so that you have no question about the right interpretation. A report with the right content and design is easy to understand by ordinary people like you and me. Designing a report so that the content is easy to figure out can be a challenge. For example, it s difficult right off the bat to interview a business professional and puzzle out from the interview all of his or her reporting needs. Even if the interview session goes well, determining what reports are best for his or her purposes and perspectives is difficult. To do a great job at this, you need to have an understanding of all the information coming from the key data sources within the organization. Therefore, good information content in a report requires that you have a holistic understanding of a great many aspects about the business and its operational data assets. Chapter 1: Getting Familiar with Reporting Services 13

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services