Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Subtotal of column as a By Field or a Compute
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Subtotal of column as a By Field or a Compute
 Login/Join
 
Gold member
posted
Hello,

I'm trying to filter a table by the subtotal of a column and having a little trouble.

What I want to do is something like:
 

TABLE FILE
SUM PAID_AMOUNT
BY MEMBER
BY COUNTRY
WHERE MEMBER SUBTOTAL GT 50000
END

 


I know the syntax above isn't correct. I have tried having two SUM statements in the same table file but that takes a while to run and the data isn't accurate when aggregating. I'm hoping there is a way to get the MEMBER SUBTOTAL into another column, like as a BY or a SUM and then I can filter on that static value. Can someone think of another way to do this?

Thank you in advance!

This message has been edited. Last edited by: Brandon Andrathy,


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Not sure if this is what you're looking for but WHERE TOTAL might solve this problem.
  
TABLE FILE EMPLOYEE
SUM CURR_SAL
BY LAST_NAME AND BY FIRST_NAME
WHERE TOTAL CURR_SAL EXCEEDS 20000
WHERE DEPARTMENT IS 'MIS'
END


WebFOCUS 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
Hey Babak. Thank you very much for the reply.

WHERE Total would work but I would want to filter only by CURR_SAL and LAST_NAME. Not for FIRST NAME as well. Can you think of a way to do that?

As a precursor, Doing something like the below unfortunately takes a very long time to load so I'm looking for a more efficient solution:

 

TABLE FILE EMPLOYEE
SUM 
    CURR_SAL
BY LAST_NAME 
WHERE TOTAL CURR_SAL GT 20000
SUM
    CURR_SAL
BY LAST_NAME
BY FIRST_NAME
END

 


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
What about this ?
TABLE FILE XYZ
SUM PAID_AMOUNT NOPRINT
BY MEMBER
WHERE TOTAL PAID_AMOUNT GT 50000;
ON TABLE HOLD AS MBRLST FORMAT ALPHA
END
-RUN

TABLE FILE XYZ
SUM PAID_AMOUNT
BY MEMBER
BY COUNTRY
WHERE MEMBER IN FILE MBRLST;
END
-RUN


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2099 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
Hey Martin, thank you!

I actually tried the IN FILE solution already but when there are too many records going into the second hold file, the report crashes. Do you have an idea how to handle more than say 3k records with IN FILE?

I have thought about wanting to JOIN up to the synyonym directly in a table file but apparently that's not feasible lol.


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Guru
posted Hide Post
Is this what you are looking for?
TABLE FILE GGSALES
SUM DOLLARS/D12M
    DOLLARS WITHIN PRODUCT NOPRINT
BY  PRODUCT RECOMPUTE
BY  REGION
WHERE TOTAL DOLLARS GT 5000000;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/flat.sty, $
ENDSTYLE
END
  


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 365 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
Originally posted by Brandon Andrathy:
Do you have an idea how to handle more than say 3k records with IN FILE?


According to this, you shouldn't have any problem using IN FILE. The only point is that the data cannot be a variable field lenght AnV, it must be a fixed field length An :
What is the size limit of a WHERE clause in WebFOCUS 8


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2099 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
Hey Martin, thank you very much for your reply. So it looks like the maximum of 32000 characters applies here. when I look at the WHERE IN FILE condition being utilized in query, it does this:

WHERE MEMBER_ID IN '99999999999', '99999999999'

So as you can see, with the length of my character string, this gets out of control pretty quickly and causes a reporting server error.


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
From where does your MEMBER_ID for the WHERE clause IN FILE are coming from ?
Do you build the file dynamically ?

Not the most efficient, but an option, if you don't have any other then using a IN FILE (and still limited) clause may be the following:
DEFINE FILE XYZ
ROW_ID /I6 = ROW_ID + 1;
END
TABLE FILE XYZ
SUM PAID_AMOUNT NOPRINT
BY ROW_ID
BY MEMBER_ID
WHERE TOTAL PAID_AMOUNT GT 50000;
ON TABLE HOLD AS EXTMBR
END
-RUN

TABLE FILE EXTMBR
BY MEMBER_ID
WHERE ROW_ID LE 30000;
ON TABLE HOLD AS MBRLST1 FORMAT ALPHA
END
-RUN

TABLE FILE EXTMBR
BY MEMBER_ID
WHERE ROW_ID GT 30000 AND ROW_ID LT 60000;
ON TABLE HOLD AS MBRLST2 FORMAT ALPHA
END
-RUN

TABLE FILE EXTMBR
BY MEMBER_ID
WHERE ROW_ID GT 60000;
ON TABLE HOLD AS MBRLST3 FORMAT ALPHA
END
-RUN

TABLE FILE XYZ
SUM PAID_AMOUNT
BY MEMBER
BY COUNTRY
WHERE MEMBER_ID IN FILE MBRLST1 OR MEMBER_ID IN FILE MBRLST2 OR MEMBER_ID IN FILE MBRLST3;
END
-RUN


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2099 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
Hallway!!!! You just made my day Smiler Smiler . Wow!! I can't believe I didn't see your post earlier.

Thank you so much!!!!

Martin, thank you also for your suggestion. I was thinking about something like that but didn't know how to go about coding it. Hallway saved the day.

Thanks everyone! I'll go ahead and close.


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Guru
posted Hide Post
Excellent!! I'm glad it worked for you.


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 365 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
Originally posted by Brandon Andrathy:
So it looks like the maximum of 32000 characters applies here.


I use it often.
I have a file where this week has 39 639 records, each with 15 characters long without any issues in a WHERE abc IN FILE xyz.
Your problem was probably somewhere else.

But glad to see that you solved it


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2099 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Master
posted Hide Post
@Hallway - +1 Excellent example of using the WITHIN and WHERE TOTAL phrases. Thanks!
 
Posts: 760 | Registered: April 23, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Subtotal of column as a By Field or a Compute

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.