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

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!