Show all values in a dimension

[#1]

Hi All,

I have a requirement to show all countries by month in a pivot with month running horizontally and country vertically. When the user changes the month, the dimension country is reduced to show only countries that have a value and everything else is a null.

I have tried setting to not suppress nulls, but this doesnt work, i have tried to show all values but this does not work

If a create a listbox and write this expression concat(DISTINCT {1}country,',') then irrespective of selections I can see all countries. This expression does not give the same output as a dimension and actually populates nothing.

So how do i get the concat string function output in a listbox to work in a dmension on a pivot table

Your help is much appreciated

Kind Regards,

Byron

[#2]

Hi. This work for most solutions. But as soon you do apply filters to your set it can get absolutly weird.

When is this the case:

• Attached example of a cross tab: dimensions used which have no intersection: There are customers without a transaction = they have no relation to the calendar
• As long nothing is selected everything works fine. Why? because still all relations are in the selected set
• As soon you select a year everything falls appart: Why? because by selecting a year you filterd out all customers from the current set. Even activating as proposed "show all values" for  a dimension doesn't solve the problem as the relations to other dimensions are gone
• Solution: take a set into your formula with contains the link between the dimensions.

Example:

sum(Ordervalue)+ sum({1-1<[Year]={"*"}>}0)

= First part is the original formula. The second part selects then all customers and subtracts all customers with a valid link to a year = show all customers with absolutly no revenue. There may be other requirements like: show as well all customers which had revenue but in other years. In such cases you would use getFieldsSelection() in the set definition as modifier.

Example is attached .

[dadumas]

This seemed to work better for me:

anyexpression   + sum({$<[Year]={"*"}>}0)

When I tried the 1-1 vs the $, the 1-1 produced very strange results.  Using $ (current selection state) worked perfect.

Dave

[LR]

I have a very similar question to this. But I am unable to open the qvw. file attached. I am trying to show all values in a field irrespective of my where clause. I am using Section Access for my security and need to show all Departments, for example, even if they have no data tied to them. Can you help with the load script?

Thanks!