[SOLVED] Add an extra column under an across for a particular value

August 29, 2018, 10:43 AM

Monika Acosta

I have an odd requirement from my users (whats new) and I am not sure how to code it. I have a report with the across and on the across total they want a metric to show up that isnt in the other across columns. I tried just hiding it but you cant hide the metric but it hides it everywhere. At least I dont think you can.

Period (label)
Period 1 Period 2 Period 3 Total
Row 1 metric 1 meric 2 metric 1 meric 2 metric 1 meric 2 metric 1 metric 2 metric 3
Row 2 metric 1 meric 2 metric 1 meric 2 metric 1 meric 2 metric 1 metric 2 metric 3

This message has been edited. Last edited by: FP Mod Chuck,

August 29, 2018, 10:48 AM

Monika Acosta

Sorry my grid apparently didnt post well... it looked good in the preview...

Period Label
Period 1 Period 2 Period 3 Total
Row1 metric 1 metric 2 metric 1 metric 2 metric 1 metric 2 metric 1 metric 2 metric 3
ROw2 metric 1 metric 2 metric 1 metric 2 metric 1 metric 2 metric 1 metric 2 metric 3

August 29, 2018, 10:53 AM

MartinY

Instead of using the ACROSS-TOTAL option, create your own total columns to append them to the original data then displayed in whished order.

See following post for sample
Manual Across

quote:

Sorry my grid apparently didnt post well... it looked good in the preview...

Use the code tag when posting sample code or layout. The last one on the ribbon:

</>

August 29, 2018, 11:47 AM

Monika Acosta

I did try that.

I made three different hold files

A) has the non totals and the 2 metrics
B) Has the totals and the 2 metrics
c) ha the totals and the extra metric

I then did a more to combine A and B and from there didnt know what to do.

I cant do a more to add C because it doesnt have the same amount of columns.

I thought about doing a join but then it would add a column to A and B and just be null.

I couldnt get the match to work. Im still relatively new and havent used that much

  
	Period (label)								
	Period 1		Period 2		Period 3		Total		
Row 1	metric 1	meric 2	metric 1	meric 2	metric 1	meric 2	metric 1	metric 2	metric 3
Row 2	metric 1	meric 2	metric 1	meric 2	metric 1	meric 2	metric 1	metric 2	metric 3

August 29, 2018, 12:06 PM

Doug

Sorry Monika, I'm confused. What is the column, are the columns, that you only want to show up sometimes?

August 29, 2018, 12:31 PM

Monika Acosta

Sorry - its a bit confusing.

Here is an example using the car file:

TABLE FILE CAR
SUM
CAR.BODY.SEATS
CAR.BODY.RETAIL_COST
CAR.BODY.SALES
BY CAR.COMP.CAR
ACROSS LOWEST CAR.ORIGIN.COUNTRY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE ROW-TOTAL
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END

In this example they only want to see the SALES in the TOTAL. Not under each country.

August 29, 2018, 01:13 PM

Tony A

Something like this, but you might have to play with the column titles using SUBHEAD etc. Also, the column notation (C1, C2 etc.) would need to be thought out in a preprocess maybe, to allow you the knowledge of how many across values you have.

Anyway, it's a start ...

TABLE FILE GGSALES
  SUM DOLLARS    AS 'Metric 1'
      BUDDOLLARS AS 'Metric 2'
	  UNITS NOPRINT
   BY ST
ACROSS REGION AS ''
RECAP XX1/D12c = C1 + C4 + C7 + C10; AS 'Total'
RECAP XX2/D12c = C2 + C5 + C8 + C11; AS ' '
RECAP XX3/D12c = C3 + C6 + C9 + C12; AS 'Metric 3 '
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  grid=off, $
ENDSTYLE
END

August 29, 2018, 01:57 PM

Monika Acosta

I have never seen the recap command. This looks like it might work. Ill try and let you know.

Thanks!

August 29, 2018, 02:02 PM

MartinY

Tony has a good option but, as he mentioned, you need to know the number of ACROSS field to perform your COMPUTE/RECAP. Which can be a pain when it's not always the same.

quote:

I cant do a more to add C because it doesn't have the same amount of columns

Here another option. Need to have the same columns' name and format.

-* EXTRACT SOURCE DATA
DEFINE FILE GGSALES
YR /YY = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS/D8C AS 'VAL'
    BUDDOLLARS/D8C AS 'BDG'
BY REGION
BY YR
ON TABLE HOLD AS EXTDATA
END
-RUN

-* CREATE YR ROW TOTAL (NEW COLUMN)
TABLE FILE EXTDATA
SUM VAL
BY REGION
BY TOTAL COMPUTE YR /YY = 9900;
ON TABLE HOLD AS YRTOT
END
-RUN

-* CREATE YR ROW VAR TOTAL (NEW COLUMN)
DEFINE FILE EXTDATA
VAR/D8C = BDG - VAL;
END
TABLE FILE EXTDATA
SUM VAR AS 'VAL'
BY REGION
BY TOTAL COMPUTE YR /YY = 9999;
ON TABLE HOLD AS VARDATA
END
-RUN

-* NMERGE DETAIL WITH TOTAL
TABLE FILE EXTDATA
SUM VAL
BY REGION
BY YR
ON TABLE HOLD AS RPTDATA
MORE
FILE YRTOT
MORE
FILE VARDATA
END
-RUN

-* PRODUCE FINAL REPORT
SET ACRSVRBTITL = ON
DEFINE FILE RPTDATA
YRX /A20V = IF YR EQ 9900 THEN 'Total'
       ELSE IF YR EQ 9999 THEN 'Var To Bdg'
       ELSE FPRINT(YR, 'YY', 'A4');
END
TABLE FILE RPTDATA
SUM VAL AS '$'
BY REGION
ACROSS YR  NOPRINT
ACROSS YRX AS ''
ON TABLE COLUMN-TOTAL AS 'Grand-Total'
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN

August 29, 2018, 03:17 PM

Monika Acosta

Thanks guys! I was able to get it working, I really appreciate all the help!

September 05, 2018, 08:33 AM

Danny-SRL

Another solution. Use multi-set request and sequence:

  
-SET &ECHO=ALL;
-* File monika01.fex
TABLE FILE CAR
SUM 
DCOST
RCOST
SALES
BY CAR
SUM 
DCOST
RCOST
BY CAR
ACROSS SEATS
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
-*ON TABLE ROW-TOTAL
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,
$
TYPE=REPORT, COLUMN=C1, SEQUENCE=21,
$
TYPE=REPORT, COLUMN=C2, SEQUENCE=22,
$
TYPE=REPORT, COLUMN=C3, SEQUENCE=23,
$
ENDSTYLE
END

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