How do you populate columns of the Metric set on one visualization with data from another visualization (on the same dashboard layer)?

#1

Hi guys.


On a dashboard of mine, I'm using data labels as KPI's where both the text colour in the label (and a state indicator in the form of triangles using a copied metric set) change colour when compared to the 12 month average.


Now on the surface this is simple. I merely only needed the metric sets to have measures of the values of the last 12 months (sorted in descending order) and then compared to the moving annual average (or MAA) over 12 months by using the "add period over period" data tool.


But unfortunately I also need to be able to filter down the month (within the last year) that the data was collected in with a click event. That filter iteslf was not difficult to implement by changing parameters.

But unfortunately; when selecting a new month, thecalculation period of 12 month MAA also moves back to begin at the month of selection (as the data in the metric set also moves to the selected month). What I need is for that MAA to remain constant from the latest reported month (and for those state comparisons to continue comparing the selected month with the average up until the latest month)

I'm already using another data label as a KPI displaying merely the MAA over the last year that is not sublected to any filtering.


So my question is: How do I go about using for a comparer in the filtered data label; the data from the metric set of the fixed MAA in the non-filterd data label?

I've tried using a dummy field (a measure with a constant value of 0) in the metric set of the filtered data label and then usig scripting to populate the cells of this field with the value in the metric set of the non-filtered data label. But that doesnt actually update the values in the data preview.


I've seen this article in the help files where usage is made of a "DataRetrievalServĂŻce:

https://www.dundas.com/support/support-center/support-articles/scripting/read-data-from-a-visualization-by-script


I follow that in this example; a button is implmented that retrieves cells of data from a visualization's metric set. But it pretty much loses me when I get to the following line:


"Once the data has been retrieved in the CellSet, the tuples can be accessed using the following code snippet:


1
 CellSet.rows.forEach( function(row) { //your code }"


Because I honestly have no idea what sort of code I should put in this function.


Could someone please at least point me in the right direction on this?

#2

One option to try is using a Formula visualization as that can combine data from 2 seperate metric sets - in your case the filtered one and the non filtered one. Will that meet your requirements?

#3

Thanks for putting me on to Formula Visualizations Ariel.

But unfortunately; that article does not explain how to combine data from more than one source.

Furthermore; the main KPI needs to be filtered-to by selecting a month (within the last year). When using a formula visualisation, this filtering will also apply to the data taken from the non-filtered set anyway.


What I need is a way of importing the data from the non-filtered data set as a unchangable constant.

#4

The formula visualization will combine data from both if you create a calculation between them so for example, if you use create a blank dashboard and just drop 2 visuals on it the formula visualization will look like this:

$table1.Metric Set 1.Sales Amount$ -$table2.Metric Set 2.Sales Amount$

The default end result for this will be a table visualization that is showing both result of the formula as well as the original measures from each visualization (metric set). Then you re-visualize this table to a state indicator visualization (you may need to sort it first) and apply your states.

You are right about the fact that the formula visualization will apply by default the filter to both measures taken from the 2 different metric sets. To overcome that, you can create a public parameter for the date of the first metric set at the data cube level. If your source is SQL based then you can just open the "define parameters" menu under the select transform and create an output elment filter which is public. If you are using a non SQL based source (i.e. an excel file), you can do the same but not on the tabluar select transform but on any other transform that follows in your data cube. note I haven't tried my case with bridge paramteres but if you need a bridge parameter I suspect it will work as well.

Then once you connect that filter to both the first metric set and the formula visualization it will only filter the first metric set and will leave the static one as is.


I hope this helps - let me know if that does the trick.

#5

Hi Ariel thanks again for your help. I unfortunately found nothing in the documentation on formula visualizations that indicated that data could be sourced from more than one source, so you've been a big help.



Just one thing I should note: Both metric sets are drawn from the same data cube, it's just that they use different view parameters at the dashboard layer. Won't applying a public parameter at the data cube effect both metric sets?

If that's the case; would you recommend copying the data cube and setting two different public parameters (for the two different metric sets)?

#6

Glad this helps - Indeed I would recommend setting up 2 data cubes one with a public parameter and the other without. As for the documentation - we have published a tip about it here in the cube but will make sure we also add it our support site documentation - thanks for pointing that out.

#7

Just one thing Ariel:


I've applied the public parameter to the data cube (as a test). The dashboard now takes a lot of CPU usage & time when loading

#8

This really depends on your data cube setup. I would start by asking what is your data source? ideally the parameter is defined on the very first transform of your data cube. In that case - you shouldn't see any decrease in performance (probably even an increase). If you apply the parameter on a later transformation it is possible that the transformation is forcing the data to be brought in its entirety to the Dundas BI server and only then filtered which will indeed make things slower in most cases.

Here is how to define the parameter on the first transform (assuming it's a SQL based source): configure the "SQL Select" transform use the "Define parameters" option to create a new "Output element filter" pointing to your date field.

Image title
ZaZ97MMIT7eXUIfK39iQ_Define%20Parameter.PNG1060Ă—1054


#9

No that's exactly how I set it up. At the very first SQL Select in the cube.


I should proably note though; that the cube includes 2 SQL selects, from the same source (but filtered on different values) and that I've applied a public filter to both.

#10

In that case and assuming your original data cube wasn't warehoused or in-memory, I think a deeper investigtaion is required. You can use SQL Profiler or turn on query logging from the admin config settings and compare the queries genearted in each case (with and without the public parameter) or work with the support team to further investigate this.

#11

Hi Ariel.

Is should tell you: The data cube is warehoused.


EDIT: I've just discovered that the cube can't warehouse with the public parameter:

"An error occurred while running the job: Dundas.BI.ValidationException: This data cube cannot be built as a warehouse or in-memory cube because it has parameters or settings that may change the result."

#12

Ok - in that case since you are using public parameters, the warehousing can no longer be used (see the note here) meaning your data cube is executed on the fly which explains the higher CPU usage and slower load.

What you can try, is to create a new data cube that is starting with your original warehoused data cube (without the public parameter) and then apply the public parameter in the new data cube by adding a simple transform like the filter transform and configuring the public parameter on it:

Image title

1 Like
#13

Hi Ariel.


I've made a copy of the data cube and applied an output element filter parameter to one of the later filter nodes.

I've then made a data label for the constant with data from the duplicate cube with the parameter.

I've then made a label from a formula visualization that references both the dynamic latest month ratio and the intended constant ratio and used them for comparisons for a state.


Unfortunately when I apply the filter to the label (from a formula visualization) to change the latest month; it still changes the latest month of the intended constant (over the last 12 months).

#14

Hi Daniel, I was searching for this thread as I wanted to share the video I created for it with another user only to discover that I never finished posting the video - I'm really sorry about that. Here is the video I created back then with my setup - is it different than yours?