Hi,

I would like to pull a list of all reports that have hyperlink button object used in them. Can this be done using the free version of Motio PI?.

On checking the documentation of Motio Pi, I could not find out if this can be done using free version of Motio PI. Please suggest. is there any other way this can be done?.

Best Regards,

Hello!

You can execute SQL query to CS:

SELECT
    "Query_CS"."CMID" AS "CMID", 
    "Query_CS"."OBJNAME" AS "OBJNAME", 
    "Query_CS"."PATH0" AS "PATH0", 
    "Query_CS"."CREATED" AS "CREATED", 
    "GetSpec"."SPEC" AS "SPEC"
FROM
    (
    SELECT
        "SQL1"."LEV" AS "LEV", 
        "SQL1"."CMID" AS "CMID", 
        "SQL1"."PCMID" AS "PCMID", 
        "SQL1"."OBJNAME" AS "OBJNAME", 
        "SQL1"."PATH" AS "PATH0", 
        "SQL1"."CLASSID" AS "CLASSID", 
        "SQL1"."CREATED" AS "CREATED"
    FROM
        (
        
select t.* from(
select level as lev, cmid, o.pcmid, objname, SYS_CONNECT_BY_PATH(o.objname, ' > ') path, o.CLASSID, o.CREATED
                from (
                       select obj.CMID CMID
                        , case 
                                when obj.cmid = obj.pcmid then null 
                                else obj.PCMID 
                          end PCMID
                        , nm.name objname
                        , obj.CLASSID,
                obj.CREATED
                       from CMOBJECTS obj 
                            inner join CMOBJNAMES nm on obj.cmid = nm.cmid
                       where nm.isdefault = 1 
                     ) o
                start with o.PCMID = 0
                connect by prior o.CMID = o.PCMID) t
where t.CLASSID =10 
        ) "SQL1"
    ) "Query_CS"
        INNER JOIN 
        (
        SELECT
            s."CMID", 
            s."SPEC"
        FROM CMOBJPROPS7 s
        ) "GetSpec"
        ON "Query_CS"."CMID" = "GetSpec"."CMID" 
WHERE instr("GetSpec"."SPEC", 'HTMLItem') > 0

Hi,

I would like to pull a list of all reports that have hyperlink button object used in them. Can this be done using the free version of Motio PI?.

On checking the documentation of Motio Pi, I could not find out if this can be done using free version of Motio PI. Please suggest. is there any other way this can be done?.

Best Regards,

Hi Ilyas,

Thank you. Can I run the query you provided on the Content Store DB as it is or is any modification required? I need to find out where content store DB  is located in our Cognos environment and try it.

Going back to my first question, can the same thing be done using Motio PI (free version)? I assume this is something that can be achieved using Motio PI Pro. Please let me know if you are aware of it.

Best Regards,

Hello!

You can execute SQL query to CS:

SELECT
    "Query_CS"."CMID" AS "CMID", 
    "Query_CS"."OBJNAME" AS "OBJNAME", 
    "Query_CS"."PATH0" AS "PATH0", 
    "Query_CS"."CREATED" AS "CREATED", 
    "GetSpec"."SPEC" AS "SPEC"
FROM
    (
    SELECT
        "SQL1"."LEV" AS "LEV", 
        "SQL1"."CMID" AS "CMID", 
        "SQL1"."PCMID" AS "PCMID", 
        "SQL1"."OBJNAME" AS "OBJNAME", 
        "SQL1"."PATH" AS "PATH0", 
        "SQL1"."CLASSID" AS "CLASSID", 
        "SQL1"."CREATED" AS "CREATED"
    FROM
        (
        
select t.* from(
select level as lev, cmid, o.pcmid, objname, SYS_CONNECT_BY_PATH(o.objname, ' > ') path, o.CLASSID, o.CREATED
                from (
                       select obj.CMID CMID
                        , case 
                                when obj.cmid = obj.pcmid then null 
                                else obj.PCMID 
                          end PCMID
                        , nm.name objname
                        , obj.CLASSID,
                obj.CREATED
                       from CMOBJECTS obj 
                            inner join CMOBJNAMES nm on obj.cmid = nm.cmid
                       where nm.isdefault = 1 
                     ) o
                start with o.PCMID = 0
                connect by prior o.CMID = o.PCMID) t
where t.CLASSID =10 
        ) "SQL1"
    ) "Query_CS"
        INNER JOIN 
        (
        SELECT
            s."CMID", 
            s."SPEC"
        FROM CMOBJPROPS7 s
        ) "GetSpec"
        ON "Query_CS"."CMID" = "GetSpec"."CMID" 
