Retrieving values from filtered metric set

#1

Hello,

I’m building a series of dashboards to replace an Access database currently being used in our organization.
I have a dashboard that will display patient data in a sort of form view, for lack of a better term.
Instead of using many data labels, I’d like to retrieve values from each of the columns of the filtered metric set, which will only have one patient’s data (one row only).
Users navigate to a specific patient’s record (this dashboard) by clicking on that patient’s record on another dashboard from a list of patients.
I can retrieve the values right now by using the script below, but from the metric set unfiltered, all rows, all patients.

Here is a portion of my script:

var baseViewService = this.getService(“BaseViewService”);

//metric set
var metricSetId = “d1ddb307-3b43-477d-9580-8da8fa231df2”;
var dataRetrievalService = this.getService(“DataRetrievalService”);
var viewService = this.getService(“ViewService”);
var request = new dundas.data.Request({
“objectId”: metricSetId,
“requestData”: dundas.Utility.createGuid(),
});
var def = dataRetrievalService.getData(request);
viewService.showLoadingRestCall(def);

//Assign a value to currentCINLabel
def.done(function (dataResults) {
var cinMember = dataResults[0].cellset.rows[0].members[1].loadMemberValue();
var cinValue = cinMember.uniqueName;
var cinIndex = cinValue.indexOf(".");
currentCINLabel.labelText = cinValue.substring(0, cinIndex);
});

This will retrieve those values / captions from the first row of the metric set, but does not necessarily match the actual row of data the metric set has been filtered to.

Any help with this will be much appreciated!
Thanks,
Jim

#2

There is a lot of code required if you want to pull from a metric set and then apply filtering, all programmatically. You’re much better off adding a control to the dashboard and handling the filtering through the UI or by setting parameter values via script. Either of these will make things a lot simpler for you.

Once a control is on the dashboard, you can still access the data it returns after it filters and do anything with it. You just don’t have to code all the filtering with the request payload.

#3

Jeff,

Thanks for the reply.
I had attempted that before, but I ran into the same issue, where the parameter values gave me the first values for the columns to which they’re connected. I visualize the metric set as a data table, which gets filtered by a parameter.
If I use the parameter value of the unique ID (in this cas, a patient’s CIN), this will work because that is the parameter I’m mapping for the navigation from the previous dashboard. The rest of the parameter values remain unchanged. Is there a way to update these parameters to grab the only level values in the data table visualization?

Jim

#4

Hi Jim,

I’m afraid i’m not completely following. All the code aside, it sounds like you’re generally having an issue mapping multiple parameters through a navigation. Is this the core of the issue you’re experiencing?

#5

I think the what Jim is trying to do is to grab data from a table and use that to populate ‘normal’ (not data) labels.

We do this quite frequently and there are 2 approaches.

  1. The recommended and official way - this is to pass the filter parameters in to the data retrieval service as a parameter in the data request, which will cause it to only return one row.
  2. The way that we do it :slight_smile: which is to get Dundas to do it by having an actual table off canvas which is filtered by the filters like any table.
    We then use table1.metricsetBindings[0].dataResult.cellset.rows[0] (then getting column, members and values like you are doing already) to pull the data from the filtered table. The other benefit of this method is that you can look at the table to help work out what column/row you are accessing and its value. This script needs to go on the ‘data changed’ interaction of the table to be fired whenever it updates.
#6

David,

Yes, that is correct. Sorry, Jeff -I know my explanation was a bit confusing because of my poor word choice!
I’ll give this a go.

Thank you both!

Jim

1 Like
#7

Good luck James, if you get stuck then just ask, there is always some gotcha

#8

David,

This worked out perfectly! Thank you much.

Jim

#9

David,

Sorry for resurrecting an old thread. I applied the approach you outlined and it works (sort of) for my application. Unfortunately, it only returns 44 rows and no more (there are hundreds of rows). I am now trying the dataRetrievalService as follows but it is returning nothing. Any idea what I am doing wrong?

var BatchID = this.parentView.control.getViewParameterByName(“BatchID”);

BatchID.parameterValue.clearTokens();
BatchID.parameterValue.clearValues();

var dataRetrievalService = dundas.context.getService(“DataRetrievalService”);
var request = new dundas.data.Request({ objectId: table1.metricSetBindings[0].metricSetId});
request.pagingOptions.pagingKind = dundas.data.PagingKind.NONE;

// Request the data:
var dataPromise = dataRetrievalService.getData(request);
dataPromise.done(function(results) {
// The request was successful:
var cellset = results[0].cellset;

for (var i = 0; i < cellset.rows.length; i++) {
var memberValue = cellset.rows[i].members[0].loadMemberValue();
// push member value onto view parameter’s parameter value’s values array
BatchID.parameterValue.values.push(memberValue);
}
BatchID.invalidateParameterValueLastModifiedTime();
BatchID.refreshAllAdapters();
});

#10

Hi Keith

If there are hundreds of rows, it’s probably best to do the data retrieval route. The code looks ok at first glance - are you getting any errors in the developer console?

If you want to do it the naughty way - you have to have all the rows visible in the off canvas datagrid. I’ve done this before by setting a font size of 2 pixels and a table height of 1000.

Hope that helps

#11

No errors, but I am getting a warning on the request “One or more parameters have no value selected.” I am investigating that now. If I don’t get this figured out soon I may try the naughty way. I have been at this for 2 weeks!

Thank you.

#12

David,

It is strange. Before I had maybe 10 rows visible but the dataResult would max out at 44. Now, I have changed the table to have maybe 100 rows visible and now the dataResult is allowing a lot more ( I haven’t found the limit). There seems to be no rhyme or reason to this…