Add second series to CrossTabDataProvider

[aas4mis]

I'm trying to add a series of "PlanData" to a crosstabdataprovider. Here is my code.

=====================================================================================Protected Sub Populate_Chart(ByRef sender As Chart, _ Optional ByVal Chart_FieldName As String = "Inventory $", _ Optional ByVal Chart_Legend As String = "Inventory $", _ Optional ByVal Chart_FiscalYear As String = "11")

sender.Reset()

''Get Chart TemplateDim ChartTemplate As New ChartTemplateChartTemplate = GetChartTemplate(Chart_Legend)''Set Chart AttributesSetChartAttributes(sender, ChartTemplate)

''DataDim Filter As StringFilter = "DisplayName = '" + Chart_FieldName + "' AND Fiscal_Year >= " + Chart_FiscalYearDim mySelectQuery As String = _   "with cte as ( " & _   "SELECT TOP 52 DisplayName, Fiscal_Year, FiscalWeek, Weekdata, PlanData, Calendar_Month " & _   "FROM delete_chart_data " & _   "WHERE " + Filter & _   "Order By Fiscal_Year desc, FiscalWeek desc " & _   ") SELECT * FROM CTE ORDER BY FISCAL_YEAR, FISCALWEEK "Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("conStringDFPortal").ConnectionStringDim myConnection As New SqlConnection(myConnectionString)Dim daChart As New SqlDataAdapter(mySelectQuery, myConnection)Dim dsChart As New DataSet()daChart.Fill(dsChart, "delete_chart_data")Dim cfxCT As New CrosstabDataProvidersender.DataSourceSettings.Fields.Add(New FieldMap("DisplayName", FieldUsage.ColumnHeading))Select Case Chart_FieldName   Case "Freight (FCOGS)"   sender.DataSourceSettings.Fields.Add(New FieldMap("Calendar_Month", FieldUsage.RowHeading))   Case Else   sender.DataSourceSettings.Fields.Add(New FieldMap("FiscalWeek", FieldUsage.RowHeading))End Selectsender.DataSourceSettings.Fields.Add(New FieldMap("WeekData", FieldUsage.Value))cfxCT.DataSource = dsChartsender.DataSource = cfxCTsender.LegendBox.Visible = Truesender.LegendBox.Dock = DockArea.TopIf dsChart.Tables(0).Rows.Count = 0 Then   sender.Titles.Add(New TitleDockable("Chart Data Not Available"))Else   sender.Series(0).Text = Chart_Legend   sender.DataBind()End IfEnd Sub=====================================================================================

I would think "sender.DataSourceSettings.Fields.Add(New FieldMap("PlanData", FieldUsage.Value))" would evaluate to another series, but it doesn't. Seems to overwrite the first "Fieldusage.Value" fieldmap.

Any advice on the matter would be greatly appreciated.Thanks!

[aas4mis]

The following is the response I received from the SoftwareFX tech support team, they were very quick to respond. I stopped using the crosstab and modified my code to no longer use FieldUsage.ColumnHeading; I am now using FieldUsage.Label and FieldUsage.Value (twice) to suit my needs.

SoftwareFX Tech Support:

I am afraid this is by design. Due to the nature of the Crosstab Data Provider, only one value column can be defined; if more than one value is defined, the first one defined will be used. If specifying multiple value columns is a requirement in your application, please note that the data will need to be manipulated to generate the desired format (without using the Crosstab Data Provider) before passing it to the chart.

Chart FX will apply default rules to construct the chart when it has been bound to a database (without using Crosstab). These rules pick the information from the result set of the query and assign legends and values to the chart according to the field type. This behavior allows the creation of a default chart that fits or closely resembles the chart you want to achieve.

The rules to construct a chart when bound to a database table are:

- All numerical columns in the query will be plotted as different series and all string and/or date columns will be plotted as point or X-axis labels (joined by the '-' character).

- Series Legends will be taken from the numerical field names.

- All string and numerical fields specified in the SELECT statement will be plotted in the chart.

For example, if you are trying to bind the chart to the result set of the following SQL query:

SELECT Month, Product1, Product2 FROM Sales

The resulting chart will contain 2 series ( Product1 and Product2 ) and X-axis labels containing the name of the months available in the Month field.

Please note that you can also customize the way in which Chart FX interprets the data by modifying the Fields collection of the DataSourceSettings class. The following link provides detailed information on how to do it:

http://support.softwarefx.com/OnlineDoc/CfxNet70//WinAPI/DataSourceSettings_Fields.htm

I hope this helps.

Regards,

Software FX Support

End SoftwareFX Tech Support:

Here is the new code from filling the dataset on...

daChart.Fill(dsChart,

"delete_chart_data")

'Dim cfxCT As New CrosstabDataProvider

'sender.DataSourceSettings.Fields.Add(New FieldMap("DisplayName", FieldUsage.ColumnHeading))

''Check for Plan Data

Dim i As Integer

Dim hasPlanData As Boolean

For i = 0 To dsChart.Tables(0).Rows.Count - 1

  If dsChart.Tables(0).Rows(i).Item("PlanData").ToString = "" Then

  hasPlanData = False

Else

  hasPlanData = True

End If

Next

'Asign Chart Fields

Select Case Chart_FieldName

  Case "Freight (FCOGS)"

  sender.DataSourceSettings.Fields.Add(New FieldMap("Calendar_Month", FieldUsage.Label))

  Case Else

  sender.DataSourceSettings.Fields.Add(New FieldMap("FiscalWeek", FieldUsage.Label))

End Select

If hasPlanData = True Then

  sender.DataSourceSettings.Fields.Add(New FieldMap("PlanData", FieldUsage.Value))

End If

sender.DataSourceSettings.Fields.Add(New FieldMap("WeekData", FieldUsage.Value))

'cfxCT.DataSource = dsChart

'sender.DataSource = cfxCT

sender.DataSource = dsChart.Tables(0)

sender.LegendBox.Visible =

True

sender.LegendBox.Dock = DockArea.Top

If dsChart.Tables(0).Rows.Count > 0 Then

  sender.Series(0).Text = Chart_Legend

If hasPlanData = True Then

  sender.Series(1).Text = "Plan"

End If

sender.DataBind()

End If

 

I hope this helps somebody.

--aas4mis