Beanshell Script - Dynamic Y-axis by Partition


#1

Hi all, long time viewer, first time caller… so here’s my deal… any help would greatly be appreciated:

Let say I have 10 Companies, and each has ?? weeks of data that return, X-axis grouped by Company and Week, and Y-axis the NumberOfWidgits.
Within my Bar Chart, I have a Beanshell post-script that dynamically builds out my Y-axis based on the NumberOfWidgits returned in my mainQuery… works great.
Beanshell code here:

import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.axis.ValueAxis;
import java.util.;
import java.math.
;
import java.util.List;
import java.util.ArrayList;

CategoryPlot chartPlot = chart.getCategoryPlot();
ValueAxis yAxis= chartPlot.getRangeAxis();
data = chartPlot.getDataset();
keys = data.getRowKeys();
ArrayList dataVals = new ArrayList();
for (int i=0;i<keys.size();i++)
{
ab=data.getValue(i,i);
dataVals.add(ab);
}
Double maxVale=(Collections.max(dataVals)).doubleValue();
Double minVale=(Collections.min(dataVals)).doubleValue();
yAxis.setUpperBound(maxVale+500);
yAxis.setLowerBound(minVale-5);

HERE IS MY PROBLEM:
I have altered my mainQuery, I have added a ‘Partition By’ : ROW_NUMBER() OVER (PARTITION BY MemberId ORDER BY DateCreated desc)

This now allows me to return the last 10 Weeks of info of each Company… this works correctly

BUT now when the post script runs, it ONLY dynamically looks at the first partition, and bases the Y-axis MAX on that particular set of ten.
(Company 1 has a MAX of 500 Widgits, Company 4 has a MAX of 4000 Widgits, but the dynamic Y-axis only builds itself out to 500.)

How can I get the post-script to look at all partitions and returns and uses Any Companies Widgit MAX?

Thanks!
Iowabeef


#2

It only takes public shaming to come up with my own solution…

Change this portion of the Beanshell script from…

keys = data.getRowKeys();
ArrayList dataVals = new ArrayList();
for (int i=0;i<keys.size();i++)

  • TO -

keys = data.size();
ArrayList dataVals = new ArrayList();
for (int i=0;i<keys();i++)

Thanks all, hope this helps someone else down the road
Iowabeef