Across Function? [SOLVED]

Hello, I'm fairly new to Webfocus and I'm having trouble using the Across function to get the results I'm looking for. I currently have output that looks like this:

Col 1 Col 2 Col 3 Col 4 Description
Val1 Val2 Val3 Val4 ABC
Val1 Val2 Val3 Val4 DEF
Val1 Val2 Val3 Val4 GHI

The data in the first 4 columns is the same, only the description is different. I want to be able to combine the 3 different rows into a single row that has additonal columns for the description:

Col 1 Col 2 Col 3 Col 4 Descr Descr Descr
Val1 Val2 Val3 Val4 ABC DEF GHI

If I apply the use of Across then I get the first 4 columns repeating with each Description on top. That's close but not really what I want.

The first 4 columns are values like Transaction and Invoice Numbers. Is there a better way?
Thanks.

This message has been edited. Last edited by: jazzman172,

DS ver 8008

Posts: 3 | Registered: August 25, 2014

MartinY

Virtuoso

posted

Hide Post

This is giving what you're looking for :

TABLE FILE CAR
SUM MODEL
BY COUNTRY
BY CAR
ACROSS MODEL NOPRINT
WHERE COUNTRY EQ 'W GERMANY';
WHERE CAR EQ 'BMW';
ON TABLE SET BYDISPLAY ON
END

But, if you remove the WHERE clause, you will have 18 different "ACROSS" column because there is 18 different MODEL.

So, if you have a lot of different description, you will have as many column as you have description. ACROSS columns are limited !!!

Otherwise if you have a fix number of description then create as many define field as you have description and assign one description by define; which is more complex but feasible.

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Show Post

Tony A

Expert

posted

Hide Post

Alternatively :-

TABLE FILE GGSALES
   SUM MAX.DOLLARS    AS 'Dollars' -* If your values are truly the same then using a prefix operator like MAX will help
       MAX.BUDDOLLARS AS 'Budget'
   SUM ST AS 'State'
-* If you need to segregate your rows using a BY statement, then add it here
ACROSS ST NOPRINT
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  grid=off, size=9, $
ENDSTYLE
END

As Martin indicates, there are limits for the number of columns and field lengths, so if your ACROSS are descriptions that might be large, then be aware that your report might break in time so ensure limits are known.

T

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

jazzman172

Member

posted

Hide Post

Thanks Martin and Tony. I still don't think that that is what I'm looking for.

In this example, the first line, Ref# 53718835A would produce 5 lines as the description of the charges occurs 5 times (even when zero). Using the across
condition I'd like it to render only 1 line as shown below. My other attempts have repeated the first 4 columns 5 times, once for each of my ACROSS conditons, DESCRIPTION of the charge in this case. Perhaps I'm not understanding what you were each telling me. I hope that the formatting in my example persists.

Date BOL# Ref # Name Charge Surchrg Charge1 Charge2 Charge3
5/19/2014 8:04:49 AM 130-8696 53718835A ABC COMPANY 847 133 250 0 0
5/19/2014 8:04:49 AM 130-8711 53718836A ABC COMPANY 847 133 0 0 0
5/19/2014 8:04:49 AM 130-8703 53710851A DEF COMPANY 642 79 250 0 0
5/19/2014 8:04:49 AM 130-81 53713447A BILLS CONSTRUCTION 190 0 0 0 0
5/19/2014 8:04:49 AM 130-8708 53721461A BILLS CONSTRUCTION 642 73.5 0 0 0

DS ver 8008

Posts: 3 | Registered: August 25, 2014

Ignored post by jazzman172 posted

Show Post

j.gross

Virtuoso

posted

Hide Post

Something like thai?

TABLE FILE CAR
SUM COMPUTE
SEQ/I5=
IF COUNTRY EQ LAST COUNTRY
AND CAR EQ LAST CAR
THEN LAST SEQ + 1 ELSE 1;
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD
END

TABLE FILE HOLD
SUM MODEL
BY COUNTRY
BY CAR
ACROSS SEQ NOPRINT
END

The first TABLE assigns a sequence number to each Model, within Country and Car.

The second one uses those in ACROSS to position the Models, 1,2,3.

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Show Post

jazzman172

Member

posted

Hide Post

This is now solved. I used defines to create headings for each of my charges and added the invoice charge.

Thanks for the input everybody!

DS ver 8008

Posts: 3 | Registered: August 25, 2014

Ignored post by jazzman172 posted