Building a Localized Report on a SQL Server 2005 Analysis Service Cube Data Source
SQL Server 2005 Analysis Services
SQL Server 2005 Reporting Services
Summary: Learn how to use an Analysis Services cube as a data source by using parameterized MDX queries, as well as how to localize label strings in a report, in SQL Server 2005. (17 printed pages)
Creating the Delivery Analysis Report
Uploading Reports Using .NET Assemblies
This article presents an excerpt from the book, Microsoft SQL Server 2005 Reporting Services, by Brian Larson. Learn how to use an Analysis Services cube as a data source via parameterized MDX queries as well as localize label strings in a report. To access all the sample code in this article, go here.
Galactic Delivery Services
The sample reports in this excerpt are based on the business needs of a company called Galactic Delivery Services (GDS). To better understand these sample reports, some background on GDS is presented here.
GDS provides package-delivery service between several planetary systems in the near galactic region. It specializes in rapid delivery, featuring same-day, next-day, and previous-day delivery. The latter is made possible by its new Photon III transports, which travel faster than the speed of light. This faster than light capability allows GDS to exploit the properties of general relativity and actually deliver a package on the day before it was sent.
Despite GDS's unique delivery offerings, it has the same data-processing needs as any more conventional package-delivery service. It uses a SQL Server 2005 database to track packages as they are moved from one interplanetary hub to another. It also uses a SQL Server 2005 Analysis Services database to host a data mart used for business intelligence applications. In addition, SQL Server 2005 Reporting Services is used to create and manage reports from both data stores.
Creating the Delivery Analysis Report
Features Highlighted by This Report
The following features of Microsoft SQL Server 2005 Reporting Services are highlighted during the creation of the Delivery Analysis Report:
- Using an Analysis Services cube as a data source via an MDX query.
- Parameterizing an MDX query.
- Localizing the label strings in a report.
The Galactic Delivery Services long-range planning committee is working on forecasting the equipment and work force needs that are necessary for future growth. They need a report showing the number of deliveries and the average weight of those deliveries grouped by customer by quarter. They would also like to select whether the data includes next day deliveries, same day deliveries, previous day deliveries, or some combination of the three. The data for this report should come from the GalacticDeliveriesDataMart cube hosted by Microsoft SQL Server 2005 Analysis Services.
There are committee members from a number of planets. Most speak English, but the committee does include several Spanish-speaking members. (I know that is rather strange for people in a galaxy far, far away, but most of you don't have the Borlaronese and Noxicomian languages loaded in Windows, so English and Spanish will work much easier.)
The Delivery Analysis Report is created through the completion of two tasks:
- Copy the .NET Assembly into the Appropriate Location.
- Create a New Report.
- Create a Reference to the Assembly.
- Create a Dataset using the MDX Query Designer.
- Add a Table to the Report Layout.
- Populate the Table.
- Localize the Report Strings.
Delivery Analysis Report, Task 1
Note You need to download the GalacticOLAP project and deploy it to a SQL Server Analysis Services server before you can complete this report.
If you have not already done so, download the ReportUtil.dll and its source code.
Copy this file to the Report Designer folder. The default path for the Report Designer folder is: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies.
Launch the Microsoft .NET Framework 2.0 Configuration program from the Administrative Tools section of your Control Panel.
Click Configure Code Access Security Policy.
Click Increase Assembly Trust.
Select Make changes for this computer and click Next.
Click Browse. Browse to the PublicAssemblies folder and select ReportUtil.dll. Click Open, and then click Next.
Select This one assembly and click Next.
Change the Trust level slider to Full Trust and click Next.
Close the .NET Framework 2.0 Configuration program.
Reopen the Chapter08 project in the Report Designer if it was closed.
Create a new report called DeliveryAnalysis using the GDSReport template.
Open the Report Properties dialog box. Select the References tab.
Click … next to the References area. The Add Reference dialog box appears.
Scroll down and select the ReportUtil assembly. Click Add to add this file to the Selected projects and components list.
Click OK to exit the Add Reference dialog box. Click OK to exit the Report Properties dialog box.
Create a new dataset called DeliveryInfo. Select New Data Source from the Data source drop-down list. The Data Source dialog box appears.
Enter GalacticDM for the Name. Select Microsoft SQL Server Analysis Services from the Type drop-down list.
Click Edit next to the Connection string text box. The Connection Properties dialog box appears.
Enter the name of the SQL Server Analysis Services server for Server name.
Select GalacticOLAP from the Select or enter a database name drop-down list. You can test the connection if you like, but if GalacticOLAP shows up in the drop-down list, the connection has already been tested.
Click OK to exit the Connection Properties dialog box. Click OK to exit the Data Source dialog box. Click OK one more time to exit the Dataset dialog box. The MDX Query Designer appears, as shown in Figure 1.
Figure 1. The MDX Query Designer
Expand the Measures in the Metadata pane. Expand the Delivery measure group and expand the Delivery entry within it.
Drag the Delivery Count measure onto the Results pane (the pane with the words Drag levels or measures here to add to the query in the center). The total count of all deliveries currently in the GalacticDeliveriesDataMart cube is shown in the Results pane.
Expand the Customer dimension in the Metadata pane. Drag the CustomerName attribute onto the Results pane to the left of the Delivery Count. The Results pane now shows the total count of all deliveries for each customer.
Expand the Time dimension in the Metadata pane. Drag the Delivery-Quarter attribute onto the Results pane to the left of the CustomerName column. The Results pane now shows the total count of all deliveries for each customer for each quarter.
Right-click in the Calculated Members pane and select New Calculated Member from the Context menu. The Calculated Member Builder dialog box appears.
Enter AvgWeight for the Name.
In the Expression area, enter ROUND(. Expand the Measures in the Metadata area, expand the Delivery measure group, and then expand the Delivery entry within it. Double-click Package Weight to add it to the expression.
Enter / at the end of the expression. Double-click Delivery Count to add it to the expression.
Enter ,2) at the end of the expression and click Check to check the syntax of the expression. Click OK to close the Check Syntax dialog box. Make any corrections to the expression, if a syntax error is encountered.
Click OK to exit the Calculated Member Builder dialog box.
Drag the AvgWeight calculated member onto the Results pane to the right of the Delivery Count.
In the Filter pane (the pane in the upper-right corner of the MDX Query Designer), select Service Type from the drop-down list in the Dimension column.
Select Description from the drop-down list in the Hierarchy column.
Select Equal from the drop-down list in the Operator column.
Examine the values in the drop-down window in the Filter Expression column, but do not make a selection. The Filter Expression column enables you to select one or more values for the right side of the filter expression. Instead of doing this at design time, we let our users make the selection at run time. Click Cancel to exit the drop-down window.
Check the box in the Parameters column. This selection enables the user to select the values of the filter expression at run time. The MDX Query Designer should appear, as shown in Figure 2.
Figure 2. The MDX Query Designer containing the query for the Delivery Analysis Report (Click on the image for a larger picture)
Task 1 Notes
The MDX Query Designer works almost entirely through drag-and-drop. We drag measures, dimensions, and hierarchies from the Metadata Browser pane and place them in the Results pane to create our query. We can define calculated members and add them to the Results pane, as well.
At the top of the Metadata pane is the name of the cube being queried. To select a different cube, click the … button and make a selection from the Cube Selection dialog box that appears.
Notice that two refresh buttons are in the toolbar for the designer. The refresh button on the left refreshes the fields for this dataset in the Datasets window. The refresh button on the right refreshes the metadata from the cube. The toolbar button with the pickaxe switches from the MDX Query Designer to the DMX Query Designer. The toolbar button with the x-axis and y-axis switches back to the MDX Query Designer. The MDX Query Designer is used to query cubes in an Analysis Services database, while the DMX Query Designer is used to query data mining models in an Analysis Services database. Because the same Analysis Services database may contain both cubes and data mining models, the Report Designer may not be able to tell which query designer you need simply by examining the database. Therefore, it is necessary to have a way to switch between the two.
The Show Empty Cells toolbar button toggles between showing and hiding empty cells in the Results pane. An empty cell is a combination of dimension and hierarchy members that have a null value for every measure, calculated or otherwise, in the Results pane. If empty cells are hidden in the Results pane, they are also hidden in the final report query. The Design Mode toolbar button enables you to toggle between the design view and the query view of the MDX query. If you are comfortable with MDX query syntax, you may want to type your queries into the query view rather than creating them through the drag-and-drop programming method of the design view. The Auto Execute toolbar button toggles autoexecute mode in the query designer. When autoexecute mode is on, the cube is re-queried and the Results pane is updated every time an item is added to or removed from the Results pane.
The Filters pane enables us either to hardcode filter expressions at design time or use report parameters for the user to make selections at run time. When the Parameters check box is checked, a parameterized filter is created. Several things happen when we move from the Data tab to the Layout tab for the first time after a parameterized filter has been added to the query. When this occurs, the Report Designer creates a new dataset for each item being used in a parameterized filter. This dataset includes all the valid members of that item.
In addition to the datasets, new report parameters are created for each parameterized filter. The datasets are used to populate the available values for these report parameters. The report parameters are multi-valued. Using this mechanism, the user is allowed to select one or more valid members to be used in the parameterized filters at the time the report is executed.
Delivery Analysis Report, Task 2
Select the Layout tab.
Place a text box onto the body of the report and set its properties as follows:
Property Value Font: FontSize 20 pt Font: FontWeight Bold Location: Left 0 in Location: Top 0 in Size: Width 5.875 in Size: Height 0.375 in
Set the content of the text box to the following expression:
Place a matrix onto the report body.
Expand the DeliveryInfo dataset and drag DeliveryQuarter into the Columns cell. Click the Bold button and the Center button in the toolbar.
Drag CustomerName into the Rows cell. Click the Bold button in the toolbar.
Drag Delivery_Count into the Data cell.
Drag AvgWeight into the same cell in which Delivery_Count was placed. Drag it to the right side of the cell. This creates a second data column to the right of the first.
Enter the following expression in the text box that contains Delivery:
Enter the following expression in the text box that contains AvgWeight:
Open the Report Parameters dialog box. You can see the ServiceTypeDescription report parameter, which was created to work with the parameterized filter.
Enter Select Service Types for the Prompt.
Click OK to exit the Report Parameters dialog box.
Select the Preview tab. Check All in the Select Service Types drop-down list and click View Report. The report is shown in Figure 3.
Figure 3. The Delivery Analysis Report (Click on the image for a larger picture)
Select Save All from the toolbar.
Task 2 Notes
You may have noticed we did not type text strings for the report title and the two column headings on the report. Instead, we used expressions that call the LocalizedString method of the Localization class in the ReportUtil assembly. (Localization refers to the process of making a report or computer program appear in the language of a certain location.) This method requires two parameters: the name of the string to localize, and the language it should be localized into. The string name is hardcoded in each expression. The language comes from the User!Language global variable. This global variable is populated with the language of the client application requesting the report.
The ReportUtil assembly uses multiple resource files to manage the localization. One resource file exists for each language it must support. In the demonstration code supplied for this example, the ReportUtil only has two resource files: one for English and one for Spanish. To support another language, you simply need to add another resource file and rebuild the project.
We used the LocalizedString method to get localized versions of the report title and the two column headers. The remainder of the report content are either proper names or numeric. Neither of these need to be translated. If you are sharp, you will notice the report parameter prompt and the items in the report parameter drop-down list have not been localized. We cannot use expressions for either of these items, so we cannot use our nifty LocalizedString method.
The drop-down list content is selected from the database, so some localization of the data could be done as part of the query. The report parameter is a bigger problem. In fact, the current version of Reporting Services does not have a nice way to deal with this.
Uploading Reports Using .NET Assemblies
Now, let's look at the steps necessary to move a report that references a .NET assembly to the Report Server.
Copying the .NET Assembly to the Report Server
For a report to access a .NET assembly, it must be in the application folder of the Report Server. No fancy deployment, upload, or installation routine is required here. Simply copy the assembly's DLL file to the appropriate directory. We can give this a try using the Delivery Analysis Report and its .NET assembly, ReportUtil.dll. Here are the steps to follow:
Locate the ReportUtil.dll file. You also need the ES folder that contains the Spanish version of the ReportUtil.dll. This Spanish version is called ReportUtil.resources.dll. (The folder name, ES, is the two-letter code for Español.) If you do not have them anywhere else, they should be in the Public Assemblies folder on your development computer. The default path for the Public Assemblies folder is:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies.
Copy and paste the file and the ES folder into the Report Server application folder on the computer acting as your Report Server computer. The default path for the Report Server application folder is:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ ReportServer\bin.
Code Access Security
Because Reporting Services is a .NET application, it uses code access security to determine what execution permissions are possessed by each assembly. A code access group associates assemblies with specific permissions. The criteria for membership in a code access group is determined by a security class, and the permissions are determined by named permission sets.
Figure 4 provides an illustration of code access security. A .NET assembly or Web service can gain entry into a code access group only if it matches the criteria specified by the security class. Once the .NET assembly or Web service is allowed into a code access group, it can use the named permission set associated with that code access group to gain rights. These rights allow the .NET assembly or Web service to perform tasks on a computer. A number of different types of rights can be included in a named permission set.
Figure 4: Code Access Security
Code access groups can be nested one inside another. A .NET assembly or Web service can be allowed into a parent group and gain its permissions; then it can try to gain membership in child code access groups to accumulate additional rights. A code access group can be a first match code group, in which a .NET assembly or Web service can only gain membership in one code access group—the first one it matches. Or, a code access group can be a union code group, in which a .NET assembly or Web service is allowed to gain membership in a number of code access groups, joining together the permissions from each group.
A security class describes the conditions a .NET assembly or Web service needs to meet to get into a code access group. We use the StrongNameMembershipCondition security class for the ReportUtil.dll assembly.
The UrlMembershipCondition security class says that any assembly or Web service being executed from a specified URL is to be included in a particular code access group. The URL that must be matched is listed in each code access group using the UrlMembershipCondition security class. For example, the MyServer code access group may use UrlMembershipCondition and give http://MyServer/* as the URL that must be matched. Any Web service running on MyServer would be included in this code access group.
The StrongNameMembershipCondition security class uses the strong name associated with an assembly to identify it. The strong name, which is a long string of hexadecimal digits that uniquely identifies an assembly, is assigned to the assembly when it is created. The StrongNameMembershipCondition security class is a good way to ensure that only the intended assembly is allowed into your code access group.
You see a couple of other security classes in the Report Server security configuration. The AllMembershipCondition security class allows in all .NET assemblies and Web services. The ZoneMembershipCondition security class allows in .NET assemblies and Web services that originate in a particular zone. Some sample zones are MyComputer, intranet, and Internet.
Named Permission Sets
Named permission sets group together the permissions to be assigned by code access groups. The security configuration used by the Report Server contains three named permission sets:
- The Nothing permission, which grants no rights, is used to initially take away all rights from a .NET assembly or Web service before specific rights are added back by subsequent code access groups. This ensures each .NET assembly or Web service has only the rights it should have.
- The Execution permission grants execution rights to a .NET assembly or Web service. This means the .NET assembly or Web service can be run. The .NET assembly or Web service does not, however, have rights to access any protected resources, such as the file system, the registry, or the Internet.
- The FullTrust permission grants the .NET assembly or Web service access to everything. This includes access to all the protected resources. FullTrust permission should only be granted to .NET assemblies and Web services that you trust not to mess up your computer!
Modifying the Report Server's Security Configuration
Now that you have a basic understanding of code access security, we can modify the Report Server's security configuration to allow the ReportUtil.dll to run.
Caution Consult with your network or server administrator before making any changes to server security.
We need to make some additions to the Report Server's security configuration to provide our custom assemblies with the rights they need to execute. The security configuration for the Report Server is in the rssrvpolicy.config file. The default path for this file is:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ ReportServer.
This file contains the code-access security information in an XML structure.
Caution Make a backup copy of the rssrvpolicy.config file before making any modifications to it. If you accidentally create an invalid XML structure or otherwise cause a problem with the security configuration, the Report Server cannot execute any reports.
The XML structure in the rssrvpolicy.config file can be divided into three sections: Security Classes, Named Permission Sets, and Code Groups. We only need to make changes to the Code Groups section of the document. Here are the steps to follow:
Open the rssrvpolicy.config file in Notepad or another text editor.
Scroll down until you locate the Code Group portion of the document. The Code Group portion of the document starts on the line after the closing XML tag for the named permission sets:
The first code group is the parent code group, which makes use of the AllMembershipCondition to assign the Nothing permission to all .NET assemblies and Web services. Another parent code group uses ZoneMembershipCondition to assign Execute permissions to all .NET assemblies and Web services in the MyComputer zone. We add a new child code group right beneath this. Insert this new code group as shown here (add the lines shown in bold). Note, the Description and PublicKeyBlob should each be entered on one line.
<CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Execution" Description="This code group grants MyComputer code Execution permission. "> <IMembershipCondition class="ZoneMembershipCondition" version="1" Zone="MyComputer" /> <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="MSSQLRSCodeGroup" Description="Code group for the MS SQL RS Book Custom Assemblies"> <IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="0024000004800000940000000602000000 240000525341310004000001000100B9F7 4F2D5B0AAD33AA619B00D7BB8B0F767839 3A0F4CD586C9036D72455F8D1E85BF635C 9FB1DA9817DD0F751DCEE77D9A47959E87 28028B9B6CC7C25EB1E59CB3DE01BB516D 46FC6AC6AF27AA6E71B65F6AB91B957688 6F2EF39417F17B567AD200E151FC744C6D A72FF5882461E6CA786EB2997FA968302B 7B2F24BDBFF7A5" /> </CodeGroup> <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Microsoft_Strong_Name" Description="This code group grants code signed with the Microsoft strong name full trust. "> <IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="0024000004800000940000000602000000 24000052534131000400000100010007D1 FA57C4AED9F0A32E84AA0FAEFD0DE9E8FD 6AEC8F87FB03766C834C99921EB23BE79A D9D5DCC1DD9AD236132102900B723CF980 957FC4E177108FC607774F29E8320E92EA 05ECE4E821C0A5EFE8F1645C4C0C93C1AB 99285D622CAA652C1DFAD63D745D6F2DE5 F17E5EAF0FC4963D261C8A12436518206D C093344D5AD293" /> </CodeGroup>
Save the modified file and exit your text editor.
Deploying the Report
We want to have just one shared data source for each unique connection that is needed by our reports. This small group of shared data sources should be placed in one central location. That still leaves us with the task of manually pointing each report at the central group of shared data sources after each report upload.
There is a property on the report project's Property Pages dialog box specifying the folder path at which the shared data source is to be deployed. Let's try deploying the Delivery Analysis Report from the Report Designer while making use of this property. Try the following:
- Open the Chapter08 project in Visual Studio or the Business Intelligence Development Studio.
- From the Main menu, select Project | Chapter08 Properties. The Chapter08 Property Pages dialog box appears.
- Enter /Galactic Delivery Services/Shared Data Sources/ for the TargetDataSourceFolder.
- Enter /Galactic Delivery Services/Chapter 08/ for the TargetReportFolder.
- Enter http://ComputerName/ReportServer for TargetServerURL. Substitute the name of the computer hosting Reporting Services for ComputerName.
- Click OK to close the Chapter08 Property Pages dialog box.
- Select Save All from the toolbar.
- Right-click the entry for the DeliveryAnalysis report in the Solution Explorer window and select Deploy from the Context menu.
- Switch to the browser and navigate to the Chapter 08 folder.
- Execute the DeliveryAnalysis report.
- Select a number of service types from the drop-down list and click View Report. The report displays using the shared data source found in the Shared Data Sources folder.
A Look at Localization
You may recall we used the ReportUtil.dll assembly to present the report labels in both English and Spanish. We passed the User!Language parameter to the LocalizedString method to retrieve a report label in the appropriate language. The User!Language parameter contains the language setting for the application requesting the report. When we are using the Report Manager, the browser is that application.
Let's try changing the language setting of the browser and see if our localization works the way it should. (The following directions apply to Microsoft Internet Explorer.)
Select Tools | Internet Options from Internet Explorer's Main menu. The Internet Options dialog box appears.
Click Languages. The Language Preference dialog box appears.
If an entry for Spanish (Mexico) [es-mx] is not in the Language list, click Add. The Add Language dialog box appears.
Highlight Spanish (Mexico) [es-mx] in the Language list and click OK to exit the Add Language dialog box.
Highlight Spanish (Mexico) [es-mx] in the Language list and click Move Up as many times as necessary to move the Spanish entry to the top of the list.
Click OK to exit the Language Preference dialog box. Click OK to exit the Internet Options dialog box.
Click the Refresh Report button in the Report Viewer toolbar. The User!Language parameter now has a value of es-mx because you set the primary language of your browser to Spanish (Mexico). Because of this, the title of the report and the column headings are now Spanish, as shown in Figure 5.
Figure 5. The Delivery Analysis Report with title and column headings localized in Spanish (Click on the image for a larger picture)
Use the Language Preference dialog box to delete the Spanish entry, if you created it in Steps 3 and 4. Make sure you return the correct language to the top of the Language list.
The ReportUtil.dll assembly has resource files for English and Spanish. English is the default language. If the parameter passed to the LocalizedString method is any of the cultural variations of Spanish, the method uses the Spanish resource file to look up the text for the report title or a column heading. If anything else is passed to the LocalizedString method, the English resource file is used.