I'd like to know how I can turn the native Cognos ReportPath () component into breadcrumbs. For Example:
ReportPath ():
/content/folder[@name='My Department']/folder[@name='My Application']/report[@name='My Report']
Breadcrumbs:
My Department > My Application > My Report
My specific environment uses Microsoft SQL Server. Unfortunately, the Layout Calculation expression builder does not allow the replace() function. I've tried using multiple combinations of substring, position, and character_length to manipulate the ReportPath () component, but I cannot wrap my head around how to make this work.
Please share the method you use to turn the native Cognos ReportPath () component into breadcrumbs.
You can definitely do something, but you're going to run into problems when you've got multiple subfolders - this is a starter for you
substring(reportpath(),position ('@name',ReportPath ())+7,
position(']', substring(reportpath(),position ('@name',ReportPath ())+7,99) )-2)
You'd then need to append on the folder name (using a similar process)...and I guess you could do that multiple times (as many times as the largest number of subfolders. You might need to test for 0 in the position function and append a '' in that instance.
I have a "refined" version of the audit package that comes with Cognos.
So let's concentrate around the [Run Reports] namespace.
Next, I create a data item called 'Path Delineated':
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace([Audit].[Run Reports].[Report search path], '/content/folder[@name=' , ' '),
'report[@name=' , ' '),
'folder[@name=' , ' '),
'[' , ''),
']',''),
'folder@name=', ' '),
'reportView@name=', ' '),
'dashboard@name=', ' '),
'interactiveReport@name=', ' ')
Finally I create a data item, called 'Path' based on the Path Delineated data item:
replace(replace(replace(ltrim([Path Delineated]), '"', ''), '/', ' /'), '''', '')
Lastly, I bring 'Path' into my report which appears like:
Finance / Receivables / Electronics / Past Dues
Hope this helps, God Bless!
Here's the code I came up with. I decided to not include the report name in the breadcrumbs since that report expression is available separately as <%ReportName ()%>. I used an if construct because we use the Integrated Control Suite and this accommodates the reports that have been checked out.
if ( position ( 'CAMID' , reportpath() ) > 0 ) then
(
substring (
reportpath(),
(position(')/folder[@name=',reportpath())+16),
(position(']/folder[@name',reportpath())-1) - (position(')/folder[@name=',reportpath())+16)
)
+ ' > ' +
substring (
reportpath(),
(position(']/folder[@name=',reportpath())+16),
(position(']/report[@name',reportpath())-1) - (position(']/folder[@name=',reportpath())+16)
)
)
else
(
substring (
reportpath(),
(position('/content/folder[@name=',reportpath())+23),
(position(']/folder[@name',reportpath())-1) - (position('/content/folder[@name=',reportpath())+23)
)
+ ' > ' +
substring (
reportpath(),
(position(']/folder[@name=',reportpath())+16),
(position(']/report[@name',reportpath())-1) - (position(']/folder[@name=',reportpath())+16)
)
)