Drill up/ Down not updating filters

[mel4363]

Hi Everyone,

I have a report designed in Cognos Report Studio version 10.2.1. This report is using SQL as its data source modeled through Framework manager.
I have a crosstab in the report with columns: OrganisationStructure (Hierarchy), Nested to this item are Months and Year data items. I then have number of vehicles and Fuel quantity as the rows.

My problem then occurs when I drill-down on OrganisationStructure as this has -> Clusters > Branch > Business Units > Registration Numbers. Vehicles in the clients organisation may change business units at any given time. When the vehicle changed business units in January when I run the report for February I shouldn't see data for January on the Business Unit but this is not happening. When I drill down to Registration number data for January shows even when the report was run for February.


Hope this is clear and thanks for the help

Mel

[Robl]

It sounds like you need to supress zeros and/or missing values.

The cars are part of the structure so will always be there regardless of any other data, so you just need to supress them.

[mel4363]

It sounds like you need to supress zeros and/or missing values.

The cars are part of the structure so will always be there regardless of any other data, so you just need to supress them.

Hi Robl,

Thank you so much for your response.

Wish the solution was that simple. I have suppression enabled but It wouldn't work even if I did as there is data for that vehicle but my problem is it displays for a month that i didn't initially run the report for

[MFGF]

Hi Robl,

Thank you so much for your response.

Wish the solution was that simple. I have suppression enabled but It wouldn't work even if I did as there is data for that vehicle but my problem is it displays for a month that i didn't initially run the report for

How is your report specifying that you want to see only February? Are you using a detail filter? A slicer? A dimensional expression?

MF.

[mel4363]

How is your report specifying that you want to see only February? Are you using a detail filter? A slicer? A dimensional expression?

MF.

I am using a detail filter on Month and on Year, These are nested to the hierarchy in the report as columns.

[MFGF]

I am using a detail filter on Month and on Year, These are nested to the hierarchy in the report as columns.

Ah - that's your issue right there. You must be using a dimensional package or you wouldn't be able to drill down or up. Using detail filters in a dimensional reporting paradigm leads to unpredictable, often incorrect results. Use dimensional expressions in your report instead.

I assume you are prompting for Year and Month? If so, do the following:

1. Delete the detail filters
2. Replace the Year level in your crosstab with a Query Calculation (type Other expression) with the expression [Your year level] -> ?Your Year parameter?
3. Replace the Month level in your crosstab with a Query Calculation (type Other expression) with the expression [Your month level] -> ?Your Month parameter?

MF.

[mel4363]

Ah - that's your issue right there. You must be using a dimensional package or you wouldn't be able to drill down or up. Using detail filters in a dimensional reporting paradigm leads to unpredictable, often incorrect results. Use dimensional expressions in your report instead.

I assume you are prompting for Year and Month? If so, do the following:

1. Delete the detail filters
2. Replace the Year level in your crosstab with a Query Calculation (type Other expression) with the expression [Your year level] -> ?Your Year parameter?
3. Replace the Month level in your crosstab with a Query Calculation (type Other expression) with the expression [Your month level] -> ?Your Month parameter?

MF.

Thank you MF.


Tried this solution, however when I drill down on a particular Business Unit for instance it still shows all the other months that the vehicle was reporting for, Months of which the vehicle didn't belong to the business unit selected in the prompt page.

Thank you

Mel

[mel4363]

Hi Everyone,

I have found the solution to this problem, turns out this had more to do with my FM Package. I revisited it and changed the relationship between my tables. I joined my BusinessUnit dimension table to my fact_tables and then joined my vehicles table to the fact_table. In this way I will always receive vehicles relevant to my businessUnit and month that I run the report for (As each month will have a businessUnit relevant to it in my fact_table).

Hope the above explanation is clear.

Thank you all for your suggestions

Mel