WHERE instr("GetSpec"."SPEC", 'HTMLItem') > 0

Hi,

I would like to pull a list of all reports that have hyperlink button object used in them. Can this be done using the free version of Motio PI?.

On checking the documentation of Motio Pi, I could not find out if this can be done using free version of Motio PI. Please suggest. is there any other way this can be done?.

Best Regards,

1. Yes, you can run this query without modification.
2. This is not possible in the free version.

Hi Ilyas,

Thank you. Can I run the query you provided on the Content Store DB as it is or is any modification required? I need to find out where content store DB  is located in our Cognos environment and try it.

Going back to my first question, can the same thing be done using Motio PI (free version)? I assume this is something that can be achieved using Motio PI Pro. Please let me know if you are aware of it.

Best Regards,

Hello!

You can execute SQL query to CS:

SELECT
    "Query_CS"."CMID" AS "CMID", 
    "Query_CS"."OBJNAME" AS "OBJNAME", 
    "Query_CS"."PATH0" AS "PATH0", 
    "Query_CS"."CREATED" AS "CREATED", 
    "GetSpec"."SPEC" AS "SPEC"
FROM
    (
    SELECT
        "SQL1"."LEV" AS "LEV", 
        "SQL1"."CMID" AS "CMID", 
        "SQL1"."PCMID" AS "PCMID", 
        "SQL1"."OBJNAME" AS "OBJNAME", 
        "SQL1"."PATH" AS "PATH0", 
        "SQL1"."CLASSID" AS "CLASSID", 
        "SQL1"."CREATED" AS "CREATED"
    FROM
        (
        
select t.* from(
select level as lev, cmid, o.pcmid, objname, SYS_CONNECT_BY_PATH(o.objname, ' > ') path, o.CLASSID, o.CREATED
                from (
                       select obj.CMID CMID
                        , case 
                                when obj.cmid = obj.pcmid then null 
                                else obj.PCMID 
                          end PCMID
                        , nm.name objname
                        , obj.CLASSID,
                obj.CREATED
                       from CMOBJECTS obj 
                            inner join CMOBJNAMES nm on obj.cmid = nm.cmid
                       where nm.isdefault = 1 
                     ) o
                start with o.PCMID = 0
                connect by prior o.CMID = o.PCMID) t
where t.CLASSID =10 
        ) "SQL1"
    ) "Query_CS"
        INNER JOIN 
        (
        SELECT
            s."CMID", 
            s."SPEC"
        FROM CMOBJPROPS7 s
        ) "GetSpec"
        ON "Query_CS"."CMID" = "GetSpec"."CMID" 
WHERE instr("GetSpec"."SPEC", 'HTMLItem') > 0

Hi,

I would like to pull a list of all reports that have hyperlink button object used in them. Can this be done using the free version of Motio PI?.

On checking the documentation of Motio Pi, I could not find out if this can be done using free version of Motio PI. Please suggest. is there any other way this can be done?.

Best Regards,

@Ilyas Yulyev, 

I requested the DB admin to run the query on content store and it gave the following errors. The content Store is on MS SQL Server.

Msg 195, Level 15, State 10, Line 21 'SYS_CONNECT_BY_PATH' is not a recognized built-in function name. Msg 102, Level 15, State 1, Line 34 Incorrect syntax near 'o'. Msg 319, Level 15, State 1, Line 35 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Msg 102, Level 15, State 1, Line 46 Incorrect syntax near 'GetSpec'.

1. Yes, you can run this query without modification.
2. This is not possible in the free version.

Hi Ilyas,

