Now I got the table ChartsOfAccounts and the related JvHeader and JVDetails which is linked to my master calendar table
in my pivot table I'm putting the CategoryName and SubCategoryName, and MonthYear of the master calendar as my dimensions and as expressions I put sum(field1-field2) of JVDetail table
the JVHeader table is linked to SubCategories via the subCategoryCode
when there is no selection on my document all the dimension values appear and of course the expression values
when the user selects a year of course values of the JVDetail will be filtered based on the selected year and the problem is that if there is no data in JVDetail related to the selected year, the related Categories and sub-categories are filtered too
what can I do to preserve the categories and sub-categories when the user selects a specific year?
actually I want filtered out expressions to display zeros instead of really being filtered out
Hi there - Pivot tables are a bit restrictive around what you can do with regard to hiding columns and including zeros.
A route I have gone in the past is to use a straight table with an expression for each month (limited to a single month using Set Analysis), plus an expression for the annual total. In order to preserve the rows when the selection would exclude it you just need to add an extra expression with '1' as the expression. This column can then be hidden by using the radio button on the Presentation tab.
There are other advantages of having a straight table like this rather than a Pivot - such as being able to sort on the values in any given month.
