Working with Queries

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 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

Relating Queries to Layouts

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 template, query and layout are automatically linked. In addition, you can create a query and manually link it to a data container. In the layout, when you drag a data container to the work area, you can specify which query to use by clicking the Query property in the Properties pane. You then add the items in the query that you want to see in the layout by dragging them from the Data Items tab in the Insertable Objects pane.

Working with Dimensional Data Sources

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.

Connecting Queries Between Different Data Sources

Cognos 8 supports the following:

• RDBMS to RDBMS joins

• set operations of any two queries

• master detail relationships of 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

Multiple-fact Queries

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, as well as on the level of granularity and on the additive nature of the data.

The following data source has characteristics that affect 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 available for changing a query to obtain the results that you want.

Conformed Dimensions

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, Time and Product.

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.

Non-conformed Dimensions

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.

Filters on Non-conformed Dimensions

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 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 Filter Data.

Add Queries to a Report

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.

Steps

1. Pause the pointer over the query explorer button  and click Queries.

2. In the Insertable Objects pane, drag one of the following objects to the work area.

   Object	Description
   Query	Adds a query.
   Join	Adds a join relationship.
   Union	Adds a union operator.
   Intersect	Adds an intersect operator.
   Except	Adds an except (minus) operator.
   SQL	Adds SQL.
   MDX	Adds MDX.

3. In the Properties pane, specify the properties you want.

   For example, if you added a join, double-click the Join Relationships property to define the join.

4. Double-click a query.

5. In the Insertable Objects pane, on the Source tab , drag data items that you want in the query to the Data Items pane.

   Tip: You can add data items to the query that you do not want to appear in the layout. For example, if you want to filter on Product line code but you want users to see Product line in the layout, you must add both data items to the query.

6. To create a new data item in the query, in the Insertable Objects pane, on the Toolbox tab , drag Data Item to the Data Item pane.

7. To add a filter, in the Insertable Objects pane, on the Toolbox tab, drag Filter to the Detail Filters pane or to the 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 the one of the filters panes and then completing the filter expression. If you are working with a dimensional data source, you can quickly filter data by dragging a member instead of 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 then completing the filter expression to specify to show data only for the year 2006.

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

Create a Union Query

Create a union query to combine two or more queries into one result set.

You can combine queries that use different data sources. For example, you can combine a query that returns data from a dimensional data source with a query that returns data from a relational data source.

To combine two queries, the following conditions must be met:

• The two queries must have the same number of data items.

• The data types of the data items must be compatible and the data items must appear in the same order.

  For numeric data types, integer, float, double, and decimal are compatible.

  For string data types, char, varChar, and longVarChar are compatible.

  For binary data types, binary and varBinary are compatible.

  Date data types must match perfectly.

Steps

1. Pause the pointer over the query explorer button  and click Queries.

2. In the Insertable Objects pane, do the following:

   • Drag Query to the work area.

   • Drag Union, Intersect, or Except to the right of the query.

     Two drop zones appear to the right of the operator.

   • Drag a Query object to each drop zone.

   Two queries are created in the work area and a shortcut to each query appears in the drop zones.

3. Double-click each query that makes up the union query and add the data items that you want.

4. On the toolbar, press the back button to return to the Queries work area.

5. Click the set operator that you added in step 3.

6. In the Properties pane, click the Duplicates property and choose whether you want to remove or preserve duplicate rows.

7. Double-click the Projection List property.

   The projection list shows the list of projected data items for the set operation.

8. If you want Report Studio to automatically produce the list of projected data items, click Automatically generated.

   Report Studio generates the projection list using only one of the two queries being unioned.

9. To add, delete, move, or rename data items in the projection list, click Manual and make the changes you want.

10. Click OK.

11. Double-click the union query.

12. In the Insertable Objects pane, on the Source tab , drag the data items that you want to add to the query to the Data Items pane.

The union query is complete. You can now link it to a data container in the layout.

Example - Create a Two-column List Report for Three Data Items

You are a report author at the Great Outdoors Company, which sells sporting equipment. You are requested to create a list report that shows revenue for all product lines and order methods. However, you want the product lines and order methods to appear in a single column. To create this report, you use a union query to join the Product line and Order method data items.

Steps

1. In the Cognos Connection Welcome page, click the Public Folders link.

