Cognos 8查询以查找所有报告和列名称 [英] Cognos 8 Query to find all Report and Column Names

问题描述

我想查询Cognos 8中的元数据以查找所有报告和列名称。如果可能的话，我想包括列定义。

I want to query the meta data in Cognos 8 to find all report and column names. If possible, I'd like to include the column definitions.

我可以使用Cognos报告执行此操作还是需要查询某些存储库？

Can I do this using a Cognos report or do I need to query some repository?

谢谢。

推荐答案

您可以从以下列表中选择报告列表内容存储，并带有以下查询：

You can select a list of reports from the content store with the following query:

SELECT     CMOBJNAMES_BASE.NAME AS ObjName, CMOBJECTS.PCMID, CMCLASSES.NAME AS ClassName, CMOBJPROPS7.spec
FROM       CMOBJECTS 
JOIN       CMOBJNAMES_BASE ON CMOBJECTS.CMID    = CMOBJNAMES_BASE.CMID
JOIN       CMCLASSES       ON CMOBJECTS.CLASSID = CMCLASSES.CLASSID
LEFT JOIN  CMOBJPROPS7     ON CMOBJECTS.CMID = CMOBJPROPS7.CMID
WHERE     CMOBJECTS.CLASSID IN (10, 37)
ORDER BY CMOBJECTS.PCMID;

我在Cognos 10中使用它。我相信在Cognos 8中，CMOBJNAMES_BASE表实际上被命名为 CMOBJNAMES

I use that in Cognos 10. I believe in cognos 8 the CMOBJNAMES_BASE table is actually named 'CMOBJNAMES' without the _BASE.

更新：已经过测试并可以在Cognos 11r9中使用。

UPDATE: Has been tested and works in Cognos 11r9.

报表元数据已存储在CMOBJPROPS7的 SPEC列中为XML。您可以解析此XML，以去除报表中使用的列。

The Report metadata is stored in the 'SPEC' column of CMOBJPROPS7 as XML. You can parse this XML in order to strip out the columns used in the report. It will not be a simple task.

如果有时间但没有钱，可以编写自己的代码来解析该XML。如果您的金钱超过时间，则可以购买第三方程序来完成此任务，例如Motio或BSP Metamanager。

If you have time but not money, you can write your own code to parse that XML. If you have more money than time, you can buy a 3rd party program to accomplish this, such as Motio or BSP Metamanager.

上面的查询对于构建干净的列列表，但是非常适合搜索特定的数据项。例如，您有要在数据源中更改的列，但是不确定哪个报表使用该列。运行上面的查询，并搜索数据项。它将以Cognos MDX格式（即）嵌入到XML中。 [演示文稿视图]。[销售摘要]。[销售]

The query above is less useful for building a clean list of columns, but great for searching for specific data items. For example, you have column you are wanting to change in a data source, but you are not sure which report uses that column. Run the query above, and search for the data item. It will be embedded within the XML in the Cognos MDX format, ie. [Presentation View].[Sales Summary].[Sales]

编辑：如下所示，这是一个包含文件夹路径的查询。

As requested below, here is a query that includes folder paths.

-- List of Reports, the folder they are in, and the package they are using
select distinct temp2.name as package,temp1.folder,temp1.name from
(SELECT    temp.PARENTNAME AS FOLDER,CMOBJECTS.PCMID,CMOBJNAMES.CMID, CMOBJNAMES.LOCALEID, CMOBJNAMES.MAPDLOCALEID, CMOBJNAMES.ISDEFAULT, CMOBJNAMES.NAME, 
                      CMOBJECTS.CLASSID
FROM         CMOBJNAMES INNER JOIN
                      CMOBJECTS ON CMOBJNAMES.CMID = CMOBJECTS.CMID
INNER JOIN
(SELECT P.CMID AS PARENT,P.NAME AS PARENTNAME FROM CMOBJNAMES P where P.LOCALEID between 24 and 52) temp
ON CMOBJECTS.PCMID = TEMP.PARENT
WHERE     (CMOBJECTS.CLASSID = 10)
AND SUBSTR(TEMP.PARENTNAME,1,1) NOT IN ('1','2','3','4','5','6','7','8','9') AND
TEMP.PARENTNAME NOT LIKE 'Backup%') temp1
inner join
(SELECT  CMREFNOORD1.CMID AS PID, CMREFNOORD1.REFCMID, CMOBJNAMES.NAME
FROM         CMREFNOORD1 INNER JOIN
                      CMOBJECTS ON CMREFNOORD1.REFCMID = CMOBJECTS.CMID INNER JOIN
                      CMOBJNAMES ON CMOBJECTS.CMID = CMOBJNAMES.CMID
WHERE     (CMREFNOORD1.PROPID = 31 AND CMOBJNAMES.LOCALEID between 24 and 52)) temp2
on temp1.cmid = temp2.pid and LOCALEID between 24 and 52;

这篇关于Cognos 8查询以查找所有报告和列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！