Create Graphs using RSUM Data

SonPat99

Dear Gurus,

I am using the latest on-prem OBIEE 12c (12.2.1.4)

I am trying to create a simple Weekly report with Weekend and Distinct Count of tickets; where the dates are not continuous, meaning the week-ends are skipping a few in between (like: Weekend is 4th April, then next data is for 25th April skipping intermediate weeks of 11 and 18).

The user wants to see data for all weeks in a continuous manner with incremental values getting added to current week.

  

In normal report, RSUM was not working, so I referred to OBIEE 12C: RSUM Is Not Working on Column with Null values (Doc ID 2389093.1), which again needed some changes. I had to revert my physical formula for RSUM to just count and use "Display as Running Sum" from Results section.

I am getting the expected result now.

However, when I am trying to create a simple Line chart with X as Weekend and Y as Total Count, it is just taking the dates present in my original data and NOT the output of Running Sum.

1. Please help me understand how can I create Reports on the RSUM data?

2. I need to create a Delta Report, which will display what has changed between current week and last week.

where Delta is Current Week - Last Week. Please help me understand how can I take the current and last week from the RSUM Output.

Regards,

SonPat

Jerry Casey

Hi SonPat,

Have you tried a union query where you add a query that that fills the skipped dates?  It would have zeroes in the measure columns, and you would build the graphed columns at the header level using "saw_x" terminology.  (Add a result column)

SonPat99

Hello Jerry,

Apologies for coming this late.

The main problem that I am facing is: Point in-time data is required.

I want the analysis to be generalized instead of hard-coded. I have to pass week-end dates in above condition so that it calculates all the open issues from start till the weekend date (instead of the hardcoded date above).

I tried with TODATE, but somehow that is not working in my case. Any suggestions will be highly appreciated.

Jerry Casey

I'm still unclear about the issue.

You can make a filter like: "Table"."Created Date" <=(CURRENT_DATE) AND "Table"."Created Date" > "START DATE" AND DAYOFWEEK("Table"."Created Date")=7

To get the difference between adjacent rows, a formula like "Col X"-(MSUM("Col X",2)-"Col X") works. (moving sum of last 2 rows minus the current row gets you the previous value.)

Jerry Casey

By wrapping each Amount in an ifnull statement, this worked:

SonPat99

Hello Jerry,

This looks good for point #2 (for getting the delta values). Allow me sometime to check on that.

Do you have any solution for the point #1 where I want to get the count of all the open issues week by week.

In short, the query will pass the weekend date and will get all Open issues till the weekend date.

If the table has data starting May 2019, then when passing the weekend  as 11th April 2020, it should give me count of all issues starting from May 2019 till 11th April 2020.

When Weekend = 25th April 2020, expected count should be from May 2019 till 25th April 2020.

Jerry Casey

A simple two column example, top query has several missing dates, the bottom contains all Saturday dates:

Weekly Delta formula:

(sum(saw_1 by saw_0)-(MSUM(sum(saw_1 by saw_0),2)-sum(saw_1 by saw_0)))/sum(rcount(saw_1 by saw_0) by saw_0)

This is a pivot table with Amount duplicated, shown as running sum:

SonPat99

Hello Jerry,

The problem is:

1. There are other measures as well for which this calculation is required.

2. This is part of a self service and similar reports will be created by end users, who barely know OBIEE.

They are now trying their hands on OBIEE, so I don't expect them to be able to understand or create such reports using complex logic. I was trying to get a simpler solution so that I can explain the end-users about the same and then they can take it from there.

Jerry Casey

Hello SonPat,

I think your best hope of doing that is to pre-calculate the fields in the rpd.  Good luck!

Jerry