How to get Report path from content store tables....

[mycognos]

Hi,

Can anyone tell me how to get the report path from the contentstore tables. I am using cognos8.3. I have managed to get the report names by querying the tables CMOBJNAMES_BASE and CMOBJPROPS7. Still couldnt find where the path information is...any help would be appreciated......thanks...

[crn.siva]

Hi,

If you want get the report path--> simply goto the cognos connection in that select what ever the report path you want in the right side one option called set properties in that you get the report path,

I think it is useful for you.

I don't know to get the report path in the content store.

[mycognos]

hi,

actually i was analising the metadata to find out some object dependencies, in this case i need to get the report name and path from the content store tables not form cognos connection. Report name is available but not the path...

[charlie]

Is it not possible to do a query on content store such that the Reports and their Paths are selected for all reports. It becomes cumbersome to go and see the properties of each report.

Help gurus.

Charlie

[Suraj]

Follow IBM documentation to enable url_xml as a data source.
Then audit sample report 'Report Usage' actually gives report path.
Modify that report to what you want in the report.
For example, I get this from the report:

Rank   ReportPath   Times used   Last used on
-1   /content/package[@name='Audit']/report[@name='Report usage no. of runs with last date used']   5   01/31/2011
-2   /content/package[@name='ProjectTracker']/report[@name='Current Project Assignments by WORKGROUP']   4   01/31/2011

Reports in user's my folders will have CAMID instead of user's name:
   CAMID("DevAD:u:658f881c00f0304d8cd679087059f8f4")/folder[@name='My Folders']/report[@name='Test for package move to folder']   1   01/31/2011

[FM]

i highly doubt you can get anything from the cognos content store. besides of which they are free to, and they change the layout of the "RDBMS" between versions.

your best bet might be to use the SDK. assuming you it, it should allow query cognos and search for a specific report and get anything you want for it, whether it be a report path, latest version, or anything else.

[riley.murphy]

If you are using Sql server, please email me - I can help you out....I've written some scripts to create metadata tables with schedule info, email address info, and path info for report objects...

[riley.murphy]

If you are using sql server....download attached script.

Replace the 'BI_Admin' database string in this script with the name of the database you will be storing this data in (don't store it in your content store DB).

replace 'c8_content_store.dbo.'  with the name of your content store database.

then run this script. it will create some stored procs and some tables.

Then, execute the 'ExtractContentStoreObjects' that this script creates.

Then query the 'ContentStoreObjects' table, and look at the 'object_path' field for the paths.

If someone figures out how to modify this to get the package info (with package path) for each report, please share and repost.

This was built for 8.3 - Don't hate me if Cognos changes their content store schema. I reverse engineered it, and wrote this script. I've got a lot of good info online for cognos...Figured I'd contribute something for once.

[charlie]

thanks man. keep up the spirit :)

'll let u know if it works

Charlie

[subah]

Thank you Riley for your valuable script. I was able to use it and extend it a bit more to get the report path.

I only used the select statement since I didn't want to create tables. The catch in the code though is that you will need to know the maximum folder level in your system. So for e.g. your public or private folder is level 1 and thereafter it increments for every folder level. Below is what I did after establishing that we have 10 levels of folder in our system.

You will have to filter out the report paths being null to get what you want. To expand the script's scope to even more levels just add additional levels to the the select, from and where clauses (the script is intiuitive for sql folks). Feel free to post if you have any questions!

[Monty]

Thanks Riley and Subah for your scripts, they have been helpful in understanding the cognos 8 content store DB. As they say, sharing is caring - cheers for your effort!

[Grim]

Thanks Riley and Subah for your scripts, they have been helpful in understanding the cognos 8 content store DB. As they say, sharing is caring - cheers for your effort!

This may help as well...
http://www.ibm.com/developerworks/data/library/techarticle/dm-0808masters/

[MFGF]

Sir Grim,

I hereby bestow on you the title of Honorary Master of IBM Links. :)

[barrysaab]

Oh! I missed out again,It is not fair,Muppet!

[RobsWalker68]

Hi,

As it has yet to be mentioned

http://www.ibm.com/developerworks/data/library/cognos/development/utilities/page509.html

Install the Cognos 8 or Cognos 10 Audit extension and perform a content Audit.  This will collect all relevant content information in your content store including report paths.

Rgds

Rob

[Grim]

Sir Grim,

I hereby bestow on you the title of Honorary Master of IBM Links. :)

Sweet!

[cccschip]

I believe this is the query (in Oracle) that was desired...

    select cmid, objname, SYS_CONNECT_BY_PATH(o.objname, ' > ') path
    from (
           select obj.CMID CMID, case when obj.cmid = obj.pcmid then null else obj.PCMID end PCMID, nm.name objname
           from CMOBJECTS obj inner join CMOBJNAMES nm on obj.cmid = nm.cmid
           where nm.isdefault = 1
         ) o
    where objname like '???'               -- <<<< Your object name here
    start with o.PCMID = 0
    connect by prior o.CMID = o.PCMID

[michal]

sorry to resurrect an old beast, but i'm curious

any way to get at the attached files discussed in this thread?  i cant seem to find them anymore :/
while the audit extension can be used, figuring out what reports in your portal are actually used or not is a chore.

how do i find what reports ARENT used anymore?

i find it rather disappointing that BI cant really audit itself very well.

edit : oooops, after logging in i was able to download the attachment.  sorry.!

Still, if anyone knows of a good way to audit unused reports / packages etc please share....

[mpradeep22]

Thank you Riley for your valuable script. I was able to use it and extend it a bit more to get the report path.

I only used the select statement since I didn't want to create tables. The catch in the code though is that you will need to know the maximum folder level in your system. So for e.g. your public or private folder is level 1 and thereafter it increments for every folder level. Below is what I did after establishing that we have 10 levels of folder in our system.

You will have to filter out the report paths being null to get what you want. To expand the script's scope to even more levels just add additional levels to the the select, from and where clauses (the script is intiuitive for sql folks). Feel free to post if you have any questions!
* Report path.txt (7.42 kB - downloaded 80 times.)

thank you very much subash, for your valuable information..... it helps me alot...

can I get the user name of the particular report in the same query???

Thanks inadvance

[riley.murphy]

Are you asking about the report owner/creator?

[mpradeep22]

Are you asking about the report owner/creator?

Yes Riley..... exactly.

i need to include the owner of the report also into the same querey

Thanks in advance

[stummala]

I have added the package name and author name to the query.

[Ravisha]

I have added the package name and author name to the query.

Why I'm not able to download the attached file?

[singh.prabhleen123]

Follow IBM documentation to enable url_xml as a data source.
Then audit sample report 'Report Usage' actually gives report path.
Modify that report to what you want in the report.
For example, I get this from the report:

Rank   ReportPath   Times used   Last used on
-1   /content/package[@name='Audit']/report[@name='Report usage no. of runs with last date used']   5   01/31/2011
-2   /content/package[@name='ProjectTracker']/report[@name='Current Project Assignments by WORKGROUP']   4   01/31/2011

Reports in user's my folders will have CAMID instead of user's name:
   CAMID("DevAD:u:658f881c00f0304d8cd679087059f8f4")/folder[@name='My Folders']/report[@name='Test for package move to folder']   1   01/31/2011

I was able to get the list of report and its path using this.
Thanks a lot!!

I exported the report data as excel file and did data manipulation on the path field to clean it up.
I used power query in Excel to split it up using delimiter and then in the end combined all the split columns to get a clean path to the report