How to pass the selected data point from visual of datasetA as dataset parameter of datasetB

[Kang_Hong]

Background:

I have 2 datasets; DatasetA and DatasetB.

DatasetA has a dataset parameter using dates and it returns an ID column.

DatasetB has a dataset parameter using cid and it returns many columns.

We are using dataset parameter to reduce the records retrieved, instead of SELECT-without-condition-then-apply-visual-filters.

Requirement:

I want the dashboard user to input a date control to retrieve the relevant records from DatasetA, then select/click on one of the returned ID value.

This selected ID value should then be passed as the dataset parameter of DatasetB for B to retrieve the relevant records.

What I tried #1:

In visual of DatasetA, add a Filter Action with the selected fields = ID column.

Target visual as DatasetB visual and ensuring the name of DatasetA.ID and DatasetB.cid matches (to satisfy field mapping).

I can only achieve the behaviour if DatasetB is a SELECT-without-condition.

What I tried #2:

In visual of DatasetA, add a Navigation Action to target the analysis parameter mapped from DatasetB dataset parameter cid. But the parameter value to pass seems to only allow ALL or Custom.

Is there a placeholder variable like <> I can put in the parameter value to mention the selected data point?

I hope the description is clear, Thank you very much in advance.

[andrepgn]

Hi @Kang_Hong , welcome to the QS Community

I’ve tried replicating your need, I’m not sure 100% that I’ve got it right, so please let me know if this works for you or not.

I’m supposing you have two different datasets coming from two database tables.
Let’s say:

Table A

ID	transaction_date	amount	description
C001	2024-01-01	1000.00	“Initial transaction”
C001	2024-01-15	500.50	“Follow-up payment”
C002	2024-01-02	750.25	“New subscription”
C003	2024-01-03	1250.75	“Premium package”
C004	2024-01-04	325.00	“Basic service”
C005	2024-01-05	899.99	“Advanced package”

Table B

cid	customer_name	email	status	last_update	additional_info
C001	John Doe	john.doe@email.com	Active	2024-01-20	“Premium customer”
C002	Jane Smith	jane.smith@email.com	Inactive	2024-01-18	“Basic plan”
C003	Bob Wilson	bob.wilson@email.com	Active	2024-01-15	“Corporate account”
C004	Alice Brown	alice.brown@email.com	Pending	2024-01-19	“Trial period”
C005	Charlie Davis	charlie.davis@email.com	Active	2024-01-17	“VIP member”

Let’s say I’ve created the two datasets adding the parameters needed in the DataPrep in this way:

Dataset A Query:
SELECT * FROM "default"."dataseta"
WHERE transaction_date >= <<$DateFrom>> AND transaction_date <= <<$DateTo>>

Dataset B Query:
SELECT * FROM "default"."datasetb"
WHERE CID = <<$SelectedID>>

Now, on Analysis window.
If all the defaults you set in the DataPrep are outside your data rage, you should have something like this: first visual created from Dataset A, second one from Dataset B

I now create 3 parameters (select the defaults based on your needs):

• PDateFrom (date)
• PDateTo (date)
• PSelectedID (string)

For all these I’ve added also a Control on top of the page:

On the Selected ID control, choose Edit. Here you can define this control to show only values coming from the Dataset A id field:

Now you have three params in Analysis and the corresponding params in each Dataset.
From the Params view in Analysis you can now map the Parameters:

ATTENTION POINT:
Arrived at this point, I noticed that the Selected ID control does not seem to be updated accordingly to the values shown selecting dates for Dataset A.
An alternative can be (if it’s feasible for you) creating a visual that will just show the distinct values of the IDs in that date range, and then add (instead of a control) an Action on that visual.

Something like a visual with the IDs list:

and when you click on one, Dataset B is updated using the SelectedID param

The result will be:

NOTE:
Just be sure that, by doing this (using custom queries w params), you do not overload the original data source, since each time you’ll change something in these parameters, a new query will be launched on your original source

Hope this is helpful!
Andrea

[Kang_Hong]

Hi Andrea,
Thank you for the help.
Unfortunately I can only try it out next week.

One thing I wanted to ask is the Action that you set up,
where you put ‘Field : id’.
I remember the options for parameter value are ‘All’ or ‘Custom Value’.
Is ‘Field : id’ manually input by you in the ‘Custom Value’ cell?
Does QS recognise this ‘Field’ syntax to refer to the clicked data point?

Thank you~

[andrepgn]

You should have the choice to select also a Field:

If not, try to specify the fields type you have, how you built the param etc, maybe there’s some configuration that is preventing you to select the field, but it’s strange.

Let us know!

Andrea

[Kang_Hong]

Hi Andrea,

Thank you very much for your pointers.

Previously I was only able to see the All and Custom when Set Parameter value. Turns out there was data type mismatch.

After aligning the data type, I am able to select the Field:id and get the action behaviour to work.