Creating Reports Based on a UDM

You have so far seen some capabilities provided by Reporting Services 2005 and how it can facilitate the creation of reports from a relational database. In Chapter 16 you learned the tight integration of Analysis Services with Integration Services that helps you load data into Analysis Services and perform administrative operations. By adding to those functions, Reporting Services 2005 provides you the ability to create reports from the UDM by which Microsoft's SQL Server 2005 provides a truly end-to-end business intelligence solution to the market. Designing reports from Analysis Services 2005 databases is actually similar to designing reports from a relational database using Reporting Services 2005.

In the event you have experience with Reporting Services 2000, you will be excited to know that Reporting Services 2005 contains both new features and extended capabilities associated with the original feature set.

In Reporting Services 2000, specifying an MDX query and using the OLEDB provider for Analysis Services to integrate cube-based data into your reports was about the extent of the integration between the two products. There are significant improvements on that model; you will find much tighter integration exists between Reporting Services 2005 and Analysis Services 2005. Specifically, the product now has an MDX query designer, which enables you to retrieve data from any UDM through the technique of drag and drop, without you actually having to understand and write MDX queries. Assuming you worked through this whole book, you already know some MDX, but in the event you are a shameless chapter surfer who started here, well, you're in luck.

Some other points more than merit mention here. In the 2000 version, you could only pass a single parameter to Analysis Services from a Report for dynamic report building. For example, you could send a single country name. Now, you can send a whole list of country names as parameters; this enhances what you can accomplish in terms of building dynamic reports. The ability to retrieve intrinsic properties for dimension members and cells is intact with 2005, not to mention accessing member and extended properties. You are sure to love this next one — Reporting Services 2005 takes advantage of aggregated data provided by Analysis Services! This helps increase performance of report processing — especially when there is a large amount of source detail-level data (like sales per store) and the report includes aggregations based on that (like sales per region.) There is much to like about the integration between Reporting Services and Analysis Services in this SQL Server 2005; report building directly off a UDM, for one, is awesome.

Designing Your Analysis Services Report

In this section you create a sales report from the UDM for AdventureWorks2005Tutorial using the Report Wizard. You later refine the report based on certain requirements surprisingly imposed by your boss at the last moment. At the end of this section you will be familiar with creating specialized reports on a UDM. The following steps will help you to build reports from Analysis Services by establishing a connection to Analysis Services, building the MDX query, and previewing the report. Follow the steps to create your Analysis Services reports:

1. Open the AdventureWorks2005Tutorial under Chapter 17 and deploy it to your Analysis Services instance.

2. Right-click the Reports folder and select AddNew Item. Select the Report Wizard and click the Add button. If a Welcome to the Report Wizard page appears, click Next.

3. The first step in the Report Wizard is to provide the connection details to the data source. In the Data Source Wizard select the Microsoft SQL Server Analysis Services option for data source type, as shown in Figure 17-12. Then click Edit.

   
   Figure 17-12

4. Click the Edit button in the Select Data Source page to launch the Connection Properties dialog. In the Connection Properties dialog enter the machine name of your Analysis Services instance, select the AdventureWorks2005Tutorial project as shown in Figure 17-13, and test your connection. Once your connection has been tested successfully, click OK.

   
   Figure 17-13

5. The next page of the Report Wizard is the Query Builder page. Click the Query Builder button to launch the MDX query builder shown in Figure 17-14. The MDX query builder contains a metadata pane where you can select a specific cube from the database and see the measures and dimensions of the cube. There is a pane in which you can specify calculated members that will be within the scope of the query sent to your Analysis Services instance. There is a filter pane to restrict data, and finally, there is a data pane where you can drag and drop the dimensions and measures that you want to include in your report.

   
   Figure 17-14

6. Drag and drop the measure Fact Internet Sales-Sales Amount from the metadata pane to the data pane. At this moment the MDX query builder creates the MDX query to retrieve the selected measure from the Analysis Services instance and shows the results in the data pane. Using the Report Wizard you will create a report of Internet sales of products in the U.S. along with the customer's gender information. Drag and drop the gender attribute hierarchy from the customer's dimension and the State Province Name attribute hierarchy from the geography dimension, which indicates the customer's geographical location. You will now see results set in the data pane. Because the UDM contains Internet sales information from various countries, you see the data for all the provinces of various countries.

