Diagram pane: Shows the tables in the

Sketching out your design layout After you have your query defined as a dataset in the Data view of Report Designer so that it returns a result set you like, you can move further along the Report Road by clicking the Layout view. The Layout view provides you access to the fields of your dataset defined in the Data view as well as access to a toolbox of report items that you can use to place the info from your datasets where you want it. Before I let you go down that road, however, I want you to get acquainted with all the windows and panes in the Layout view of the Report Designer the basic building blocks for all the spiffy reports you re about to create. The first pane is the Toolbox pane, as shown in Figure 3-7. Two types of report items are available to you in the Toolbox: Data regions are areas on a report that contain data from a data source that is repeated. Examples of data regions type tools are the List, Matrix, Table, and Chart controls which, as you might suspect, are used to set up and format lists, matrices, tables, and charts, respectively. In the upcoming steps, I show you how to use a Table control to create a nice little mini-spreadsheet within your report. Independent items are areas on a report that are not associated with a dataset. A text box, for example, represents a text constant for titles or comments in a report. I discuss report items in depth in Chapter 5. The next handy pane is the Datasets pane, shown on the left in Figure 3-8. When a dataset is created, the Report Designer retrieves a list of fields from the data source and populates the list. This list of fields is available for dragging into the Layout view to populate those controls that you have previously had the presence of thought to drag from the Toolbox window onto your report template. Very handy. You can select from the report items in the Toolbox to create a simple report from your dataset. The placement of report items in a report is completely free-form. You can place them anywhere in the body of the report. To see how you can put such freedom into action, try the following. Build your first report by following these steps: 1. In Layout view, drag a Table control from the Toolbox onto the layout region of the Layout view and drop it into the area. (If the Toolbox pane isn t visible, on the View menu click Toolbox or click the folder named Toolbox on the far left side.) A Table control appears in the report, as shown in Figure 3-8. 2. To switch from the Toolbox pane to the Datasets pane, you can click the View menu and select Datasets or click the Datasets tab on the far left. 56 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

Diagram pane: Shows the tables in the

Diagram pane: Shows the tables in the query Grid pane: Shows the columns selected from the tables and various properties defined by the query itself SQL pane: Shows the SQL query you entered previously or generated from selecting tables and columns Results pane: Shows the rows returned from the tables after running the query You can show or hide these panes based on your preference by clicking the tools in the toolbar sprawling across the top of the Data view window. You can see the whole shebang in Figure 3-6. You can also run execute, if you prefer this query and test to make sure that the SQL query brings back a results set. You can execute the query you have designed by clicking the ! button in the Data view toolbar. The query results are shown in the grid at the bottom of the Data view window shown in Figure 3-6. Figure 3-6: The Data view within the Report Designer showing your result set. Chapter 3: Building and Running a Simple Report 55

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

The previously useless gray area of the BI

2. On the Query tab of the Dataset dialog box, enter a name for your query in the Name field. Again, CustomerSales has a nice ring to it. 3. Choose your data source (usually a database name) from the Data Source drop-down menu. Selecting AdventureWorksDW (shared) would be a good choice here. Use the button with the ellipsis (…) to modify any aspect of the data source. Clicking this button brings up the Shared Data Source dialog box shown earlier in Figure 3-2. 4. From the Command Type drop-down menu, choose Text. Other choices here include StoredProcedure and TableDirect. Don t worry about them for now; just concentrate on the basics. 5. In the text window beneath the Command Type drop-down menu, enter the following SQL query: SELECT Name AS Store Name , COUNT(*) AS Num Orders , SUM(OrderHeader.SubTotal) AS Total Sales FROM Sales.SalesOrderHeader AS OrderHeader, Sales.Customer AS Customer, Sales.Store AS Store WHERE OrderHeader.CustomerID = Customer.CustomerID AND Customer.CustomerID = Store.CustomerID AND (YEAR(OrderHeader.OrderDate) = 2004 ) GROUP BY Store.Name HAVING (SUM(OrderHeader.SubTotal) > 200000) ORDER BY SUM(OrderHeader.SubTotal) DESC Note: This query is based on tables found in the AdventureWorks sample database shipped with SQL and is asking to return the store name, the number of orders, and total sales amount in 2004 for orders greater than $200,000. This requires looking at three tables in the database: OrderHeader, Customer, and Store. The query further requires that the results be returned in order of highest to lowest total sales amount. 6. When done entering your SQL query in the text window, click OK in the Dataset dialog box. Your SQL query returns your result set: 2004 Store Sales from highest to lowest. Note that if you click the Generic Query Designer button (the button in the Data view toolbar with the icon showing a pencil and two tables), the SQL changes into a lovely graphical view of the query environment, which includes these panes: 54 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 JSP Web Hosting services