2. Click the GO Data Warehouse (query) link; click the Launch link in the upper-right corner of the page, and then click Report Studio.

3. In the Welcome dialog box, click Create a new report or template.

4. In the New dialog box, click Blank and click OK.

5. Pause the pointer over the query explorer button  and click Queries.

6. In the Insertable Objects pane, do the following:

   • Drag Query to the work area.

   • Drag Union to the right of the query.

     Two drop zones appear to the right of the operator.

   • Drag a Query object to each drop zone.

     Query2 and Query3 are created in the work area, and a shortcut to each query appears in the drop zones.

7. Double-click Query2.

8. In the Insertable Objects pane, on the Source tab , drag the following data items to the Data Items pane:

   • from the Product folder, Product line

   • from the Sales fact folder, Revenue

9. In the Insertable Objects pane, on the Toolbox tab, drag Data Item to the Data Items pane.

   The data item will be used to sort product lines and order methods in the report.

10. In the Expression Definition box, type 'A' and click OK.

11. In the Properties pane, click the Name property, delete the default name, and type

    Sort key

12. On the toolbar, press the back button  to return to the Queries work area.

13. Double-click Query3.

14. In the Insertable Objects pane, on the Source tab, drag the following data items to the Data Items pane:

    • Order method
    • Revenue

    Tip: You can find these items in the Order method and Sales fact folder.

15. Repeat steps 9 to 11 to create a Sort key data item in Query3 with 'B' as its definition.

16. On the toolbar, press the back button to return to the Queries work area.

17. Click the Union operator.

18. Double-click the Projection List property.

    The Product line item in the projection list contains both product lines and order methods.

19. Click Manual.

20. Click Product line and then click the edit button .

21. In the Edit box, type the following after Product line:

    & Order method

22. Click OK twice.

23. Double-click Query1.

24. In the Insertable Objects pane, on the Source tab, drag the following data items to the Data Items pane:

    • Product line & Order method
    • Revenue
    • Sort key

25. With Sort key selected, in the Properties pane, click the Pre-Sort property and click Sort ascending.

    When you run the report, all product lines will appear first, followed by all order methods.

26. Pause the pointer over the page explorer button  and click Page1.

27. In the Insertable Objects pane, on the Toolbox tab, drag List to the work area.

28. Click the list.

29. Click the select ancestor button  in the title bar of the Properties pane and click List.

30. Click the Query property and click Query1.

    The list is linked to the union query.

31. Double-click the Properties property.

32. Select the Sort key check box and click OK.

    Since the Sort key data item does not appear in the list, you must make it a property of the list before it can sort product lines and order methods.

33. In the Insertable Objects pane, on the Data Items tab , drag the following items from Query1 to the list:

    • Product line & Order method
    • Revenue

34. Run the report.

A list report with two columns is produced. All product lines and order methods appear in the first column.

Create a Join Relationship

You can create a join relationship to join two queries.

In general, join relationships should be created in the Framework Manager model. Create a join relationship in Report Studio if what you are trying to do cannot be modeled in Framework Manager.

Steps

1. Pause the pointer over the query explorer button  and click Queries.

2. In the Insertable Objects pane, do the following:

   • Drag Query to the work area.

   • Drag Join to the right of the query.

     Two drop zones appear to the right of the operator.

   • Drag a Query object to each drop zone.

     Two queries are created in the work area, and a shortcut to each query appears in the drop zones.

3. Double-click each query that makes up the join query and add the data items that you want.

4. On the toolbar, press the back button  to return to the Queries work area.

5. Click Join.

6. In the Properties pane, double-click the Join Relationships property.

7. Click New Link.

8. Click a data item in the left query, and then click a data item in the right query to create a link.

9. For each query, click Cardinality, and then click the cardinality that you want to use.

10. Click Operator, and then click the operator that you want to use.

11. To convert the join relationship to an expression, click the Convert to expression button.

    Convert the join relationship to an expression to make changes to the join definition.

    Note: After you convert the relationship to an expression, you cannot change it back to a relationship.

12. Click OK.

13. Double-click the join query.

14. In the Insertable Objects pane, on the Source tab , drag the data items that you want to add to the Data Items pane.

The join query is complete. You can now link it to a data container in the layout.

Add Dimension Information to a Query