Thank you. Can I run the query you provided on the Content Store DB as it is or is any modification required? I need to find out where content store DB  is located in our Cognos environment and try it.

Going back to my first question, can the same thing be done using Motio PI (free version)? I assume this is something that can be achieved using Motio PI Pro. Please let me know if you are aware of it.

Best Regards,

Hello!

You can execute SQL query to CS:

SELECT
    "Query_CS"."CMID" AS "CMID", 
    "Query_CS"."OBJNAME" AS "OBJNAME", 
    "Query_CS"."PATH0" AS "PATH0", 
    "Query_CS"."CREATED" AS "CREATED", 
    "GetSpec"."SPEC" AS "SPEC"
FROM
    (
    SELECT
        "SQL1"."LEV" AS "LEV", 
        "SQL1"."CMID" AS "CMID", 
        "SQL1"."PCMID" AS "PCMID", 
        "SQL1"."OBJNAME" AS "OBJNAME", 
        "SQL1"."PATH" AS "PATH0", 
        "SQL1"."CLASSID" AS "CLASSID", 
        "SQL1"."CREATED" AS "CREATED"
    FROM
        (
        
select t.* from(
select level as lev, cmid, o.pcmid, objname, SYS_CONNECT_BY_PATH(o.objname, ' > ') path, o.CLASSID, o.CREATED
                from (
                       select obj.CMID CMID
                        , case 
                                when obj.cmid = obj.pcmid then null 
                                else obj.PCMID 
                          end PCMID
                        , nm.name objname
                        , obj.CLASSID,
                obj.CREATED
                       from CMOBJECTS obj 
                            inner join CMOBJNAMES nm on obj.cmid = nm.cmid
                       where nm.isdefault = 1 
                     ) o
                start with o.PCMID = 0
                connect by prior o.CMID = o.PCMID) t
where t.CLASSID =10 
        ) "SQL1"
    ) "Query_CS"
        INNER JOIN 
        (
        SELECT
            s."CMID", 
            s."SPEC"
        FROM CMOBJPROPS7 s
        ) "GetSpec"
        ON "Query_CS"."CMID" = "GetSpec"."CMID" 
WHERE instr("GetSpec"."SPEC", 'HTMLItem') > 0

Hi,

I would like to pull a list of all reports that have hyperlink button object used in them. Can this be done using the free version of Motio PI?.

On checking the documentation of Motio Pi, I could not find out if this can be done using free version of Motio PI. Please suggest. is there any other way this can be done?.

Best Regards,

Hi Ilyas,

Thank you. Can I run the query you provided on the Content Store DB as it is or is any modification required? I need to find out where content store DB  is located in our Cognos environment and try it.

Going back to my first question, can the same thing be done using Motio PI (free version)? I assume this is something that can be achieved using Motio PI Pro. Please let me know if you are aware of it.

Best Regards,

Hello!

You can execute SQL query to CS:

SELECT
    "Query_CS"."CMID" AS "CMID", 
    "Query_CS"."OBJNAME" AS "OBJNAME", 
    "Query_CS"."PATH0" AS "PATH0", 
    "Query_CS"."CREATED" AS "CREATED", 
    "GetSpec"."SPEC" AS "SPEC"
FROM
    (
    SELECT
        "SQL1"."LEV" AS "LEV", 
        "SQL1"."CMID" AS "CMID", 
        "SQL1"."PCMID" AS "PCMID", 
        "SQL1"."OBJNAME" AS "OBJNAME", 
        "SQL1"."PATH" AS "PATH0", 
        "SQL1"."CLASSID" AS "CLASSID", 
        "SQL1"."CREATED" AS "CREATED"
    FROM
        (
        
select t.* from(
select level as lev, cmid, o.pcmid, objname, SYS_CONNECT_BY_PATH(o.objname, ' > ') path, o.CLASSID, o.CREATED
                from (
                       select obj.CMID CMID
                        , case 
                                when obj.cmid = obj.pcmid then null 
                                else obj.PCMID 
                          end PCMID
                        , nm.name objname
                        , obj.CLASSID,
                obj.CREATED
                       from CMOBJECTS obj 
                            inner join CMOBJNAMES nm on obj.cmid = nm.cmid
                       where nm.isdefault = 1 
                     ) o
                start with o.PCMID = 0
                connect by prior o.CMID = o.PCMID) t
where t.CLASSID =10 
        ) "SQL1"
    ) "Query_CS"
        INNER JOIN 
        (
        SELECT
            s."CMID", 
            s."SPEC"
        FROM CMOBJPROPS7 s
        ) "GetSpec"
        ON "Query_CS"."CMID" = "GetSpec"."CMID" 
