Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Generating Chart from Dynamic Data Table

 Post Reply Post Reply
Author
Bryan Urteaga View Drop Down
New Member
New Member
Avatar

Joined: 17 Jul 2013
Posts: 4
Post Options Post Options   Quote Bryan Urteaga Quote  Post ReplyReply Direct Link To This Post Topic: Generating Chart from Dynamic Data Table
    Posted: 14 Aug 2013 at 2:02pm
I cannot determine how to generate a chart when the source does not have a constant column count. I'm generating a table which is passed to FlexCell. Because the table is generated, I do not know the column count and column names at design time. Therefore, I'm generating the report data using:
<#Table.**>
<#Table.*>
 
When the chart is generated the series does not encompass the data, but is split in two. The first series range covers the first two rows, where the first row is column headers. The second series range covers three rows, but skips three. The report template has the series defined across 27 columns and 5 consecutive rows. In other words, the template chart series is defined as:
 
='Worksheet'!$A$25:$AA$29
 
but the generated report has the series changed to:
 
='Worksheet'!$A$25:$AA$26,'Worksheet'!$A$30:$AA$32
 
What needs to be done differently to generate a chart given a dynamic table?
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1233
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 14 Aug 2013 at 3:38pm
Hi,
FlexCel won't add series to a chart when you insert cells, this is done mostly for Excel compatibility (because Excel doesn't add series either), but also because when adding series we would have to assign them random properties (like the color or the marker type for the new serie)

You can see the exact thing if you do it in Excel, open the generated file, make the chart range use all the cells, and then insert a column in the middle. You will see that the range is split in 2.

Now, looking at your result (series are horizontal instead of vertical) I think there is another problem besides this, and I'll need to explain this a little more in depth.

Even when Excel (2007 or up) will show you a "data range" like ='Worksheet'!$A$25:$AA$29 internally what you have are "Categories" and "Series".

Normally we want to have the less number of series, so if you have a "data range" going from A1 to C1000, Excel will create 3 vertical series, one for column A, the other for column B and the last for Column C. Excel will always look at your data range, and assign the smallest dimension to the series.

And this is normally what you want, but no when you are using a template. This is because if you have a "dummy" datarange in the template of A25:AA29, Excel will see about 27 columns and 5 rows. So it will assign the series horizontally, because in the template there are more columns than rows.

But of course, in the generated file you might have about 1000 rows and the same 27 columns, and so the original decision was wrong, we should have put the series in the columns, not in the rows. (a chart with 1000 series is virtually unreadable) You want 27 series, not 1000.

So, when designing the template, in the "Select Data" dialog, press the button "Switch Row/Column"  As said, you will need to press this button in almost all templates, because Excel will guess wrong the rows and the columns when the chart has no data.

Once your series are in columns, not in rows, the data will expand when you insert rows. FlexCel will still not add series, so you will have the original 27 series, but this is as expected. 

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down