Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Open in Smart View option does not work in Firefox

IGMFeb 20 2019 — edited May 23 2019

Hi,

based on the following article https://docs.oracle.com/applications/smartview/800/SVREA/toc.htm#SVREA-smart_view_11125600_readme_112 (see section Using Firefox ESR 60 and Later with Smart View) but as well as this blog post The Finnish Hyperion Guy: Smart View 11.1.2.5.800

I assumed that I should now be able to use the Open in Smart View option in with Mozilla Firefox ESR 60 in order to open data grids/data forms in Smart View/Excel from within the browser. Unfortunately, having installed the latest version of the Oracle Smart View for Office add-in for Firefox I am getting the following error when I click the Open in Smart View button in any document:

The address wasn't understood

Firefox doesn't know how to open this address, because one of the following protocols (oraclesv) isn't associated with any program or is not allowed in this context.

You might need to install other software to open this address.

Any ideas?

My config:

Oracle EPM 11.1.2.4

Oracle Smart View for Office 11.1.2.5.810 Build 149

Firefox Quantum ESR 60.5.1esr (64-bit)

of the Oracle Smart View for Office add-in for Firefox

548849

Why did you miss values for week_num 6 & 7 ? 

The result is 0 whereas i was expecting 5 & 10.

Could you also post some create and insert scripts of the table ?

548091

Hi,

Apologies for belated reply.

There's no missing values for wk_num 6 & 7.

Below is the part of the data as to how the 0 appears for negative values. The "Logical Running Sum" is not a physical column, but I am giving it below just for understanding, and using this logical column the Result column is having 0 for negative values.

WK_NUM	Product	Qty	Logical Running Sum	Result
4	A	-15	-15	                0
5	A	-20	-35	                0
6	A	10	-25	                0
7	A	5	-20	                0
8	A	50	30	                30

Same way it works for wk_num's 11 to 14 and wk_num's 15 to 16.

Looking forward to any solutions.

Thanks,

Satyaki_De

Whenever you post any problem please post the DDL & DML for that environment. Or,

WITH tab
AS
   (
       SELECT <column list> FROM <table name>
       UNION ALL
       SELECT <column list> FROM <table name>
   )
SELECT *
FROM tab;

This will help us to concentrate on your main problem rather preparing the environment first.

Got me?

Regards.

Satyaki De.

548091

Below is the script for the data which is in the Prod table:

with prod as (
    select 1 as wk_num, 'A' as prod, 0 as qty from dual union all
    select 2 as wk_num, 'A' as prod, 10 as qty from dual union all
    select 3 as wk_num, 'A' as prod, 20 as qty from dual union all
    select 4 as wk_num, 'A' as prod, -15 as qty from dual union all
    select 5 as wk_num, 'A' as prod, -20 as qty from dual union all
    select 6 as wk_num, 'A' as prod, 10 as qty from dual union all
    select 7 as wk_num, 'A' as prod, 5 as qty from dual union all
    select 8 as wk_num, 'A' as prod, 50 as qty from dual union all
    select 9 as wk_num, 'A' as prod, 15 as qty from dual union all
    select 10 as wk_num, 'A' as prod, 0 as qty from dual union all
    select 11 as wk_num, 'A' as prod, -10 as qty from dual union all
    select 12 as wk_num, 'A' as prod, -30 as qty from dual union all
    select 13 as wk_num, 'A' as prod, -40 as qty from dual union all
    select 14 as wk_num, 'A' as prod, 100 as qty from dual union all
    select 15 as wk_num, 'A' as prod, -20 as qty from dual union all
    select 16 as wk_num, 'A' as prod, 40 as qty from dual union all
    select 17 as wk_num, 'A' as prod, 35 as qty from dual union all
    select 18 as wk_num, 'A' as prod, 20 as qty from dual union all
    select 19 as wk_num, 'A' as prod, 10 as qty from dual
)    
select *
from prod;

hope the above helps.
Thanks,

Hoek

I think I'm missing one requirement here, on what do you want your running total exactly?

