I have developed a customisable table allowing users to select dimensions and metrics from a list.
I am loading the following Excel table in my script -
|Number of enrolments||1||Activity|
|Number of credits||2||Activity|
|% of total enrolments||3||Activity|
|% of total credits||4||Activity|
|% completed successful||5||Success|
|Number completed successful||6||Success|
|% partial success||7||Success|
|Number partial success||8||Success|
|% further withdrawal||9||Success|
|Number further withdrawal||10||Success|
|% early withdrawal||11||Success|
|Number early withdrawal||12||Success|
I am using the conditional checkbox for the expression and (as an example for Metric number 12) using the following expression -
SubStringCount(Concat(_metricsNo, '|'), '12')
However, whenever a user selects metrics number 10,11,12 the table also shows metric 1 and 2. How do I change the expression to avoid that?
I also have created customizable pivot table objects using a similar concept.
What I find works well is creating list boxes which are presented a toggle-able (check boxes) and then using this as the expression for the condition for each dimension (or expression, I have choices for each).
GetFieldSelections(_metrics) LIKE '*Number of enrolments*' OR GetFieldSelections(_metrics) LIKE '*ALL*'
Put a version of this with each metric name within the first LIKE criteria.
The drawback with this method is that when the selections default to 'NOT Number of enrolments' because all but that are selected, it breaks it, but I don't find people tend to use that many metrics, so it works well.
Also, I'd set a pivot to always fully expanded. That works best.
Hope this helps,
we usually wrap the ID in non-printables (chr(30)).
$(vSep) & _noMetrics & $(vSep)as _noMetrics
You need to wrap your _metricsNo in those non-printables within script and can access them from UI as following:
SubStringCount(Concat(Dim), vSep & '12' & vSep )
Hope this helps.
the problem is, that if you select metric 10 you Concat-String looks like '10'.
SubstringCount('10', '1') is true.
Just add a separator around each metricNo (e.g. chr(30))
Now you can use SubstringCount(Concat(MetricNo), chr(30) & '1' & chr(30)) (chr(30) = record separator)
If you select metric 10 your Concat String looks like '[RS]10[RS]' and SubstringCount('[RS]10[RS]', '[RS]1[RS]') is false
try with below expressions for each metrics
1) To display metrics with number 10
=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|10|*')
2) To display metrics with number 11
=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|11|*')
3) To display metrics with number 12
=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|12|*')
4) To display metrics with number 1
=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|1|*')
like wise for all the expression number