Hi,

Iam a newbie in creating graphs/charts and Iam trying to create a Bar chart using my sql table.
Iam using this code below for making the hold file permanent and to create a graph using that hold file:

-SET &ECHO='ALL';
-INCLUDE IBFS:/WFC/Repository/Myfolder/Ref_folder/oracle_conn.fex
-RUN

-DEFAULTH &CNT_IF_3_65 = '';
-DEFAULTH &CNT_IF_2_65 = '';
-DEFAULTH &CNT_IF_1_65 = '';
-DEFAULTH &CNT_IF_0_65 = '';
-DEFAULTH &CNT_IF_3_80 = '';
-DEFAULTH &CNT_IF_2_80 = '';
-DEFAULTH &CNT_IF_1_80 = '';
-DEFAULTH &CNT_IF_0_80 = '';
-DEFAULTH &CNT_IF_3_90 = '';
-DEFAULTH &CNT_IF_2_90 = '';
-DEFAULTH &CNT_IF_1_90 = '';
-DEFAULTH &CNT_IF_0_90 = '';
-DEFAULTH &CNT_IF_YTD_90 = '';
-DEFAULTH &HEADING1 = '';
-DEFAULTH &HEADING2 = '';
-DEFAULTH &HEADING3 = '';
 
SQL SQLORA PREPARE REPORT1 FOR
SELECT * FROM CONN1.TABLE_REF;
END
 
APP HOLD BASEAPP
-RUN
TABLE FILE REPORT1
PRINT
-*DIVISION
REGION AS 'Region'
-*STATE
DATA
CNT_IF_3_65
CNT_IF_2_65
CNT_IF_1_65
CNT_IF_0_65
CNT_IF_3_80
CNT_IF_2_80
CNT_IF_1_80
CNT_IF_0_80
CNT_IF_3_90
CNT_IF_2_90
CNT_IF_1_90
CNT_IF_0_90
CNT_IF_YTD_90
WHERE DIVISION NE '' OR '??'
WHERE STATE LIKE '%TOTAL%'
WHERE REGION NOT LIKE '%EAST' AND REGION NOT LIKE '%WEST'
WHERE DATA EQ '% Congested w/o PE Total' OR '% Congested w/o PE Grand Total'
ON TABLE HOLD AS HOLD_ACCESS FORMAT FOCUS
END
-RUN
  

Later after creating the file in Baseapp,Iam doing something like this to see how the data looks like:

  TABLE FILE HOLD_ACCESS
PRINT
REGION
DATA
CNT_IF_3_65
CNT_IF_2_65
CNT_IF_1_65
CNT_IF_0_65
CNT_IF_3_80
CNT_IF_2_80
CNT_IF_1_80
CNT_IF_0_80
CNT_IF_3_90
CNT_IF_2_90
CNT_IF_1_90
CNT_IF_0_90
CNT_IF_YTD_90
END
-RUN
-EXIT

 Output of the above code:
PAGE 1
Region		Data			CNT_IF_3_65	CNT_IF_2_65	CNT_IF_1_65	CNT_IF_0_65	CNT_IF_3_80	CNT_IF_2_80	CNT_IF_1_80	CNT_IF_0_80	CNT_IF_3_90	CNT_IF_2_90	CNT_IF_1_90	CNT_IF_0_90	CNT_IF_YTD_90
E	    % Congested w/o PE Total	  3.04%		3.06%		2.99%		2.86%		1.07%		1.13%		1.02%		1.00%		0.52%		0.49%		0.46%		0.46%		0.49%
MW	    % Congested w/o PE Total	  4.90%		4.86%		4.48%		4.53%		2.27%		2.26%		1.96%		2.08%		1.37%		1.26%		1.12%		1.16%		1.28%
MTW	    % Congested w/o PE Total	  2.60%		2.72%		2.52%		2.27%		1.55%		1.63%		1.48%		1.32%		0.95%		0.99%		0.87%		0.74%		0.92%
W	    % Congested w/o PE Total	  4.10%		4.33%		4.11%		4.06%		2.30%		2.41%		2.11%		2.17%		1.32%		1.38%		1.20%		1.22%		1.30%
TOTAL CTL   % Congested w/o PE Grand Total3.64%         3.73%		3.51%		3.39%		1.81%		1.88%		1.66%		1.65%		1.05%		1.05%		0.92%		0.91%		1.01% 

