creating a field with values as expressions in script

[divya_anand]

Hello everyone,

Currently, I have a drill-down chart with Region->City and I have 5 expressions say A,B,C,D & E. I see 5 bars(A,B,C,D,E) when on "Region" or when I drill down to "City". But B,C,D & E are subset of A. So, I want to see only 1 bar (A) when in "Region" or "City" and I would like to see the subsets (B,C,D,E)  only when I make a selection in City.

So, my idea is that I have to create a new level "Values" on the script side and define a hierarchy (Region-> City-> Values). And this "Values" contains B,C,D & E.

Could someone help me in creating this, the script side of it?

The expression that I use now for A,B,C,D,E is something like this:

=COUNT({<Date=, Year=, Month=, Day= >} DISTINCT IF((EVT_RSTATUS = 'R' or EVT_RSTATUS = 'Q') and EVT_SCHEDEND < today() and EVT_STATUS <> 'S'
and EVT_JOBTYPE='PM'

,EVT_CODE))

EVT_JOBTYPE is different for A,B,C,D & E

Thank you very much.

[sunny_talwar]

What is the reason for creating a 5 expressions. Are these different fields within a single table? May be you want to transpose your data into rows instead of having them as separate columns using The Crosstable Load

[divya_anand]

Hi Sunny,

The reason why there are 5 expressions is that they have different values for EVT_JOBTYPE in the expression I've provided. eg: A: EVT_JOBYTYPE='PM'

                      B: EVT_JOBYTYPE='OT'

                      & so on.

I don't think changing the rows into columns is a good idea, because it is a huge database and if I make that change then it would impact the many expressions I've used on the Layout.

[Anonymous]

Hi Divya,

You dont need to create 5 expressions.

Create one variable with this:

YourVar content:

=COUNT({<Date=, Year=, Month=, Day= >} DISTINCT IF((EVT_RSTATUS = 'R' or EVT_RSTATUS = 'Q') and EVT_SCHEDEND < today() and EVT_STATUS <> 'S' and EVT_JOBTYPE=$1 ,EVT_CODE))

When you call your var:

$(YourVar('PM'))

Then, you can simplify your expression:

=COUNT({<Date=, Year=, Month=, Day=,EVT_RSTATUS = {'R','Q'},EVT_STATUS=-{'S'},EVT_JOBTYPE={$1}>} IF(EVT_SCHEDEND < today(),EVT_CODE))

Then, you can add more than one EVT_JOBTYPE, like this: $(YourVar('PM,OP1,OP2,OP3,...'))

Regards!!!

[divya_anand]

Hi Manuel,

So you mean, I must create a variable

vVar=COUNT({<Date=, Year=, Month=, Day=,EVT_RSTATUS = {'R','Q'},EVT_STATUS=-{'S'},EVT_JOBTYPE={$1}>} IF(EVT_SCHEDEND < today(),EVT_CODE))

and have 1 expression for B,C,D & E as say

B: $(YourVar('PM'))

C: $(YourVar('OT'))

& so on.

right?

[Anonymous]

Hi Divya,

Yes, you can use variables like a function using parameters. $1 is your parameter, and you pass the values like this $(YourVar(ParameterValue)). If your parameter is a string or a formated date, use single quotes.

Regards!!!