Hi Everyone,

I am trying to create a report whose output looks like this.

  

			 |Month1	    |Month2		      |	Month3		|| Quarter	        |BUDUNITS|BUDDOLLARS|			
----------------------------------------------------------------------------------------||--------              |        |          |
			 |10/01/2018	|   |11/01/2018	     |        |12/01/2018	||		        |        |          |
                         |              |   |                |        |          	||	                |        |          |
Seq_no|     Region|      |ID|Sales|Profit|  |ID|Sales|Profit |        | ID|Sales|Profit ||Tot.Sales|Tot.Profit	|        |          |

I am trying to do an across with date field and label it as month1 and month2 and month3 and another across with quarter and show total columns underneath it. Next two columns are measures which I am trying to align in same level as across columns.

This is what I have started with, any input will be helpful.

TABLE FILE GGSALES
SUM
CNT.PCD
DOLLARS
UNITS
BY SEQ_NO
BY CATEGORY
ACROSS DATE
END

Regards
BI Dev

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

Hi BI Dev,
First when sharing code and/or sample data/output please use the code tag. Last one on the ribbon

</>

It will avoid the need of all theses dashes and pipes where you are trying to align stuff

It's not yet all clear for me.

It seems that you will include only the three months from a quarter and have the value for each.
Then the quarter total
Then two other measures not related to the months and quarter
All this side by side ?

Hi MartinY,

Sorry about the previous unclear post, Yes, Quarter total for only 2 out of 3 columns, and then two other measures not related to the month and quarter.

Regards
Bi Dev

quote:

Originally posted by MartinY:
Hi BI Dev,
First when sharing code and/or sample data/output please use the code tag. Last one on the ribbon

</>

It will avoid the need of all theses dashes and pipes where you are trying to align stuff

It's not yet all clear for me.

It seems that you will include only the three months from a quarter and have the value for each.
Then the quarter total
Then two other measures not related to the months and quarter
All this side by side ?

Much more clear now.

Need to think about it...but the solution may not be simple

Not yet exactly as your sample output, but it's a start to work with

DEFINE FILE GGSALES
QTR     /Q     = DATE;
MTH     /MONTH = DATE;
END
TABLE FILE GGSALES
SUM CNT.DST.PCD/I5 AS 'PCD_CNT'
    DOLLARS
    UNITS
    BUDDOLLARS
    BUDUNITS
BY REGION
BY CATEGORY
BY QTR
BY MTH NOPRINT
BY TOTAL COMPUTE COLID   /I2  = IF QTR   EQ LAST QTR THEN COLID + 1 ELSE 1;
BY TOTAL COMPUTE BEG_MTH /A10 = FPRINT(MIN.DATE, 'I8YYMD', 'A10');
BY TOTAL COMPUTE COLTXT  /A10 = DECODE COLID (1 'Month1' 2 'Month2' 3 'Month3');
ON TABLE HOLD AS EXTDATA
END
-RUN

TABLE FILE EXTDATA
SUM BUDDOLLARS AS 'Bdg Dol'
    BUDUNITS   AS 'Bdg Units'
    DOLLARS    AS 'Qtr Dol,Total'
    UNITS      AS 'Qtr Units,Total'
BY REGION      AS ''
BY CATEGORY    AS ''
WHERE QTR EQ 'Q1';

SUM PCD_CNT    AS 'ID Count'
    DOLLARS    AS 'Dollars'
    UNITS      AS 'Units'
BY REGION      AS ''
BY CATEGORY    AS ''
BY QTR         NOPRINT

ACROSS COLID   NOPRINT
ACROSS COLTXT  AS ''
ACROSS BEG_MTH AS ''

WHERE QTR EQ 'Q1';
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN

To have the totals (Qtr & Bdg) located at right of the months, it will need another technic that needs more work and handling

Long Live The Text Editor...