The previously useless gray area of the BI

The previously useless gray area of the BI Development Studio is now populated with the kinds of tabs and windows that you expect in a development environment which means that you are ready to rock. Working with your data When you have the wonders of the Report Designer tool spread before you in all their glory, you ll notice the Solution Explorer pane on the right, displaying a shared data source in its Shared Data Sources folder and a report in its Reports folder. You can pin the Solution Explorer pane open so that it shows all the time, or you can unpin it so that it shows only when you move your mouse over the far right margin of Report Designer. (This pinning and unpinning business is what the push-pin icon is for.) On the left, you ll see either a Fields pane or a Toolbox pane. These panes become important after you define the query from your data source so more on them later. To get Report Designer to work for you, you do have to bring some stuff to the table. More specifically, you need to know which tables in your database contain the information you seek, as well as which columns you need in order for the report to patch together a SQL query. If this doesn t sound familiar, visit Chapter 2, where I cover basic SQL and database matters. To define a query that returns the data you want, you need to set up a dataset because that s where you store your query information for a report. First make sure that the Data view is front-and-center in Report Designer which should look like Figure 3-6. After ascertaining that fact, do the following: 1. Choose New Dataset from the Dataset drop-down menu. The Dataset dialog box makes an appearance, as shown in Figure 3-5. Figure 3-5: The Dataset dialog box from the Data tab of the Report Designer. Chapter 3: Building and Running a Simple Report 53

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

Supply the information needed to log on to

Welcome to the Report Designer Nestled within any new project listed in the Solution Explorer, you will find two folders: a Shared Data Sources folder and a Reports folder. (You can read about the Shared Data Sources folder in the preceding section.) Now is the time to move on to the next stage of creating a report: the Add a Report Object to Your Report Project part. Here s how: 1. Make sure that the Solution Explorer pane is displayed on the right side of the BI Development Studio. If it s not visible, choose View.Solution Explorer from the main menu. 2. Right-click the Reports folder. A handy pop-up menu appears indicating your choices for how to add a report. 3. Choose Add.New item from the pop-up menu, taking your cue from Figure 3-4. The Add New Item dialog box appears, as shown in Figure 3-4. Here you ll find templates for use in creating a report. Right off the bat, your choices are slim pickin s Report Wizard, Data Source, and Report, to be precise but that will change as you add new templates. 4. In the Add New Item dialog box, click the Report template icon and then enter a name for your report in Name field. Something like CustomerSales would do nicely. Note that Reporting Services wants you to end your filename with the .rdl file extension. (RDL stands for Report Definition Language, which is what Reporting Services creates for you while you build your reports.) 5. Click the Add button. Figure 3-4: Settling on the right template. 52 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 JSP Web Hosting services

Supply the information needed to log on to

Supply the information needed to log on to the server. This consists of the server name and the database name running on that server. You can supply user ID, password, and workstation ids specific to an individual user if necessary by clicking the Advanced button and specifying these property values. 4. Click the Test Connection button located at the bottom left of the Connection Properties dialog box to confirm that you entered the correct information for the database connection. If the test is successful, Reporting Services will notify you that the test connection succeeded. When you have successfully created a connection, click OK to proceed. If you need to enter more advanced information about the connection (like a timeout setting), you can use the Advanced button of the Connection Properties dialog box. If you crossed your i s and dotted your t s and faithfully followed my instructions, you should now have a solid connection to your data source. Need reassurance? Then check whether a new icon bearing the name of the database you just connected to appears in the Shared Data Sources folder in the Solution Explorer pane. If the icon pops up there, you are now ready to define the query you want to use as the basis of your report. Figure 3-3: Specify your connection properties here. Chapter 3: Building and Running a Simple Report 51

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

6. In the Location field, enter the directory

