Dynamic queries

[jvandyck]

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/EhIwpfWtXJpHrjnWYGOfIc8BH2Lg...

[jvandyck]

No, the columns Will be different per indicator.

[smpa01]

@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]

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

[jvandyck]

I have added a better example, including the bookmark approach: https://acerta-my.sharepoint.com/:u:/r/personal/joos_van_dyck_acerta_be/Documents/Share/New%20folder...

However I would really like to make it more flexible. I would like to generate the flag query based on the KPI info datasource instead of bookmarks which contains the filter settings. Eg the query for KPI001 would be:

SELECT base.* , fl.*
FROM [DQ_Framework].[Draft].[ONDERNEMING_CKB_BASE] base
,[DQ_Framework].[Draft].[ONDERNEMING_CKB_BASE_FLAG] fl
where 1=1
and base.nk_ODN_IDEN_NO = fl.nk_ODN_IDEN_NO
and not (fl.[FL_PTY_UP_FMT_NM_INGEVULD] = 1 and fl.[FL_PTY_UP_FMT_NM_STARTS_BLANCK]=0 )

Ideally it should also be possible to use the kpi info table to indicate the columns in the select.

So what it comes down to is the following: I have KPI data which is a calculation based on flags. Both the KPI an the flags link to the base table, which contains customer master data. When In the report one selects a KPI, the report should show the KPI data as well as the flag data. The flag data shown is different for each KPI and I want to make this dynamic so I can easily add KPI's. Please let me know if you want me to explain further?

[smpa01]

@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