Reporting service 2005: Stripping a string expression?

[joda84]

Dear reader,

Currently I’m making a report that shows the used Microsoft products in our organization. This information needs to be ordered by the market group where the computer is in. This information is stored in our OU structure. All the required information is in a sql database (by landesk) and with a query I can get the required information. Currently I have the following problem:

I use the ldaplocation to filter on the market groups, the ldap location value looks like this:

CN=C5,OU=Computers,OU=LOC-11,OU=DE1,OU=Nedap-Locations,DC=leet,DC=local
Where OU=LOC-11 is the market group, in my query I filter for ldaplocation like "%OU=LOC-11%"

CN=0018,OU=Computers,OU=General,OU=AAgri,OU=R121 - Main Hall,OU=LLLOCc-7A - AAgri,OU=NV,OU=Nedap-Locations,DC=leet,DC=local
Where OU=AAgri is the market group, in my query I filter for ldaplocation like "%OU=AAgri%"

The sorting and finding is not the problem. The problem is that I only want to display the market group name and not the entire string. As you can see the string is not always the same because there are different words before and after the market name. Is it possible to make an expression that strips down a string except for some given values?

Thanks In advance!

 

[acessn]

Try creating a calculated field with something like this:

=
Right((Fields!test.Value),
Len(Right((Fields!test.Value),(Len((Fields!test.Value))-Instr((Fields!test.Value),","))))-
Instr(
Right((Fields!test.Value),(Len((Fields!test.Value))-Instr((Fields!test.Value),",")))
,",")
)

This will return: OU=LOC-11,OU=DE1,OU=Nedap-Locations,DC=leet,DC=local

Do further string manipulation when displaying the field.

 

[joda84]

Dear Acessn,

Thanks for your reply! With that function I can strip down everything left of the market group? I was wondering, is it also possible to strip down everything left and right of the string?

 

[acessn]

Yes, if you write this as a calculated field, you could also do similar logic on the remainer of the string in the textbox you dispay it in. Just search for the comma position, and do a left.

You could of course do it directly in your calculated field, but the code gets rather messy and difficult to read.