Skip to Content

Date Provider month-1 giving incorrect result set

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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 02, 2013 at 05:02 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 02, 2013 at 07:05 PM

    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..

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.