I want to graph which is like this : Image has been uploaded in tinypic.com and you can access via :

OR
Direct link for layouts
http://i68.tinypic.com/30ct9og.jpg

I tried creating the graph giving the Region as X axis and the other fields such as CNT_IF_3_65,CNT_IF_2_65,CNT_IF_1_65 and CNT_IF_0_65 as the Y axis along with the data field and also viceversa but Iam not able to get the graph as shown in the image.
Please help.

Thanks a lot in advance.

Regards,
IP

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

Normally:
Dimension = BY field
Measure = SUM field

Your Dim = Region
Your Measure = all other 13 fields
Your "Data" field is not significant unless you add it in the sub-title. And only "% Congested w/o PE" seems significant to me as a sub-title.

You'll end up with 5 sections of 13 bar each.

You don't have any "Date/Time" field, so cannot look like your sample image.

Hi Martin,

Basically,Iam doing something like this as shown below to get the date values for the 13 fields :

 TABLE FILE HOLD_ACCESS
PRINT
CNT_IF_3_65
CNT_IF_2_65
CNT_IF_1_65
CNT_IF_0_65
CNT_IF_3_80
CNT_IF_2_80
CNT_IF_1_80
CNT_IF_0_80
CNT_IF_3_90
CNT_IF_2_90
CNT_IF_1_90
CNT_IF_0_90
CNT_IF_YTD_90
WHERE REPORT_TAB EQ ''
ON TABLE HOLD AS MYFILE
END
-RUN

-READFILE MYFILE
-RUN
-TYPE &CNT_IF_3_65
-TYPE &CNT_IF_2_65
-TYPE &CNT_IF_1_65
-TYPE &CNT_IF_0_65
-TYPE &CNT_IF_3_80
-TYPE &CNT_IF_2_80
-TYPE &CNT_IF_1_80
-TYPE &CNT_IF_0_80
-TYPE &CNT_IF_3_90
-TYPE &CNT_IF_2_90
-TYPE &CNT_IF_1_90
-TYPE &CNT_IF_0_90
-TYPE &CNT_IF_YTD_90

Output of these 13 fields(CNT_IF_3_65,CNT_IF_2_65,CNT_IF_1_65,CNT_IF_0_65 etc) would be :
27-JAN-17 10-FEB-17 24-FEB-17 10-MAR-17 27-JAN-17 10-FEB-17 24-FEB-17 10-MAR-17 27-JAN-17 10-FEB-17 24-FEB-17 10-MAR-17 YTD AVG 2017 

TABLE FILE HOLD_ACCESS
PRINT
REGION
DATA
CNT_IF_3_65 AS '&CNT_IF_3_65'
CNT_IF_2_65 AS '&CNT_IF_2_65'
CNT_IF_1_65 AS '&CNT_IF_1_65'
CNT_IF_0_65 AS '&CNT_IF_0_65'
CNT_IF_3_80 AS '&CNT_IF_3_80'
CNT_IF_2_80 AS '&CNT_IF_2_80'
CNT_IF_1_80 AS '&CNT_IF_1_80'
CNT_IF_0_80 AS '&CNT_IF_0_80'
CNT_IF_3_90 AS '&CNT_IF_3_90'
CNT_IF_2_90 AS '&CNT_IF_2_90'
CNT_IF_1_90 AS '&CNT_IF_1_90'
CNT_IF_0_90 AS '&CNT_IF_0_90'
CNT_IF_YTD_90 AS '&CNT_IF_YTD_90'
END
-RUN
-EXIT 

Now basically I need my Measure as :to show the percentages values (3.04%,4.90%,2.60%,4.10% etc and others) on the Y axis and the Dates(13 fields) along with the region on the X axis but Iam not able to proceed further on how exactly to get the graph as mentioned in the image.

Could anyone help please?

Thanks a lot in advance.

Regards,
IP

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

I'm little confused.
In your first post you mentioned:

TABLE FILE HOLD_ACCESS
PRINT
REGION
DATA
CNT_IF_3_65
CNT_IF_2_65
CNT_IF_1_65
CNT_IF_0_65
CNT_IF_3_80
CNT_IF_2_80
CNT_IF_1_80
CNT_IF_0_80
CNT_IF_3_90
CNT_IF_2_90
CNT_IF_1_90
CNT_IF_0_90
CNT_IF_YTD_90
END
-RUN
-EXIT

