I have my months as an Across in my report (apr, May, June) etc.

I have a couple of BY fields (service, then employee)

Then I have 3 metrics (SUM, COUNT, AVERAGE) of 3 fields.

Right now, my three metrics are showing under the ACROSS:

April May
Service Employee Metric 1 Metric 2 Metric 3 Metric 1 Metric 2 Metric 3
General John 25 20 21 34 21 23


I would like the Services to go by ROW not by COLUMN so it looks something like:

April May
Service Employee
General John Metric 1 25 34
Metric 2 20 21
Metric 3 21 23

Is there anyway I can do this?

This message has been edited. Last edited by: <Kathryn Henning>,

Sorry I realized the formatting got messed up when I posted, here is an image of what I am talking about what it looks like now versus what I would like:

Check out the syntax for using OVER.

T

I tried to add in the OVER and it didn't make any difference...here is my code:

SUM
CNT.FACT_DAY.FACT_DAY.ACCOUNT_NUMBER AS 'Cases' OVER
FACT_DAY.FACT_DAY.DAYS AS 'Days' OVER
FACT_DAY.CONSERVABLE_DAY.LOS AS 'Acute LOS'
BY LOWEST FACT_DAY.FACT_DAY.SERVICE
BY LOWEST FACT_DAY.FACT_DAY.NAME AS 'Employee'
ACROSS LOWEST J2.DIM_DATE.FISCAL_MONTH_OF_YEAR_NAME AS '' COLUMNS 'Apr' AND 'May' AND 'Jun' AND 'Jul' AND 'Aug' AND 'Sep' AND 'Oct' AND 'Nov' AND 'Dec' AND 'Jan' AND 'Feb' AND 'Mar'
ON TABLE SUBHEAD
""
WHERE J2.DIM_DATE.FISCAL_YEAR_NAME EQ '2014/2015';

It looks like you need to do something like:

TABLE FILE ABC
SUM YOUR_MEASURES
BY SERVICE
BY EMPLOYEE
BY METRICS
ACROSS MONTH
END

Yes that is what I am looking to do

Except I cannot just do a "BY METRICS" because then they are no longer "summed" or "counted",

For example, if I have a count of employee IDS as one metric, if I put "employee ID" as a BY field, it just lists each of the employee ID as a separate row, but not the actual count of employee in a separate row

You could try this:

  
-* File hfung01.fex
TABLE FILE CAR
SUM 
LENGTH COMPUTE LNAME/A6='LENGTH';
WIDTH  COMPUTE WNAME/A6='WIDTH';
HEIGHT COMPUTE HNAME/A6='HEIGHT';
BY CAR
BY MODEL 
BY SEATS
ON TABLE HOLD AS HFUNG FORMAT ALPHA
END
!TYPE HFUNG.MAS
-RUN
EX -LINES 9 EDAPUT MASTER,HFUNG,C,MEM
FILENAME=HFUNG   , SUFFIX=FIX
 SEGMENT=HFUNG, SEGTYPE=S0
 FIELDNAME=CAR, ALIAS=E01, USAGE=A16, ACTUAL=A16, $
 FIELDNAME=MODEL, ALIAS=E02, USAGE=A24, ACTUAL=A24, $
 FIELDNAME=SEATS, ALIAS=E03, USAGE=I3, ACTUAL=A03, $
 SEGMENT=METRICS, PARENT=HFUNG, OCCURS=VARIABLE
 FIELDNAME=METRIC, ALIAS=E04, USAGE=D5, ACTUAL=A05, $
 FIELDNAME=NAME, ALIAS=E05, USAGE=A6, ACTUAL=A06, $
-RUN
DEFINE FILE HFUNG
MONTH/A12=DECODE SEATS (2 APRIL 3 MAY 4 JUNE 5 JULY ELSE DECEMBER);
END
TABLE FILE HFUNG
SUM METRIC
BY CAR
BY MODEL
BY NAME AS ''
ACROSS SEATS NOPRINT
ACROSS MONTH AS ''
ON TABLE SET ACROSSTITLE SIDE
END

