Mini Chart in crosstab row

Case:

You want to display a mini chart in the row of a crosstab.

Solution:

Create a crosstab report with for example: Product-line, year and sales.

Go to the Query Explorer and rename Query1 to Crosstab.

Go to the Query Explorer and copy the Crosstab query. Then click paste and rename the query to Chart.

Create a line chart with the query Chart as source. Add year-month from the date dimension into the query.

From the query drag year-month into the X-axis Catagory. Then drag Sales into Y-axis Default measure.

Define a sort on the X-axis. Select the abc on the X-axis, go to the properties and choose NO for Visible. Select the Y-axis and go to the properties and choose NO for Visible. In the properties of the Chart set Hide for Legend.

Set the size of the graph to 70px (height) and 140px (width) in the Size&Overflow properties of the Chart.

Unlock all objects in the report.

Drag a table into the row with the productlines in the crosstab (1 row, 2 columns).

Drag and drop Product-line into the table. Choose align left for that field of the table.

Now drag the chart into the left field of the table.

Go to the properties of the chart and click on Master Detail Relationships. A propertie box opens. In here create a new link between Product-line from the Query Crosstab and Product-line from the query Chart.

Now run the report to see the result.

Justify empty values

Case:

When you work with a report, based on an dimensional model, it can occur that the result shows a lot of empty values.

justify_empty_values_1

This is not what you would like to see. Instead you would like to see only the rows that have a value.

Solution:

To achieve this you can use the following solution:
– In the report choose filter
– Click on the icon to add a new filter

justify_empty_values_3

– Put a filter on the Amount [Aantal] column of the FACT

justify_empty_values_4

– In the Expression Definition you put: [Aantal] IS NOT NULL

justify_empty_values_2

 

Hide empty table

Case:

Often when you run a report it shows no data, because of filters for example. In that case you only see the header of the table, the data is empty. Instead you want to show the user there is no data.

Solution:

Use the following steps:

1. Select the table witch you don’t want to show in case of no data

2. In the properties pane go to “No data contents” (Geen gegevens beschikbaar)

hide_empty_table_1

3. Change the property from “No” to “Yes”. The table changes.

hide_empty_table_2

4. Select a text item into the new area. Edit the text as you like, eg. “No data for this selection”

hide_empty_table_4

Result:

Result when running the report

hide_empty_table_5

Cognos Error PDS-PPE-0116

This error occurred while running a report.

The IBM site says:

Cause:

While running the report, Cognos 8 server will temporarily store all data sets in the Cognos8_install_directory/temp directory. If the disk is out of the space, the error message will be generated.

Solution:

Assign more disk space on the Cognos 8 server machine.

The other solution is to search in the cognos log files what query is running for the report and look if there are full outer joins in the query.

Perhaps a solution is to make an other selection for the report to avoid the full outer joins.