Formatting data

The column data format specifies how column values are displayed in dashboards, stories, explorations, or reports. You can choose a format type such as text, percent, or currency.

Uploaded Microsoft Excel spreadsheets retain the column data formats that were defined in Excel. These formats are set as default data formats in the base data modules that are created from these spreadsheets.

Each format type contains properties that further specify how the data is displayed. Initially, the properties are set based on the format options that are returned from the source. If no option is returned from the source, the property is set to the data module default.

Some characters are language-sensitive and display properly only when your locale supports the applicable font. For example, for Japanese currency symbols to display correctly, your locale must be set to Japanese.

About this task

In a data module, you can format data by using the basic formatting options and advanced formatting options.

Basic data formats can be viewed on the Grid tab in the data module, and in reports, dashboards, stories, and explorations which use this data module as their data source. However, the advanced options are supported only in Reporting.

For example, to display the date 2024-1-15 as 2024/01, the following advanced data formats can be specified for a Date column in the data module:

Advanced data formatting options

The following table shows how the date formatted this way is displayed in the different Cognos® Analytics UIs:

UI Date display
The data preview (the Grid tab) in a data module 2024/1/15
Dashboards 2024/1/15
Stories 2024/1/15
Explorations 2024/1/15
Reports 2024/01

As you can see, only Reporting supports the advanced data formatting options that were specified in the data module.

If you require the advanced formatting options for your data in dashboards, explorations, or stories, ensure that the data already exists in the format you require in the data source (database, Excel spreadsheet). You can also use various Cognos Analytics functions to format the data. For example, to produce the output 2024/01, you can use the following function: 

cast(YEAR(DAY_DATE), char(4)) || '/' || cast(RIGHT('0' + RTRIM(MONTH(DAY_DATE)), 2), char(2))

Procedure

  1. In the data module tree, from a column context menu Vertical actions menu icon, click Format data.
    Tip: You can define the format type for several columns at the same time if the columns contain the same type of data.
  2. In the Data format dialog box, select the appropriate Format type.

    For example, you can select Date, Time, Date/Time, or other type.

    If the data was not formatted in the source, the Unformatted type is assigned in the data module. If the source supplies a format, the format is reflected in the module.

    The Custom format is an advanced option where you can supply your own format string and use it to format the data.

  3. Specify properties for the selected format type.

    For example, for the Date type, you can specify the Date separator, the Date style, and Date ordering properties. For the Currency type, you can specify the Number of decimal places property.

    The Default setting is specific to the property that it refers to. You can view the default for each property by clicking the information icon Information about the data format property. next to the property. For example, for the thousands separator, the default value is inherited from the user's content language. For the decimal places default, if the property is not set, the number of decimal places vary depending on the number rendered.

    Click Advanced options to specify more data format properties.

    Note: The advanced data formats that are specified here are applied only in Reporting. In other Cognos Analytics UIs, only the basic data formats are applied. For more information, see the earlier discussion in the About this task section.

    Click Reset properties if you want to reset the properties to their default values.

  4. Click OK to apply the formatting.

    View the formatted data on the Grid tab in the data module, or in reports, dashboards, stories, and explorations which use this data module as their data source.