Hello,
I have an inline table that is defined as the following for my first selection.
RankOrganizationalDimensions:
LOAD * INLINE [
@RankOrgNum, @RankOrgDesc, @RankOrgDisplay
01, Company, 'Company'
02, Group, GroupName
03, Division, DivisionName
04, Market, MarketName
05, Facility, FacilityName
];
My second selection is dynamic:
ESLDimensions:
LOAD * INLINE [
@PLRankNum, @PLRankDesc
01, Level 1, $(vPLLevel1)
02, Inpatient Med/Surg, IPMedSurg
03, Level 2, $(vPLLevel2)
04, Level 3, $(vPLLevel3)
05, Level 4, $(vPLLevel4)
06, Level 5, $(vPLLevel5)
];
With the variable defined as:
SET vPLLevel1 = "=@PL_Level1";
SET vPLLevel2 = "=@PL_Level2";
SET vPLLevel3 = "=@PL_Level3";
SET vPLLevel4 = "=@PL_Level4";
SET vPLLevel5 = "=@PL_Level5";
I have a straight table with the following condition
GetSelectedCount(@RankOrgDesc) > 0
AND
GetSelectedCount(@PLRankDesc) > 0
AND
(GetSelectedCount(@VarFixAdHocMetric) > 0 OR GetSelectedCount(@DirIndAdHocMetric) > 0)
With each dimension in the table with a condition as =SubStringCount(Concat(@RankOrgNum,'|'),'02') - for the example of Group.
This creates an ad-hoc like report, but there are three levels of selections and I want to add a rank option to a single expression at a time. =SubStringCount(Concat(@AdHocMetricNum,'|'),'001') would be the example of getting Volume.
However, when I add a rank with the same @RankOrgNum conditional and ranking on the metric name i.e. Rank([Volume]) it will only rank at the first level. How can I get this to rank the @RankOrgDesc level by @PLRankDesc . The number of dimensions selected with typically be 2, but it could and will be more than 2 a times.
The closest I came is adding each metric's expression in a field @PLRankCalc then calling rank like this:
Where vRank = =@PLRankCalc
=MaxString({<@RankOrgDesc = {'&CHR(34)&'=RANK('&'$'&'(vRank),4)=1'&CHR(34)&'}>}@RankOrgDesc)
