​In Cognos 11 Auditing, what is the simplest way to parse out the report path into separate fields?

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------

#CognosAnalyticswithWatson

I have done this using a nesting of position and substring in an expression.
Honestly its a easier in Excel

------------------------------
NORBERT Bracke
------------------------------

As Norbert said, you can use position and substring. If you open the Audit FM Package in Framework Manager and view some of the query items it will give you and idea of what to do. Specifically have a look at the Package Query Item under Run Reports.

if ([Audit].[COGIPF_RUNREPORT].[PACKAGE] = '' ) then (' ')
else
if ([Audit].[COGIPF_RUNREPORT].[PACKAGE] = 'null') then (' ')
else
if(position('/package[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])>0)
then(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/package[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+16, position(']', substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/package[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+16)) -2))
else
if(position('/module[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])>0)
then(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/module[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+15, position(']', substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/module[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+15)) -2))
else
if(position('/uploadedFile[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])>0)
then(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/uploadedFile[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+21, position(']', substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/uploadedFile[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+21)) -2))
else
if(position('/dataSet2[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])>0)
then(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/dataSet2[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+17, position(']', substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/dataSet2[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+17)) -2))
else
(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], 1, character_length( [Audit].[COGIPF_RUNREPORT].[PACKAGE])))

------------------------------
DENNY NAREZNY
------------------------------

Thanks Norbert, but I really need the information in a Analytics report vs excel.​

Denny, unfortunately I don't have access to the FM Package.  I will try your code and see if I can get it to work.  I've been trying to use position and substring, but haven't gotten it work quite right. THANKYOU!

Jen

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Fri October 04, 2019 08:57 AM
From: DENNY NAREZNY
Subject: Audit -Parse report path field

As Norbert said, you can use position and substring. If you open the Audit FM Package in Framework Manager and view some of the query items it will give you and idea of what to do. Specifically have a look at the Package Query Item under Run Reports.

if ([Audit].[COGIPF_RUNREPORT].[PACKAGE] = '' ) then (' ')
else
if ([Audit].[COGIPF_RUNREPORT].[PACKAGE] = 'null') then (' ')
else
if(position('/package[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])>0)
then(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/package[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+16, position(']', substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/package[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+16)) -2))
else
if(position('/module[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])>0)
then(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/module[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+15, position(']', substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/module[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+15)) -2))
else
if(position('/uploadedFile[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])>0)
then(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/uploadedFile[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+21, position(']', substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/uploadedFile[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+21)) -2))
else
if(position('/dataSet2[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])>0)
then(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/dataSet2[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+17, position(']', substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], position('/dataSet2[', [Audit].[COGIPF_RUNREPORT].[PACKAGE])+17)) -2))
else
(substring ([Audit].[COGIPF_RUNREPORT].[PACKAGE], 1, character_length( [Audit].[COGIPF_RUNREPORT].[PACKAGE])))

------------------------------
DENNY NAREZNY
------------------------------