How to pull a list of reports containing custom SQL?

sdk development
1

Hello All,

We would need to know what reports from our complete list of reports, contain custom SQL. Is there a way to obtain this information from Auditor or ???

Thanks,
Richard

rlang (BOB member since 2010-03-03)

2

You will need to use the SDK to get this information. Look at the Java ReBean SDK

jwhite9 :us: (BOB member since 2006-07-28)

3

Hi Richard,

Please let me know on how you proceed for this task? I have a exact similar task.

@jwhite9 : Have you experienced before, Can you please give some more pointers/details.
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

4

Here is some psuedo-code on how to get this infomation, sorry but I can’t post the actual code I wrote. I’m not sure how it will handle combined queries and I only have Webi reports so you may have to tweak it to look at Crystal Reports.

[list]Login to CMS
Query for InfoObjects you want to look at
Loop through the list of IInfoObjects
Get a DocumentInstance
Open the document
Get the DataProviders
For each DataProvider cast it to a SQLDataProvider
Get the SQLContainer
Get each node in the SQLContainer and cast it to a SQLSelectStatement
Check if it has custom SQL
[/list]

jwhite9 :us: (BOB member since 2006-07-28)

5

Moderator Note:
Moving from Auditor to SDK

Marek Chladny :slovakia: (BOB member since 2003-11-27)

6

Thank you so much for the information. I hope you have done using BOE SDK right?
Can you please let me know on how to start with. Whatever information you have is enough and will be of a great help , in the programming point of view and what components are involved.
I have Webi and Deski reports
Thanks

JohnJustus :us: (BOB member since 2007-06-25)

7

How many total reports do you have in the system that you need to find this information out for?

If it’s less than 500, I might seriously just consider opening these all up. As jwhite mentioned, it could get ugly with combined queries and other funky nuances of opening every DP programmatically…

Atul Chowdhury (BOB member since 2003-07-07)

8

There are code samples available from SAP forums as well as in the SDK forum here on BOB that will help you get started with the SDK’s.

You will need to use the Enterprise SDK and the ReportEngine SDK to accomplish this.

If you haven’t done any work with the SDK then I would suggest starting with a small app that connects to the CMS and retrieves an object and some of its properties. From there you can expand it into what you are looking for.

jwhite9 :us: (BOB member since 2006-07-28)

9

I believe it will be more than 500.
Is there a way to find how many reports are there in the repository.
If it is below 500 then I can recommend my team about the options.
I am using XIR2.
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

10

Hi Jwhite,
Thank you.
I will follow the same. Is there any specific editor / Studio that I can use to write and compile the code? Do you recommend any?
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

11

You can use any text editor, I personally use eclipse which is very common in the Java world, and it is free.

jwhite9 :us: (BOB member since 2006-07-28)

12

Ok, Thanks.
So, to achieve my requirement I need to have BOE SDK, ReportEngine SDk, Java IDE (Eclipse or anyother) installed on my machine and few other setups like setting up Java environment etc.,.
Once these are all configured correctly, then I can step in to programming.
Is there any thing specific I need to take care of? I have started gathering intial setup’s. Perhaps, if you have one available that you have documented will be of great help to make sure I am not missing anything with the initial setups.
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

13

Hi JWhite,

A Quick question. With out using Java etc., is it something possible to achieve it in Query Builder because I don’t have java expertise.
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

14

Hi Datahog,

I used the below formula in the querybuilder

Select count(si_id) from ci_infoobjects where si_kind in ('FullClient', 'Webi', 'CrystalReport')

and found there are 721 reports in the below format,

SI_AGGREGATE_COUNT SI_ID 721

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

15

You can only determine reports that have custom SQL by using the SDK.

jwhite9 :us: (BOB member since 2006-07-28)

16

This number includes not only reports but report instances as well.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

17

Thanks. So Java programming knowledge is required and mut right to achieve this?

JohnJustus :us: (BOB member since 2007-06-25)

18

Is java still the only way to do this? I know this is a 2010 question, but I need to obtain the list now, and with .NET

is it possible to do it now?

sirharold (BOB member since 2014-06-11)