Challenge: using part of the value of a field in set analysis and expression?

[germanboxers]

I need to parse a variable that is set from the variable input extension and use the parsed string in two ways in a single expression.  I cannot change the data model for complicated reasons.

Currently I have 9 sheets to convey backlog conditions for 9 units designated by letters.  If I can parse the variable in two spots in the expression, I can reduce 9 sheets to one sheet and add much better understanding.  The full expression looks like this now:

sum({$<csp_calendar.plan_facility_cd={'B'}>}csp_calendar.plan_tons * [yield.b])

The variable that is set from the variable input extension is Facility_Backlog.  The values for this variable take the form "b_backlog_type",  "p_backlog_type", or "c_backlog_type", etc.  I need to parse the first letter in the variable's value (i.e. blue letter) and insert it in the two places highlighted by red in the original expression above:

1. The first place to use it is in the set analysis in the expression.  I have tried variations on the following bolded red  part, but what I've tried didn't work.
   sum({$<csp_calendar.plan_facility_cd={'$(Capitalize(Left(Facility_Backlog,1)))'}>}csp_calendar.plan_tons * [yield.b])
2. The second place I need to use it is in the measure portion of the expression.  I need to concatenate "yield." with the first letter of the value of the variable.  I tested the bolded below, but that didn't work either.
   =sum({$<csp_calendar.plan_facility_cd={'B'}>}csp_calendar.plan_tons * (="$('yield.' & Left(Facility_Backlog,1))"))

Any help would be greatly appreciated.

Thanks,

Jordan

[germanboxers]

Currently I have 9 sheets to convey backlog conditions for 9 units designated by letters.  If I can parse the variable in two spots in the expression, I can reduce 9 sheets to one sheet and add much better understanding.  The full expression looks like this now:

sum({$<csp_calendar.plan_facility_cd={'B'}>}csp_calendar.plan_tons * [yield.b])

The variable that is set from the variable input extension is Facility_Backlog.  The values for this variable take the form "b_backlog_type",  "p_backlog_type", or "c_backlog_type", etc.  I need to parse the first letter in the variable's value (i.e. blue letter) and insert it in the two places highlighted by red in the original expression above:

1. The first place to use it is in the set analysis in the expression.  I have tried variations on the following bolded red  part, but what I've tried didn't work.
   sum({$<csp_calendar.plan_facility_cd={'$(Capitalize(Left(Facility_Backlog,1)))'}>}csp_calendar.plan_tons * [yield.b])
2. The second place I need to use it is in the measure portion of the expression.  I need to concatenate "yield." with the first letter of the value of the variable.  I tested the bolded below, but that didn't work either.
   =sum({$<csp_calendar.plan_facility_cd={'B'}>}csp_calendar.plan_tons * (="$('yield.' & Left(Facility_Backlog,1))"))

Any help would be greatly appreciated.

Thanks,

Jordan

[shwethaa]

Hi Jordon,

Try below expression in the set expression:

1)
'$(=Capitalize(left('$(Facility_backlog)',1)))'

2)
'$(=left('$(Facility_backlog)',1))'

Let me know if this works.

Regards,

Shwetha A

[germanboxers · Accepted solution]

Thank you for replying, Shwethaa!  Ultimately, I decided to handle most of this with variables and it works beautifully.  I still parse the elements I need from the data fields, but do so in the variables declaration.  It's much easier to manage too.

This is the full expression now:

=sum({$<$(vPlan_Facility)={$(vFacility)}>*<$(vFacilityPlanDate)={"$(=Date(vRep_Date))"}>}$(vFacilityPlanTons) * $(vYield))

And a snippet of how the single screen looks, compared to having 9 different sheets.