Manipulating Fixed String Formats

As well as Saved Requests (in the Webcat Path), OBIEE 'Usage Tracking' creates fixed string formats for dashboard paths.

Create a nicely formatted dashboard name, using Answers you manipulate the string by combining SUBSTRING and POSITION functions:

SUBSTRING(Topic.Dashboard FROM (POSITION('_portal/' IN Topic.Dashboard)+8 ))

POSITION function finds the start of the specified string, so here the length of the string is added to give the correct startpoint for the SUBSTRING function.

From DR. OBI

Combining the functions this way works for shared dashboards only, so filter out individual user dashboards or one way to handle the situation is shown here:

CASE
WHEN POSITION('/users/' IN Topic.Dashboard) = 1 THEN 'My Dashboard'
ELSE SUBSTRING(Topic.Dashboard FROM (POSITION('_portal/' IN Topic.Dashboard)+8 ))
END

Of course, with more sophisticated string manipulation you can isolate the username for each users' dashboard.