Total ignoring filters

[Harl]

Hi, I have a crosstab and I have a column that is showing the totals for the year of the members in the different rows.

For example, I have in the rows cars, tables and chairs.
There is a column showing the sales for those products for example for the whole year.

Then I have a prompt value with the months. So if I select august, in another column it is showing the sales of august.

But I want the first column to still show the total of the year ignoring that filter of month.

If anyone could help me please. I have tried different ways but selecting the month it changes also the first column.

Thank you!!

[MFGF]

Hi, I have a crosstab and I have a column that is showing the totals for the year of the members in the different rows.

For example, I have in the rows cars, tables and chairs.
There is a column showing the sales for those products for example for the whole year.

Then I have a prompt value with the months. So if I select august, in another column it is showing the sales of august.

But I want the first column to still show the total of the year ignoring that filter of month.

If anyone could help me please. I have tried different ways but selecting the month it changes also the first column.

Thank you!!

Hi,

The first (and probably most important) question is whether your report is based on a relational package or a dimensional package. This will drive how best to obtain the result you need. If you are using a dimensional package, the approach would be to replace the filter with a dimensional expression for the month column in the crosstab (for example, a calculation with the expression [Your month level] -> ?MonthParameter? ). There would then be no impact on the Year column, or if there was, you could use a completeTuple() expression to get the year values regardless.
If using a relational package, things are a little more complex. You would need to add a separate query to the report to retrieve the Year values, join it to the main query based on Year, then use the resultant query from the join to feed your crosstab.

If you are unsure whether you are using a relational or dimensional package, check the FAQs post below. It's old, but still valid:

https://www.cognoise.com/index.php/topic,27563.0.html

Cheers!

MF.

[lukwiso]

You can try using a calculated field or a conditional aggregation in your crosstab. Basically, you need to create a formula that calculates the year-to-date sales regardless of the month filter. For the first column, you could set the calculation to ignore the month filter, so it always shows the total for the entire year.