Date Provider month-1 giving incorrect result set

[sachin_v_joshi]

Version : 14.0.4 Build:1024

Hi Experts,
I have a webi report which runs on Universe "XYZ"
This is a monthly report where some KPI reflect Month-1 data.
We have 2 aggregated tables Tab_A having partitions on bill date and monthly bracket and Tab_B having partition on Bill year and yearly bracket.

We are supposed to use Tab_B in order to overcome performance improvement on this report.

In the original report, I have a query which is formed on Month-1 data provider which uses Tab_A sql section is as below

( Tab_A.BILL_DATE BETWEEN trunc(add_months(to_date('31/10/2013','DD/MM/YYYY'),-12),'MM')
and  add_months(to_date('31/10/2013','DD/MM/YYYY'),-12)  )

I'm unable to find a matching object in universe but I'm getting following objects along with its KPI's for Tab_B for billing purposes.

1)Invoicing Month uses aggregate aware functionality : @Aggregate_Aware(Tab_B.BILL_MTH,Tab_C.FISCMONTH,Tab_A.BILL_MTH)
2)Year of invoicing uses same functionality of aggregartiion : @Aggregate_Aware(Tab_B.BILL_YEAR,Tab_C.FISCYEAR,Tab_A.BILL_YEAR)
3)Billed Volume : @Aggregate_Aware(sum(nvl(Tab_B.CSUBTO4LC,0)),sum(nvl(Tab_C.CSUBTO4LC,0)),sum(nvl(Tab_A.CSUBTO4LC,0)))
4)Net Volume : @Aggregate_Aware(sum(nvl(Tab_B.NET_WGT_DL,0)),sum(nvl(Tab_C.NET_WGT_DL,0)),sum(nvl(Tab_A.NET_WGT_DL,0)))

I want to told to use Tab_B in the new report but KPI's do not match for a particular month for month

Queries that get generated for month-1 data providers are as below:

Original Script	Modified one
SELECT   sum(nvl(Tab_A.CSUBTO4LC,0)),   sum(nvl(Tab_A.NET_WGT_DL,0)),... FROM ..   Tab_A, .. WHERE .. ( Tab_A.BILL_DATE BETWEEN trunc(add_months(to_date('31/10/2013','DD/MM/YYYY'),-12),'MM') and  add_months(to_date('31/10/2013','DD/MM/YYYY'),-12)  )	SELECT   sum(nvl(Tab_B.CSUBTO4LC,0)),   sum(nvl(Tab_B.NET_WGT_DL,0)),... FROM ..   Tab_B, .. WHERE .. (     Tab_B.BILL_YEAR  BETWEEN  2013  AND  2013     AND     Tab_B.BILL_MTH  BETWEEN  9  AND  10    )

I'm running this report to generate monthly data for Oct.
How can i get month-1 KPI's same as that of original reports?

[Former Member]

Your original script where condition is from 10/31 and end in 10/31 and modified script has a condition of month 9 and 10. In this case they will not produce same output...

Looks like original script is incorrectly defined..

[Former Member]

Hi Sachin,

Can you please clarify below points:

1. Is your aggregate awareness not working as expected?

2. What is month-1 data provider?

Can you please clarify a little bit more, of what are you expecting?

Thanks

Gaurav