WHERE instr("GetSpec"."SPEC", 'HTMLItem') > 0

Hi,

I would like to pull a list of all reports that have hyperlink button object used in them. Can this be done using the free version of Motio PI?.

On checking the documentation of Motio Pi, I could not find out if this can be done using free version of Motio PI. Please suggest. is there any other way this can be done?.

Best Regards,

Hi Mack,

If you have access to IBM Cognos Configuration on your server, you will find what you need in :

Data Access / Content Manager / Content Store.

If you don't know the user/password used for the Content Store database, you need to export the cogstartup.xml then you will be able to read the password in this file.

Best regards,

Hi Ilyas,

Thank you. Can I run the query you provided on the Content Store DB as it is or is any modification required? I need to find out where content store DB  is located in our Cognos environment and try it.

Going back to my first question, can the same thing be done using Motio PI (free version)? I assume this is something that can be achieved using Motio PI Pro. Please let me know if you are aware of it.

Best Regards,

Hello!

You can execute SQL query to CS:

SELECT
    "Query_CS"."CMID" AS "CMID", 
    "Query_CS"."OBJNAME" AS "OBJNAME", 
    "Query_CS"."PATH0" AS "PATH0", 
    "Query_CS"."CREATED" AS "CREATED", 
    "GetSpec"."SPEC" AS "SPEC"
FROM
    (
    SELECT
        "SQL1"."LEV" AS "LEV", 
        "SQL1"."CMID" AS "CMID", 
        "SQL1"."PCMID" AS "PCMID", 
        "SQL1"."OBJNAME" AS "OBJNAME", 
        "SQL1"."PATH" AS "PATH0", 
        "SQL1"."CLASSID" AS "CLASSID", 
        "SQL1"."CREATED" AS "CREATED"
    FROM
        (
        
select t.* from(
select level as lev, cmid, o.pcmid, objname, SYS_CONNECT_BY_PATH(o.objname, ' > ') path, o.CLASSID, o.CREATED
                from (
                       select obj.CMID CMID
                        , case 
                                when obj.cmid = obj.pcmid then null 
                                else obj.PCMID 
                          end PCMID
                        , nm.name objname
                        , obj.CLASSID,
                obj.CREATED
                       from CMOBJECTS obj 
                            inner join CMOBJNAMES nm on obj.cmid = nm.cmid
                       where nm.isdefault = 1 
                     ) o
                start with o.PCMID = 0
                connect by prior o.CMID = o.PCMID) t
where t.CLASSID =10 
        ) "SQL1"
    ) "Query_CS"
        INNER JOIN 
        (
        SELECT
            s."CMID", 
            s."SPEC"
        FROM CMOBJPROPS7 s
        ) "GetSpec"
        ON "Query_CS"."CMID" = "GetSpec"."CMID" 
WHERE instr("GetSpec"."SPEC", 'HTMLItem') > 0

Hi,

I would like to pull a list of all reports that have hyperlink button object used in them. Can this be done using the free version of Motio PI?.

On checking the documentation of Motio Pi, I could not find out if this can be done using free version of Motio PI. Please suggest. is there any other way this can be done?.

Best Regards,

I've requested that these get removed.

Hello!

You can execute SQL query to CS:

SELECT
    "Query_CS"."CMID" AS "CMID", 
    "Query_CS"."OBJNAME" AS "OBJNAME", 
    "Query_CS"."PATH0" AS "PATH0", 
    "Query_CS"."CREATED" AS "CREATED", 
    "GetSpec"."SPEC" AS "SPEC"