7. To restrict the data to the provinces in the United States, drag and drop the Sales territory country attribute from the geography dimension to the filter pane. Similar to filtering data while browsing a UDM, set the filter expression equal to United States by selecting the United States member. You will see the Internet Sales from various provinces within the United States along with the gender of the customer as shown in Figure 17-14. You can see the MDX query by switching from the graphical design view to the MDX query view by clicking on the Design/MDX icon shown in Figure 17-14.

   • The MDX query generated by the query builder (which you can happily ignore if so inclined) is

          SELECT NON EMPTY { [Measures].[Fact Internet Sales - Sales Amount] } ON COLUMNS,       NON EMPTY { ([Dim Geography].[State Province     Name].[State Province Name].ALLMEMBERS       * [Dim Customer].[Gender].[Gender].ALLMEMBERS ) }       DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS       FROM       ( SELECT ( { [Dim Geography].[Sales Territory Country].&[United States] } )       ON COLUMNS       FROM [Adventure Works DW])       WHERE ( [Dim Geography].[Sales Territory Country].&[United States] )       CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,       FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS 

   • The query generated by the MDX query builder within Report Designer is an MDX subselect query, which you learned about in Chapter 7. The above query contains two MDX select queries. The inner SELECT query restricts the cube space based on the member [Dim Geography].[Sales Territory Country].[Sales Territory Country].&[United States], and the outer SELECT query retrieves the data within the cube space provided by the inner SELECT query.

   • One important thing you should be aware of in the MDX query builder is that if you switch from the design view to the MDX view and make changes, at that point you are at risk of losing the original configuration built in the design view if you then return to the design view. Therefore, we do not recommend that particular action — if you want to return to the design view. In the design view as you drag and drop fields, the automatically generated MDX query is executed immediately and displays the results. If you know that your query is going to retrieve a large result set, you can turn off the autoexecute query mode using the icon in the toolbar or by right-clicking in the result pane and deselecting auto-execute mode. If you are an MDX expert, you might actually prefer to use the MDX view. In such a circumstance, switch to the MDX view, type in your MDX query, and then click Execute to ensure your query is correct and returns results expected by you. Click OK once you have selected the fields you need for the query.

8. The MDX query will now be shown in the Design the Query page, as shown in Figure 17-15. Click the Next button.

   
   Figure 17-15

9. In the Select the Report type page select the Tabular report which is the default option and click Next.

10. In the Design the Table page you will see the three fields you selected in the MDX query builder. In this report you will group the sales of customers based on the provinces. Therefore, select the State_Province_Field and click on the Group button to move the field to the Group pane. Select the fields Customer Gender and Fact_Internet_Sales__Sales_Amount fields and click on the Details button to move the fields to the Details pane as shown in Figure 17-16.

    
    Figure 17-16

11. Similar to the relational report you created earlier, select Stepped layout in the next page. Select the option to have subtotals displayed as well as enable drilldown in this page as shown in Figure 17-17 and click the Finish button. In the Final page of the wizard, name the report AnalysisServicesSalesReport and click the Finish button.

    
    Figure 17-17

12. You will now be in the Layout view of the Report Designer. Click the various items in the layout such as the table and the grouping row within the table based on the State Province, and look at their properties to have a brief overview of how the Report Wizard created your layout. Once you have created your report, you will want to see the behavior of the report before you deploy it on to your Report server. To preview your report, click the Preview tab. You will now see a report as shown in Figure 17-18.

Enhancing Your Analysis Services Report

You have successfully created your first report on top of a UDM. This is a very basic report. The report you created in the previous section only includes the Fact Internet Sales information for the states within the U.S. and the genders of various customers. In this section you enhance your report by including the countries, and instead of sales information you will be creating a report that shows sales profits of each state for various years. Follow the steps below to enhance your report.

Figure 17-18

First you need to change the query that retrieves the results from your Analysis Service instance. To change the MDX query, switch from the Preview pane to the Data pane. The Data pane is nothing but the MDX query builder you saw in the Report Wizard. Since your new report needs to include the profit, you need to create a measure that will calculate the profit. The Adventure Works DW cube contains measures for Internet and Reseller sales along with the cost of the products sold over Internet and Reseller sales. You need to create calculated members in the cube to aggregate these data and then calculate the profit. Instead of creating these calculated measures within the cube, you know you can create calculated measures in an MDX query using the WITH MEMBER clause, which you learned in Chapter 3. The MDX query designer allows you to graphically specify these calculated members instead of writing the full MDX query. Creating the calculated members using the designer allows you to still work in the design mode.