Output of the above code:
PAGE 1
Region		Data			CNT_IF_3_65	CNT_IF_2_65	CNT_IF_1_65	CNT_IF_0_65	CNT_IF_3_80	CNT_IF_2_80	CNT_IF_1_80	CNT_IF_0_80	CNT_IF_3_90	CNT_IF_2_90	CNT_IF_1_90	CNT_IF_0_90	CNT_IF_YTD_90
E	    % Congested w/o PE Total	  3.04%		3.06%		2.99%		2.86%		1.07%		1.13%		1.02%		1.00%		0.52%		0.49%		0.46%		0.46%		0.49%
MW	    % Congested w/o PE Total	  4.90%		4.86%		4.48%		4.53%		2.27%		2.26%		1.96%		2.08%		1.37%		1.26%		1.12%		1.16%		1.28%
MTW	    % Congested w/o PE Total	  2.60%		2.72%		2.52%		2.27%		1.55%		1.63%		1.48%		1.32%		0.95%		0.99%		0.87%		0.74%		0.92%
W	    % Congested w/o PE Total	  4.10%		4.33%		4.11%		4.06%		2.30%		2.41%		2.11%		2.17%		1.32%		1.38%		1.20%		1.22%		1.30%
TOTAL CTL   % Congested w/o PE Grand Total3.64%         3.73%		3.51%		3.39%		1.81%		1.88%		1.66%		1.65%		1.05%		1.05%		0.92%		0.91%		1.01%

So from this I understand that each CNT_IF field contains a percentage.
But now you mentioned:

 TABLE FILE HOLD_ACCESS
PRINT
CNT_IF_3_65
CNT_IF_2_65
CNT_IF_1_65
CNT_IF_0_65
CNT_IF_3_80
CNT_IF_2_80
CNT_IF_1_80
CNT_IF_0_80
CNT_IF_3_90
CNT_IF_2_90
CNT_IF_1_90
CNT_IF_0_90
CNT_IF_YTD_90
WHERE REPORT_TAB EQ ''
ON TABLE HOLD AS MYFILE
END
-RUN

-READFILE MYFILE
-RUN
-TYPE &CNT_IF_3_65
-TYPE &CNT_IF_2_65
-TYPE &CNT_IF_1_65
-TYPE &CNT_IF_0_65
-TYPE &CNT_IF_3_80
-TYPE &CNT_IF_2_80
-TYPE &CNT_IF_1_80
-TYPE &CNT_IF_0_80
-TYPE &CNT_IF_3_90
-TYPE &CNT_IF_2_90
-TYPE &CNT_IF_1_90
-TYPE &CNT_IF_0_90
-TYPE &CNT_IF_YTD_90

Output of these 13 fields(CNT_IF_3_65,CNT_IF_2_65,CNT_IF_1_65,CNT_IF_0_65 etc) would be :
27-JAN-17 10-FEB-17 24-FEB-17 10-MAR-17 27-JAN-17 10-FEB-17 24-FEB-17 10-MAR-17 27-JAN-17 10-FEB-17 24-FEB-17 10-MAR-17 YTD AVG 2017 

What does CNT_IF fields contains exactly, a percentage or a date ?

