Skip to main content
Planview Customer Success Center

Using a Cognos Data Module with custom SQL query

Symptoms

Customer was trying to get data using custom SQL in a data module. When opening Data Module > New Data module > Create table using SQL, the only source available was Team content. When user selected Changepoint unsecured, there was no option for creating a table. 

Reason

Data Server source connection with SignOn credentials was not set up.

Resolution

In Cognos 11.1.5, customers can use data sets and data modules for their dashboards and Self-Service Analytics. This requires the Hosted team to first set up a Data Server source connection with SignOn credentials. Use the non-production environment to test first.

Do the following:

  1. Create SQL user account and grant the user dbo access to the Changepoint database.
  2. Login to Cognos Analytics as a Cognos Administrator. 
  3. Go to Manage > Data Server Connections and then click +.
  4. Select Microsoft SQL Server.
  5. Input the SQL ServerName and port.
  6. Create a Signon user. Use the same SQL user account that was earlier created in Step 1.
  7. Go to the Schemas tab. The Changepoint objects appear.

image001.png

 

image002.png

 

You must create the data module by using the newly created Data Server instead of Team content.

image003.png

You can create new tables in a data module that are based on a custom SQL syntax. The SQL is executed against a source that is already in the data module.

If the SQL validation is successful, the table is populated with a set of projected column names and rows of data.

  1. From the data module context menu , select Create table using SQL.

  2. In the table editor, type the table name.
  3. From the SQL type drop-down menu, select the native SQL.
  4. From the Source drop-down menu, select the source to associate the table with.
  5. For data server connections, select the connection name.
  6. For other types of sources, select the source location, which is either Team content or My content.
  7. In the Expression box, type or paste the SQL syntax for your table. The syntax is executed only against the source that you selected in the previous step.

    The expression editor provides the following syntax validation and editing options:

    • Validation icon in expression editor - Validate the syntax. You can validate the whole statement, or only selected segments of code.
    • Preview icon in expression editor - Preview columns and rows in your projected table. If the syntax is not correct, the columns are not displayed.
    • Help icon - View descriptions of functions, and examples of their usage.
    • Comment icon in expression editor - Insert the cursor anywhere in a line of code and select this button to comment out the entire line. To comment out multiple lines of code, select the lines and select this button. The comment string (--) is added at the beginning of each selected line.
    • icon_ee_contrast.jpg - Use high-contrast mode.
    • Change the font size.
  8. Click OK to save the table.

    You can save the table even if it contains syntax errors, and edit the syntax later. However, you cannot modify any aspect of the SQL table, or view its data in the grid, until the table is successfully validated.

    The table name appears at the top of the data module tree.

To edit the table SQL, from the table context menu, click Edit SQL table.

You can also edit a column expression in an SQL-based table. However, subsequent updates to the original SQL statement might overwrite the updated expression. 

You can use and model SQL-based tables in the same way as other data module tables. For example, you can create relationships between this type of tables and other tables. You can also create calculations and navigation paths that include columns from these tables.