1. To create a calculated measure Total Sales, click the calculator icon or right-click in the Calculated Members pane and select New. The Calculated Member Builder dialog shown in Figure 17-19 will be launched.

2. Type Total Sales Amount in the Name text box for the calculated measure. Drag and drop the Sales Amounts from Internet and Reseller Sales measure groups from the Metadata pane and add a plus (+) sign between these measures as shown in Figure 17-19. Click OK to create the calculated measure.

   
   Figure 17-19

3. Create a calculated measure called Total Product Cost as the sum of the product costs of the Products sold via Internet and Reseller.

4. Create a calculated measure called Total Profit, which is the difference between the numerical values in the calculated measures Total Sales Amount and Total Product Cost. You will now see the three calculated measures in the calculated members' pane of the query builder as shown in Figure 17-20.

   
   Figure 17-20

5. Remove the Internet sales amount measure from the result pane by dragging and dropping the field from Result pane to the Metadata pane or by selecting the field, right-clicking, and selecting Remove. Similarly remove the field Gender from the data pane. Drag and drop the calculated member Total Profit from the Calculated Members pane to the Result pane. Add the attribute hierarchy Sales Territory Country from the Geography dimension and Calendar Year hierarchy from the Order Date dimension to the data field as shown in Figure 17-21.

   
   Figure 17-21

6. Click the filter Sales Territory Country and drop-down the filter expression list box. Select all the countries except N/A and Unknown from the list as shown in Figure 17-22.

   
   Figure 17-22

   The Result pane now shows the Sales profit for various countries and provinces for all the years, as shown in Figure 17-23. Now you have all the data required for enhancing your report.

   
   Figure 17-23

7. Switch from the Data pane to the Layout pane. The Layout pane contains an object that includes the fields State Province Name, Gender, and Fact Internet sales as shown in Figure 17-24. This object is called a table. A table report item is used whenever you have multiple rows of data to show. Select the existing table object, right-click, and select delete to delete the table. You can see the fields from the new Result pane in the Datasets pane as shown in Figure 17-24. You will use the new fields to re-design the report.

   
   Figure 17-24

8. In your report you need the profits of each country to be seen on different pages. In order to design such a report you need a report item called List in your report designer layout. Click the toolbox window and drag and drop the report item List to your report designer layout below the title of the report as shown in Figure 17-25. If you cannot see the tool box window select Toolbox from the View menu item. You now need to select the field by which you want to group the details of the report. To select this field, click the properties window for the List report item and launch the dialog to select the grouping property of this list item. You will now see the dialog shown in Figure 17-26. Select the Country field.

   
   Figure 17-25

   
   Figure 17-26

9. Next, drag and drop the Table report item within the List item. You want to group the yearly sales based on the states. To see a grouped report, right-click one of the rows of the table report item and select Insert Group. You will now see the Grouping and Sorting Properties dialog. Select the field State_Province_Name as the expression to Group on. Hide the table footer of the table report item by right-clicking and selecting Table footer which is a toggle switch. Drag and drop the State Province Name field to the group header row (after the table header row), first column of the table. Drag and drop the fields Year and Total Profit to the second and third columns of the detail row within the table. Notice the titles of these fields will automatically be updated with appropriate names in the header. Drag and drop the Country as a text box on top of the table. Drag and drop Total Profit field to the last column in the group footer row. Your layout should now look similar to Figure 17-27.

   
   Figure 17-27

10. Click on the Preview tab to see the initial version of the report you have designed. You should see a report as shown in Figure 17-28. The report shows the profit report for a specific country in a single page which includes the states within the country along with profits for each year. The report also shows the aggregated profit for each state. You can switch to the profit report for the next country by selecting the next page or entering a specific page number as shown in Figure 17-28.

    
    Figure 17-28

