Discussion about conditional expressions, calculation time, export to excel time
Hi,
I have to make a pivot (preferably) with 4 dimensions (this way they can be expanded/hidden without conditional expressions) that has 30-35 expressions and we need to hide/not show some of them for the different reports. The fully expanded pivot table with 20 expressions, 4 dimensions and no conditional expressions has over 1 mln rows and is calculated quite fast (the expresssions are not complex, most of them I calculated in the script, just simple sums to do are left) - for 20 sec or so and this takes 6 GB RAM. Exporting this to excel is slow - it actually never happened (via Desktop, the Server is 16GB RAM, 4 cores).
I hoped that introducing conditional expressions will cut the calculation/exporting time and RAM usage but actually it is the reverse - using the in-built conditional expressions for pivot chart expressions makes the presentation slow and the export (or copy Tabe data) impossible even for 30K rows and less than 10 expressions selected (whose export to excel is fast otherwise without conditionals - 10 seconds or so). I used the standard approach with island table for expression names that have to be selected as condition.
Then I tried ValueList($(Expr1),$(Expr(2),etc), where the variables are the same conditions and when island table filed values are not selected the corresponding variables return null and thus are not shown and disappear from the table (there is no conditional expression here, just all expressions are written as one with many if clauses to make the correspondence with each ValueList case). This performs better than in-built conditionals on the presentation time and export to excel/copy data - time- almost as fast as the normal pivot table, but it takes a lot of RAM to be calculated - 8 GB for 4 expressions and 16 (actually never calculated them) for 8 expressions. I think this is because the values of the new dimension require one by one calculation for each correspondence. So it is not possible to calculate more than 5-6 expressions in that way.
Do you know any other way to hide expressions via selection by the user, so that export to excel of at least 50K rows is possible? Is my only option writing a macro again?
