Reporting the MDB using MS-SQL 2005 Reporting Services A brief introduction to MDB Reporting and Microsoft SQL 2005 reporting services
Date:
ca.com
March 6, 2007
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
Table of contents
1.1 MS-SQL 2005 Reporting Services 1.1.1 Licensing 1.2 Reporting the MDB 1.3 Installing Reporting Services 2.1 Configure Reporting Services to use a CA Color Template 2.2 Create a project 2.3 Creating a data source 2.4 Create a Simple Report 2.5 Publish the report to the server 2.6 Viewing the Reports 3.1 The Results 4.1 Build a Advance Search View/Table 4.2 Populate the data 4.3 Building a Report from the Output 4.4 Use the View Definition Directly in Report A.1 UAPM Reconciliation Samples A.2 DSM Sample A.3 License Management Light
Document History Date 07-marts-2007
Version 0.1
Author KR
3 3 4 4 5 6 6 7 10 10 12 14 17 17 17 19 20 20
Description First version – Alpha version
Future plans: – More sample reports, on other products – Creating a Chart EXCEPT AS OTHERWISE STATED IN THE APPLICABLE LICENSE AGREEMENT, TO THE EXTENT PERMITTED BY APPLICABLE LAW, CA PROVIDES THIS DOCUMENTATION “AS IS” WITHOUT WARRANTY OF ANY KIND, INCLUDING WITHOUT LIMITATION, ANY IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NONINFRINGEMENT. IN NO EVENT WILL CA BE LIABLE TO THE END USER OR ANY THIRD PARTY FOR ANY LOSS OR DAMAGE, DIRECT OR INDIRECT, FROM THE USE OF THIS DOCUMENTATION, INCLUDING WITHOUT LIMITATION, LOST PROFITS, BUSINESS INTERRUPTION, GOODWILL, OR LOST DATA, EVEN IF CA IS EXPRESSLY ADVISED OF SUCH LOSS OR DAMAGE. The use of any product referenced in the Documentation is governed by the end user’s applicable license agreement. All trademarks, trade names, service marks, and logos referenced herein belong to their respective companies.
Disclaimer The information in this document is subject to change without notice and should not be constructed as a commitment by CA. CA takes no responsibility for any errors that may appear in this document. These materials are provided "as is", for informational purposes only. In no event shall CA or its resellers be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business information, or other pecuniary loss) arising out of the use of or inability to use this job, even if CA has been advised of the possibility of such damages.
r 0.1
Page 2 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
1. Introduction There are many reporting tools to choose from. Some of those tools are highly specialized and designed for specific jobs while others are built to perform more generic reporting tasks. When it comes to selecting the right MDB reporting tool for your requirements, you need to consider: 1. 2.
Whether the company has standardized on a particular reporting tool What the implementation costs would be
Two reporting tools to consider are: -
CA Cleverpath Reporter Microsoft SQL Reporting Services
Although Cleverpath Reporter is not as advanced as some other reporting solutions, it is included with most of the CA tools making it a convenient and cost effective option. Microsoft SQL Reporting Services is also included, provided you are running the MDB under MS-SQL, and relatively easy to use, but it includes the benefit of more advanced reporting options. This document discusses how to use the MS-SQL Server 2005 Reporting Services to report on the CA management data contained in the MDB.
1.1 MS-SQL 2005 Reporting Services As previously mentioned, MS-SQL Server Reporting Services is a reporting solution that is included with Microsoft SQL Server 2005 (MS-SQL 2005) solution. It is also available for MS-SQL 2000; however, the version provided with MS-SQL 2005 is much more integrated, flexible and comprehensive than the earlier version. SQL Server Reporting Services allows you to build reports from Microsoft Visual Studio and easily deploy those reports to a reporting server where they can be viewed, formatted, exported and printed from a simple Web browser.
For more detailed information on MS-SQL 2005 Reporting Services consult the following Microsoft link: http://www.microsoft.com/sql/technologies/reporting/default.mspx 1.1.1
Licensing
MS-SQL Reporting Services is included with all editions of MS-SQL 2005, and it is licensed with the edition you acquired, including the free Express edition. Basically, you need to acquire a license for each machine on which the server software is running. Therefore, for example, if you run the Reporting Services remotely from the SQL database server you will need a license for both servers. Additional licensing information can be found on the following Microsoft How to Buy link:
r 0.1
Page 3 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
http://www.microsoft.com/sql/howtobuy/howtolicensers.mspx Depending on which SQL Server edition you have, there may be differences in the number of design and management tools provided but what each edition has in common is the ability to run dynamic reports from the reporting server. For more information on what features are included in each edition, consult the following Microsoft link: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx Note: Always check the current license rules before deploying.
1.2 Reporting the MDB Every CA product that uses the MDB includes some sort of reporting tool. These tools are primarily product-specific and, in general, do not typically include a high focus on other product data. The MDB, however, is designed to incorporate and integrate all of CA’s management tools in a way that enables you to utilize all of the MDB data through combined queries. There are many useful documents detailing how to combine queries, however, the following document is particularly useful: http://supportconnectw.ca.com/public/impcd/r11/MDBMain/Doc/CORA_MDB_and_Assets_SC.pdf In addition, the MDB Schema Viewer provides in depth details regarding the individual tables used by the MDB. It can be accessed through the following link: http://supportconnectw.ca.com/public/impcd/r11/MDBMain/schema/viewer/index.htm The intent of this document is to provide an introduction to the MS-SQL Reporting Services and how it can be used for MDB reporting. It is not intended to provide cross product details are advanced reporting options. Furthermore, although Reporting Services can use highly complex queries, this document will be using simple Unicenter Desktop and Server Management (DSM) queries in order to keep the focus on the MS-SQL Reporting Services.
1.3 Installing Reporting Services MS-SQL 2005 Reporting Services is not installed on the server by default. It must be selected, either during the initial SQL Server install or after the database installed has completed, by choosing “Reporting Services” and “…..Development Tools” from the installation dialogs.
r 0.1
Page 4 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
2 Using Reporting Services to report on the MDB This section describes how to build a Reporting Project, create a data source, create a report and, finally, deploy it to the reporting server. The steps provided assume that the Reporting Service has been installed, along with the Visual Studio Development tool (which is not available with the Express edition). This only a brief introduction to the Reporting System, but it should be sufficient enough to enable you to build your own reports. For information on more advanced features, consult the online documentation and help provided with the product. Although each step in the process has been documented in a separate section to enable you to more easily find exactly what information you need, you can also choose to use the Create Project Wizard which will walk you through all the necessary steps.
2.1 Configure Reporting Services to use a CA Color Template Since the Reporting Services do not automatically include the CA Template, you will have to register it with your Visual Studio. To do this you need to modify all the “StyleTemplates.xml” files in the Business Intelligence folder. The default location for this is: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles You will also need to modify the files in the language specific subfolders. For example: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles\en Open the file using a text editor and locate the “” string at the end of the file. For example:
Insert the “CA Reporting Services Template add on.xml” file just before this line so that the end result is as follows:
r 0.1
Page 5 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
2.2 Create a project Next, start Microsoft Visual Studio 2005 by selecting All Programs, Microsoft Visual Studio, Microsoft Visual studio 2005 from the Windows Start menu. Once this opens, select Create New Project in the portlet at the right and specify the name and directory. Then, click Save.
You will now see the Project Development Window. The Solution Explorer will appear on the left, allowing you to control what files and settings are available to the project.
2.3 Creating a data source The first thing you need to create for the project is a “data source” which defines where and how to get the data that we are reporting on. To define a new data source, right click on the data source in the Solution Explorer ad Select Add New Data Source.
r 0.1
Page 6 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting Provide a name for the data source and click Edit.
Specify the source database server or indicate ‘(local)’ if you are referring to the local database server. Then, select the preferred authentication method and identify the MDB as the database to connect to. Click OK and verify that the settings in the Data Source dialog are correct before clicking OK again to create the data source. You only need to define the data source once per project; all reports will be able to share this data source.
2.4 Create a Simple Report This section documents how to define a simple tabular report that you can drill into. The report itself is a DSM report that includes brief information about the computer, organized by the vendor name, as a dynamic expandable report. To create this report, right click on Reports in the Solution Explorer and select Add New Report:
Next, select the data source (i.e., the “MDB”). Then, specify the SQL Query that will be used to return the reporting data. To do this you can either simply type the query in the text field or use the point and click query builder. f you press the button, you will get a Microsoft Access-like query builder which can help non-SQL experts select the right data.
r 0.1
Page 7 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
Execute the query by pressing the ‘!’ (exclamation) button. This will display the raw data. When you are satisfied with your query, click OK to return to the reporting wizard.
From here, click Next and select the Report Type - either Tabular or Matrix. For the purposes of this example, we have selected Tabular.
The next step is to select how the data will be arranged. – –
Page: Causes a page shift every time this data changes (one page per data group) Group: Divides the data on the page into groups based on the field. Reporting Services supports up to 3 groups.
r 0.1
Page 8 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting –
Details: Designates where the tabular data goes.
Use the Table Layout dialog to determine if the data should be boxed and expanded. Selecting Enable drilldown allows you to collapse each group and only expand them when the user chooses to. Click Next to continue.
Finally, select the Table Style from the list of options and provide a name for the report. Note!: Since the CA table style is not there by default it will not include a Preview pane. See 2.1 Configure Reporting Services to use a CA Color Template on how to add the CA style to your Visual studio. Click Finish to complete the wizard. You will be presented with the development environment for the report.
Here you can resize fields, change colors, texts and, basically, modify everything on the report. You can also select ‘Preview’ to see how the report will look when it is published.
r 0.1
Page 9 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
2.5 Publish the report to the server Once you are happy with how your report looks, the next step is to publish it to the reporting server so that other users can view it. To do this, first ensure that the Project Properties ‘Reporting Server’ is set to the correct server. Right click on the Project in the Solution Explorer and select Properties:
In this example we are using the local Reporting server. You can now deploy any of the project components or the whole project simply by right-clicking on the component/project and selecting Deploy. The result is shown in the output window, in the lower Right hand corner of the screen:
2.6 Viewing the Reports Once the report has been published, you can access it online through the following link: http://localhost/reports
r 0.1
Page 10 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting The result will be similar to the following, where you can then browse to the report using the menu selections:
You can collapse and expand the groups by pressing the + (plus) sign.
r 0.1
Page 11 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
3 Reporting on DSM Result Sets This section discusses options for using the Unicenter DSM report to de-normalize the data so that users with no DSM data mode knowledge can build a report on the DSM data. This section does not document the entire process of building, running, exporting or saving the results. Please refer to the following link for more details on building DSM reports and working with the results: http://supportconnectw.ca.com/public/impcd/r11/MDBMain/downloads/DSM%20Reporting%20%20Using%20external%20tools.zip
3.1 The Results All results from the DSM report are saved in 2 tables.
Rxxxxxxx: Which contains the data from the Result set Rxxxx_M: Which contains all the labels for all the tables (e.g., the Column called GeneralInven_Speed_I00200, has a label called: Processors[1st]>Speed). This is also known as the “mapping table.”
Here is an example of an Rxxxxx table using the following simple SQL command:
Note: These tables are created under the DSM user: ‘ca_itrm’. Therefore, you need to use that as a prefix when building your reporting query. Use the steps documented earlier in section 2.4 Create a Simple Report to build the reports you need. Remember that you can rename the column headers in the layout editor. The following example shows 2 changed and 3 unchanged headings.
r 0.1
Page 12 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
r 0.1
Page 13 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
4 Reporting on UAPM Data using Advance Search Reporting on Unicenter Asset Portfolio (UAPM) can be done in many ways. UAPM provides an ad-hoc query system that is referred to as “Advanced Search.” The Advanced Search function allows you to build advanced views in the UAPM GUI, but it also allows you to build a database table or database view with the result data for outside access. The advantage of using the Advanced Search is that it allows users with no advanced insights into the UAPM data model to easily define a database query to list specific data. This section will show you how to build a simple software asset report using a database view and Reporting Services.
4.1 Build a Advance Search View/Table To use the Advanced Search feature, first open the UAPM Web client and select Asset.
This will launch the Asset Search dialog. Select the Advanced Search tab and you will see a list of all the searches that were previously built.
Select New to access the Search Builder dialog.
Select the appropriate Display Fields and Criteria Fields as needed. Next, you will need to define the criteria of the view (in this case Asset Type = Software):
r 0.1
Page 14 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
To register the criteria in the lower part of the screen you need to remember to click the Add Criteria button. You can test your search by selecting the Search Results tab. This will display the output from the query.
You can then toggle back and forth between the Search Builder and Search Results tab to refine the search. Once you are satisfied with the search, select the Search Builder tab and click the Save button in the top of the screen.
Use the Advanced Search Save dialog to provide a name for the search. If you scroll down to the bottom of the screen you will find the Export Details field which allows you to expose the data outside of the product. By default, no Exports is defined..
r 0.1
Page 15 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting Type options include: View: A database view is one way to query the data. Database views encapsulate an advance SQL script and expose it to the user just like a table. The advantage of selecting a view is that it will resolve the query every time the view is accessed. Select View if you what the data to be dynamic. Table: A database table creates a totally new table in the MDB to which all the output data will be copied. Unlike a view, a table is not evaluated on the next select. Select Table if you need static data (for example, Snapshots or Monthly listings). The Table Name is the name of the Table/View in the MDB database. It is recommended that you keep the suggested prefix of ‘UCAPMR_’. The Refresh Interval indicates how often the data warehouse application will update the table data or recreate the database view. Select ‘Disable’ if you do not want any action or select ‘Every Run’ if you require constant data update. Otherwise, select the update rate you need.
r 0.1
Page 16 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
4.2 Populate the data Once the Advanced Search has been built you need to populate the database. Regardless of whether you have selected to output the results to a table or a view, you still need to run the Data Warehouse application to create the database. To so this select, All Programs, Computer Associates, Unicenter, Asset Portfolio Management, Data Warehouse from the Start menu.
This application will take a little while to run to populate the MDB with the table data or view. Following is an example of the view from the above Advanced Search.
Remember that the update rate is specified by the Refresh Interval (discussed in the previous section).
4.3 Building a Report from the Output You can now build a report based on the View by following the steps detailed in 2.4 Create a Simple Report earlier in this guide. The result will be similar to the following:
4.4 Use the View Definition Directly in Report Although using the View is the easiest method to build a report, if you intend to move the report to other installations, it might be easier to include the full SQL query with in the reporting definition. This section documents how to get the SQL script behind the View and use it in the report. First, open the SQL Server Management Studio and browse to the Databases section. Locate the MDB and expand that selection. Select and expand Views and locate the specific view for which you need to obtain the script (‘UCAPMR_SW_Assets’ in the following example). Then, right-click on that view and select Script View as, CREATE To, New Query Editor Window from the menu.
r 0.1
Page 17 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
This will open a new window from which you can change the View and execute the script to redefine the View. To get the query itself you need to delete all that has to do with the View and save the actual SQL statement.
To do this, locate the first occurrence of ‘AS SELECT’ then delete everything before ‘SELECT’. You will now have the SQL statement that shows the data. To test this, press the ! Execute button to display the data. You can now build your report as you have done before in this section, but, when you get to the Query dialog, paste the SQL statement into the text field. For example:
r 0.1
Page 18 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
Appendix A - Examples This section contains a set of demo files, for UAM and UAPM reporting. You can load the samples by right-clicking on Reports in the Solution Explorer and selecting Add, Existing Item. From there you can browse the file/files, select one or more of those files and click Add.
Then, deploy the reports to the Web server as previously described. Note: The MDB data source must be predefined as a shared data source. Following are additional examples.
A.1 UAPM Reconciliation Samples
r 0.1
Page 19 of 20
Reporting Using MS-SQL 2005 Reporting services A brief introduction to MDB Reporting
A.2 DSM Sample
A.3 License Management Light The following examples require the installation of “License Management Light” which is documented separately.
r 0.1
Page 20 of 20
