Set 2 dataset parameters (datetime) based off a dropdown

Question & Answer
, ,
1

Hi All,

I have a dataset that for example has 3 columns,

name (String),
startDate (DateTime),
finishDate (DateTime),

I want to setup a dropdown control that lets you set the name. Once the name is selected I want the parameters startDate and FinishDate to be set to the corresponding column value from my dataset.

This in turn will be mapped to dataset parameters, which I am hoping will re-execute the direct query and update the dashboard accordingly.

I am stuck with the part in bold. Is this even possible? My end goal is to use a string based dropdown to set two dataset parameters which are used in a direct query to reduce data for my dashboard.

Thanks,
Ivan

1 Like
2

Hi @Ivan_Wahlrab

so you have something like

Name | startDate | finishDate
Max | 1/12/2023 | 12/7/2023
Kim | 1/1/2024 | 2/29/2024
Sue | 3/1/2023 | 12/31/2024

and every name has only one entry?

BR

3

Correct, eventually if possible I may try move to multiselect, but I will have to revisit my custom query at that stage.

4

Can you link the name column to the user?

5

I can but there are multiple name columns per user.

6

maybe you can use a cascading filter, but it will work only with dimension fields and not with a date field.

grafik
grafik823×476 24.3 KB

But i am not 100% sure it will work.

1 Like
7

@Ivan_Wahlrab - Hello Ivan, Thanks for posting this interesting use case. I know EriK has already shared some valuable suggestions. I just wanted to share my 2 cents. I am not 100% sure if this is going to work since I haven’t tried it first hand. Please feel free to explore it.

Since you mentioned that you have a table with the mapping between the names and its dates, I was thinking if you can use parameterized direct query dataset with the query being something like below :

select * from primary_table
where parimary_table_date_column >= (select start_date from secondary_table where name = <<$name>>)
and primary_table_date_column <= (select end_date from secondary_table where name = <<$name>>)

Here name is the dataset level parameter that you define and you link that parameter to the table where you have that user name with corresponding dates present. so, now in your report/analysis you will only have a single dropdown prompt where user will select only the user name and because of the one to one mapping between the name and dates, the main dataset query should be able to fetch its corresponding dates. This is only going to work if you have a single row per user. Sharing the associated link as well regarding usage of direct query dataset parameters for your reference. Let me know if this helps!

1 Like