Establishing a shared data source Your report will get its facts and figures from some kind of source more likely than not, a database-oriented one. The Connection Properties dialog box (refer to Figure 3-3) lets you define that data source and for good measure, lets you refer to that source as your Shared Data Source, where shared means that it can be referenced by many reports within the same report project. Here s how you specify a data source: 1. In the Solution Explorer pane of the BI Development Studio, right-click the Shared Data Sources folder of your new project. The Shared Data Source dialog box appears. (Note: For more on setting up a new project, see the Facing Down the BI Development Studio section, earlier in this chapter.) 2. On the General tab of the Shared Data Source dialog box, select the data source type that you want. Your choices here could include OLE DB providers (for Oracle, SQL Server, Outlook, and Directory Services); ODBC drivers (for a wide variety of relational databases); SQL Server Analysis Services, SAP; and others. Your choice here sets the context for the next action. 3. If you specify a data provider for a relational database, click the Edit button and the Connection Properties dialog box will appear (see Figure 3-3). Figure 3-2: Specify your data source here. 50 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

6. In the Location field, enter the directory

6. In the Location field, enter the directory path under which you want to save your report project. You can alternatively click the Browse button and then navigate to the folder you want. 7. Click OK to close the dialog box. An icon representing your new project appears in the Solution Explorer pane. Working with Your Reporting Project After you set up a new project in the BI Development Studio (see the preceding section), it s time to get down to the nitty-gritty of report building. Note that the Solution Explorer pane of the BI Development Studio initially shows an icon bearing the name of your report project as well as two folders associated with that project. You can use the Solution Explorer pane to view the contents of these folders, set the properties of such contents, and most importantly add your data sources. To see how this works, go ahead and right-click the Shared Data Sources folder. Doing so displays the Shared Data Source dialog box, as shown in Figure 3-2. If you don t see the Solution Explorer pane at first, choose View.Solution Explorer from the main menu. Figure 3-1: Specify project types here. Chapter 3: Building and Running a Simple Report 49

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

Chapter 3 Building and Running a Simple Report

Facing Down the BI Development Studio You work with Report Service s Report Designer tool whenever you want to build a report, so it won t be too long before you become intimately familiar with it. (Report Designer is just one of the authoring tools within Reporting Services, but it is definitely the most powerful and flexible for creating the widest range of report styles.) Report Designer runs within the Business Intelligence Development Studio, so if you ever actually want to use Report Designer (and I m guessing that you will), you have to launch Report Designer and set up a new project for it from within the BI Development Studio. Here s how that s done: 1. From the Windows taskbar, choose Start.All Programs.Microsoft SQL Server 2005.Business Intelligence Development Studio. The Business Intelligence Development Studio displays onscreen, providing the environment where you ll design your reports. Frankly, it s not much to look at. All you ll see for now is a blank Explorer pane (known as the Solution Explorer pane) on the right, a blank Output pane at the bottom, and a big gray expanse in the middle. If the Solution Explorer pane is not visible, choose View.Solution Explorer from the main menu to see it. 2. Get the process for a new project started by choosing File.New. Project from the main menu. The New Project dialog box appears onscreen, as shown in Figure 3-1. 3. In the New Project dialog box, verify that the Business Intelligence Projects folder is selected in the Project Types pane. Reporting Services adds the Business Intelligence Projects category at installation. This project type contains all the report tools that you need to design and build reports using Reporting Services. 4. In the Templates pane, select the Report Server Project Wizard. The BI Development Studio allows you to create a report from scratch or to use one of the Visual Studio installed templates. 5. Enter the name for your report in the Name field of the New Project dialog box. If you refer to Figure 3-1, you can see that I chose the evocative name Example Project for my new project. 48 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

Chapter 3 Building and Running a Simple Report

Chapter 3 Building and Running a Simple Report In This Chapter Facing down BI Development Studio Working with your reporting project Seeing the capabilities of the Report Designer Modifying, previewing, and saving your report Printing your report Reporting Services is all about letting you create reports a no-brainer if there ever was one. The somewhat trickier part involves getting all the groundwork done so that Reporting Services can do its job preparing the soil, as it were, so that your little report can flower. Groundwork in this context means Defining your data sources: Where is the hiding place of the data you want to work with? Defining your query: What exactly is it that you re looking for? Formatting your results set for your intended user: What s the best way to present this information, given your intended audience? Then all you have to do is run the darn thing and print it out. This process may sound like a lot to take care of, but don t worry too much. In this chapter, I walk you through all these steps, using as my data source the handy AdventureWorks database, which is included as part of SQL Server 2005. Before I can do that, however, I have to take you on a tour of the Reporting Services workspace, known affectionately as the BI Development Studio. (BI is short for Business Intelligence; now, does anybody remember the old joke about Military Intelligence?)

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