Find the report definition's report dependencies

[Jhunnie_boy]

Hello All,

I did some test report definitions and now I see my test folder starting to get messy. Is there a faster way to find the report that is using my report definition. The only way I know now is to edit the report one by one and see the report def used. I needed to delete one report definition but I just can't due to one report associated with it. But I can't find that report.

Any idea you can suggest, please do let me know and I shall appreciate it very highly.

Thanks in advance to you all,

Jhun

[jacparke · Accepted solution]

A useful report to run against the CUIC data source:

select

v.id,

c.name Folder,

v.name ReportName,

rd.name Definition,

v.description,

v.version,

v.cisco          ,

v.bypass         ,

v.layout         ,

v.partition      ,

v.sharingpermission,

u.name Owner ,

trim(v.url)              ,

v.templatehelpid   ,

v.entityversion    ,

v.author

from cuicreport v, cuiccategory c, cuicreportdefinition rd, outer cuicuser u

where v.parentcategoryid      = c.id

and v.reportdefinitionid=rd.id

and v.owner=u.id

Group it by Description (within the report view) and you'll get what you're looking for.

Regards,

Jack Parker

[Jhunnie_boy]

Thanks so much Jacparke,

I really think this is exactly what I need. I just find issue thoug with the object names, like cuicreport table is resulting to invalid object name error.

I am sql novice and still finding my way around the management studio. I always use imucc_awdb when running sql queries, since your query is more on system table, what should I use here. I tried the master but it's still giving invalid object name.

I really appreciate your help and your time putting the above code.

Thanks again,

Jhun

[jacparke]

Run it against the CUIC data source.  That's not in SQLServer, but rather on the CUIC platform itself.

Regards,

Jack Parker

[Jhunnie_boy]

Do I create a report definition and put this code there? Or do I need to go to the data sources section in cuic, that button that sits together with Value Lists?

You may find me too stupid but I am totally lost where to put or how to use your code?

By the way, I went to CLI and was able to pull a report from there. It was ugly, but it works, too.

But I'd love to give your code a try. I think if I can set that once, then I will be able to use if again and again. CLI is just hard to maneuver for me

Thanks again for your patience, jacparke.

[jacparke]

Does not look like I can attach a file to this message.  Send me your email address and I'll mail it to you.  Then you can just import it into your CUIC.

Essentially you create a new report definition, select CUIC as the data source, copy and paste the code above, create fields and save the report definition.

Then create a report which uses that report definition, edit the view, select "Grouping" (upper left) and group by Description.

Regards,

Jack Parker

[Jhunnie_boy]

Thanks Jack,

Actually, prior to sending reply to you yesterday, I was trying to create a report definition using this code but I get the invalid object name for cuicreport table. Also is that "outeruser" table as outer user with a space is also not valid?

Please send me the file for import at jmercado08@yahoo.com and I will import to cuic. I will let you know if I become successful. Really appreciate your patience and your kind help. Thanks very much Jack.

thanks,

Jhun

[Jhunnie_boy]

Thank you so much for helping me out. I am so very happy today that this is already sorted out. Not to mention I learned something new from our very short session.

Thanks very much.

Regards,

Jhun

[Jhunnie_boy]

Jac,

I wanted to add another columns here which is the report folder location and the report owner, how do I add in this query below? Also, is there any chance I can check the informix table columns online? I have no luck in finding them, would you know?

SELECT A.Name as ReportName, B.Name as ReportDefinition, C.Name as FolderLocation

FROM cuicreport A, cuicreportdefinition B, cuiccategory C

WHERE A.ReportDefinitionID = B.ID AND B.ParentCategoryID = C.ID

Thank you so much.

[jacparke]

Looks like you already have the folder from the category table.

There is an "owner" column in each of the three tables you are currently pulling from.

CUIC does not publish their schema, but some digging will show you how to read it out of the database.  I would suggest here: Using the Informix System Catalogs

Regards,

Jack Parker

[Jhunnie_boy]

Hi Jack,

Your last post is not just helpful, this is the very BEST answer. I looked into that link which is a rare find for me, you are right they did not publish that. Now I can see all system tables and the particular ones I need, I see the possible columns that matter to me. I used to have problems identifying the owner of the report, now it just one click of that Run button in the report I created and BOILA! I got the report owner and location.... SUPER DUPER THANKS! amazing what we can do in this world, if we are well-informed, not necessarily well-fed (I mean spoon fed).

I am extremely happy and if there is anything I can do to give back, please let me know as well.

Zillion thanks again, Jack

Jhun Mercado

[daniel.wilson2]

This query you posted is awesome and I just wanted to say the few steps not included.

1. Create a report definition...

2. Dump the code into the query slot after setting the Query type to Anonymous Block.

3. Verify the Data source is CUIC(this might be different on your system I am still new to this).

4. Create a report as normal and run it.

[SANJ21]

I know this is an old thread but am wondering if you know how to include the value list associated which each report definition in this query as I can't find the right table/fields anywhere. I have a need to  update a stock value list (basically replace it with a custom one) due to a deployment change from PCCE to UCCE and need to know which report definitions I need to update as there are many. The value list I need to specific update/re-create is Agent value list. TIA.