Alright first time poster here but I've been lurking around here for a while and I haven't found anything on what I think should be a simple solution.

So I basically just need to get my Compound Bar Graphs to display 0's for any month with no data in the past year. I've seen a few options such as manually naming the across columns but my graphs are set to pull from the last year to today's date, so it will always shift.

-SET &PREVM = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'M',-12),'YYMD','I8YYMD');
-SET &EOM1 = DATECVT(&PREVM,'I8YYMD','YYMD');
-SET &EOM2 = DATEMOV(&EOM1, 'EOM');
-SET &EOMR = DATECVT(&EOM2, 'YYMD','A8YYMD');

COMPOUND LAYOUT PCHOLD FORMAT PDF
UNITS=IN, $
SECTION=section1, LAYOUT=ON, METADATA='0.5^0.5^0.5^0.5', MERGE=OFF, ORIENTATION=LANDSCAPE, PAGESIZE=C,  $
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1, BOTTOMMARGIN=0.5, TOPMARGIN=0.5,
METADATA='BOTTOMMARGIN=0.5,TOPMARGIN=0.5,LEFTMARGIN=5,RIGHTMARGIN=5,', $
COMPONENT='report1',  TEXT='report1',  TOC-LEVEL=2, POSITION=(1.0 *), DIMENSION=(* *),
METADATA='Z-INDEX: 100; LEFT:  3.200in; WIDTH: 10.000in; LENGTH: 10.000in; POSITION: absolute; TOP:  9.500in; HEIGHT: 2.000in', $
COMPONENT='graph1',  TEXT='graph1',  TOC-LEVEL=2, POSITION=( 1.00  5.500), DIMENSION=(9.000  5.000), 
METADATA='Z-INDEX: 100; LEFT:  3.200in; WIDTH: 10.000in; LENGTH: 10.000in; POSITION: absolute; TOP:  9.500in; HEIGHT: 2.000in', $
COMPONENT='graph2',  TEXT='graph2',  TOC-LEVEL=2, POSITION=( 11.00  5.500), DIMENSION=(9.000  5.000), 
METADATA='Z-INDEX: 100; LEFT:  3.200in; WIDTH: 10.000in; LENGTH: 10.000in; POSITION: absolute; TOP:  9.500in; HEIGHT: 2.000in', $

DEFINE FILE EVENTS
DATE/YYM= EVENTS.DATE;
END

SET COMPONENT='report1'
TABLE FILE EVENTS
SUM CNT.EVENTS_PRIMARY_KEY' AS 'TOTAL EVENTS;
BY LOCATION
ACROSS DATE
WHERE REGION EQ 'NE'; 
WHERE DATE GE &EOMR;
ON TABLE SET PAGE-NUM OFF 
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT PDF 
ON TABLE SET HTMLCSS ON
END

SET COMPONENT='graph1'
GRAPH FILE EVENTS
SUM CNT.EVENT_PRIMARY_KEY AS 'Total Events' 
ACROSS DATE 
WHERE LOCATION EQ 'LOCATION1';
WHERE WHERE DATE GE &EOMR;
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB ON
ON GRAPH SET 3D OFF
ON GRAPH SET VZERO ON
ON GRAPH SET GRID ON
ON GRAPH SET GRWIDTH 1
ON TABLE PCHOLD FORMAT PDF 
ON GRAPH SET GRAPHSTYLE *
setzerovaluedatatextdisplay(true);

SET COMPONENT='graph2'
GRAPH FILE EVENTS
SUM CNT.EVENT_PRIMARY_KEY AS 'Total Events' 
ACROSS DATE 
WHERE LOCATION EQ 'LOCATION2';
WHERE WHERE DATE GE &EOMR;
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB ON
ON GRAPH SET 3D OFF
ON GRAPH SET VZERO ON
ON GRAPH SET GRID ON
ON GRAPH SET GRWIDTH 1
ON TABLE PCHOLD FORMAT PDF 
ON GRAPH SET GRAPHSTYLE *
setzerovaluedatatextdisplay(true);

I took out the styling code since I figured it was irrelevant but left the set zero value because I've seen that posted as a solution thought it didn't work for me.

Strange thing is when I set up a BY statment that encompasses all the LOCATIONS in a REGION all of the 0's are accounted for and display on every graph.

Such as:

-SET &PREVM = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'M',-12),'YYMD','I8YYMD');
-SET &EOM1 = DATECVT(&PREVM,'I8YYMD','YYMD');
-SET &EOM2 = DATEMOV(&EOM1, 'EOM');
-SET &EOMR = DATECVT(&EOM2, 'YYMD','A8YYMD');

