Linking relational and multidimensional data views in a ShowCase® Analyzer™ report
This is a complimentary tip available to SPSS Direct subscribers.
Question
How can I produce one report that contains data views from multiple data sources? I have a relational data source and a multidimensional data source and would like to link them in one ShowCase Analyzer report.
Answer
ShowCase Analyzer 7.2 enables you to compare, analyze, and disseminate data from two different data sources and then create custom reports. You can create graphical reports, such as a chart, or you can make a spreadsheet. You can also view and drill down into information on a single screen, allowing for quicker analysis and easier side-by-side comparison of data. (To perform this function, however, you must have permission to use Report Designer).
Begin by creating a custom report and designing the report layout:
- Hide the View Pane
- Switch to Report Designer
- Add a panel object to the report layout
- Anchor the panel to the top of the report, and then adjust the size to about 1/10 the height of the report
- Add a label object to the panel
- For the Caption, specify a title and then specify font properties
- Add a split panel to the report layout
- Anchor the split panel in the slack space on the report
- Adjust the divider bar so the panels are of approximately equal size
Next, add a multidimensional spreadsheet report. You can do this by creating a new report and adding a multidimensional spreadsheet component:
- Add a spreadsheet object to the left panel in the report layout
- Select the database connection for your designated database
- In the Cube Navigator, place the desired dimension in the Rows section
- Select the desired siblings to add to rows
- Place desired dimension in Columns section
- Select your desired personal variable
- Anchor the spreadsheet object to the slack space of the left panel
Figure 1: Customizing a spreadsheet object in a two-data-source report.
Once the spreadsheet object is in place, add an SQL relational query.
Then, add a freeform grid component and define an SQL query:
- Add a freeform grid object to the report layout
- The Set Grid Size dialog box is displayed.
- For Number of Rows and Number of Columns, specify desired number
- Anchor the freeform grid object to the slack space of the right panel
- Switch to Analyze View
Figure 2: Analyze View in Report Designer.
- Right click on the grid, and then choose Add SQL Query. The Enter SQL Query dialog box is displayed.
- For the JDBC Driver value, select the appropriate driver for your data source
- In the JDBC Connection String, replace the library name with the appropriate library
- Enter your username and password
Figure 3: Specifying SQL Query options.
- In the SQL Query section, click Query Builder, and you’ll see the SQL Query Builder dialog box
- On the Tables tab, select desired dimensions
- On the Columns tab, select the desired columns from the available list
- You can use advanced features by clicking the available column name and clicking the Add Column Advanced button
- In the Select Column Advanced dialog box, use the Function drop down menu to choose the desired function
- Specify the desired name for the Displayed As value
- Click OK to return to the SQL Query Builder. The new column definition is shown in the Selected Columns list.
- On the Filters tab, click Add. An equal sign is shown in the Comparator column.
- Click Column to show a list of available columns for defining filters, and then select the desired column. You may need to adjust the width of the Columns column and the Values column in order to see the full name/value of each column.
- For Value, select the desired column from the list
Figure 4: Specifying Filter in SQL Query Builder.
- On the Groups tab, select the desired group
- On the Sorting tab, select the appropriate column and then click Add Sort Ascending (or desired sort function)
- Click OK to return to the Enter SQL Query dialog box. The new query definition is shown in the SQL Query box.
Figure 5: Here’s the complete new query definition.
- Click OK to return to the report
- Make any adjustments to columns to show the full values in each field
- If necessary, adjust the width of the panels to show each report
Figure 6: View of the completed report.
Finally, save the report with the desired name and description in the appropriate folder.
For more information on generating reports, please refer to the Web Client Users Guide or online help. For a list of new features in ShowCase Analyzer 7.2, please review the readme.doc included on the installation media.
