BusinessObjects Board

How to find all WebI reports based on specific Excel spreadsheet?

building reports webintelligence
#1

Is there a way in the CMC, Query Builder, or something else to find all Web Intelligence reports based on a specific Excel spreadsheet?

Thanks

#2

May take a minute or two to run:

Query Builder:
select *
from ci_infoobjects
where si_webi_doc_properties like ‘%DSNAME=“ExcelDocName”%’

1 Like
#3

That is definitely a step in the right direction. However, it seems to me there could be mroe than one spreadsheet of the same name in different folders and the query you provided would not be able to differentiate.

The highlighted number in the screen shot below is the ID of the Excel spreadsheet I am interested in. How do I go about querying that second level item?

image
image1169×200 6.42 KB

Thanks

#4

try
where si_excel_of_document = 1238274

…edit
Just realized it’s a nested property. You’ll either need to use the SDK or the CMS universe to get at it. No way, that I’m aware of, to use query builder to access that level.

#5

if you already know the SI_EXCEL_OF_DOCUMENT you can select with:

… like ‘%1238274%‘
or
‘SI_EXCEL_OF_DOCUMENT.1‘ = ‘1238274‘

#6

Be careful, using .1 assumes there is only one query based on an Excel spreadsheet per document.

1 Like
#7

… but of course it was only an example how you can access that level.
When not using the „like …“ you can add
.2 =
or
.3 =
or
…
to your select-statemant

#8

It’s a classic use-case for the getting hands dirty into the SAP BusinessObjects RESTful Web Service SDK User Guide for Web Intelligence and the BI Semantic Layer :sunglasses:

For example

GET /documents/7738/dataproviders

give you:

<dataproviders>
    <dataprovider>
        <id>DP0</id>
        <name>Query 1</name>
        <dataSourceId>6187</dataSourceId>
        <dataSourceType>unv</dataSourceType>
        <updated>2006-09-20Z</updated>
    </dataprovider>
    <dataprovider>
        <id>DP1</id>
         <name>Query 2</name>
         <dataSourceId>6191</dataSourceId>
         <dataSourceType>unx</dataSourceType>
         <updated>2014-04-23T09:08:20.000+02:00</updated>
    </dataprovider>
    <dataprovider>
        <id>DP2</id>
        <name>Query 3</name>
        <dataSourceId>6120</dataSourceId>
        <dataSourceType>bex</dataSourceType>
        <updated>2014-04-23T09:08:20.000+02:00</updated>
    </dataprovider>
    <dataprovider> 
        <id>DP3</id>  
        <name>Query 4</name>  
        <dataSourceId>6641</dataSourceId>  
        <dataSourceType>excel</dataSourceType>  
        <updated>2014-04-29T13:37:24.000+02:00</updated>
    </dataprovider>
    <dataprovider> 
        <id>DP4</id>  
        <name>Query 5</name>  
        <dataSourceId>6250</dataSourceId>  
        <dataSourceType>fhsql</dataSourceType>  
        <updated>2014-08-29T08:32:37.000+02:00</updated> 
    </dataprovider>
</dataproviders>

It’s nice but less accessible compared to the Query Builder.

1 Like
#9

I don’t need to know that bad. :wink:

1 Like