How to create a "backlog" report Service Requests

[Wangd]

I need to create a "backlog" report showing daily incomplete Service Requests (SRs) within the month.   As the day changes, the status changes. So the calculation needs to be dynamic.

I have a SR Status, Created Date and a measure of SR Count.   

How do I get the beginning backlog (open) total? Then for each day, the backlog will change by SR created minus SR closed. 

Thanks for any insight. 

Deanna

[RubenvdLinden]

Do you have a data item for the change date?

[Wangd]

Unfortunately, I don't.  Are you suggesting to create a data item to keep track of the changes?  How?

[bdbits]

Maybe I have missed something, but isn't this a straightforward calculation comparing system date/time to the created date/time, with a result based on the age? If that is right, a case or if statement should do the trick.

[Wangd]

It is a little bit more than that. By comparing the created date with the current date will only give me the current picture of what's not completed. I have attached a screen print of the graph I eventually wants to produce.

The report is run every morning and we would like to show the backlog as of the end of each day.  For example, at the end of 1/2/18, there were 80 incomplete SR. On 1/3, there were 50 incomplete SRs.  On 1/4, there were 40.  However, the data is changing ever minutes. By the time I run the next report, 1/2 might have only 70 left and 1/3 still has 50 and 1/4 has 30 and so forth. The goal is to capture the 80, 50 and 40 on the graph so that a trend is presented. I don't have a container to hold the number as of the end of each day until the SR is completed.

Hope this is clearer.  Thank you very much for looking at this request.  Really appreciate it.  Let me know if you have more questions.

[Wangd]

The post here is similar to what I want to achieve, but I can't completely follow it and I am not sure if the final expression eventually worked.

http://www.cognoise.com/index.php?topic=25633.0

Thanks for any help.

Deanna

[RubenvdLinden]

Without a change date (or finish date), you can't monitor when service requests changed.

[Wangd]

I do have completed date.  It only records the actual date/time that the SR is completed.  So if the SR is open on 1/4 and completed on 1/8, the completed date field is blank until 1/8.  I still need to count the SR as open for 1/4, 1/5, 1/6 and 1/7. I am stuck thinking there needs to be a way to calculate between the completed date and the each calendar date, but I don't have a table or holder for the calendar date. 

Maybe there is a better way.  Please advise. 

Thanks!
Deanna

[RubenvdLinden]

What RDBMS do you use (e.g. Oracle, MSSQL)? There are some tricks to generate a calendar table on the fly.

[Wangd]

We use Oracle.  What makes things complicated is that the application and database are hosted in the vendor's site.  All I can do is to use Cognos Report Studio to connect to the data packages for reports.  However, we can request the vendor to create stuff for us.  I just need to know what to ask.

Thanks!
Deanna

[Wangd]

I also need to know if there is nothing I can do at the report level. 

Will any of the dimensional measures such as PeriodsToDate be useful?

Thanks!
Deanna

[RubenvdLinden]

periodsToDate will only work on a dimensional package such as a PowerCube or DMR. In case of DMR, you will also need a date dimension that contains all the dates.

In Oracle, you can run this query to generate the dates of the last 365 days, including today:

Code Select Expand

SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
  FROM DUAL CONNECT BY ROWNUM < 366

In Report Studio, simply add SQL from your query toolbox and alter the last part of the query (< 366) to your needs.

[hespora]

Here's a post with a syntax that yields any range of dates, plus an example report def that shows a couple other things you can do: http://www.cognoise.com/index.php/topic,31986.msg104569.html

[bdbits]

I like the method hespora linked to from tjohnson3050 as it is database independent, incorporates a selectable range, and in this case I think it is probably good to have the processing on the Cognos server.

Join that up with your request data and use an expression with the open/close dates and that should get you there.

[Wangd]

Thank you, everyone for the suggestions.  I will give them a try.

Deanna