Enterprise status report

[geethkal12]

Hello All,

I need to create EDW status report from Audit package so that the report can be integrated to cognos portal pages which displays useful information to the user when he is using cognos environment. In that report there should be a graph showing all the data items like Users online, reports run today, reports failed today, %of reports failed, % of reports succeded, average report run time, max report run time. please advise me which report type should i use to build report

please reply with answers
thanks
kalyani

 

[blom0344]

Sounds like a typical Report Studio report to me based on either a table / repeater+table / repeater+list.
But that is how I interpret your question 'which report type'

Ties Blom

 

[geethkal12]

Yep its a typical report studio question. Got to prepare Performance Entreprise status report. i managed to build a line graph with days of the week on X_axis and execution time on Y-axis. but my team leader asking me to prepare to represent the previous 7 days from today's date on X-axis(such that today`s date appears as the right-most value in the graph).

I am trying on this but not getting it. Please list some suggestions so that i work on it as it is pretty urgent.

Thank you very much
divya

 

[blom0344]

Sounds you only need to apply a filter that fetches the last 7 dates:

[somedate] >= _add_days(current_date,-7)

Ties Blom

 

[geethkal12]

Hi Ties,

I am still trying to work on that garph. my requirement is to get previous week days plus today(day names) on X-axis and it should get refreshed everyday.
for example: today is tuesday. on the x-axis of the graph
it should represent
Wed Thu Fri sat SUn Mon Tue.
Please provide me the code for this relative time.

Thanks
geetha

 

[blom0344]

CASE
WHEN _DAY_OF_WEEK([YOURDATE],1) = 1 THEN 'Mon'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 2 THEN 'Tue'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 3 THEN 'Wed'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 4 THEN 'Thu'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 5 THEN 'Fri'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 6 THEN 'Sat'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 7 THEN 'Sun'
else ''
END

you need to sort this new dataitem by the actual date, otherwise it will sort alfabetically on the day codes.

Use the filter I mentioned to fetch only the last 7 days of data..



Ties Blom

 

[geethkal12]

Hi Ties,

Thank you very much for the reply.

I tried as per your suggestion. but i am getting mon to sun.
then tried the below code:
CASE Current_date
WHEN (_add_days([Date], 0)) THEN ('THU')
WHEN (_add_days([Date], -1)) THEN ('WED')
WHEN (_add_days([Date], -2)) THEN ('TUE')
WHEN (_add_days([Date], -3)) THEN ('MON')
WHEN (_add_days([Date], -4)) THEN ('SUN')
WHEN (_add_days([Date], -5)) THEN ('SAT')
WHEN (_add_days([Date], -6)) THEN ('FRI')
ELSE ('SOMETHING')
END.

when i try to execute the report it shows only THU on x-axis.

but i need to get past 7 days starting from today. everyday it needs to get refreshed.

One more thing:

Pls tell me how to sort the data item by actual date.

Thank you very much
geetha

 

[blom0344]

CASE Current_date
WHEN (_add_days([Date], 0)) THEN ('THU')
WHEN (_add_days([Date], -1)) THEN ('WED')
WHEN (_add_days([Date], -2)) THEN ('TUE')
WHEN (_add_days([Date], -3)) THEN ('MON')
WHEN (_add_days([Date], -4)) THEN ('SUN')
WHEN (_add_days([Date], -5)) THEN ('SAT')
WHEN (_add_days([Date], -6)) THEN ('FRI')
ELSE ('SOMETHING')
END.

makes no sense at all..

CASE
WHEN _DAY_OF_WEEK([YOURDATE],1) = 1 THEN 'Mon'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 2 THEN 'Tue'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 3 THEN 'Wed'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 4 THEN 'Thu'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 5 THEN 'Fri'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 6 THEN 'Sat'
WHEN _DAY_OF_WEEK([YOURDATE],1) = 7 THEN 'Sun'
else ''
END

will yield a new dataitem that displays the daycode for a certain date.

So, you need a new dataitem (with the above expression) AND a filter to fetch only the last 7 days..
Obviously use the new dataitem in the graph

You can add a custom sort by selecting the dataitem in the graph and then go to the sort icon on the menu. Choose advanced sorting and add the original date in the sort list.

Ties Blom

 

[geethkal12]

Hi Ties,

Hope I m troubling you a lot. You been very helpful.

I just used the above case statement with code name Day Names.
In the filter i used expression :
[Day Names] = _add_days(current_date, -7)

Then i dragged Day Names on to X-axis and click sort-- advanced sorting and added (cast([Timestamp], date).
but i am getting below error when i try to execute the report

RQP-DEF-0177

An error occurred while performing operation 'sqlScrollBulkFetch' status='-51'.

Please help me with this

Thanks
geetha

 

[blom0344]

The filter and the newly devised dataitem are 2 completely different things.

Let us assume that the original DATE coming from the query is called: [DATE_IN]

1.Then your filter expression is:

[DATE_IN] >= _add_days(current_date, -7)

2. the expression for the new dataitem (to be used in the graph) is:

CASE
WHEN _DAY_OF_WEEK([DATE_IN],1) = 1 THEN 'Mon'
WHEN _DAY_OF_WEEK([DATE_IN],1) = 2 THEN 'Tue'
WHEN _DAY_OF_WEEK([DATE_IN],1) = 3 THEN 'Wed'
WHEN _DAY_OF_WEEK([DATE_IN],1) = 4 THEN 'Thu'
WHEN _DAY_OF_WEEK([DATE_IN],1) = 5 THEN 'Fri'
WHEN _DAY_OF_WEEK([DATE_IN],1) = 6 THEN 'Sat'
WHEN _DAY_OF_WEEK([DATE_IN],1) = 7 THEN 'Sun'
else ''
END


Ties Blom

 

[geethkal12]

Hello Ties,

Thank you very very much. with your assistance i am able to finish my task.

Now i got to integrate that line graph in to cognos connection portal. can you give me any idea?

anyway thank you
geetha

 

[blom0344]

graph is part of a report ;report runs on a package. Make sure rights are set for users to run the report..

Ties Blom

 

[geethkal12]

Hello Ties,

I need to integrate that chart report(Line graph) in a small window (portlet) on the cognos connection page. do i need to goto tools -- my preferences. How to get that graph in one corner of that cognos connection page.

Please give me some idea so that i will start working on it.

Thanks in advance
Geetha