Add dimension information to a query to obtain the results that you want. Add dimension information 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, Cognos 8 creates a default dimension structure.

Dimension information is not intended to define the presentation of information, but to help query planning. It 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.

Steps

1. Pause the pointer over the query explorer  button and click the query you want.

2. In the Properties pane, click the Override Dimension Info property and click Yes.

   The Dimension Info tab appears in the work area.

3. Click the Dimension Info tab.

4. 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.

5. To create a new dimension, in the Insertable Objects pane, on the Toolbox tab , drag Dimension to the Dimensions pane, and then build it by adding the objects you want:

   • 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 Working with Dimensional Data.

6. In Insertable Objects pane, on the Source tab, drag the data items you want 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.

7. To create a fact, in the Insertable Objects pane, on the Source tab, drag the data item you want to the Facts pane.

Example - Create a Crosstab Report that Shows Empty Rows

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.

Steps

1. In the Cognos Connection Welcome page, click the Public Folders link.

2. Click the GO Data Warehouse (query) link, click the Launch link in the upper-right corner of the page, and then click Report Studio.

3. In the Welcome dialog box, click Create a new report or template.

4. In the New dialog box, click Crosstab and click OK.

5. In the Insertable Objects pane, on the Source tab , drag the following data items to the crosstab:

   • Product line as rows

   • Month as nested rows

   • Order method as columns

   • Revenue as the measure

   Tip: You can find these items in the Product, Time dimension, Order method and Sales fact folders.

6. Right-click the crosstab and click Go to Query.

7. In the Properties pane, click the Override Dimension Info property and click Yes.

   The Dimension Info tab appears at the bottom of the work area.

8. Click the Dimension Info tab.

9. In the Insertable Objects pane, on the Source tab, drag the following items to the Dimensions pane:

   • Product line
   • Date

   Product line and Date become separate dimensions in the query.

10. Pause the pointer over the page explorer button  and click Page1.

11. Click Month.

12. In the Properties pane, double-click the Sort property.

13. In the Data Items box, drag Date to the Sort List box and click OK.

14. Run the report.

All order years appear for all product lines, even if no revenue was produced.

Reference Package Items in Child Queries

When you create a child query in Report Studio, you can only reference items from its parent or from other queries. For example, if you add a filter to a child query, the only items that you can insert into the expression are items that exist in other queries defined in the report. To add an item from the package, you must unlink the child query from its parent.

Steps

1. Pause the pointer over the query explorer button  and click Queries.

2. Unlink the child query from its parent by selecting the parent query shortcut to the right of the child query and clicking the delete button.

3. Double-click the child query.

   Package items are now available on the Source tab  of the Insertable Objects pane.

4. Add the package items you want.

   For example, to create a detail filter that references a package item, drag the filter object from the Toolbox tab to the Detail Filters pane, and then add the items you want in the Expression Definition box.

5. Click the up arrow in the toolbar to return to the queries work area.

6. Recreate the link between the child query and the parent query by dragging the parent query to the right of the child query.

7. If necessary, double-click the child query to complete it.

   For example, if you created a filter, you may need to complete the filter expression.

Working with SQL or MDX

For each query in a report, you can work with the SQL or MDX that is executed when you run a report. You can

• view the SQL or MDX

• edit the SQL or MDX

• build a report using your own SQL or MDX

• choose the data source

When working with a relational data source, the SQL that is produced by Report Studio depends on the report output selected.

View the SQL or MDX

View the SQL or MDX to see what is passed to the database when you run a report.

Steps

1. 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.

2. 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 you want.

   • 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 (Run menu, View Tabular Data), which 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.

Edit the SQL or MDX

You can edit the SQL or MDX for a query to suit your particular needs. For example, you may want to edit the SQL or MDX to improve performance or to troubleshoot problems.

If you did not originally build the query using SQL or MDX, you must convert the query to SQL or MDX before you can make changes. Converting a query to SQL or MDX is an irreversible process.

Steps

1. Pause the pointer over the query explorer button  and click the query you want.

2. In the Properties pane, double-click the Generated SQL/MDX property.

3. Click Convert.

4. Make the changes you want.

   If you are working with MDX, you must be aware of the MDX syntax that Report Studio supports.

5. Click Validate to check for errors.

6. Click OK.

