BusinessObjects Board

Query for objects in Inbox

other Auditor
#1

Hi,

I am wondering if it is possible to use BO XIr3.1 Auditing to query what reports have been sent to the users Inbox, or if there is another method other then Auditing please let me know.

Working with a particular test user I built the following query which takes the Template (parent report) CUID and the initials of the user and returns some reports, during further testing I have discovered the report doesn’t work for all users and doesn’t list all reports.

SELECT TOP 1 ae.Object_CUID
      , ae.Start_Timestamp
      , r.[Report_User]
      , r.[Report_CUID]
      , r.report_name
      , ad.Detail_Text AS 'Template_ID'
FROM    AUDIT_DETAIL ad
        INNER JOIN AUDIT_EVENT ae ON ad.Event_ID = ae.Event_ID
                                     AND ad.Server_CUID = ae.Server_CUID
        INNER JOIN ( SELECT [dbo].[AUDIT_EVENT].[Object_CUID]
                          , MAX(CAST(Report_User AS VARCHAR(10))) AS 'Report_User'
                          , MAX(CAST(Report_CUID AS VARCHAR(150))) AS 'Report_CUID'
                          , MAX(CAST([Template_CUID] AS VARCHAR(150))) AS 'Template_CUID'
                          , MAX(CAST(Report_Name AS VARCHAR(150))) AS 'Report_Name'
                     FROM   AUDIT_EVENT
                            INNER JOIN ( SELECT [Object_CUID]
                                              , CASE WHEN [dbo].[AUDIT_DETAIL].[Detail_Type_ID] = 92 THEN [Detail_Text]
                                                END AS 'Report_CUID'
                                              , CASE WHEN [dbo].[AUDIT_DETAIL].[Detail_Type_ID] = 69 THEN [Detail_Text]
                                                END AS 'Report_User'
                                              , CASE WHEN [dbo].[AUDIT_DETAIL].[Detail_Type_ID] = 91 THEN [Detail_Text]
                                                END AS 'Template_CUID'
                                              , CASE WHEN [dbo].[AUDIT_DETAIL].[Detail_Type_ID] = 3 THEN [Detail_Text]
                                                END AS 'Report_Name'
                                         FROM   DETAIL_TYPE
                                                INNER JOIN AUDIT_DETAIL ON DETAIL_TYPE.Detail_Type_ID = AUDIT_DETAIL.Detail_Type_ID
                                                INNER JOIN AUDIT_EVENT ON AUDIT_DETAIL.Event_ID = AUDIT_EVENT.Event_ID
                                                                          AND AUDIT_DETAIL.Server_CUID = AUDIT_EVENT.Server_CUID
                                         WHERE  [Event_Type_ID] = 327687
                                                AND [dbo].[AUDIT_DETAIL].[Detail_Type_ID] IN ( 69, 91, 92, 3 )
                                       ) a ON [dbo].[AUDIT_EVENT].[Object_CUID] = [a].[Object_CUID]
                     GROUP BY [dbo].[AUDIT_EVENT].[Object_CUID]
                   ) r ON ae.[Object_CUID] = r.[Object_CUID]
                          AND CAST(ad.[Detail_Text] AS VARCHAR(150)) = r.[Template_CUID]
WHERE   ad.Detail_Text LIKE 'Aah99QyrbtRNnhR0_5R8SFQ'
        AND ae.[Event_Type_ID] = 327687
        AND [r].[Report_User] = 'ABC'
ORDER BY [ae].[Start_Timestamp] DESC

I am thinking that for the test user, the reports were probably opened or something.

What I am wanting to do is retrieve the most recent instance of a report based for a parent report CUID for a particular user.

I have been able to retrieve the reports using Query Builder, I had to query all folders, choose a particular user and then use another query to list all objects in the Inbox. Is it possible to transfer QB to something more useful?

We are running BO XI 3.1, the reports are a mixture of WebI and DeskI reports all reports are sent to the users Inboxes in PDF format.

Your help is greatly appreciated.

matt_65 :australia: (BOB member since 2009-08-12)

#2

Step 1 : take the query that you used in the Query builder
Step 2 : execute the query in a SDK-script and export the output to a textfile
Step 3 : upload the textfile in a table in the audit database
Step 4 : use the CUID to link the tables in the audit database with the table that contains the SDK-info

Geert :belgium: (BOB member since 2002-07-10)

#3

Thanks for the reply Geert.

I ended up convincing the boss to let me work with the SDK to build a solution that is much more capable (and solves some other issues we wished were improved).

I found the SAP example of using the SDK to view other types of report objects (PDF, XLS, TXT, etc), seeing how easy it was I extended this to use AD authentication, open reports in the users inbox in PDF or if the report is a Deski/WebI report use OpenDoc to load the report.

The SDK looks well thought out and I didn’t have any issues in finding documentation online to overcome any issues encountered.

Thanks again,

Matt

matt_65 :australia: (BOB member since 2009-08-12)