If they are dates, you need to "convert" them to become 1 BY field you also need your measure (I don't know now which one is going to be if CNT_IF field are the dates) then your graph will be

GRAPH FILE ABC
SUM "measure"
BY unique_CNT_IF_dateField
BY REGION
...
END

What I can suggest is to first try to have the data as a "regular" report (not a graph). From there it's going to be much easy to create the graph.
Because as I can see, your data is not properly defined for what you are trying to do. You have 13 date fields where it should be 1 field with multiple values.

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

Hi,

When I do this :
SQL SQLORA PREPARE REPORT1 FOR
SELECT * FROM CONN1.TABLE_REF;
END

This is the output which I get :

REPORT_TAB 	DIVISION 	REGION 	STATE 	DATA 	CNT_IF_3_65 	CNT_IF_2_65 	CNT_IF_1_65 	CNT_IF_0_65 	CNT_IF_3_80 	CNT_IF_2_80 	CNT_IF_1_80 CNT_IF_0_80 	CNT_IF_3_90 	CNT_IF_2_90 	CNT_IF_1_90 	CNT_IF_0_90 	CNT_IF_YTD_90 
. 		. 		. 	. 	Date 	27-JAN-17 	10-FEB-17 	24-FEB-17 	10-MAR-17 	27-JAN-17 	10-FEB-17 	24-FEB-17 	10-MAR-17 	27-JAN-17 	10-FEB-17 	24-FEB-17 	10-MAR-17 	YTD AVG 2017	
ACCESS 		EAST 		E 	AL 	% Congested w/o PE 5.16% 5.67% 5.62% 5.28% 1.93% 2.33% 2.04% 2.10% 1.15% 1.09% 0.95% 1.08% 1.09% 
  

and other rows for few other regions.

So,to rename the CNT_IF fields with the date values,Iam doing these steps in the code:

 TABLE FILE HOLD_ACCESS
PRINT
CNT_IF_3_65
CNT_IF_2_65
CNT_IF_1_65
CNT_IF_0_65
CNT_IF_3_80
CNT_IF_2_80
CNT_IF_1_80
CNT_IF_0_80
CNT_IF_3_90
CNT_IF_2_90
CNT_IF_1_90
CNT_IF_0_90
CNT_IF_YTD_90
WHERE REPORT_TAB EQ ''
ON TABLE HOLD AS MYFILE
END
-RUN

-READFILE MYFILE
-RUN
-TYPE &CNT_IF_3_65
-TYPE &CNT_IF_2_65
-TYPE &CNT_IF_1_65
-TYPE &CNT_IF_0_65
-TYPE &CNT_IF_3_80
-TYPE &CNT_IF_2_80
-TYPE &CNT_IF_1_80
-TYPE &CNT_IF_0_80
-TYPE &CNT_IF_3_90
-TYPE &CNT_IF_2_90
-TYPE &CNT_IF_1_90
-TYPE &CNT_IF_0_90
-TYPE &CNT_IF_YTD_90
 

But actually the CNT_IF fields contains the percentages.

Hope Iam clear now Martin.
Please let me know.

Regards,
IP

Ho ! That's getting worst than I thought

Your table is a "multi value type container", burk which also seems to be "pre-formatted" for another purpose.

What I think you have to do is (one from many solutions) :
1- Extract the date record and save info in variables as you did.

2- Extract the other records, but you need to "pivot" the 13 CNT_IF fields from individual column to one measure column based on new BY date field from the variables and add a new MeasureType field.
Since your data seems to be 4 times 3 types of value.

3- You will also need to change the measure field format since you cannot create a graph where the Y axis is an alpha. It needs to be a numeric value.

You need to create something such as:

DIVISION REGION STATE PERIOD       MeasType  Meas
EAST     E      AL    27-JAN-17      1       5.16
EAST     E      AL    27-JAN-17      2       5.67
EAST     E      AL    27-JAN-17      3       5.62
EAST     E      AL    10-FEB-17      1       5.28
EAST     E      AL    10-FEB-17      2       1.93
EAST     E      AL    10-FEB-17      3       2.33
EAST     E      AL    24-FEB-17      1       2.04
EAST     E      AL    24-FEB-17      3       2.10
EAST     E      AL    24-FEB-17      4       1.15
EAST     E      AL    10-MAR-17      1       1.09
EAST     E      AL    10-MAR-17      2       0.95
EAST     E      AL    10-MAR-17      3       1.08
EAST     E      AL    YTD AVG 2017   1       1.09
...

Be aware that the MeasType field I don't know what it could be. You know what CNT_IF_3_65, CNT_IF_3_80 and CNT_IF_3_90 that all have 27-JAN-17 are. Same pattern for other dates.

In conclusion, I think that your greatest challenge is to have your data properly defined.
All this can probably be accomplish partially, totally or combining coding, master file, MacGyver and/or other technics.

Hi Martin,

Yes it is a bit complicated one
Can you please help me with a sample car file for the below one:
2- Extract the other records, but you need to "pivot" the 13 CNT_IF fields from individual column to one measure column based on new BY date field from the variables and add a new MeasureType field.
Since your data seems to be 4 times 3 types of value.

I know that you have really helped me so far but a sample using a CAR FILE would help me a lot.Thanks a lot in advance.
Please help!

Regards,
IP

Go see your other post
How to convert the multiple fields from individual column to a single column

IP, please change your post subject by adding [SOLVED] at the beginning of it.