Build a Report Using Your Own SQL or MDX

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.

Steps

1. From the File menu, click New.

2. Click Blank and click OK.

3. Pause the pointer over the query explorer button  and click Queries.

4. In the Insertable Objects pane, drag Query to the work area.

5. In the Insertable Objects pane, do one of the following:

   • To build a 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.

6. In the Properties pane, double-click the Data Source property to choose the data source.

7. Click the data source you want and click OK.

8. If required, in the Catalog property, type the name of the catalog that you want to use.

9. Double-click the SQL or MDX property.

10. Type the SQL or MDX you want to use.

11. Click Validate to check for errors.

12. Click OK.

13. Double-click the query.

    If the SQL or MDX is valid, data items defined in the SQL or MDX appear in the Data Items pane.

14. Pause the pointer over the page explorer button  and click a report page.

15. In the Insertable Objects pane, on the Toolbox tab, drag the report type you want to create to the work area.

    For example, drag a list, crosstab, chart, or repeater.

16. Click the data container.

17. In the Properties pane, click the select ancestor button  and then click the container you just created.

    For example, if you created a list, click List.

18. Click the Query property and click the query you want to use for the report type.

19. In the Insertable Objects pane, on the Data Items tab , drag the items you want from the query you chose in the previous step to the data container.

Choose the Data Source

If you converted a query to SQL or MDX or created a query by adding your own SQL or MDX, you must choose the data source against which you want to execute the query.

Steps

1. Pause the pointer over the query explorer button  and click Queries.

2. Click the Data Source object of the query for which you want to choose the data source.

3. In the Properties pane, double-click the Data Source property.

4. Click the data source you want and click OK.

First-Rows Optimization

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.

Supported MDX Syntax

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, with the following exceptions.

Report Studio does not support the following MDX functions. This is a partial list and applies only when you are working with the following data sources:

• cubes

• DB2 OLAP

• Cognos Consolidation

• Cognos Contributor

For these data sources, Cognos 8 uses a variation of the MS Analysis Services syntax.

Add a Query Macro

Add a query macro to allow run time changes to be made to SQL queries. A macro is a fragment of code that you can insert in the Select statement of a query or in an expression. For example, add a macro to insert a new data item containing the user’s name.

You can also add query macros to the Framework Manager model. For more information, including examples, see the Framework Manager User Guide.

Support for query macros in Report Studio includes the same capabilities as macros used in Framework Manager, including support for parameter maps defined within the Framework Manager model. However, Report Studio query macros do not extend to the layout. Therefore, when making changes to the query using macros, you must bear in mind the side-effects on the layout. For example, if a macro removes a column from the query that the layout refers to, a run-time error will occur.

Steps

1. Pause the pointer over the query explorer button  and click Queries.

2. If the query to which you want to add a macro was built by using your own SQL, do the following:

   • Click the SQL object that is linked to the query.

   • In the Properties pane, double-click the SQL property.

   • In the SQL dialog box, type the macro.

   • Click OK.

3. If the query to which you want to add a macro was not built by using your own SQL, do the following:

   • Click the query.

   • In the Properties pane, double-click the Generated SQL/MDX property.

   • In the Generated SQL/MDX dialog box, click Convert.

   • In the SQL dialog box, type the macro.

   • Click OK.

Tip: For a list of supported macro functions, see Using the Expression Editor.

Creating Prompts Using Query Macros

You can create mandatory and optional prompts in reports using query macros. Use the prompt macro functions prompt and promptmany to create single-value and multiple-value prompts. You can use prompt macro functions when working with a relational data source or a dimensionally modeled relational (DMR) data source.

The prompt and promptmany functions have the following mandatory and optional parameters. All argument values must be specified as strings.

Example - Select a Country Prompt

You are a report author at the Great Outdoors Company, which sells sporting equipment. You are requested to create a report that will prompt users to choose the country for which they want to see data.

The following code shows how you can use macros to create a prompt:

select 

	COUNTRY_MULTILINGUAL.COUNTRY_CODE
as COUNTRY_CODE,

	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,

	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,

	COUNTRY_MULTILINGUAL.CURRENCY_NAME
as CURRENCY_NAME 

from 

	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL

where COUNTRY_MULTILINGUAL.COUNTRY = #prompt('CountryName')#