FROM
    (
    SELECT
        "SQL1"."LEV" AS "LEV", 
        "SQL1"."CMID" AS "CMID", 
        "SQL1"."PCMID" AS "PCMID", 
        "SQL1"."OBJNAME" AS "OBJNAME", 
        "SQL1"."PATH" AS "PATH0", 
        "SQL1"."CLASSID" AS "CLASSID", 
        "SQL1"."CREATED" AS "CREATED"
    FROM
        (
        
select t.* from(
select level as lev, cmid, o.pcmid, objname, SYS_CONNECT_BY_PATH(o.objname, ' > ') path, o.CLASSID, o.CREATED
                from (
                       select obj.CMID CMID
                        , case 
                                when obj.cmid = obj.pcmid then null 
                                else obj.PCMID 
                          end PCMID
                        , nm.name objname
                        , obj.CLASSID,
                obj.CREATED
                       from CMOBJECTS obj 
                            inner join CMOBJNAMES nm on obj.cmid = nm.cmid
                       where nm.isdefault = 1 
                     ) o
                start with o.PCMID = 0
                connect by prior o.CMID = o.PCMID) t
where t.CLASSID =10 
        ) "SQL1"
    ) "Query_CS"
        INNER JOIN 
        (
        SELECT
            s."CMID", 
            s."SPEC"
        FROM CMOBJPROPS7 s
        ) "GetSpec"
        ON "Query_CS"."CMID" = "GetSpec"."CMID" 
WHERE instr("GetSpec"."SPEC", 'HTMLItem') > 0

Hi,

I would like to pull a list of all reports that have hyperlink button object used in them. Can this be done using the free version of Motio PI?.

On checking the documentation of Motio Pi, I could not find out if this can be done using free version of Motio PI. Please suggest. is there any other way this can be done?.

Best Regards,

Hi @Ilyas Yulyev, Would you say this is a genereic query and that you may query all kind of object types used in reports just by modifying the last WHERE condition? 

Hello!

You can execute SQL query to CS:

SELECT
    "Query_CS"."CMID" AS "CMID", 
    "Query_CS"."OBJNAME" AS "OBJNAME", 
    "Query_CS"."PATH0" AS "PATH0", 
    "Query_CS"."CREATED" AS "CREATED", 
    "GetSpec"."SPEC" AS "SPEC"
FROM
    (
    SELECT
        "SQL1"."LEV" AS "LEV", 
        "SQL1"."CMID" AS "CMID", 
        "SQL1"."PCMID" AS "PCMID", 
        "SQL1"."OBJNAME" AS "OBJNAME", 
        "SQL1"."PATH" AS "PATH0", 
        "SQL1"."CLASSID" AS "CLASSID", 
        "SQL1"."CREATED" AS "CREATED"
    FROM
        (
        
select t.* from(
select level as lev, cmid, o.pcmid, objname, SYS_CONNECT_BY_PATH(o.objname, ' > ') path, o.CLASSID, o.CREATED
                from (
                       select obj.CMID CMID
                        , case 
                                when obj.cmid = obj.pcmid then null 
                                else obj.PCMID 
                          end PCMID
                        , nm.name objname
                        , obj.CLASSID,
                obj.CREATED
                       from CMOBJECTS obj 
                            inner join CMOBJNAMES nm on obj.cmid = nm.cmid
                       where nm.isdefault = 1 
                     ) o
                start with o.PCMID = 0
                connect by prior o.CMID = o.PCMID) t
where t.CLASSID =10 
        ) "SQL1"
    ) "Query_CS"
        INNER JOIN 
        (
        SELECT
            s."CMID", 
            s."SPEC"
        FROM CMOBJPROPS7 s
        ) "GetSpec"
        ON "Query_CS"."CMID" = "GetSpec"."CMID" 
WHERE instr("GetSpec"."SPEC", 'HTMLItem') > 0

Hi,

I would like to pull a list of all reports that have hyperlink button object used in them. Can this be done using the free version of Motio PI?.

On checking the documentation of Motio Pi, I could not find out if this can be done using free version of Motio PI. Please suggest. is there any other way this can be done?.

Best Regards,