Using calculated dimension that uses a variable to determine one of the columns required in a pivot report..

[#1]

Hi all,

I am facing a peculiar problem when using a calculated dimension in a pivot report. (on QV version 8.5 and 9.0)

I am making use of a variable that would determine what dimension field the report should use. e.g. =$(vDimLevel)

Everything works well, the report gets populated with the required dimension values, untill I click on the "CLEAR" button. I get a runtime error and I have to close the program.

When I add the same dimension explicitly, there is no problem with the data or its refresh.

Suggestions/ help is most appreciated and also required urgently 🙂

Thanks much!!

[#2]

Hi,

I d'ont understand what you're trying to do, the user have to write the name of the dimension he wants ?

Why not using a Group with different possible dimension to choose the dimension ?

[#3]

Try using

=[$(vDimLevel)]

[amit_shetty78]

Just a thought: May be when you click on clear it clears the value in the variable also and you end up having a chart without a dimension which I guess is not possible to have and leading to the runtime error.

-Amit.

[#5]

Thanks folks for responding so quickly!!

Amit, you hit the nail on the head!!

Ofcourse the variable was pointing to a selected data field, and it would have no value when selections were cleared up.

BUT now I am struggling to get the condition working for the calculated dimension. I am trying to use another field to be used when "the" variable gets cleared up. The expression looks like,

=if(len('$(vDimLevel)')>0,$(vDimLevel),[Corporate]) when I use the field name itself

OR

=if(len('$(vDimLevel)')>0,$(vDimLevel),$(vCorporate)) when I use another variable which always points to field "Corporate" similar to $(vDimLevel)

NEITHER SEEMS TO BE WORKING!!

Any ideas on this one 🙂

Thanks again!!

[#6]

Should have added. The $(vDimLevel) works but the [Corporate] / $(vCorporate) piece does not....

[#7]

I'll ask a question that was asked previously...

Why can't you create a dimension group (either cyclic or drill-down) to be able to choose different dimensions? (Dimensions tab, Edit Groups button)

What you're explaining seems like a perfect place for a cyclic group.

sjprows