Maybe something like this may answer your need :

TABLE FILE GGSALES
SUM UNITS
BY REGION
BY CITY
SUM CNT.DST.PCD AS 'Nb Product'
BY REGION
BY CITY
ACROSS CATEGORY
END

Interesting I will look into it

The reason I am looking to change this is that right now the columns overlap when I run the report in the composer with each month having 3 metrics, that means I have 12x3 = 36 columns per year which don't fit on a page. Perhaps I can make an expandable column instead of row?

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

Perhaps this will help:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
-RUN

TABLE FILE CAR
SUM
LENGTH AS METRIC1
WIDTH  AS METRIC2
HEIGHT AS METRIC3

BY COUNTRY
BY CAR
BY SEATS

ON TABLE HOLD AS HOLD0
END
-RUN

TABLE FILE HOLD0
SUM 
METRIC1 AS METRIC
COMPUTE METRIC_NAME/A8 = 'METRIC 1';

BY COUNTRY
BY CAR
BY SEATS

ON TABLE HOLD AS HOLD1
END
-RUN

TABLE FILE HOLD0
SUM 
METRIC2 AS METRIC
COMPUTE METRIC_NAME/A8 = 'METRIC 2';

BY COUNTRY
BY CAR
BY SEATS

ON TABLE HOLD AS HOLD2
END
-RUN

TABLE FILE HOLD0
SUM 
METRIC3 AS METRIC
COMPUTE METRIC_NAME/A8 = 'METRIC 3';

BY COUNTRY
BY CAR
BY SEATS

ON TABLE HOLD AS HOLD3
END
-RUN

TABLE FILE HOLD1
SUM
METRIC

BY COUNTRY
BY CAR
BY METRIC_NAME AS ''

ACROSS SEATS

MORE
FILE HOLD2

MORE
FILE HOLD3
END
-RUN

Thanks Francis! That is perfect with the sample data.

Unfortunately I do not have any experience with HOLD files but I believe I get the gist of it, you are creating a temp table of sorts. Right now I am getting an error:

Can someone help me understand the following two errors I am getting:
"
IN PROCEDURE new_procedure1
(FOC016) THE TRUNCATED FIELDNAME IS NOT UNIQUE
"
And
"IN PROCEDURE new_procedure1
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD1
BYPASSING TO END OF COMMAND
'

