I'm trying to filter the content of a pivot table using a set analysis on a dimension, filtered by another dimension calculated on values obtained by the subtraction of two variables.
Maybe I'm near the solution but I've got a question: inside my pivot table, if my expression (I changed the "only" with a "count") returns two possible values, '0' and '1', and I want to show only the '1's , how can I do that?
I checked the option "Suppresso zero-values" on Presentation folder of the expression with no result.
"exhibition_name_excluded" and "exhibition_edition_excluded" are the fields through which the user can choose the exhibition name or the exhibition edition to exclude. These fields are linked to the CustomerID_excluded inside the data model.
Once the exclusion is choosen, in my pivot table I show only the exhibiton edition (Exhibition_edition) filtered by set analysis {$<CustomerID={$(var_CustomerID_selected)}-{$(var_CustomerID_excluded)}>}
Inside the variable "var_CustomerID_selected" there are all the possible CustomerID (selected through CONCAT function) available in the document.
Inside the variable "var_CustomerID_excluded" there are all the excluded CustomerID (selected through CONCAT function), linked in the data model to the exhibitions exluded by the user.
The solution is good but with 600.000 CustomerID in my DB, the subtraction between the two variables and the calculation of the pivot table are too heavy for the server (Intel Xeon E7330@2.82 GHZ, 2 processors).
Any suggestions about that?
I was thinking to eliminate the two variables and subtract the CustomeID and CustomerID_excluded directly inside the set analysis, something like this:
if(GetSelectedCount(exhibition_name_excluded)>0 or GetSelectedCount(exhibition_edition_excluded)>0,
the solution I described before works correctly but the performances decrease massively and the application is not usable.
I thought the problem was storing a great number of customerID into the two variables and then subtract them.
Anyone can suggest me an alternative solution through which I can subtract from the possibile values (not only the selected ones) of an ID inside the document the values of another ID (in this case those selected)? I'd need to filter the data linked to the ID resulting from the previous subtraction, inside a pivot table.
The CustomerID to exclude are taken by a secondary table in the data model, not linked to the "cloud".
In this way, independently from the selection of the customerID inside the document, the user can select which Customer (Exhibition) to exclude, taken from another table and so not influenced by other selection.
It works but the subtraction between the two variables is too heavy as I sad in the previous posts.
I'm looking for an alternative set analysis with better performance.
