Jump to content
Software FX Community

Combining CrossTab with Dual Y Axes


MDS

Recommended Posts

I have a need to create a chart with left and right y axes using a crosstab (without using panes).  Example fields are: CategoryField1, DataField1, ValueField1, and ValueField2. There are distinct values in the CategoryField1 field.  ValueField1 is the left Y axis.  ValueField2 is the right Y axis.  DataField1 is the X Axis (e.g. a date).

 

Does anyone know how to do this in code?

Link to comment
Share on other sites

After you bind the crosstab data to the chart, you add the additional axis and attach it to the corresponding series as follows:

 ...

SoftwareFX.ChartFX.

SeriesAttributes series = chart1.Series[0];SoftwareFX.ChartFX.Axis axis = chart1.Axis[1];

axis.Visible =

true;

series.YAxis = (SoftwareFX.ChartFX.

YAxis)1;

 ...

Link to comment
Share on other sites

I'm getting an overflow error.  Any idea why?

Here's the code:

chart1.ClearData(SoftwareFX.ChartFX.

ClearDataFlag.AllData);string tableName = "TestTable";

DataTable dataTable = DataSet.Tables[tableName];DataTableProvider dataTableProvider = new DataTableProvider(dataTable);CrosstabDataProvider cfxCT = new CrosstabDataProvider();

cfxCT.DataSource = dataTableProvider;

cfxCT.DataType[0] =

CrosstabDataType.RowHeading; // Year Field (Integer)

cfxCT.DataType[1] =

CrosstabDataType.ColumnHeading; // Category Grouping - 3 possible values (GAS, OIL, COAL)

cfxCT.DataType[2] =

CrosstabDataType.Value; // Y Axis Left Value (Integer)

// cfxCT.DataType[3] = CrosstabDataType.Value; // Y Axis Right Value (Double) ... Error occurs whether or not this line is commented

chart1.DataSource = cfxCT;

SoftwareFX.ChartFX.SeriesAttributes series = chart1.Series[0];

SoftwareFX.ChartFX.

Axis axis = chart1.Axis[1];axis.Visible = true;

series.YAxis = (SoftwareFX.ChartFX.

YAxis)1;

chart1.Refresh();

Link to comment
Share on other sites

I was able to replicate your issue. It seems that if you let the chart automatically set the min, max, and step of the secondary y-axis, the exception is thrown; however, manually setting these properties, generates the chart as expected. This seems to be a bug; in the meantime, you can set those values manually, as follows:

SoftwareFX.ChartFX.Axis axis = chart1.Axis[1];

axis.Visible =

true;axis.Min = 4000;

axis.Step = 5000;

axis.Max = 100000;

Please adjust the values provided to reflect the values you are passing to the chart.

Link to comment
Share on other sites

Thanks, maximop.  The error is resolved, but my initial question remains unanswered.  Here's what I understand...

If you create a crosstab then the number of series are equivalent to the number of distinct values in the field assigned as the CrosstabDataType.ColumnHeading enum value.  In my case, "oil", "gas", and "coal" in the CategoryField will create 3 series.

 Using the code above enables me to create a right Y axis, but the chart still only displays the bars for the left.

I guess what I'm after is either 6 series (3 bars for the left axis and 3 bars for the right axis) or a 3D chart.

Does this make more sense?

Link to comment
Share on other sites

... yet another option is to combine the crosstabdataprovider with multiple panes, which I originally did not want to do.  However, I am flexible to the multiple pane idea.  Unfortunately, the crosstabdataprovider only allows one value field, so there seems to be no way to create multiple panes with the crosstabdata provider.

 Has anyone been able to create multiple panes using different values from a crosstabdata provider?

Link to comment
Share on other sites

Ok. I understand better now. The problem is that you only have three columns (Oil, Gas, Coal) and that is why you are seeing only three series' being plotted. Please note that ChartFX interprets the columns as the series's to be plotted and the rows as the XValues; in your case, the dates you are seeing along the x-axis are the XValues used by the Crosstab Data Provider.

