BusinessObjects Board

BO4: Reports viewed by Universe

other Auditor
#1

Does anyone know how to show reports run ondemand/scheduled sorted by universe name?

3.1 audit used to be able to do this. But now the object is gone.

I was able to find the unv names listed in ads_event and created a derived table to get me that list of unv’s. But I cant seem to find a link between report and unv to join on.

Any suggestions?

GigaGuy :us: (BOB member since 2007-02-13)

#2

I am in the same boat as you and have the same exact issue. Were you able to find a solution or workaround to this?

#3

I did actually. It only seems to work with onDemand audit data though.

First, I rebuilt thee unx into a unv. I dont think this is required, but I just couldnt stand working in the other tool.

Here is what I found, I assume you can make the “fix” in either unv or unx (I used unv).

I think this is the most important addition, to make this work.

Create a dimension, I called it “UNV Name”.
SELECT:

substr(@Select(Event Details\Event Detail Value),1,length(@Select(Event Details\Event Detail Value))-1)

Let me know if this doesnt work for you and I will post the UNV an/or the WID.
WHERE:

@Select(Event Details\Event Detail Type)='Universe Name'

There appears to be extra characters in the “event detail type”, which is why I used substr. I wanted to be able to use “in list”, and not have to select one by one.

I also self joined all the tables so LANGUAGE=‘EN’. This also probably isnt important, it just bugged me.

GigaGuy :us: (BOB member since 2007-02-13)

#4

That works or you can drag ‘Event Detail Type’ and ‘Event Detail Value’ objects in webi and filter ‘Event Detail Type’ to what you are looking for , for example, universe name.

But I haven’t been able to display two types of values from ‘Event Detail Type’ at the same time. Event Detail Type Holds various types of critical info such as universe name, scheduled to address, scheduled from address etc. Can you display ‘Universe Name’ and ‘To Address’ and ‘Number of times a report is scheduled’ in one report block.

Please reply if anyone has been able to do this or knows a way around it in BO 4.0

Thanks.

#5

The filtering was how I figure this out. Then I just created a new object in the unv.

I’d imagine you’d have to create seperate objects/dimensions to get different info.

One for unv names, one for recipient, etc.

That what you were asking?

GigaGuy :us: (BOB member since 2007-02-13)

#6

thats exactly what I did…but then the measure “# of times report was scheduled” doesnt show any value.

#7

I could only get this to work for ondemand viewing, not scheduling. That data must be inserted differently :x .

Let me know if you figure that part out.

GigaGuy :us: (BOB member since 2007-02-13)

#8

the issue is two or more types of values, from ‘Event Detail Type’ can not be displayed in one report with a measure.

#9

Interesting, must be due to their use of the CLOB data type. I would select the pieces you want and add a Derived Table. For example for Universe Name use

SELECT
	aed.EVENT_ID,
	aed.EVENT_DETAIL_ID,
	aed.EVENT_DETAIL_TYPE_ID,
	aed.BUNCH,
	CAST(aed.Event_Detail_Value AS VARCHAR(256)) AS "Universe Name"
FROM ADS_EVENT_DETAIL aed
WHERE
aed.EVENT_DETAIL_TYPE_ID=14

Join to the Event table on Event_ID.
Save the Data Layer, then create a new object in the Business Layer.
For large repositories, this will be slow, and you may need to have your DBA turn it into a Materialized View and run those once a week.

pcgeekus :us: (BOB member since 2003-10-16)