Author Topic: How to get Report path from content store tables....  (Read 42718 times)

Offline mycognos

  • Full Member
  • ***
  • Join Date: Oct 2008
  • Posts: 41
  • Forum Citizenship: +0/-0
How to get Report path from content store tables....
« on: 15 Jul 2009 06:32:36 am »
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...

Offline kss001

  • Full Member
  • ***
  • Join Date: Jan 2009
  • Posts: 37
  • Forum Citizenship: +2/-0
Re: How to get Report path from content store tables....
« Reply #1 on: 15 Jul 2009 07:58:22 am »
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.


Offline mycognos

  • Full Member
  • ***
  • Join Date: Oct 2008
  • Posts: 41
  • Forum Citizenship: +0/-0
Re: How to get Report path from content store tables....
« Reply #2 on: 16 Jul 2009 04:24:46 am »
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...

Offline charlie

  • Senior Member
  • ****
  • Join Date: Oct 2010
  • Posts: 59
  • Forum Citizenship: +3/-1
Re: How to get Report path from content store tables....
« Reply #3 on: 31 Jan 2011 10:44:10 am »
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

Offline Suraj Neupane

  • Statesman
  • ******
  • Join Date: Apr 2006
  • Posts: 550
  • Forum Citizenship: +21/-2
    • Ittoolbox
Re: How to get Report path from content store tables....
« Reply #4 on: 31 Jan 2011 12:28:12 pm »
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
--
Suraj Neupane
Moderator: communities.cognos.com

Offline FM

  • Full Member
  • ***
  • Join Date: Jul 2010
  • Posts: 28
  • Forum Citizenship: +0/-0
Re: How to get Report path from content store tables....
« Reply #5 on: 05 Feb 2011 11:22:17 am »
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.

Offline riley.murphy

  • Associate
  • **
  • Join Date: Feb 2011
  • Posts: 4
  • Forum Citizenship: +2/-0
Re: How to get Report path from content store tables....
« Reply #6 on: 08 Feb 2011 12:51:13 pm »
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...

Offline riley.murphy

  • Associate
  • **
  • Join Date: Feb 2011
  • Posts: 4
  • Forum Citizenship: +2/-0
Re: How to get Report path from content store tables....
« Reply #7 on: 08 Feb 2011 01:08:06 pm »
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.



Offline charlie

  • Senior Member
  • ****
  • Join Date: Oct 2010
  • Posts: 59
  • Forum Citizenship: +3/-1
Re: How to get Report path from content store tables....
« Reply #8 on: 09 Feb 2011 09:32:39 am »
thanks man. keep up the spirit :)

'll let u know if it works

Charlie

Offline subah

  • Associate
  • **
  • Join Date: Jan 2012
  • Posts: 1
  • Forum Citizenship: +0/-0
Re: How to get Report path from content store tables....
« Reply #9 on: 02 Feb 2012 09:43:40 am »
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!

Offline Monty

  • Associate
  • **
  • Join Date: Feb 2012
  • Posts: 1
  • Forum Citizenship: +0/-0
Re: How to get Report path from content store tables....
« Reply #10 on: 27 Feb 2012 06:36:37 am »
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!

Offline Grim

  • Honorary Master of IBM Links
  • Statesman
  • ******
  • Join Date: Nov 2011
  • Posts: 688
  • Forum Citizenship: +58/-7
Re: How to get Report path from content store tables....
« Reply #11 on: 27 Feb 2012 07:24:33 am »
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/
"Honorary Master of IBM Links"- MFGF
Certified IBM C8 & C10 Admin, Gamer, Geek and all around nice guy.
<-Applaud if my rant helped! 8)

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 9,988
  • Forum Citizenship: +600/-9
  • Cognos Software Muppet
Re: How to get Report path from content store tables....
« Reply #12 on: 28 Feb 2012 02:22:12 pm »
Sir Grim,

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

Meep!

Offline barrysaab

  • Community Leader
  • *****
  • Join Date: Jun 2010
  • Posts: 234
  • Forum Citizenship: +0/-3
Re: How to get Report path from content store tables....
« Reply #13 on: 29 Feb 2012 03:13:43 am »
Oh! I missed out again,It is not fair,Muppet!
Boy! Cognos getting on to me!!!

Offline RobsWalker68

  • Statesman
  • ******
  • Join Date: Apr 2009
  • Posts: 299
  • Forum Citizenship: +28/-1
    • Linkedin Profile
Re: How to get Report path from content store tables....
« Reply #14 on: 29 Feb 2012 04:25:09 am »
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

 


       
Twittear