BusinessObjects Board

Is there a canned BO report to show BCA stats/errors?

scheduling Broadcast Agent (BCA)
#9

heres mine. hope it helps. i use this a free hand sql report connected to my repository.

select a.document_id,
        d.M_DOC_C_NAME,
        substr(b.M_ACTOR_C_NAME,1,10), 
        decode(a.job_status,0,'Success',1,'Failure',2,'Waiting',3,'Running',4,'Suspended','Retrying'),
        substr(c.M_SRC_C_NAME,1,10),
        a.start_datetime,        
        ((to_date('15/12/1970 00:00:00', 'DD/MM/YYYY HH24:MI:SS') +  (a.start_datetime / (60 * 60 * 24))) - 6/24),
        a.end_datetime,
         ((to_date('15/12/1970 00:00:00', 'DD/MM/YYYY HH24:MI:SS') +  (a.end_datetime / (60 * 60 * 24))) - 6/24),
         ((a.end_datetime - a.start_datetime) / 60 ) / 60
  from DS_PENDING_JOB a, obj_m_actor b, OBJ_M_DOCATVAR c, OBJ_M_DOCUMENTS D
    where a.USER_SUBMIT_ID = b.M_ACTOR_N_ID
      and a.DOCUMENT_ID = c.M_DOC_N_ID
      and a.document_id       = d.M_DOC_N_ID
         order by substr(c.M_SRC_C_NAME,1,10)

jim_green_1 (BOB member since 2004-03-26)

#10

got it and it works…I am making changes and adding some charts…after I am done if any of you want a copy…let me know. thanks again!

LotusSutol (BOB member since 2003-06-30)

#11

jeffrey swoboda, I am using your universe (some mods to it)…and I am sooo close to having the trends chart complete! I have a line that shows the trend over a two week period for total BCA jobs ran for the day, total success, and total failure…what I am stuck on is getting a line showing the total scheduled for the day…

Anybody have a clue how to figure out how many are scheduled to run for the day?

LotusSutol (BOB member since 2003-06-30)

#12

Michael, glad it helped. I would love to see the finished product.

Good question about the scheduling, I’ll take a look and see if I can help.

jswoboda :us: (BOB member since 2002-06-20)

#13

Is that in the downloads area? You mind sharing?

Cindy Clayton :us: (BOB member since 2002-06-11)

#14

Cindy, are you asking me about the Universe I sent Michael or the BCA stat/errors report he is building? I do not mind sharing my Universe or report (when I say mine I really inherited it about 4 years ago I am not entirely sure where it came from). I read the directions in the Download area but it makes reference to posting in the SDK forum and then mentioning to a moderator. This is really just a .unv file and corresponding report, if you think it could benefit others if we put it in the download area just let me know what I need to do and I would be happy to share. - thanks

jswoboda :us: (BOB member since 2002-06-20)

BCA Auditing and Reporting
#15

also, when I am done I have no problem sharing the report/edited unv file that I did too…

LotusSutol (BOB member since 2003-06-30)

#16

I’d just send the .unv and .reps as per the instructions and provide a bit of info on what it does and what it is for. I’ve got a .rep or two and maybe a .unv in the downloads area myself. My audit stuff I think. It is the perfect place for your stuff too.

Thanks!

Cindy Clayton :us: (BOB member since 2002-06-11)

#17

The number of scheduled reports to run variable is a pain…
To get the number of scheduled reports for TODAY this seems like it works
count(DS_PENDING_JOB.BEGIN_DATE + DS_PENDING_JOB.START_DATETIME)
That takes the jobs that have ran today and adds them to the jobs that are scheduled to run today and gives me a count on them…GREAT! I have the number for today.
This can’t look into the future and it can’t look into the past (beyond today would = 0 and in the past would = just the DS_PENDING_JOB.START_DATETIME).
I could however take this value and write it into a file with the date. Then the report could grab this value and we could use it to have a history of what the number of scheduled reports should have been for those days.

I am spending more time on this than I had hoped…but it is one of those things that once you get thinking on it…you want to solve it…oh well…

LotusSutol (BOB member since 2003-06-30)

#18

I posted some code a while back using the BITAND operator to determine the “clear text” translation of the scheduling. You can use this in combination with the Original Start Date and some Sysdate functions (to determine the day of the week as well as the # of weeks since it was scheduled).

If you can get me some sample rows, I can help out in this, but it’s been a while since I’ve looked at the DS_PENDING_JOB table in any detail.

digpen :us: (BOB member since 2002-08-15)

#19

Hi there all,

First of all, this universe is very handy! This forum has supplied so many useful gizmos!!

I’m trying to convert this universe it to SQL Server, I’m doing ok but being a novice at all things SQL I’m having trouble with the dates.

Can someone help me convert the following to MS SQL Server?

to_char((to_date(‘14-12-1970 11:00:00’, ‘DD-MM-YYYY HH24:MI:SS’) + dbo.DS_PENDING_JOB.SUBMIT_DATETIME/(246060)+(7/24)), ‘mm/dd/yy hh24:mi:ss’)

Any tips appreciated,

Cheers,
Pete

foshdafosh :uk: (BOB member since 2004-05-04)

#20

I think you can use dateadd( , , ‘s’), but I don’t have access to SQLServer any more to check the syntax. :frowning:

Paul Williams :uk: (BOB member since 2002-07-10)

#21

Thanks very much, that’s part of it done, much appreciated!

Now I just need a function that does the same as ‘to_date’.

Will ‘convert’ do that? I’ve been playing with it but I can’t make it work…

foshdafosh :uk: (BOB member since 2004-05-04)

#22

You can use convert to swap text to a date. This might be helpful.

The problem you’re having might be down to the format of the date, which is what the third argument refers to. The styles are listed in the link above.

Paul Williams :uk: (BOB member since 2002-07-10)

#23

There’s no might about it, that’s very useful!

Problem solved, thanks very much for your help.

foshdafosh :uk: (BOB member since 2004-05-04)

#24

Has anyone tried to somehow show the scheduled reports in a cross tab, split by hours across the top and day of month down the side??

I’ve been thinking about it lately but haven’t tinkered yet!

stuartgmilton :uk: (BOB member since 2003-01-30)

#25

Not able to use the universe. It say, I am not authorized to use it.

subodhgupta (BOB member since 2004-06-22)

#26

Do you have access to your GS account? You should be able to open it with that.

jswoboda :us: (BOB member since 2002-06-20)

#27

LotusSutol,

I was reading you topic about your MODS to the report that Jeff Swoboda sent you…Are you finished with the MODS, if so can I get a copy of the final project…

battles3 :us: (BOB member since 2003-06-09)

#28

Hi Cindy,

How do I access to the download area? I’d like to take a look at the BCA universe myself.

Thanks,
Chi

chitran69 (BOB member since 2003-11-26)