Forum Discussion
Dynamic queries
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
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/DQ%20Test%20MU.pbix?csf=1&web=1&e=cG7mtd
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?