SQL> with prod as (
  2      select 1 as wk_num, 'A' as prod, 0 as qty from dual union all
  3      select 2 as wk_num, 'A' as prod, 10 as qty from dual union all
  4      select 3 as wk_num, 'A' as prod, 20 as qty from dual union all
  5      select 4 as wk_num, 'A' as prod, -15 as qty from dual union all
  6      select 5 as wk_num, 'A' as prod, -20 as qty from dual union all
  7      select 6 as wk_num, 'A' as prod, 10 as qty from dual union all
  8      select 7 as wk_num, 'A' as prod, 5 as qty from dual union all
  9      select 8 as wk_num, 'A' as prod, 50 as qty from dual union all
 10      select 9 as wk_num, 'A' as prod, 15 as qty from dual union all
 11      select 10 as wk_num, 'A' as prod, 0 as qty from dual union all
 12      select 11 as wk_num, 'A' as prod, -10 as qty from dual union all
 13      select 12 as wk_num, 'A' as prod, -30 as qty from dual union all
 14      select 13 as wk_num, 'A' as prod, -40 as qty from dual union all
 15      select 14 as wk_num, 'A' as prod, 100 as qty from dual union all
 16      select 15 as wk_num, 'A' as prod, -20 as qty from dual union all
 17      select 16 as wk_num, 'A' as prod, 40 as qty from dual union all
 18      select 17 as wk_num, 'A' as prod, 35 as qty from dual union all
 19      select 18 as wk_num, 'A' as prod, 20 as qty from dual union all
 20      select 19 as wk_num, 'A' as prod, 10 as qty from dual
 21  )    
 22  select wk_num
 23  ,      prod
 24  ,      qty
 25  ,      sum(qty) over (order by wk_num) running_total
 26  ,      case when sign(sum(qty) over (order by wk_num)) = -1 then 0
 27              else sum(qty) over (order by wk_num)
 28         end result
 29  from prod
 30  order by wk_num;

    WK_NUM P        QTY RUNNING_TOTAL     RESULT
---------- - ---------- ------------- ----------
         1 A          0             0          0
         2 A         10            10         10
         3 A         20            30         30
         4 A        -15            15         15
         5 A        -20            -5          0
         6 A         10             5          5
         7 A          5            10         10
         8 A         50            60         60
         9 A         15            75         75
        10 A          0            75         75
        11 A        -10            65         65
        12 A        -30            35         35
        13 A        -40            -5          0
        14 A        100            95         95
        15 A        -20            75         75
        16 A         40           115        115
        17 A         35           150        150
        18 A         20           170        170
        19 A         10           180        180

19 rows selected.

701278

Hi Mak2,

since your requirement is based on procedural rules, you could use the model clause like this:

SQL> WITH prod AS (
  2  SELECT 1 wk_num,'A' prod, 0 qty FROM dual UNION ALL
  3  SELECT 2 ,'A', 10 FROM dual UNION ALL
  4  SELECT 3 ,'A', 20 FROM dual UNION ALL
  5  SELECT 4 ,'A', -15 FROM dual UNION ALL
  6  SELECT 5 ,'A', -20 FROM dual UNION ALL
  7  SELECT 6 ,'A', 10 FROM dual UNION ALL
  8  SELECT 7 ,'A', 5 FROM dual UNION ALL
  9  SELECT 8 ,'A', 50 FROM dual UNION ALL
 10  SELECT 9 ,'A', 15 FROM dual UNION ALL
 11  SELECT 10 ,'A', 0 FROM dual UNION ALL
 12  SELECT 11 ,'A', -10 FROM dual UNION ALL
 13  SELECT 12 ,'A', -30 FROM dual UNION ALL
 14  SELECT 13 ,'A', -40 FROM dual UNION ALL
 15  SELECT 14 ,'A', 100 FROM dual UNION ALL
 16  SELECT 15 ,'A', -20 FROM dual UNION ALL
 17  SELECT 16 ,'A', 40 FROM dual UNION ALL
 18  SELECT 17 ,'A', 35 FROM dual UNION ALL
 19  SELECT 18 ,'A', 20 FROM dual UNION ALL
 20  SELECT 19 ,'A', 10 FROM dual
 21  ) SELECT wk_num, prod, qty, running_total, results FROM prod p
 22  MODEL
 23     PARTITION BY (prod)
 24     DIMENSION BY (wk_num)
 25     MEASURES (qty, 0 AS running_total, 0 AS results)
 26     RULES (
 27        running_total[ANY]=CASE
 28                             WHEN nvl(running_total[cv()-1], 0) >= 0 THEN qty[cv()]
 29                             ELSE nvl(running_total[cv()-1], 0) + qty[cv()]
 30                           END,
 31        results[ANY]=CASE
 32                       WHEN running_total[cv()] >= 0 THEN running_total[cv()]
 33                       ELSE 0
 34                     END
 35     );

    WK_NUM P        QTY RUNNING_TOTAL    RESULTS
---------- - ---------- ------------- ----------
         1 A          0             0          0
         2 A         10            10         10
         3 A         20            20         20
         4 A        -15           -15          0
         5 A        -20           -35          0
         6 A         10           -25          0
         7 A          5           -20          0
         8 A         50            30         30
         9 A         15            15         15
        10 A          0             0          0
        11 A        -10           -10          0

    WK_NUM P        QTY RUNNING_TOTAL    RESULTS
---------- - ---------- ------------- ----------
        12 A        -30           -40          0
        13 A        -40           -80          0
        14 A        100            20         20
        15 A        -20           -20          0
        16 A         40            20         20
        17 A         35            35         35
        18 A         20            20         20
        19 A         10            10         10

19 rows selected.

Regards,

--
Vincent

Satyaki_De

Perhaps you are looking for this ->