If you have three columns and six rows, as I think you have, you will plot three series' with six data points; you may want to try using the dates as the column headers and the categories as the rows, this way you will have six columns and three rows.

Link to comment
Share on other sites

Actually, I have 10 columns in the original DataTable.  I am setting all of them to NotUsed initially.

There are 3 rows ("oil", "gas", and "coal") for every year.  There are 7 years worth of data, so I have 21 rows in the original DataTable.

Column1 (first field in the DataTable) is an integer representing years - e.g. 2003, 2004, 2005, 2006, etc.  I want these dates to make up the X Axis.

Column2 (second field in the DataTable) is a string representing a category.  The possible values are "oil", "gas", and "coal."  When creating a CrossTabDataProvider, these represent the series.

Column 3 (third field in the DataTable) is an integer representing a value.  This needs to be a dependent (Y Axis) value.

Column 4 (fourth field in the DataTable is a double representing yet another value (the remaining fields in the DataTable are either doubles or integers).  This also needs to be a dependent (Y Axis value) displayed simultaneously with the values in Column 3.

I set Column1/DataType[0] to CrosstabDataType.RowHeading.

I set Column2/DataType[1] to CrosstabDataType.ColumnHeading.

I set Column3/DataType[2] to CrosstabDataType.Value (only 1 allowed in the CrosstabDataProvider).

I would like to set Column4/DataType[3] to CrosstabDataType.Value also.

 

If I set "the dates as the column headers and the categories as the rows, this way you will have six columns and three rows," then how do I get my second set of values.  Is it true that the crosstab only supports the intersection of a row and a column for a single value?  Is there a way to accomplish what I'm trying to do with a normal DataTableProvider.

Link to comment
Share on other sites

The Crosstab Data Provider converts data from tabular format to columnar format so it can be plotted by the chart. Since you are using the categories as the series', then the chart understands that there are only three columns to be plotted. Look at the following sample data:

Crosstab2<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Field1

Field2

Field3

Field4

Oil

2004

4524

4750

Gas

2004

4760

5000

Coal

2004

4850

4995

Oil

2005

1500

1700

Gas

2005

1350

1700

Coal

2005

1600

1806

Oil

2006

2035

2157

Gas

2006

2175

2985

Coal

2006

1986

2300

Oil

2007

4543

2343

Gas

2007

2343

2546

Coal

2007

4355

4324

Oil

2008

4353

9374

Gas

2008

2435

5346

Coal

2008

5326

5656

Oil

2009

5634

1245

Gas

2009

4325

8873

Coal

2009

2363

2351

Oil

2010

4352

8912

Gas

2010

4325

2435

Coal

2010

2423

3244

If you set the first column as the column header, you will plot three series with seven dates being interpreted as the xvalues. The same way, if you use the second column as the column header, you will plot seven series. Now, you can only plot one column of data per series; with the exception of using floating bars which use an initial value and an ending value. That is why I included a fourth column above.

In your case, you want to plot more than one column of data per series; that unfortunately, is not possible. If you wish to use two columns of data, you must specify which one will be the initial value; otherwise, the chart will plot the last column of values passed to the chart. To plot initial values, you can do the following:

cfxCT.DataType[0] = CrosstabDataType.ColumnHeading;

cfxCT.DataType[1] =

CrosstabDataType.RowHeading;cfxCT.DataType[2] = CrosstabDataType.IniValue;

cfxCT.DataType[3] =

CrosstabDataType.Value;

 Hope this clears any misunderstandings.

Link to comment
Share on other sites

The thing is that using the crosstab is not the problem, the problem is that you are plotting three columns and expecting six. You will either have to invert the data and have the dates as the columns, or add additional categories which is probably not what you are looking to do in the first place.

 Again, the main point is that you need to pass six columns of data to the chart so you can see six series'.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...