Forum Discussion

jvandyck's avatar
jvandyck
Helper IV
4 years ago

Dynamic queries

Hi

 

I have a table with a number of flags, called "Flags". This table contains a number of basic flags, but also a number of flags based on a combination of basic flags.

An example is loaded in the table indicator. This table has 2 columns. The first one is the name of the indicator and also occurs in the flags table. The second column is a select statement which could bring back the correct records/columns from the flags table.

 

In my report I would like to show a list of indicators and the indicator selected should dynamically trigger the query from the indicator table which I can then use to show on a report.

The number of indicators will grow and this way I can create one dynamic report covering all indicators.

I have already tried with a parameter, but I cannot get it working. 

Thank you in advance for your help!

I have created a mockup here: https://acerta-my.sharepoint.com/:f:/g/personal/joos_van_dyck_acerta_be/EhIwpfWtXJpHrjnWYGOfIc8BH2Lg9oz89Y8sQ7mDAyYNDw?e=d4sPeB

Need Help

5 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    jvandyck  will the SELECT statement always contain same set of columns per indicator?

    SELECT [tf_createdate],
       [tf_jobid],
       [tf_source],
       [tf_sourcefilename],
       [tf_partition],
       [nk_odn_iden_no],
       [nk_pty_iden_no],
       [fl_npsn_adr_email_juist_patroon],
       [fl_nt_npsn_adr_email_ingevuld],
       [fl_nt_npsn_adr_email_juist_patroon]
FROM   [BI_Temp].[Draft].[jvd_onderneming_ckb_base_flag]
WHERE  [fl_nt_npsn_adr_email_juist_patroon] = 1
        OR [fl_nt_npsn_adr_email_ingevuld] = 1
    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      jvandyck  technically it is possible.

       

      But how painful the development of that would be, depends on how many different indicators you have.

       

      To the best of my knowledge, you can create Dax measures and viz for each indicator (slicer selection) and save them as bookmark. Depending on what users select, you can connect those bookmarks to each indicator (slicer selection) and which is how you can enable generating diffrent query as per each indicator selection.

       

      But if you had the same set of columns regardless the indicators, where the only difference was the filtering, e.g

       

       

       

      WHERE  [fl_nt_npsn_adr_email_juist_patroon] = 1
        OR [fl_nt_npsn_adr_email_ingevuld] = 1

       

       

       

       

      you could have created Dax measures and a SWITCH stament to hook up that to the slicer selection without creating bookmarks.

       

       

       

      • jvandyck's avatar
        jvandyck
        Helper IV
        4 years ago

        Just to explain....there will be a number of indicators. The exact number I do not know upfront.

        Each indicator will be based on a number of flags. The number of flags linked to an indicator can range from 1 to x. Right now all the indicators and flags are in one table as separate columns. If I would go for the bookmark solution, the number of rows would explode, and it would require work each time indicators and flags are added. The row explosion would be because I would have to unpivot all the flag/indicator columns