Pivot table and Variables error locator?

[shahafei2]

Hi all,

I have a Pivot table that contains lot of vars as expressions and dimensions

there is a way to locate which var/s contain an error that cause to an empty chart to be shown?

[miguelbraga]

Hey there,

Yes, it is possible. Do as follows:

1. Go and enable one by one expression and see which causes the error.

2. Only when you've checked that go to the expression(s) that causes the error and after that check if any variable works outside the pivot chart in a KPI object or in a textbox.

3. Search if the expression gives values and if its correctly formed (might have problems in set analysis or in the syntax). It helps if you remove the label to see witch values comes to the set analysis.

Try and simplify and divide the problem in little parts to check where comes the error. Search deep on your Script, Variables, Dimensions and Measures. I'm sure you could find which causes the error.

Best regards,

MB

[marcus_sommer]

Just disable them one after another (maybe also in groups) and you will find which ones don't work.

- Marcus

[shahafei2]

Thank you but the problem is that i'm using a Pick(DimensionID,v1(A),v2(A),v3(B),v4(B)) as the expression 

because i have a Dimension and SubDimension and the expression should pick the right var for each cell in the table

[marcus_sommer]

You could add another expression like: = 1 to enforce that the table is shown but if there are a syntax-errors within one of your variables your origin expression will be probably display NULL for all but maybe the expression-label (if you left it empty) may give you a hint by hovering with the mouse which ones are causing the issue.

If this doesn't work you could try to comment some parts of your expression like:

Pick(DimensionID,v1(A),v2(A)) // ,v3(B),v4(B))

Pick(DimensionID,v1(A), /* v2(A),v3(B), */ v4(B))

otherwise you will need to check each single variable.

Beside this I'm not sure if something like v1(A) returned always a valid expression - I use usually $(v1(A)) for it.

- Marcus

[miguelbraga]

Maybe just maybe the problem is that you use the variables as is. Try using this instead:

=Pick(DimensionID,$(v1(A)),$(v2(A)),$(v3(B)),$(v4(B))).

Also, could you try and see in a text box if there is any value retrieved in each variable. Try in each variable like this:

1. First Text Box

=v1(A)

2. Second Text Box

=$(v1(A))

See if anything gives you meaningful values.

Additionally, what you want to acquire with the specific expression? What's your goal?

This could give us a hint on how the problem occurs.

Best regards,

MB

[shahafei2]

When i check each one of them desperately in text box i see a result

but when i use them in one expression with pick function it's not showing the result in the pivot table

 The HeaderID field is the RowNo of the rows in the Pivot table

[marcus_sommer]

Are you sure that your pick-approach itself is working, just check it with:

Pick(DimensionID, 1,2,3,4,....)

- Marcus

[shahafei2]

I Know that it should work (I already saw it in a different model)

[marcus_sommer]

It could only work if DimensionID is unique so that a only(DimensionID) will work - if not then use count(DimensionID) and/or concat(DimensionID, ',') to see what happens amd adjust it appropriate.

- Marcus