0

Dynamics Line Chart

Jet Reports Historic Posts

February 07, 2017 04:45

None

Follow

Hi,

Could you help how to setup "Source Data" in Excel for the Line Chart so that the chart will look like this:



The Jet Report Design is like this:


My concern is my row replicator is not included in the chart, only the first line.
The result is like this:



Kindly please advice.

Thanks & Regards,
Olwin

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts February 08, 2017 14:11

  I have built pivot tables connected to a report using ROWS replicator, by including the row just below replicator.
  
  IE, if my headers are in row 4, and my ROWS command is in row 5, my pivot table looks to rows 4-6.
  This results in a blank row in my pivot table output, and I have a few ways around that
  
  1. if I am running the report with the Jet scheduler, I run as a VALUES report (so Jet formulas and hidden rows/columns are removed) - and I put a HIDE command in the blank line below my replicator (in the example above, this is row 6)
  
  2. if I don't mind a bit of manipulation in my report and I can't use option #1, I just delete the blank row (row 6) after the report runs and refresh the pivot table.
  
  3. I filter out the blanks in the pivot table (which I know won't work with a chart)
  
  I haven't tried this technique with a chart, but I'm guessing you could probably use a similar technique.
  I'll see if I can't test this later today and report back (depends on how busy the day gets)

• 0
  
  Jet Reports Historic Posts February 14, 2017 18:40

  Hi Heather,
  
  Thanks for your feedback. I'm still unable to create my dynamics chart.
  Anyway, can somebody share how to use NL (Table) to address my previous report layout ?
  Seems that I need to use NL (Table) to make Excel dynamically adjust my chart.

• 0
  
  Jet Reports Historic Posts February 15, 2017 15:28

  Olwin, I'm sorry to say you are correct. No matter what I try, the only thing I get is the top row, and my hidden row of data. :x
  
  I was able to get a chart using my pivot table trick - but that might be more of a work around than you want.
  it required building a pivot table from the ROWS command (concealing the final row as I discussed below), and then a pivot chart from the Pivot table.
  that method does still work - I've attached a sample doc (in report mode) to show what I did.

  • Pivot Table Example.xlsx
• 0
  
  Jet Reports Historic Posts February 21, 2017 20:49

  Olwin,
  
  Just as with nested rows and subtotaling, you can include the 'anchor' row in your Source Data range. So from your example, your source data would include row 10 in its range.
  
  Jet's sample report 'NAV038 - Professional - Top Items by Sales Volume and Margin.xlsx' (attached) is an example of a chart created using a Row replicator function.
  
  Regards

  • NAV038 - Professional - Top Items by Sales Volume and Margin.xlsx
• 0
  
  Jet Reports Historic Posts February 23, 2017 04:58

  Hi Heather,
  
  Thanks for your update. I've tried to use Pivot Table and Pivot Chart and it solved my problem. The things is that, If I use row and column replicator at the same time, Excel Chart didn't recognized the floating rows, but when we do it using Pivot for matrix view (based on NL(Table)), the Pivot Table will also adjust the chart to floating rows and column.
  
  Hi krd1996,
  
  Thanks for your response. In the sample report attached, the report only have the floating rows and you are correct, if we want to create dynamics chart, we just need to include the "anchor" row. But when we design a report using NL Row and NL Column at the same time, the chart didn't show correctly.
  
  BR,
  Olwin