11. The report you just designed will not win any beauty contests nor is it particularly well formatted. You can beautify the report by making the headers bold, adding a background color, etc. Multi-select all the cells within the table by holding the Ctrl key down and then selecting each cell in the table. In the properties window change the border style to Solid; next select the BackgroundColor Silver. Select the header row and in the properties window change the Font property so that the Font Size is 12 pt and Font Weight is Bold. For the text box showing the Country change the property such that the BackgroundColor is Silver, the FontColor is Dark Red, FontSize is 12 pt and FontWeight is Solid. If you preview the report you will see a report similar to the one shown in Figure 17-29.

    
    Figure 17-29

12. One of the key things in reports involving profit is the ability to easily distinguish the amount of profit. Typically in ledgers positive amounts are shown in black and negative amounts are shown in red. In this report we will modify the profit to be shown in green or red depending upon the profit amount. To specify appropriate colors to be displayed for profit select the cell corresponding to the profit in the layout mode. In the properties window set the property Color to be an expression. You will immediate see the Expression editor dialog. You can use VBA functions as part of the expressions. To check if the profit amount is positive or negative we will use the VBA function IIF. Set the expression for Color to check if the value for profit is greater than zero. If yes, the function will return the color green, or else the color red. Once you specify the expression your expression window should look like Figure 17-30. Click the OK button.

    
    Figure 17-30

If you preview the report after setting the colors you will see a report as shown in Figure 17-31. You have seen some of the enhancements that can be made to your UDM reports using Report Designer. Next you will look at some of the new extensions in Reporting Services 2005 that have been specifically added to have a tighter integration with Analysis Services 2005.

Figure 17-31

Enhancing Your Report using Extended Properties

The dimension members and cells in Analysis Services have certain specific properties associated with them. These properties can be retrieved from the Analysis Server along with the query result. There are certain properties from Analysis Services that get mapped on to properties in Reporting Services. These are called predefined properties and are accessed within reports as Fields!FieldName.PropertyName. Predefined properties in Reporting Services are Value, UniqueName, IsMissing, BackgroundColor, Color, FontFamily, FontSize, FontWeight, FontStyle, TextDecoration, FormattedValue, LevelNumber, and ParentUniqueName. Extended properties are additional properties that are returned from Analysis Services. Since these properties are not returned as fields, you cannot drag and drop from the field list to your report layout. Reporting Services 2005 provides functionality to access these values in a unique way and include them in the report. You can access the extended properties in one of the following formats.

• Fields!FieldName!PropertyName

• Fields!FieldName ("PropertyName")

• Fields!FieldName.Properties ("PropertyName")

To see an example of how extended properties can be used in your reports you will now enhance the report in the previous section by using the extended property FORMAT_STRING that is returned by Analysis Services for the measure Total Profit. Switch to the Layout mode in Report Designer and select the cell showing the Total Profit in the report. Select the property Format for this cell and set its value as an expression. In the Expression dialog enter the value =Fields!Total_Profit!FORMAT_STRING. Save the report and switch to the preview mode. You will see a report where the profit values are formatted based on the format string that is retrieved from Analysis Services as shown in Figure 17-32. Using the extended properties you will be able to retrieve and use the member properties for dimension members and cell properties for cells that are not part of the predefined reporting services properties.

Figure 17-32

Custom Aggregates

When you create reports that contain groups, then most likely you have subtotals for the group members. For example, if you have sales for various years and products then you might want to view the sales for each year which needs to be aggregated. Reporting Services provides you a set of aggregation functions such as sum, count, distinct count, etc. For a detailed list of the aggregation functions supported by Reporting Services 2005 please refer to the product's documentation. In addition to these aggregate functions, it supports custom aggregates supported by data providers. If a data provider such as Analysis Services supports custom aggregates then Reporting Services has the ability to retrieve that data for the aggregate rows by the aggregate function called Aggregate. Custom Aggregates are also referred to as Server Aggregates.

Examples of custom aggregates in Analysis Services 2005 are semi-additive measures which use aggregate functions such as ByAccouunt, FirstNonEmpty, LastNonEmpty, FirstChild, LastChild, AverageofChildren, None. Some of the custom aggregate functions supported by Analysis Services 2005 are also supported by Reporting Services 2005. However we recommend you use custom aggregates for improved performance; in this way Reporting Services does not have to calculate the aggregate once again, for that was already done by Analysis Services. In this section you create a report that uses customer custom aggregate function to retrieve semi-additive measures from Analysis Services 2005. Follow the steps below to generate a custom aggregate report.

