Calculation Errors

building reports webintelligence
1

Hi,

This is the calculation I am using the designer and its giving a different percentage value of 2 the same calculation if I take the objects in the reports and do it and its giving a value of 0.49 as not able to understand how designer is handling what changes are needed to get the correct calculated value. I have used the cast function but nothing is working out what could be the possible reason?

(
@Select(OTSM Summary Measures\Os_Day01_Total)+@Select(OTSM Summary Measures\Os_Day02_Total)
+@Select(OTSM Summary Measures\Os_Day03_Total)
+@Select(OTSM Summary Measures\Os_Day04_Total)
+@Select(OTSM Summary Measures\Os_Day05_Total)
)
/ (@Select(OTSM Summary Measures\Os_Pkg_Total)  )
Day05total=
cast(
  cast(sum(SMARTPOST_BUSOBJ_L2F5_VIEW_DB.fxsp_ontime_svc_meas_summary.tot_dlvd_day_5_qty) as char(8)) as decimal (7,4) )

/cast(cast(SUM(SMARTPOST_BUSOBJ_L2F5_VIEW_DB.fxsp_ontime_svc_meas_summary.tot_pkg_qty) as char(8)) as decimal(7,4))

Requesting for suggestions to solve the issue
Thanks
[Moderator Edit: Added code formatting - Andreas]

s7jyothi (BOB member since 2008-09-07)

2

I can’t which remember on which post or which moderator made this comment a couple of days ago - heavy weekend :cheers:(!) ; but it’s generally best practice to perform averages at report level and not at db / universe. Return your totals to a report and get your averages there would be my advice, you’re probably seeing a good example of why this is so in this instance.

SteveD :uk: (BOB member since 2009-11-02)

3

Hi,
I understand that the calculations at the report level are getting the right results but one question I have do we face any performance issues if there is huge data like the calculations has to be performed from Day1 to Day 20 for one million packages.

Does the report take a long time to return the values?

I wanted to mention that datatype the actual tables is small integer for this columns day 1 to day 20 and in the designer we are using number is this is what is causing the problem and how to handle it??

@Select(OTSM Summary Measures\Os_Day01_Total)+@Select(OTSM Summary Measures\Os_Day02_Total)
+@Select(OTSM Summary Measures\Os_Day03_Total)
+@Select(OTSM Summary Measures\Os_Day04_Total)
+@Select(OTSM Summary Measures\Os_Day05_Total)
)
/ (@Select(OTSM Summary Measures\Os_Pkg_Total) )[/code]

[code]Day05total=
cast(
cast(sum(SMARTPOST_BUSOBJ_L2F5_VIEW_DB.fxsp_ontime_svc_meas_summary.tot_dlvd_day_5_qty) as char(8)) as decimal (7,4)

Requesting for suggestions to solve the issue
Thanks

s7jyothi (BOB member since 2008-09-07)