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     supress drill down on across total
Go
New
Search
Notify
Tools
Reply
  
supress drill down on across total
 Login/Join
 
Gold member
posted
I am doing an across in my report and I am drilling down on Across coumn. How do I supress drill down on Across total?
TABLE FILE CAR
SUM DEALER_COST
BY COUNTRY
ACROSS CAR AS ' ' ACROSS-TOTAL
WHERE RECORDLIMIT EQ '10000'
ON TABLE SET STYLE *
TYPE=ACROSSVALUE,
ACROSS=1,
STYLE=BOLD,
TARGET='_blank',
JUSTIFY=CENTER,
FOCEXEC=CAR,
$
ENDSTYLE
END
 
Posts: 76 | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Wow, that's a really good question. I'm stumped.

I've tried a lot of different things, but there's no way to address the ACROSS-TOTAL column. With the 5.3.2 Dev Studio Report Painter, you can only change the title of the column from "TOTAL" to something else. It would appear that the ACROSS-TOTAL column was overlooked when WebFOCUS Style Sheet was created.

Here's an inelegant method to get around this problem, using a two-verb request:

TABLE FILE CAR
SUM DEALER_COST AS 'TOTAL'
BY COUNTRY

SUM DEALER_COST AS ' '
BY COUNTRY
ACROSS CAR AS ' ' 
ON TABLE SET STYLE *
TYPE=ACROSSVALUE,
ACROSS=1,
STYLE=BOLD,
TARGET='_BLANK',
JUSTIFY=CENTER,
FOCEXEC=CAR,
$
ENDSTYLE
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Francis

nice what you are trying to do, but the question was how to "SUPRESS" the drill down on the total....




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Frank, it looks like "there's no way to address the ACROSS-TOTAL column", hence no way to "SUPPRESS the drill-down on the total" so I tried to find another way to provide an across total AND disable drill-down, which I think my example does.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks Francis. Looks like there is no other simple way of doing this other than multi-verb.
 
Posts: 76 | Registered: October 28, 2003Reply With QuoteReport This Post
Member
posted Hide Post
To remove the drill down on the total column use this code:

TYPE=ACROSSVALUE, COLUMN=ROWTOTAL, COLOR=BLACK, FOCEXEC=NONE, $

-Arin Lipman
 
Posts: 6 | Registered: August 01, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Doesn't work in 5.3.2.

TABLE FILE CAR
SUM DEALER_COST
BY COUNTRY
ACROSS CAR AS ' ' ACROSS-TOTAL
ON TABLE SET STYLE *
TYPE=ACROSSVALUE,
ACROSS=1,
STYLE=BOLD,
TARGET='_BLANK',
JUSTIFY=CENTER,
FOCEXEC=CAR,
$
TYPE=ACROSSVALUE, COLUMN=ROWTOTAL, COLOR=BLACK, FOCEXEC=NONE, $
ENDSTYLE
END


We would love to know what version you've tested this on.

Cheers,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Dosen't work in 7.6.0 either.

I've had to do this a number of times and I've always gone the long route; seems to work for me.
More inelegant than Francis, but...

Added code to get column counts...


SET ASNAMES = ON
DEFINE FILE CAR
CAR_X/A16 = 'TOTAL';
END
TABLE FILE CAR
SUM
DEALER_COST
BY COUNTRY
BY CAR_X AS 'CAR'
ON TABLE HOLD AS H1
END
-RUN
TABLE FILE CAR
SUM
DEALER_COST
BY COUNTRY
BY CAR
ON TABLE HOLD AS H2
END
-RUN

-****************************
-* Find the ACROSS Column Count
DEFINE FILE H2
CNT/I5 WITH CAR = 1;
END
TABLE FILE H2
SUM
CNT
ON TABLE SAVE AS X1
END
-RUN
-READ X1 &ACROSS.I5.
-****************************

TABLE FILE H2
SUM
DEALER_COST
BY COUNTRY
BY CAR
ON TABLE HOLD AS H3
MORE
FILE H1
END
-RUN
DEFINE FILE H3
SORT_EM/I2 = DECODE CAR ('TOTAL' 99 ELSE 1);
END
TABLE FILE H3
SUM
DEALER_COST BY COUNTRY AS ''
ACROSS SORT_EM NOPRINT
ACROSS CAR AS ' '
ON TABLE SET STYLE *

-SET &CNTR = 1;
-REPEAT DRILL &ACROSS TIMES
-SET &CNTR = &CNTR + 1;
TYPE=ACROSSVALUE,
ACROSS=2,
STYLE=BOLD,
TARGET='_BLANK',
JUSTIFY=CENTER,
FOCEXEC=CAR,
COLUMN=N&CNTR,
$
-DRILL
ENDSTYLE
END
-EXIT

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1969 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If you want to try something a little different:

Focexec:
  