satyaki>
satyaki>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>with prod as (
  2      select 1 as wk_num, 'A' as prod, 0 as qty from dual union all
  3      select 2 as wk_num, 'A' as prod, 10 as qty from dual union all
  4      select 3 as wk_num, 'A' as prod, 20 as qty from dual union all
  5      select 4 as wk_num, 'A' as prod, -15 as qty from dual union all
  6      select 5 as wk_num, 'A' as prod, -20 as qty from dual union all
  7      select 6 as wk_num, 'A' as prod, 10 as qty from dual union all
  8      select 7 as wk_num, 'A' as prod, 5 as qty from dual union all
  9      select 8 as wk_num, 'A' as prod, 50 as qty from dual union all
 10      select 9 as wk_num, 'A' as prod, 15 as qty from dual union all
 11      select 10 as wk_num, 'A' as prod, 0 as qty from dual union all
 12      select 11 as wk_num, 'A' as prod, -10 as qty from dual union all
 13      select 12 as wk_num, 'A' as prod, -30 as qty from dual union all
 14      select 13 as wk_num, 'A' as prod, -40 as qty from dual union all
 15      select 14 as wk_num, 'A' as prod, 100 as qty from dual union all
 16      select 15 as wk_num, 'A' as prod, -20 as qty from dual union all
 17      select 16 as wk_num, 'A' as prod, 40 as qty from dual union all
 18      select 17 as wk_num, 'A' as prod, 35 as qty from dual union all
 19      select 18 as wk_num, 'A' as prod, 20 as qty from dual union all
 20      select 19 as wk_num, 'A' as prod, 10 as qty from dual
 21  )    
 22  select k.wk_num,
 23         k.prod,
 24         k.qty,
 25         k.running_sal,
 26         case
 27           when running_sal < 0 
 28           or qty < 0 then
 29             0
 30         else
 31           qty
 32         end result
 33  from (
 34          select wk_num,
 35                 prod,
 36                 qty,
 37                 sum(qty) over(order by wk_num) running_sal
 38          from prod
 39       ) k;

    WK_NUM P        QTY RUNNING_SAL     RESULT
---------- - ---------- ----------- ----------
         1 A          0           0          0
         2 A         10          10         10
         3 A         20          30         20
         4 A        -15          15          0
         5 A        -20          -5          0
         6 A         10           5         10
         7 A          5          10          5
         8 A         50          60         50
         9 A         15          75         15
        10 A          0          75          0
        11 A        -10          65          0

    WK_NUM P        QTY RUNNING_SAL     RESULT
---------- - ---------- ----------- ----------
        12 A        -30          35          0
        13 A        -40          -5          0
        14 A        100          95        100
        15 A        -20          75          0
        16 A         40         115         40
        17 A         35         150         35
        18 A         20         170         20
        19 A         10         180         10

19 rows selected.

Elapsed: 00:00:00.01
satyaki>

Regards.

Satyaki De.

Aketi Jyuuzou

My model clause article (in Japanese)
http://www.geocities.jp/oraclesqlpuzzle/oow2009-olap-model.html

I used Least , greatest and model clause B-)

In model clause,
If runningSum is over than 0,
I will regard runningSum was 0 B-)

And Then I use greatest
when runningSum is negative,I will change runningSum is 0 ;-)

create table modelRun(WK_NUM,Qty) as
select  1,  0 from dual union
select  2, 10 from dual union
select  3, 20 from dual union
select  4,-15 from dual union
select  5,-20 from dual union
select  6, 10 from dual union
select  7,  5 from dual union
select  8, 50 from dual union
select  9, 15 from dual union
select 10,  0 from dual union
select 11,-10 from dual union
select 12,-30 from dual union
select 13,-40 from dual union
select 14,100 from dual union
select 15,-20 from dual union
select 16, 40 from dual union
select 17, 35 from dual union
select 18, 20 from dual union
select 19, 10 from dual;

select WK_NUM,Qty,greatest(runSum,0) as Result
  from modelRun
 model
dimension by(WK_NUM)
measures(Qty,0 as runSum)
rules(
runSum[any] order by WK_NUM =
 Least(presentv(runSum[cv()-1],runSum[cv()-1],0),0)
+Qty[cv()]);

WK_NUM  QTY  RESULT
------  ---  ------
     1    0       0
     2   10      10
     3   20      20
     4  -15       0
     5  -20       0
     6   10       0
     7    5       0
     8   50      30
     9   15      15
    10    0       0
    11  -10       0
    12  -30       0
    13  -40       0
    14  100      20
    15  -20       0
    16   40      20
    17   35      35
    18   20      20
    19   10      10

1 - 8

Added on Feb 20 2019

#business-intelligence-applications, #financial-consolidation, #hfm, #performance-management-applications

4 comments

1,258 views

Analytics Software

Open in Smart View option does not work in Firefox

Comments

Post Details