1. Add a new report item to the Reporting Services project you used in the last section called CustomAggregate.rdl.

2. Switch to the Data tab and create a new connection to the database AnalysisServices2005Tutorial.

3. The measure Fact Internet Sales - Unit Price in the cube AdventureWorksDW is a semi-additive measure with aggregate function FirstNonEmpty. Drag and drop this measure from the Metadata pane to the Result pane. Drag and drop the hierarchy Products from the Product dimension and Calendar Year hierarchy from the Order Date dimension. Your Result pane should now include columns Calendar Year, the two levels of the Products hierarchy Model Name and Product Name, and the measure Fact Internet Sales - Unit Price as shown in Figure 17-33.

   
   Figure 17-33

4. Switch to the Layout tab to design your report. Add a list report item with Grouping/Sorting option as Calendar Year similar to the report you designed in the previous section. Add a table item within the list item. Right-click on one of the rows and select Insert Group to add a new group within the table. In the Grouping/Sorting dialog select Model Name and disable group header. Disable the table footer by right-clicking on a row and selecting Table footer. Drag and drop Model Name to the group footer row as shown in Figure 17-34. Drag and drop Product Name and Fact Internet Sales - Unit Price fields to the second and third columns in the detail row as shown in Figure 17-34. Drag and drop Fact Internet Sales - Unit Price to the last column of the group footer row as shown in Figure 17-34. You will notice that Report Designer automatically adds the aggregate function Sum for the measure Fact Internet Sales - Unit Price as shown in Figure 17-34. Drag and drop the Calendar Year field above the table and change BackgroundColor, Color, and FontWeight for table header and group footer rows as shown in Figure 17-34.

   
   Figure 17-34

5. Change the Aggregate function in the group footer row from Sum to Aggregate so that Reporting Services uses the custom aggregate from Analysis Services. When you change the aggregate function to Aggregate then report designer modifies the MDX query to retrieve the aggregated data from Analysis Services. If you switch to the Data tab you will see additional rows are being returned in the Result pane that contain null values for Product Name as shown in Figure 17-35. Reporting Services detects these rows with null values for Product Name as the aggregate rows and appropriately renders the report.

   
   Figure 17-35

6. Click on the Preview tab to see the report. You will see the report shown in Figure 17-36 where the aggregated data for the various Model Names are retrieved from Analysis Services rather than being calculated by Reporting Services.

   Custom aggregates, as mentioned earlier are useful to create reports that need the aggregated data from Analysis Services. You definitely need to use custom aggregates when the aggregate function is not supported by Reporting Services. In addition to that we recommend you use custom aggregates whenever you need aggregated data from Analysis Services because you will see performance benefits, especially when the report retrieves a large set of members from Analysis Services.

Figure 17-36

Deploying Your Report

Using BIDS you have learned to design and preview the reports. However when the reports need to be accessed by the end users, then you need to deploy the reports to a centralized location. This centralized location contains the Report Server which can render the reports to the end users. Access to the functionality of the Report Server is provided through the Report Server Web Service which uses SOAP (Simple Object Access Protocol) over HTTP and exposes interfaces for report execution and report management. When you install Reporting Services, SQL Server 2005 setup sets up a web interface to the report server. You can access your reports and perform management operations through http://<machinename>/reports . In addition to this interface you can perform management operations through SQL Server Management Studio. In this section you will learn to deploy and access reports.

To deploy the reports you designed in the previous section you need to set the location of your report server. Right-click on the solution Report Project Tutorial in solution explorer and select properties. You will see the Property dialog shown in Figure 17-37. Specify http://localhost/reportserver for TargetServerURL also shown in Figure 17-37. Deploy the reports by right-clicking on the solution and selecting deploy.

Figure 17-37

If there were any errors in deployment then you will see the errors in the BIDS Output window. To make sure your reports can be accessed, open internet explorer and go to the URL http://localhost/reports. You will see the reports under the folder Report Project Tutorial. When you select the Analysis Services Report, the report server renders the report and you will see the report as shown in Figure 17-38. Reporting Services allows you to export the report in various formats. If you want to export this report, select the desired format such as PDF, CSV, Excel, etc., and then click Export.

Figure 17-38

Once the reports are deployed to your report server, the next important task is to manage (group, setup security permissions, delivering options) the reports through the web interface or through SQL Server Management Studio which you will learn in the next section.