SET JSURL=/approot/ibisamp/suppress_total.js
TABLE FILE CAR
SUM
     DEALER_COST
BY COUNTRY
ACROSS CAR AS ' '
     ACROSS-TOTAL
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=ACROSSVALUE,
     STYLE=BOLD,
     TARGET='_blank',
     JUSTIFY=CENTER,
     FOCEXEC=CAR,
$
ENDSTYLE
END


C:\ibi\apps\ibisamp\suppress_total.js
function changeAllA() {
   var anchorList = document.getElementsByTagName("a");

   for (var i = 0; i < anchorList.length; i++) {
        // alert(anchorList[i].innerHTML + " "  + anchorList[i].target);
        if (anchorList[i].innerHTML == "TOTAL") {
             anchorList[i].parentNode.innerHTML = "TOTAL";
        }
   }
}

if (window.addEventListener) {
	window.addEventListener('load', 'changeAllA', false);
} else if (window.attachEvent) {
	window.attachEvent('onload', changeAllA);
} else {
	alert("not supported");
}  


Should do the trick.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
dhagen,

if (window.addEventListener) {
window.addEventListener('load', 'changeAllA', false);
} else if (window.attachEvent) {
window.attachEvent('onload', changeAllA) ;
} else {
alert("not supported");
}



You really do have to know the column count, i.e., the bolded code - is that correct???


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1969 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Sorry Tom, I don't really follow your question.

If you have CSS on, then the bolds etc. that are applied to the column titles are applied to the TD tag, not the text in the anchor. All you need to know here is the title for the total column ... in this case "TOTAL". Bolds - or any styling for that matter, and column counts are not required.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
No, you answered it, the TITLE. Very slick!

My JS education begins in Sept. I know I have to use JS a lot now that IBI/WebFOCUS is going/gone that route.

Have to keep learning...

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1969 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
I love solving problems , how about this !!

TABLE FILE CAR
SUM DEALER_COST
BY COUNTRY
ACROSS CAR AS ' ' ACROSS-TOTAL
WHERE RECORDLIMIT EQ '100'
ON TABLE SET STYLE *
-REPEAT LINKLOOP FOR &CNT FROM 2 TO 11 ;
TYPE=ACROSSVALUE,
COLUMN=N&CNT,
STYLE=BOLD,
TARGET='_blank',
JUSTIFY=CENTER,
FOCEXEC=CAR,
$
-LINKLOOP
ENDSTYLE
END

---
I used your example and use REPEAT logic to add drill down link to all but column N12 which is ACROSSTOTAL.


-Yogesh Patel
------------------------------------------------------------------------
PROD: WF 764 on Linux Apache tomcat v5.5
DEV: WF 768 on Linux
 
Posts: 42 | Location: Edison, New Jersey | Registered: January 30, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Yogesh

If this works....I will try tomorrow, I love it and you can change the highest value by first counting the expected numbers, and put that number in an amper value.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
It works and you are right '11' in my example was the ACROSSTOTAL column number for the example we were using, that number can be made dynamic as you said by puting an amper variable.

quote:
Originally posted by ypatel:
I love solving problems , how about this !!

TABLE FILE CAR
SUM DEALER_COST
BY COUNTRY
ACROSS CAR AS ' ' ACROSS-TOTAL
WHERE RECORDLIMIT EQ '100'
ON TABLE SET STYLE *
-REPEAT LINKLOOP FOR &CNT FROM 2 TO 11 ;
TYPE=ACROSSVALUE,
COLUMN=N&CNT,
STYLE=BOLD,
TARGET='_blank',
JUSTIFY=CENTER,
FOCEXEC=CAR,
$
-LINKLOOP
ENDSTYLE
END

---
I used your example and use REPEAT logic to add drill down link to all but column N12 which is ACROSSTOTAL.


-Yogesh Patel
------------------------------------------------------------------------
PROD: WF 764 on Linux Apache tomcat v5.5
DEV: WF 768 on Linux
 
Posts: 42 | Location: Edison, New Jersey | Registered: January 30, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
-****************************
-* Find the ACROSS Column Count
DEFINE FILE H2
CNT/I5 WITH CAR = 1;
END
TABLE FILE H2
SUM
CNT
ON TABLE SAVE AS X1
END
-RUN
-READ X1 &ACROSS.I5.
-****************************
then:

-SET &CNTR = 1;
-REPEAT DRILL &ACROSS TIMES
-SET &CNTR = &CNTR + 1;
TYPE=ACROSSVALUE,
ACROSS=2,
STYLE=BOLD,
TARGET='_BLANK',
JUSTIFY=CENTER,
FOCEXEC=CAR,
COLUMN=N&CNTR,
$
-DRILL






I think I showed how to dynamically generate the # of columns based on the across value to be used later in the program....

Maybe not...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1969 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     supress drill down on across total

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