GRAPH FILE EVENTS
SUM CNT.EVENT_PRIMARY_KEY AS 'Total Events' 
ACROSS DATE 
BY LOCATION
WHERE REGION EQ 'REGION1';
WHERE WHERE DATE GE &EOMR;
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB ON
ON GRAPH SET 3D OFF
ON GRAPH SET VZERO ON
ON GRAPH SET GRID ON
ON GRAPH SET GRWIDTH 1
ON TABLE PCHOLD FORMAT PDF 
ON GRAPH SET GRAPHSTYLE *
setzerovaluedatatextdisplay(true);

I'd love to just take this code portion and be done, but in PDF format all of those graphs show up on separate pages. To make the compound report easier to read and print the customer would like the report on top with 2 graphs side by side below it for as many pages as necessary. The only way I could do that was with the big compound report layout in the first block of code.

I've tried as many different places to set up SET EMPTYREPORT = ON as I can think of all with VZERO on and setzerovaluedatatextdisplay(true).
Is there any way to set up ACROSS DATE COLUMNS '&EOMR' AND '&EOMR + 1' AND '&EOMR + 2' etc., or is there any way to style the graphs created in my second code portion into the above layout?

Thanks! I'm pretty sure I set up my signature but if I missed it I'm on WF 7.6.11 and I need a PDF output on this one.

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

Hi Mo Andy,

Welcome to Focal Point!

I've asked two of our graph experts to review this topic. As there isn't a straightforward answer, they've requested that you open a case on InfoAssist Online so that we can do some testing with your actual files.

Thanks and regards,

Kathryn

Thanks Kathryn!

Sorry I asked some of our local experts on what to do since the higher ups weren't too keen on letting non-state workers handle the data (it's student data).

This had them kinda stumped as well. But it seems a Cartesian join was what we needed. I'll post what they sent me here.

DEFINE FILE EVENTS
Date/YYM=Date;
END

-** Get all possible dates and hold ***
TABLE FILE EVENTS
PRINT DST.Date
WHERE REGION EQ 'NE'; 
WHERE SW_DELETE EQ 'N';
WHERE STATUS EQ 'RA';
WHERE Date GE &EOMR;
ON TABLE HOLD AS ALLDATES
END
-RUN

-** Get all possible facilities and hold ***
TABLE FILE EVENTS
PRINT DST.LOCATION
      COMPUTE KEY/A1=' ';
WHERE REGION EQ 'NE'; 
WHERE SW_DELETE EQ 'N';
WHERE STATUS EQ 'RA';
WHERE Date GE &EOMR;
ON TABLE HOLD AS ALLFACS FORMAT FOCUS INDEX KEY
END
-RUN

-** Cartesian join to get all facilites with a record for each possible date ***
JOIN KEY WITH Date IN ALLDATES TO ALL KEY IN ALLFACS AS JFAC

DEFINE FILE ALLDATES
KEY/A1 WITH Date='';
END
 
TABLE FILE ALLDATES
SUM KEY
BY Date 
BY CO_REPORTING_LOCATION
ON TABLE HOLD AS ALLFACDATES
END
-RUN

So now my graphs look like this.

SET COMPONENT='graph1'
GRAPH FILE ALLFACDATES
SUM Primary_Key AS 'Total Events (Reports)' 
ACROSS Date
HEADING
"<Location"
WHERE CO_REPORTING_LOCATION EQ 'CA';
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB ON
ON GRAPH SET 3D OFF
ON GRAPH SET VZERO ON
ON GRAPH SET GRID ON
ON GRAPH SET GRWIDTH 1
ON TABLE PCHOLD FORMAT PDF 
ON GRAPH SET GRAPHSTYLE *
  

So now every month will show up in my vertical bar graph even if there are no incidents (data) for that month. And I can keep the format of multiple graphs per page.

I wish I could explain this a little better but honestly I'm still trying to decipher it. But if there are any questions let me know and I'll see if I can ask our expert for some more detailed explanation.

Hi Mo Andy,

I once had a requisite sort of like yours: given a month, show the last 13 months even if they have no data.

What I did was create a .fex that, given a &MONTH parameter, would automatically create a &MONTH1, &MONTH2, etc. and -INCLUDE that .fex. Then I would use ROWS OVER &MONTH OVER &MONTH1 OVER &MONTH2 and so on.

Thanks diogopc!

I have zero experience with creating a .fex, but I appreciate the advice! I was kinda thinking it would have to be something along the lines of manually naming the months. If I get some free time I may try your solution.

Thanks again!