Function in Business Objects

Govinda2020
Govinda2020 used Ask the Experts™
on
While there is no function that I know of would it be possible to create variable/function to display just last part of the string after last \.  For example the Path (World\Countries\USA).I would need just USA to display in the report.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
WHich BO tool/application are you using?

mlmcc

Author

Commented:
I am using Webi 3.1 with Universe
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are there always 2 \'s in the string?

Do you always want the last substring?

mlmcc
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

Commented:
No, it can vary.But, I always need substring after last \
I haven't used WebI, but I assume that you're using the BO report designer, not CR.  This would be easy in CR.  :-)

 I've only used BO 6.  I don't see an easy way to do this there, but later versions may include new features that would help.  FWIW, you could look for a string function that reverses the string, or does a string search like the old BO Pos function, but starts at the end of the string, instead of the beginning.

 If you don't have anything like that, is there a limit on how many "\" characters you will have?  I've come up with something that seems to work, but it uses a separate step for each "\", so you have to decide up front how many of those there could be and include a step for each one.  Extra steps aren't a problem.  You just have to make sure you have enough steps to handle every "\" that the field could contain.

 The basic idea is to create a series of variables, each of which extracts everything after a "\" and produces the result.  The first variable works on the field.  The second variable works on the result from the first variable.  The third variable works on the result from the second variable.  And so on.  If the field or previous variable doesn't include a "\", the variable just produces that field/variable without any changes.  IOW, each variable removes everything up to the first "\", and passes the rest along.  When there are no more "\"s, whatever is left is passed along.  You would put the last variable on the report.

 So, for example, if there were four variables, and using your example string of "World\Countries\USA", you'd get:

var1 = Countries\USA
var2 = USA
var3 = USA
var4 = USA

 You'd put var4 on the report.


 The first variable would look like this:

=If Match(<your field> ,"*\*") Then SubStr(<your field> ,Pos(<your field> ,"\")+1 ,99) Else <your field>


 The second variable would look like this:

=If Match(<first variable> ,"*\*") Then SubStr(<first variable> ,Pos(<first variable> ,"\") +1 ,99) Else <first variable>


 The third variable would be the same, but with each "first variable" replaced with the name of the second variable.  And so on, for however many variables you need.  Put the last variable on the report.

 The 99 in the Substr function in each variable is how many characters it should extract, starting after the "\".  If your field is longer than 99 characters, change that accordingly.  Making the number larger than the field doesn't seem to be a problem.  I tried 999 on a field that's probably 40 or 50 characters and had no problems.

 Hope this helps.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
This is just a shot in the dark.

Replace (YourString;"*/";"")

mlmcc

Author

Commented:
Thanks for the comment , it was very well explained
Govinda2020,

 You're welcome.  Glad I could help.


 mlmcc,

 FWIW, as far as I can tell, the BO 6.5 Replace doesn't support wildcards.  There's no mention of them in the Help.  But since you brought it up, I tried * and %, just for the heck of it.  No go.  Of course it's possible that later versions support them.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Thanks James.  I didn't have a BO install to test against.  I didn't expect it to work but thought if it did it would return exactly what was needed.

If I get time I'll test it in BO XI 4

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
DOesn't work in BO XI 4

mlmcc
It was worth a shot.  :-)

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial