Search code examples
cognoscognos-10cognos-bi

Cognos - Showing every month on x-axis when some months don't have values

Let me first say I am very new to Cognos and have mainly learned by just manipulating items within active reports. I am having an issue with creating a graph that acts like a time series. I want it to display every month (with multiple values in some months and none in others). I want to visually see gaps between data points (ex: we order products every 3 months starting in January, so we should see gaps in the months we do not order products - like February and March). I have tried changing the label control to manual and setting display frequency to 1. However, I think my issue is that there is not any data within certain months.

Solution

  • You are correct in that your problem is lack of data. A standard inner join will drop rows where there is not a corresponding row in both tables, resulting in gaps.

    There are two solutions available:

    1. Use a union to create "dummy" records for each date
    2. Manually specify an outer join between the date table and the table containing the rest of information

    Since the first technique is the most common, I'll outline the basic steps for it here.

    1. Create a new query
    2. Add your month data item to the query
    3. Create a 'dummy' data item for your measure. Use 0 for its expression.
    4. If there is a date range filter in the main query apply it here
    5. Create a union
    6. Drag over your new query into the union
    7. Drag over your original query into the union
    8. Pull in the date and measure data items into the union query
    9. Set the Aggregate Function property of the measure to Total
    10. Use the union query as the source for your chart

    For every month with measure data you will have two rows, one with the measure amount and one with 0. The two rows will be combined by the auto-group and summarize function. The measures will be added together. Anything added to 0 will end up as the original amount.

    For months with no measure data, there will only be the 'dummy' row with 0 for the measure and it will be represented in your chart.