Limit # of Rows in Chart (Pivot Table)

[#1]

Hi everyone,

I have a Pivot Table in which the dimensions are conditionally enabled. The user selects from a list of dimensions and then the chart 'builds' itself just like a traditional pivot table in Excel. Dimensions can carry a varying level of granularity so I would like to have a calculated condition on the chart so that the user doesn't create a pivot with say more than 100K rows of data returned.   Is there a way to set this calc condition so that the chart won't display if the combination of user selected dimensions and expressions would result in a chart with >100K rows?

Thanks!

[antoniotiman]

Hi Mark,

all the values of Dim are already in Concat(Dim,',').

When I select Pick Dimensions, values of Dim (Dim1,Dim2,...,Dimn)

are in Concat.

Regards,

Antonio

[#3]

Hi Antonio - yes, that is correct, however in my real application I will have over 100 unique dims.  When I try to concatenate all of those dim fields together my application runs out of memory (on a 64bit PC).  This is why I am looking to adapt the string to use a variable since I've restricted that number to 10 possible dimension selections.

[antoniotiman]

Hi,

If I understant You want limit of Dim to 10.

In Condition add

and Count(DISTINCT Dim) <= 10

[#5]

Antonio - perhaps I am misinterpreting your suggestion.  Please see the attached demo of my app.  There are 130 dimensions the user can select from and I'd like to limit them to only selecting 10 dimensions.  And back to the original question, I am trying to set a calculated condition so that the resulting pivot table does not exceed 100K rows.

Edit: removed bad attachment

[antoniotiman]

Mark,

Your attachment is corrupted.

However, in calculated condition

Count(Aggr(1,$(=Concat(Dim,',')))) < 100000             (Limit 100000  Rows)

and Count(DISTINCT Dim) <= 10                               (Limit selected Dimensions   to 10)

[#7]

Hopefully this attachment works.

[antoniotiman]

Mark,

It don't work because of space in values of field AdhocFields.

You replace with

Count(Aggr(1,$(=Concat('['&AdhocFields&']',',')))) < 100000

[chriys1337]

Hello gwassenaar ,

could you please explain what your formula is doing?

sum(aggr(1, DimA, DimB, ..., DimX)

I tried the same with count() and it is the same result. Also I am not understanding why do you put after aggr( first of all a "1". Without the 1 the formula is not working.

And what does the result of the whole formula tell me?

[jhamard]

It works but eat a a lot of CPU.

Need a simple function like GetChartRowCount('CHARTID') ...

[jhamard]

Count(Aggr(1,$(=Concat(Dim,','))))

It works when you have only one measure (or if all your measure can be aggregated at the same level).

It's complicated to explain to my client that there is no way to count the exact number of rows in my dynamic table ...