My Code is as followed (using Francis's template):
TABLE FILE FACT_CONSERVABLE_DAY
SUM
CNT.FACT_ACCOUNT_NUMBER AS METRIC1
FACT_DAYS AS METRIC2
FACT_LOS AS METRIC3
BY LOWEST FACT_SERVICE AS SERVICE
BY LOWEST FACT_NAME AS NAME
ACROSS LOWEST J2.DIM_DATE.FISCAL_MONTH_OF_YEAR_NAME AS MONTH AS '' COLUMNS 'Apr' AND 'May' AND 'Jun' AND 'Jul' AND 'Aug' AND 'Sep' AND 'Oct' AND 'Nov' AND 'Dec' AND 'Jan' AND 'Feb' AND 'Mar'

ON TABLE HOLD AS HOLD0
END
-RUN

TABLE FILE HOLD0
SUM
METRIC1 AS METRIC
COMPUTE METRIC_NAME/A8 = 'METRIC 1';

BY SERVICE
BY NAME

ON TABLE HOLD AS HOLD1
END

-RUN

TABLE FILE HOLD0
SUM
METRIC2 AS METRIC
COMPUTE METRIC_NAME/A8 = 'METRIC 2';

BY SERVICE
BY NAME


ON TABLE HOLD AS HOLD2
END
-RUN

TABLE FILE HOLD0
SUM
METRIC3 AS METRIC
COMPUTE METRIC_NAME/A8 = 'METRIC 3';

BY SERVICE
BY NAME

ON TABLE HOLD AS HOLD3
END
-RUN

TABLE FILE HOLD1
SUM
METRIC


BY SERVICE
BY NAME
BY METRIC_NAME AS ''

ACROSS MONTH

MORE
FILE HOLD2

MORE
FILE HOLD3
END
-RUN

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
-RUN

You need SET ASNAMES=ON to ensure the HOLD files will contain column names using the AS instead of the original. This is probably what's causing the (FOC016) THE TRUNCATED FIELDNAME IS NOT UNIQUE error. And this means the HOLD file does not get created, so you get the (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD1 error.

Interesting! But I do have the

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
-RUN

Does this need to be set for each hold file (hold0, hold1, hold2, hold3, etc)?

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
-RUN

TABLE FILE FACT_CONSERVABLE_DAY
SUM
     CNT.FACT_CONSERVABLE_DAY.FACT_CONSERVABLE_DAY.ACCOUNT_NUMBER AS METRIC1
     FACT_CONSERVABLE_DAY.FACT_CONSERVABLE_DAY.CONSERVABLE_DAYS AS METRIC2
     FACT_CONSERVABLE_DAY.FACT_CONSERVABLE_DAY.ACUTE_LOS AS METRIC3
BY  LOWEST FACT_CONSERVABLE_DAY.FACT_CONSERVABLE_DAY.PHYSICIAN_SERVICE AS SERVICE
BY  LOWEST FACT_CONSERVABLE_DAY.FACT_CONSERVABLE_DAY.PHYSICIAN_NAME AS NAME
ACROSS LOWEST J2.DIM_DATE.FISCAL_MONTH_OF_YEAR_NAME AS MONTH AS '' COLUMNS 'Apr' AND 'May' AND 'Jun' AND 'Jul' AND 'Aug' AND 'Sep' AND 'Oct' AND 'Nov' AND 'Dec' AND 'Jan' AND 'Feb' AND 'Mar'

ON TABLE HOLD AS HOLD0
END
-RUN

TABLE FILE HOLD0
SUM
METRIC1 AS METRIC
COMPUTE METRIC_NAME/A8 = 'METRIC 1';

BY SERVICE
BY NAME

ON TABLE HOLD AS HOLD1
END

-RUN

TABLE FILE HOLD0
SUM
METRIC2 AS METRIC
COMPUTE METRIC_NAME/A8 = 'METRIC 2';

BY SERVICE
BY NAME


ON TABLE HOLD AS HOLD2
END
-RUN

TABLE FILE HOLD0
SUM
METRIC3 AS METRIC
COMPUTE METRIC_NAME/A8 = 'METRIC 3';

BY SERVICE
BY NAME

ON TABLE HOLD AS HOLD3
END
-RUN

TABLE FILE HOLD1
SUM
METRIC


BY SERVICE
BY NAME
BY METRIC_NAME AS ''

ACROSS MONTH

MORE
FILE HOLD2

MORE
FILE HOLD3
END
-RUN

quote:

it didn't make any difference

Hard to believe that it made NO difference at all -

SET PAGE = NOLEAD
FILEDEF HFUNG1 DISK HFUNG1.FTM
-RUN
EX -LINES * EDAPUT MASTER,HFUNG1,CF,MEM,FILENAME=HFUNG1, SUFFIX=COM,$
SEGNAME=ONE, SEGTYPE=S1 ,$
  FIELD=SERVICE,       ,A15   ,A15    , TITLE='Service', $
  FIELD=NAME,          ,A25   ,A25    , TITLE='Employee', $
  FIELD=MONTH,         ,A3    ,A3     , TITLE='Fscl Month', $
  FIELD=ACCTNO         ,I9    ,I9     , TITLE='Account Number', $
  FIELD=DAYS           ,I9    ,I9     , TITLE='Days', $
  FIELD=LOS,           ,I9    ,I9     , TITLE='Acute LOS', $
EDAPUT*
-WRITE HFUNG1 General,John,Jan,1,3,1,$
-WRITE HFUNG1 General,John,Jan,2,3,1,$
-WRITE HFUNG1 General,John,Jan,3,3,1,$
-WRITE HFUNG1 General,John,Jan,4,3,1,$
-WRITE HFUNG1 General,John,Jan,5,3,1,$
-WRITE HFUNG1 General,John,Jan,6,3,1,$
-WRITE HFUNG1 General,John,Jan,7,3,1,$
-WRITE HFUNG1 General,John,Jan,8,3,1,$
-WRITE HFUNG1 General,John,Jan,9,3,1,$
-WRITE HFUNG1 General,John,Jan,10,3,1,$
-WRITE HFUNG1 General,John,Jan,11,3,1,$
-WRITE HFUNG1 General,John,Feb,1,3,1,$
-WRITE HFUNG1 General,John,Feb,2,3,1,$
-WRITE HFUNG1 General,John,Feb,3,3,1,$
-WRITE HFUNG1 General,John,Feb,6,3,1,$
-WRITE HFUNG1 General,John,Feb,7,3,1,$
-WRITE HFUNG1 General,John,Feb,8,3,1,$
-WRITE HFUNG1 General,John,Feb,9,3,1,$
-WRITE HFUNG1 General,John,Feb,10,3,1,$
-WRITE HFUNG1 General,John,Feb,11,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,1,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,2,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,3,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,5,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,6,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,7,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,8,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,9,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,10,3,1,$
-WRITE HFUNG1 General,Sarah,Jan,11,3,1,$
-WRITE HFUNG1 General,Sarah,Feb,1,3,1,$
-WRITE HFUNG1 General,Sarah,Feb,6,3,1,$
-WRITE HFUNG1 General,Sarah,Feb,7,3,1,$
-WRITE HFUNG1 General,Sarah,Feb,8,3,1,$
-WRITE HFUNG1 General,Sarah,Feb,9,3,1,$
-WRITE HFUNG1 General,Sarah,Feb,10,3,1,$
-WRITE HFUNG1 General,Sarah,Feb,11,3,1,$

TABLE FILE HFUNG1
  SUM CNT.ACCTNO AS 'Cases' 
      DAYS       AS 'Days'  
      LOS        AS 'Acute LOS'
   BY SERVICE
   BY NAME
ACROSS MONTH AS '' COLUMNS 'Jan' AND 'Feb'
END

TABLE FILE HFUNG1
  SUM CNT.ACCTNO AS 'Cases' OVER
      DAYS       AS 'Days'  OVER
      LOS        AS 'Acute LOS'
   BY SERVICE
   BY NAME
ACROSS MONTH AS '' COLUMNS 'Jan' AND 'Feb'
END

T

Seeing as you got a FOC016, you probably want to add SET FIELDNAME = NOTRUNC to your profile to disable the shortest unique truncation feature.

In our experience, more often than not, that unique truncation logic will cause your typos to be interpreted as a truncation of some existing field. Since those typos usually are only minor typos, you tend to end up with a field that is very similar to the field you meant, but not necessarily that field (for starters, it will probably have a longer name that only starts the same).

It gets even more dangerous when there are database changes and the field that you were using ceases to exist and now another field which' name starts with the same string gets used.

You might as well tie your foot to a shotgun.

Rather than that, we prefer to get an error during development of such reports that the field we typed does not exist.

The problems with your last attempt is that you have not mimicked Francis' example in a few ways, and you would need to correct those omissions to get anywhere near the results you want.

Firstly, Francis does not use ACROSS ... AS MONTH within the first hold file.

Secondly, your final report uses MONTH from your interim files but you are not including MONTH within them, so how do you expect to be able to use that column name?

Finally, if the first pass of your data (ON TABLE HOLD AS HOLD0) produces output, then using OVER will give you what you want without having to parse data more than once!

All the potential solutions given will work if you follow their example (Dannys solution is particularly imaginative), but your simplest solution is to use OVER!


T

Ok Let me look up FML and see if I can figure out how to properly use this OVER function!

OVER, in this context, has nothing to do with FML and I have given you an example of its use above.

Rather than try and fit what the example is doing, just cut and paste the code into a new focexec and run it. It doesn't need any data - that is built for you in the code. The results show with and without OVER.

Once you see the two outputs then you can take a step back and understand what the difference achieves.


T

This message has been edited. Last edited by: Tony A,