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...
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.
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...
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
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 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.
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...
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.
thanks man. keep up the spirit :)
'll let u know if it works
Charlie
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!
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!
Quote from: Monty 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!
This may help as well...
http://www.ibm.com/developerworks/data/library/techarticle/dm-0808masters/
Sir Grim,
I hereby bestow on you the title of Honorary Master of IBM Links. :)
Oh! I missed out again,It is not fair,Muppet!
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
Quote from: MFGF on 28 Feb 2012 02:22:12 PM
Sir Grim,
I hereby bestow on you the title of Honorary Master of IBM Links. :)
Sweet!
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
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....
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
Are you asking about the report owner/creator?
Quote from: riley.murphy on 20 Nov 2013 09:06:27 AM
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
I have added the package name and author name to the query.
Quote from: stummala on 19 Mar 2014 07:23:23 AM
I have added the package name and author name to the query.
Why I'm not able to download the attached file?
Quote from: Suraj on 31 Jan 2011 12:28:12 PMFollow 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