scripting to change sorting direction

#1

I have a chart that sometimes needs to be sorted one way, sometimes the opposite direction.

Is there a script method to change the sort direction of a hierarchy (row or column in the data analysis) between ‘ascending’ and ‘descending’, and where would that be applied to? The chart or the dataresult? I am using an autogenerated metric set if that makes a difference

Of course I could make two charts and switch visibility, but if there’s a way to change the direction via script that would be easier.

Thanks

1 Like
#2

Hi David,

The sorting has an insane amount of customization available, more than you need (i think). I’d highly recommend going the layers approach for something as simple as your requirement, you can do with completely without code.

If you’re interested in getting deeper into the code side, here is some rough and untested code that you can use as a baseline to do your own. You can see from this why i recommend the layers approach.

function getOrCreateMetricSetBindingOverride(currentView, adapterId, metricSetBindingId) {
var override = currentView.overrides.getMetricSetBindingOverrideById(adapterId, metricSetBindingId);
if (!override) {
override = {
“adapterId”: adapterId,
“metricSetBindingId”: metricSetBindingId,
“requestOverrides”: new dundas.data.RequestOverrides()
};
currentView.overrides.metricSetBindingOverrides.push(override);
}

return override;

}

function getOrCreateMeasureOverrides(override, uniqueName) {
var measureOverrides = override.measureOverrides.filter(function (measureOverrides) {
return measureOverrides.uniqueName == uniqueName;
})[0];

if (measureOverrides == null) {
    measureOverrides = new dundas.data.RequestMeasureOverrides({
        "uniqueName": uniqueName
    });
    override.measureOverrides.push(measureOverrides);
}

return measureOverrides;

}

function getOrCreateHierarchyOverrides(override, uniqueName) {
var hierarchyOverrides = override.hierarchyOverrides.filter(function (hierarchyOverrides) {
return hierarchyOverrides.uniqueName == uniqueName;
})[0];

if (hierarchyOverrides == null) {
    hierarchyOverrides = new dundas.data.RequestHierarchyOverrides({
        "uniqueName": uniqueName
    });
    override.hierarchyOverrides.push(hierarchyOverrides);
}

return hierarchyOverrides;

}

var currentView = this.parentView.control;
var metricSetBindingOverride = getOrCreateMetricSetBindingOverride(currentView, chart1.id, chart1.metricSetBindings[0]);
var measureOverrides = getOrCreateMeasureOverrides(metricSetBindingOverride, “SalesYTD”);
measureOverrides.directions = [];

// For each hierarchy + level to sort
measureOverrides.directions.push(
new dundas.data.MeasureSortingByHierarchyLevel(
“direction”: dundas.SortDirection.ASCENDING,
“hierarchyUniqueName”: “”,
“hierarchyLevelUniqueName”: “”
)
);

1 Like
#3

Thanks Jeff - good stuff :grinning:

How do I get the chart to reload with the overrides?

#5

Hi David,

You can try to invalidate the chart controls after you have set up the overrides. Something like chart1.control.invalidate();

#6

Thanks Pankaj - I’ll give that a shot

#7

David,

Did you ever get this working? I’d love to see your final code. I have the same requirement but for a chart, and I want the redraw, so layers approach won’t work for us.

Thank you!

#8

Hi @devon.byrd

No, I didn’t get it working. I went with two layers in the end.
Sorry

1 Like
#9

Thank you sir. I appreciate it.

#10

@jeff @david.glickman @devon.byrd

Hey guys, just trying to do the same thing and found this old post. Tried the scripts here, no dice.

Then I tried what Jeff recently showed in a video he made for me for another task (THANK YOU!). I captured the override by:

  1. Make a debugger button (button with only debugger; in the script)
  2. Check in the dash (REQUIRED) and open a sandbox view
  3. Perform the sort you want, and nothing else
  4. Open the Console and click your debugger button
  5. In the console, run: JSON.stringify(this.parentView.control.overrides.metricSetBindingOverrides[0]);
  6. Grab that string and paste it into this, in a button that will do the sorting:
var override = JSON.parse([INSERT HERE WITH ITS ORIGINAL QUOTE MARKS ON EACH END]);

this.parentView.control.overrides.metricSetBindingOverrides = [override];
table1.loadData();

Click button and enjoy!

Working on how to ‘clear’ the sort next.

#11

Yup, confirmed, I can toggle the sort on and off by doing the same thing for the ‘normal’ sort.

  1. Check in and sandbox
  2. Sort all columns desired, in order
  3. Once done, run the debugger console trick again and capture the stringify text
  4. Paste into final code (stringify text should begin and end with a double quote; replace the “…” I have here):
var normal_override = "...";
var special_override = "...";

var overridesLength = this.parentView.control.overrides.metricSetBindingOverrides.length;

if (overridesLength == 0)
{
  this.parentView.control.overrides.metricSetBindingOverrides = [JSON.parse(special_override)];
  table1.loadData();
}
else
{
  if (JSON.stringify(this.parentView.control.overrides.metricSetBindingOverrides[0]) == normal_override)
  {
    this.parentView.control.overrides.metricSetBindingOverrides = [JSON.parse(special_override)];
    table1.loadData();
  }
  else
  {
    this.parentView.control.overrides.metricSetBindingOverrides = [JSON.parse(normal_override)];
    table1.loadData();
  };
};
4 Likes
#12

Nice tip @ken - thanks for sharing!

2 Likes
#13

I had a user request for this function on an important dash, so I revisited to use what I shared before. Well, asserting the normal_override to revert back to normal wasn’t working. So I dug a bit more and found this, and it works perfectly!

this.parentView.control.overrides.metricSetBindingOverrides.clear();
table1.loadData();

And now I have a fast and functional dash that sorts by script! :muscle:

NOTE: to anyone looking to use this, I’ve only designed it to work for ONE override. If you have more, your efforts will have to be deeper than what I have here, to manage multiple.

1 Like