Queries
specify what data appears in the report. In Report Studio, you create
and modify queries using Query Explorer 
. Query Explorer provides an alternative way
to modify existing reports or to author new reports. You can use
Query Explorer to perform complex tasks and other tasks that are
more difficult to do in the report layout. For example, use Query
Explorer to
improve performance by changing the order in which items are queried from the database, or by changing query properties to allow the report server to execute queries concurrently where possible
Note: By default, queries run sequentially. Your administrator must enable the concurrent query execution feature. For more information, see the Administration and Security Guide.
view or add filters and parameters and modify their properties
view or add dimensions, levels, and facts
incorporate SQL statements that come from other reports or reports that you write
create complex queries using set operations and joins
Specify the list of data items for an object when you must reference a data item that is in a query but is not in the layout. For example, you want to add a layout calculation to a list that uses a data item that is in the query definition. If the data item does not appear in the list, you must reference it in order to make the layout calculation work.
You must also specify the list of data items if you
apply conditional formatting that uses a data item that is not in the
query.
Click a layout object.
Tip: For a list of layout objects for which you can specify a list of properties, see the Properties property in Report Studio Object and Property Reference.
In the Properties pane, double-click the Properties property and select data items.
Queries and layouts work together. After you decide the type of data that you need, you must create a layout in which to show the results. Each column of data must be both selected for the query and shown in a layout unless there are some columns that you don't want to show. The query and layout portions of a report must be linked to produce a valid report.
Report Studio automatically links query and layout. For example, when you use Report Studio and the list report layout, query and layout are automatically linked.
Select a data container.
In the Properties pane, set the Query property to a query.
In the Insertable Objects pane, on
the Data Items tab 
,
drag data items from the query to the data container.
When you are working with SAP BW data sources, you can use only a single hierarchy in a query.
Creating queries using a mix of OLAP and relational data is not supported. If you create queries using a database for which you do not know the type, consult your database administrator or modeler.
When performing multi-cube queries using dimensional data sources, the following restrictions apply:
Only basic operators (+, *, /,-) are available for cross-cube calculations.
Inner joins are not supported.
All joins for multi-cube queries are outer joins.
You cannot sort or filter on a conformed dimension (query subject).
Conformed dimensions are created in Framework Manager.
Viewing MDX using the Generated SQL/MDX query property may not show you the actual MDX that is executed. This is because when a multi-cube query is executed, a number of smaller queries are actually executed.
IBM Cognos 8 supports the following:
RDBMS to RDBMS joins
set operations of any two queries
master detail relationships between any two queries
drill from any query to any other query
You cannot create the following types of joins:
cube-to-cube (homogeneous)
cube-to-cube (heterogeneous)
cube-to-RDBMS
cube-to-SAP BW
SAP-BW-to-RDBMS
You must understand the result of a query on more than one fact table to achieve the results that you want. The result of a multiple-fact query varies depending on whether you are working with conformed or non-conformed dimensions, on the level of granularity, and on the additive nature of the data.
The following data source has characteristics that affect the results if you use a multiple-fact query with Inventory levels and Sales. Granularity for time differs in that inventory levels are recorded monthly and sales are recorded daily. Also, Sales includes a non-conformed dimension, Order method.
The following examples will help you interpret the results of a multiple-fact query and understand the options for changing a query to obtain the results that you want.
Individual queries on Inventory levels and Sales by Quarter and Product yield the following results.
A query on multiple facts and conformed dimensions respects the cardinality between each fact table and its dimensions and returns all the rows from each fact table. The fact tables are matched on their common keys, Product and Time.
Product and Time apply to both Inventory levels and Sales. However, inventory levels are recorded monthly and sales are recorded daily. In this example, results are automatically aggregated to the lowest common level of granularity. Quantity, which comes from Sales, is rolled up to months.
Nulls are often returned for this type of query because a combination of dimensional elements in one fact table may not exist in the other. For example, if Husky Rope 50 was available in inventory in 200501, but there were no sales of this product in the same time period, Quantity would show a null in the Husky Rope 50 row.
If a non-conformed dimension is added to the query, the nature of the results returned by the query is changed.
Order Method exists only in Sales. Therefore, it is no longer possible to aggregate records to a lowest common level of granularity because one side of the query has dimensionality that is not common to the other side of the query. Opening inventory and Closing inventory results are repeated because it is no longer possible to relate a single value from these columns to one value from Quantity.
Grouping on the Quarter key demonstrates that the result in this example is based on the same data set as the query on conformed dimensions. Summary values are the same. For example, the total quantity for 200501 is 2,766 in both examples.
By default, to improve performance, filters are applied at the database level. The default behavior can result in unexpected nulls when you add a filter to a multiple-fact query. If you create a filter on the Order method dimension to show only the fax order method and apply the filter at the data source, the report includes nulls.
The filter is applied only to one subject area, Sales. Because Order method does not exist in Inventory levels, all products still appear in the report. For example, Course Pro Umbrella was in inventory in 200602. Because there were no sales using the fax order method for this product in 200602, Order method and Quantity are null.
To remove the nulls, change the filter so that it is applied to the result set instead of the data source. By applying the filter to the result set, you get the same results that you would get if you were working with conformed dimensions. Only the products that were ordered using the fax order method appear in the report.
The summary for quantity is 986 using either filter method, which shows that the results are based on the same data set.
In Report Studio, there are two types of filters. A detail filter is applied to the data source. A summary filter is applied to the result set. For more information about how to apply detail and summary filters, see Create a Detail or Summary Filter.
You can create multiple queries in Query Explorer to suit your particular needs. For example, you can create a separate query for each data container in a report to show different data.
Tip: The Briefing Book sample report in the
GO Sales (analysis) package and the Top 10 Retailers for 2005 sample
report in the GO Data Warehouse (analysis) package include multiple queries.
For more information about The Great Outdoors Company samples, see Sample Reports and Packages.
Pause the pointer over the query explorer button 
and
click Queries.
In the Insertable Objects pane, drag one of the following objects to the work area.
In the Properties pane, set the object properties.
Double-click a query.
In the Insertable Objects pane, on
the Source tab 
, drag data
items to the Data Items pane.
To create a new data item, in the Insertable
Objects pane, on the Toolbox tab 
,
drag Data Item to the Data Item pane.
To add a filter, in the Insertable Objects pane,
on the Toolbox tab, drag Filter to the Detail
Filters or Summary Filters pane
and define the filter expression .
Tip: You can also create a filter by dragging a data item from the Source tab to one of the filters panes and completing the filter expression. If you are working with a dimensional data source, you can quickly filter data by dragging a member to a filter pane instead of dragging the level to which the member belongs. For example, dragging the member 2006 from the Years level will filter data for the year 2006. This method is quicker than dragging the Years level and specifying data only for the year 2006 in the filter expression.
When adding queries to the report
right-click the work area and click Show Package Sources to see the queries that use data items from a package
right-click the work area and click Expand References to see the relationships that exist between queries in the report, which is useful when you are creating complex queries
You can add dimension information to a query if any of the following apply:
There is no dimension information available in the data source.
For example, the data source contains flat data that you want to model dimensionally.
You want to override the dimension information in the data source.
You want to extend or restrict dimension information in the data source.
If you do not add dimension information and the data source contains dimensions, then the dimension structure of the data source is used. If the data source contains no dimensions, IBM Cognos 8 creates a default dimension structure.
Dimension information is not intended to define the
presentation of information, but to help query planning. Dimension
information can be considered as a form of query hint. In addition, users
will not be able to drill down on dimensions that you add to a report.
Pause the pointer over the query explorer button and
click a query.
In the Properties pane, set the Override Dimension Info property to Yes.
The Dimension Info tab appears in the work area.
Click the Dimension Info tab.
To create a dimension from an existing data item, in the Insertable
Objects pane, on the Source tab ,
drag the data item to the Dimensions pane.
Report Studio automatically generates the entire dimension.
To create a new dimension, in the Insertable
Objects pane, on the Toolbox tab , drag Dimension to
the Dimensions pane, and then build the new
dimension by adding objects to the dimension:
To create a level, drag Level.
To create a level hierarchy, drag Level Hierarchy.
To create a member hierarchy, drag Member Hierarchy.
For information about these objects, see Add Dimensional Data to a Report.
In the Insertable Objects pane, on the Source tab, drag data items to the objects you added in the previous step.
For example, if you created a level, define a key by dragging a data item to the Keys folder.
To create a fact, in the Insertable Objects pane, on the Source tab, drag a data item to the Facts pane.
You are a report author at The Great Outdoors Company, which sells sporting equipment. You are requested to create a crosstab report that shows the total revenue per year for each product line broken down by order method. By default, if no revenue was produced for a particular product line in a specific year, no row appears in the crosstab for that product line and year. You override the dimension information of the crosstab so that empty rows appear in the report.
Open Report Studio with the GO Data Warehouse (query) package.
In the Welcome dialog box, click Create a new report or template.
In the New dialog box, click Crosstab and click OK.
In the Insertable Objects pane, on
the Source tab , drag the
following data items from Sales and Marketing (query), Sales
(query) to the crosstab:
from the Product folder, drag Product line as rows
from the Time dimension folder, drag Month as nested rows
from the Order method folder, drag Order method as columns
from the Sales fact folder, drag Revenue as the measure
Right-click the crosstab and click Go to Query.
In the Properties pane, set the Override Dimension Info property to Yes.
The Dimension Info tab appears at the bottom of the work area.
Click the Dimension Info tab.
In the Insertable Objects pane, on the Source tab, drag the following items to the Dimensions pane:
Product line and Month become separate dimensions in the query.
Pause the pointer over the page explorer button 
and
click Page1.
Click Month.
In the Properties pane, double-click the Sort property.
In the Data Items box, drag Month to the Sort List box.
Run the report.
All order years appear for all product lines, even if no revenue was produced.
Create a master detail relationship to deliver information that would otherwise require two or more reports. For example, you can combine a list with a chart. The list can contain product lines and the chart can show details for each product line.
Master detail relationships must appear in nested frames to produce the correct results. You can create a master detail relationship in two ways:
Use a parent frame for the master query and a nested frame for the detail query.
Associate a report page with the master query and use a data container, such as a list or crosstab, for the detail query.
You cannot display parent data in the child frame or child data in the parent frame. Also, you cannot perform calculations across master detail queries.
You can use a master detail relationship to show data from separate data sources in a single report. However, the data sources must be contained in the same package.
If you are working with an SAP BW data source, you cannot use a data item from the master query that contains non-ASCII values.
To create a master detail relationship using queries that reference two different dimensional data sources, you must first link the members by aliasing the levels that contain the members.
To use a parent frame for the master query and a nested frame for the detail query:
In the Insertable Objects pane,
on the Toolbox tab , drag a List, Repeater Table,
or Repeater to the report.
Add a second data container to the object you inserted.
You can insert a list, crosstab, chart, repeater table, or repeater into a list. You can add a list to a repeater table or repeater.
Add data items to both data containers.
To associate a report page with the master query and use a data container for the detail query:
Click anywhere in the report page.
In the Properties pane, click the select
ancestor button 
and click Page.
Set the Query property.
In the Insertable Objects pane, on the Toolbox tab, drag a data container to the report.
To link a data item in the master query to a parameter in the detail query instead of to another data item, create the parameter in the detail query.
Use parameters to filter values at a lower level in the detail query.
Pause the pointer over the query explorer
button and click the detail query.
In the Insertable Objects pane, on the Toolbox tab, drag the Filter object to the Detail Filters box.
In the Expression Definition box, create
the parameter .
Pause the pointer over the page explorer button and
click the report page.
Click anywhere in the report page.
In the Properties pane, click the select ancestor button and click Page.
Click the data container containing the details.
From the Data menu, click Master Detail Relationships.
Click New Link.
In the Master box, click the data item that will provide the primary information.
To link the master query to a data item, in the Detail box, click the data item that will provide the detailed information.
To link the master query to a parameter, in the Parameters box, click the parameter that will provide the detailed information.
Tip: To delete a link, select the link and press the Delete key.
If your detail query object is a chart, you can disconnect a chart title from the master query.
Click the chart title, and then, from the Data menu, click Master Detail Relationships.
Clear the Use the master detail relationships from the chart check box.
Tip: To avoid seeing the same data item twice in the report, click the data item in the data container driven by the detail query and click the cut button. This removes the item from the report display but keeps it in the query.
If you create a master detail relationship using queries that reference two different dimensional data sources, you may need to create a relationship between levels with the same member captions but different Member Unique Names (MUNs). For more information about MUNs, see Recommendation - Use Member Unique Name (MUN) Aliases.
For example, you may want to link the Americas member in the Sales territory level in the Great Outdoors cube with Americas in the GO Subsidiary level in the Great Outdoors Finance cube. To create the relationship, you must alias the levels that contain the members to link.
In the parent frame or the report page, double-click the level that contains the member that will provide the primary information.
The Data Item Expression dialog box appears.
In the Expression Definition box, use the expression in the following function:
caption(expression)
For example, caption([great_outdoors_company].[Sales Territory].[Sales Territory].[Sales territory])
Repeat steps 1 to 2 for the level in the data container that contains the details.
A caption alias is created for each level. You can now use the caption alias for each level to create a master detail relationship using member captions, which are the same, instead of MUNs, which are different.
You can now create the master detail relationship between the two levels.
When running a master detail or burst report that includes a chart or crosstab, disk space exhaustion may cause the report or other requests to fail. A large set of burst keys or master rows may produce one or more charts per detail, resulting in many master detail executions. This may cause the temp folder to accumulate many gigabytes of temporary files containing data required for successful chart rendering.
To avoid this issue, we recommend that you test large master detail or burst reports that include charts or crosstabs to determine the potential peak disk requirements for the report.
For each query in a report, you can work with the SQL or MDX that is executed when you run a report. You can
Note: MDX is not available with DMR data sources.
View the SQL or MDX to see what is passed to the database when you run a report.
Note: Only SQL is available when using DMR data sources because these data sources were relational.
To view the SQL or MDX for the entire report, from the Tools menu, click Show Generated SQL/MDX.
This option shows the SQL or MDX that will be executed in the data source. The SQL or MDX is organized by query and by query result. If a query is used in more than one data container, a query result is generated for each data container.
To view the SQL or MDX for a specific query, do the following:
Pause the pointer over the query explorer
button and click the query.
In the Properties pane, double-click the Generated SQL/MDX property.
The Generated SQL/MDX property shows the SQL or MDX that is executed when you view tabular data (from the Run menu, click View Tabular Data). Tabular data shows the data that is produced by the query in the form of a list. You can use this property to help you build advanced queries.
The SQL or MDX for the query appears in the Generated SQL/MDX dialog box. For SQL, you can choose to view native SQL, which is the SQL that is passed to the database when you execute the query, or Cognos SQL, which is a generic form of SQL that Report Studio uses. Cognos SQL is converted to native SQL before the query is executed.
You can build a report by adding SQL or MDX from an external source, such as another report.
If you are working with MDX, you must be aware of the MDX syntax that Report Studio supports.
If you edit the SQL of a query, you must change the Processing property for that query to Limited Local.
From the File menu, click New.
Click Blank.
Pause the pointer over the query explorer button and
click Queries.
In the Insertable Objects pane, drag Query to the work area.
In the Insertable Objects pane, do one of the following:
To build an SQL query, drag SQL to the right of the query.
To build an MDX query, drag MDX to the right of the query.
Tip: You can drag SQL or MDX anywhere in the work area, and Report Studio will automatically create a query.
In the Properties pane, double-click the Data Source property and click a data source.
If required, set the Catalog property to the name of the catalog.
Double-click the SQL or MDX property and type the SQL or MDX.
Click Validate to check for errors.
If you are working with SQL, in the Properties pane, set the Processing property to Limited Local.
Double-click the query.
If the SQL or MDX is valid, the data items defined in the SQL or MDX appear in the Data Items pane.
Pause the pointer over the page explorer button and
click a report page.
In the Insertable Objects pane, on the Toolbox tab, drag an object to the work area.
For example, drag a list, crosstab, chart, or repeater.
Click the data container.
In the Properties pane, click the
select ancestor button and click the container you just created.
For example, if you created a list, click List.
Set the Query property to the query for the report type.
In the Insertable Objects pane, on
the Data Items tab ,
drag the items from the query you chose in the previous step to
the data container.
You can convert a query to SQL to edit it. You may want to do this to improve performance or to use SQL features that are not supported directly by Report Studio.
Converting a query to SQL is an irreversible process.
Pause the pointer over the query explorer button and
click the query.
In the Properties pane, double-click the Generated SQL property.
Click Convert.
Click Validate to check for errors.
In the Properties pane, set the Processing property to Limited Local.
You can edit the SQL or MDX for a query that has been created as an SQL or MDX query or that has been converted to SQL.
Pause the pointer over the query explorer button and
click the SQL or MDX item under the query.
Double-click the SQL or MDX item.
Make changes in the text box.
If you are working with MDX, you must be aware of the MDX syntax that Report Studio supports.
Click Validate to check for errors.
The SQL produced by Report Studio depends on the report format you choose. For example, if you specify HTML format, first-rows optimization is requested. All-rows is requested if you specify PDF.
It is important for database administrators and programmers to remember that Report Studio does not always use first-rows optimization. If you assume first-rows optimization is always requested, this can cause the RDBMS optimizer to process the query differently than you expect.
If you are working with MDX, you must be aware of the syntax that Report Studio supports. Report Studio supports the MDX grammar as specified in Microsoft's Data Access SDK version 2.8.
Report Studio supports the following MDX functions. This is a partial list and applies only when you are working with the following data sources:
cubes
SAP BW
IBM Cognos TM1
DB2 OLAP
IBM Cognos Consolidation
IBM Cognos Contributor
For these data sources, IBM Cognos 8 uses a variation of the Microsoft SQL Server Analysis Services syntax.
Acos | FirstSibling | OpeningPeriod |
Aggregate | Generate | Order |
Ancestor | Head | Ordinal |
Ancestors | Hierarchize | ParallelPeriod |
Asin | Hierarchy | Parent |
Atan | IIf | PeriodsToDate |
Avg | Intersect | PrevMember |
BottomCount | Is | PreviousMember |
BottomPercent | IsAncestor | Properties |
BottomSum | IsEmpty | Qtd |
Children | Item | Rank |
ClosingPeriod | Lag | Siblings |
CoalesceEmpty | LastChild | Sin |
Convert | LastPeriods | Sinh |
Cos | LastSibling | Stddev |
Cosh | Lead | Subset |
Count | Level | Sum |
Cousin | Levels | Tail |
Crossjoin | LinkMember | Tan |
CurrentMember | Ln | Tanh |
DefaultMember | Log10 | TopCount |
Descendants | Max | TopPercent |
Dimension | Median | TopSum |
Distinct | Members | Union |
Except | Min | Value |
Exp | Mtd | Var |
Filter | Name | Wtd |
FirstChild | NextMember | Ytd |
When you are working with a dimensional data source, you can use dimensional functions to retrieve data for a specific period-to-date.
In this topic, you learn how to create a dynamic report that retrieves year-to-date revenue for each product line. The report also shows the percentage of the revenue generated in the month users select when they run the report.
It should take 15-20 minutes to complete this topic, and your report will look like this.
Create a crosstab report that uses the Go Data Warehouse (analysis) package.
Add the following data items to the report:
From Product, drag Product line to the Rows drop zone.
From Sales fact, drag Revenue to the Measures drop zone.
Tip: Use the Source tab in the Insertable Objects pane.
Create the following query calculation named Selected Month in the Columns drop zone:
[Sales].[Time dimension].[Time dimension].[Month]->?Month?
Create this query calculation named Year to Date Set next to the Selected Month column:
periodsToDate([Sales].[Time dimension].[Time dimension].[Year],[Selected Month])
Tip: Information about the periodsToDate function appears in the Information pane when you click the function in the Functions tab. The periodsToDate function is located in the M-Q folder in the Common Functions folder.
Click the Year to Date Set column
and click the cut button 
.
Create the following query calculation named YTD Revenue next to the Selected Month column:
total(currentMeasure within set [Year to Date Set])
Create the following query calculation named Percent of YTD Revenue next to the YTD Revenue column:
[Selected Month]/[YTD Revenue]
Click the lock/unlock button 
to unlock
the report.
Add this text item to the left of the text in the YTD Revenue column, and add a blank space after the text:
YTD Revenue for
Set the font style of the text to italic.
In the YTD Revenue column, click <#YTD Revenue#> and change the Source Type property to Report Expression.
Double-click the Report Expression property and drag the Month parameter to the Expression Definition box.
Tip: You can find the Month parameter in the Parameters tab.
Click the lock/unlock button to lock the report.
Right-click the Percent of YTD Revenue column title and click Fact Cells for "Percent of YTD Revenue".
In the Properties pane, double-click the Data Format property.
Under Format type, click Percent.
Set the No of Decimals property to 2.
Run the report to view what it will look like for your users.
Users are prompted to select a month. When they click OK, the report shows year-to-date revenue for each product line and the percentage of revenue generated for the selected month.
