User (Legacy) Posted March 11, 2005 Report Posted March 11, 2005 Hi, I am using the stacked area chart in an up-to-date version of Chart FX 6.2. I am trying to plot a chart with values from an SQL Server 2000 Database 1. Each database record looks something like below: TransactionNumber,StartDate,EndDate,Amount 1,2004-11-30,2008-11-30,300000 2,2005-02-01,2007-01-31,150000 I need to create a chart that uses the StartDate and EndDate to create a strip on the chart for each transaction and then stacks the transactions. I have tried to use the CrossTab dataprovider but this will not work correctly with the two dates, as each set of dates can be different and I end up with gaps and junk data. Please can someone help or suggest a different way of doing this, getting very frustrated Thanks, John
Software FX Posted March 15, 2005 Report Posted March 15, 2005 The Cross-Tab provider will do this for you but you need one more thing and that is the Point/Series Pivot. How do you want to see this chart ? How do you want each segment to be aligned ? are all segments in the same strip ? Can you provide a screenshot of what you are looking for ? -- FP Software FX
User (Legacy) Posted March 16, 2005 Author Report Posted March 16, 2005 Hi, Here is a more complete example The database stores the records in the following format, there could be any number of transactions with completely different and overlapping date ranges. Transaction StartDate EndDate Percentage 100000075 2005-03-04 2009-12-31 19.250000 100000077 2004-09-01 2008-12-31 70.000000 Using a union query to simplify this so I can use the crosstab provider returns Transaction Date Percentage 100000077 2004-09-01 70.000000 100000075 2005-03-04 19.250000 100000077 2008-12-31 70.000000 100000075 2009-12-31 19.250000 Using the crosstab provider with: .ColumnHeading .RowHeading .Value gives the stacked area chart shown in "Inital Area Chart.png" screenprint If I manually type in the missing data points (How do I avoid the 'missing' data? is there a way of specifying a date range?) gives the "Area Chart after typing in values.png" screenprint. This graph is wrong. The diagonal line implies a gradual reduction in percentage, it should be shown as a green block starting immediately after the blue block ends. If I change the type to a stacked column chart I get a more correct chart but still have to type in the 'missing' data shown in "Initial stacked column.png" and "Stacked column after adding values.png" screenprints. I hope this clarifies what I'm trying to achieve. The only other way I have found to do this is to plot every day in an OLAP cube and use the OLAP extension to display the data. This works correctly but generates a massive amount of information (every day for 25 years per transaction in a worst case scenario) Thanks John "SoftwareFX Support" <noreply@softwarefx.com> wrote in message news:wC0I92WKFHA.1924@webserver3.softwarefx.com... > The Cross-Tab provider will do this for you but you need one more thing > and > that is the Point/Series Pivot. > > How do you want to see this chart ? How do you want each segment to be > aligned ? are all segments in the same strip ? Can you provide a > screenshot > of what you are looking for ? > > > > -- > FP > Software FX > >
User (Legacy) Posted March 16, 2005 Author Report Posted March 16, 2005 Hi, Here is a more complete example The database stores the records in the following format, there could be any number of transactions with completely different and overlapping date ranges. Transaction StartDate EndDate Percentage 100000075 2005-03-04 2009-12-31 19.250000 100000077 2004-09-01 2008-12-31 70.000000 Using a union query to simplify this so I can use the crosstab provider returns Transaction Date Percentage 100000077 2004-09-01 70.000000 100000075 2005-03-04 19.250000 100000077 2008-12-31 70.000000 100000075 2009-12-31 19.250000 Using the crosstab provider with: .ColumnHeading .RowHeading .Value gives the stacked area chart shown in "Inital Area Chart.png" screenprint If I manually type in the missing data points (How do I avoid the 'missing' data? is there a way of specifying a date range?) gives the "Area Chart after typing in values.png" screenprint. This graph is wrong. The diagonal line implies a gradual reduction in percentage, it should be shown as a green block starting immediately after the blue block ends. If I change the type to a stacked column chart I get a more correct chart but still have to type in the 'missing' data shown in "Initial stacked column.png" and "Stacked column after adding values.png" screenprints. I hope this clarifies what I'm trying to achieve. The only other way I have found to do this is to plot every day in an OLAP cube and use the OLAP extension to display the data. This works correctly but generates a massive amount of information (every day for 25 years per transaction in a worst case scenario) Thanks John "SoftwareFX Support" <noreply@softwarefx.com> wrote in message news:wC0I92WKFHA.1924@webserver3.softwarefx.com... > The Cross-Tab provider will do this for you but you need one more thing > and > that is the Point/Series Pivot. > > How do you want to see this chart ? How do you want each segment to be > aligned ? are all segments in the same strip ? Can you provide a > screenshot > of what you are looking for ? > > > > -- > FP > Software FX > >
Software FX Posted March 20, 2005 Report Posted March 20, 2005 I'm still not clear on what do you want to get in the chart. All the images attached are consistent with the data being received. Please include a drawing of what you want to get for a specific set of data. The CrossTab provider expects: A series Pivot An X-Axis pivot A value An IniValue (optional) an X-Value (optional) You may contact Software FX support to get speedier answers. -- FP Software FX
User (Legacy) Posted March 21, 2005 Author Report Posted March 21, 2005 In all charts I'm trying to plot the data between two dates, the start date and end date, per series against the Y axis. 1. I don't believe it should be necessary to supply the (unknown) intersecting series data points (i.e. in the examples the missing data points are caused by other series) 2. I can't seem to achieve the result by using the Crosstab provider and Union queries in the examples as this causes the missing points. There seems to be no ability to fill missing values or work around the problem. 3. Calculating all possible data points ((i.e.) all days between start date and end date per series) in an OLAP cube gives a correct chart but a very large information set (two charts in a C# application used 90+ MB of RAM for <10 series) 4. The area chart is wrong in "screenprint 1" attached. If there are 2 series in a chart and 1 of the series go down to zero but 1 carries on I should not get the results shown in the screenprint. This screenprint implies that the data gradually reduces between 31/12/2008 and 31/12/2009 which is wrong, it should look like "screenprint 2" attached As stated earlier all series are time based with two known points per series which could in practice be anything from <1 year to 25+ years apart. I have supplied an example of a data record, the incorrect chart I get because of the missing records caused by the Union query and Crosstab provider, the chart after I type in the values (which is not feasible in a production environment) and an example of what a correct chart should look like. Thanks "SoftwareFX Support" <noreply@softwarefx.com> wrote in message news:PIEzxIYLFHA.544@webserver3.softwarefx.com... > I'm still not clear on what do you want to get in the chart. > > All the images attached are consistent with the data being received. > > Please include a drawing of what you want to get for a specific set of > data. > > The CrossTab provider expects: > > A series Pivot > An X-Axis pivot > A value > An IniValue (optional) > an X-Value (optional) > > You may contact Software FX support to get speedier answers. > > -- > FP > Software FX > >
User (Legacy) Posted March 21, 2005 Author Report Posted March 21, 2005 In all charts I'm trying to plot the data between two dates, the start date and end date, per series against the Y axis. 1. I don't believe it should be necessary to supply the (unknown) intersecting series data points (i.e. in the examples the missing data points are caused by other series) 2. I can't seem to achieve the result by using the Crosstab provider and Union queries in the examples as this causes the missing points. There seems to be no ability to fill missing values or work around the problem. 3. Calculating all possible data points ((i.e.) all days between start date and end date per series) in an OLAP cube gives a correct chart but a very large information set (two charts in a C# application used 90+ MB of RAM for <10 series) 4. The area chart is wrong in "screenprint 1" attached. If there are 2 series in a chart and 1 of the series go down to zero but 1 carries on I should not get the results shown in the screenprint. This screenprint implies that the data gradually reduces between 31/12/2008 and 31/12/2009 which is wrong, it should look like "screenprint 2" attached As stated earlier all series are time based with two known points per series which could in practice be anything from <1 year to 25+ years apart. I have supplied an example of a data record, the incorrect chart I get because of the missing records caused by the Union query and Crosstab provider, the chart after I type in the values (which is not feasible in a production environment) and an example of what a correct chart should look like. Thanks "SoftwareFX Support" <noreply@softwarefx.com> wrote in message news:PIEzxIYLFHA.544@webserver3.softwarefx.com... > I'm still not clear on what do you want to get in the chart. > > All the images attached are consistent with the data being received. > > Please include a drawing of what you want to get for a specific set of > data. > > The CrossTab provider expects: > > A series Pivot > An X-Axis pivot > A value > An IniValue (optional) > an X-Value (optional) > > You may contact Software FX support to get speedier answers. > > -- > FP > Software FX > >
Software FX Posted March 21, 2005 Report Posted March 21, 2005 I went over all the attachments again and could not find the piece I need to understand what you want to plot. I need a drawing, bitmap, anything, that shows what you want to draw for a specific set of data. I understand everything else you are saying, I understand WHY you are getting the results you are getting, the only thing I don't understand (and this is key) is what do you want to get. What I'm asking for is simple: Show me a set of data. For example: Transaction StartDate EndDate Percentage 100000075 2005-03-04 2009-12-31 19.250000 100000077 2004-09-01 2008-12-31 70.000000 Then show me a picture of what you want to see for this particular data set. I don't mean screenshot of what you are getting now, I already saw those and they correspond to what you are setting, I just need to know what you want to get in order to help you use the API or re-organize your data to get it. You may want to contact Software FX support for a speedier response. -- FP Software FX
User (Legacy) Posted March 22, 2005 Author Report Posted March 22, 2005 I have raised an incident with Software FX support "SoftwareFX Support" <noreply@softwarefx.com> wrote in message news:YXckMCkLFHA.544@webserver3.softwarefx.com... >I went over all the attachments again and could not find the piece I need >to understand what you want to plot. > > I need a drawing, bitmap, anything, that shows what you want to draw for a > specific set of data. I understand everything else you are saying, I > understand WHY you are getting the results you are getting, the only thing > I don't understand (and this is key) is what do you want to get. What I'm > asking for is simple: > > Show me a set of data. For example: > > Transaction StartDate EndDate Percentage > 100000075 2005-03-04 2009-12-31 19.250000 > 100000077 2004-09-01 2008-12-31 70.000000 > > Then show me a picture of what you want to see for this particular data > set. I don't mean screenshot of what you are getting now, I already saw > those and they correspond to what you are setting, I just need to know > what you want to get in order to help you use the API or re-organize your > data to get it. > > You may want to contact Software FX support for a speedier response. > > -- > FP > Software FX >
Recommended Posts
Archived
This topic is now archived and is closed to further replies.