topic Run multiple queries during drilldown and use selected elements from visual in SQL-where clause in Report Server

Run multiple queries during drilldown and use selected elements from visual in SQL-where clause

Anonymous — Wed, 27 Jul 2022 11:57:17 GMT

We are trying to use Power BI Desktop / Power BI report server to display results from a big data
warehouse realized in postgres. I am working on views to be used with Power BI, either with <DIRECT QUERY>
or <IMPORT MODE>.

Now my two questions are :

1) Is it only possible to run database Queries if one opens a power bi report or is it also possible
to run additional queries later during drilldowns from one page in a report to another page ?

2) During drilldown people normally select an element in a visual lets say with transaction monitoring
results. Then I would like to use the selected element for example in a matrix visual as input for
the where clause of another query which gives results of the next drilldown level. Is it possible ?

Example for better understanding:

I use query

SELECT * FROM VIEW_WITH_WEEKLY_RESULTS .

Then someone select the visual element with

WORKFLOW = Workflow_7
and
ISO_WEEK_OF_YEAR = '2022-26'

Now during drilldown I would like to run another SQL-statement like :

SELECT * FROM VIEW_WITH_DAILY_RESULTS
WHERE
WORKFLOW = 'Workflow_7'
AND
ISO_WEEK_OF_YEAR = '2022-26'

The idea is to reduce number of rows which will be returned from the query.
Also there are a lot of dimensions, where drilldown shall be possible

Currently I am using Views like

VIEW_WITH_WEEKLY_DAILY_RESULTS
VIEW_WITH_WEEKLY_DAILY_HOURLY_RESULTS

which is very ineffective and gives bad performance even in medium size test environments.

I would like to have Views

VIEW_WITH_WEEKLY_RESULTS
VIEW_WITH_DAILY_RESULTS
VIEW_WITH_HOURLY_RESULTS

VIEW_WITH_INSTANCE_DATA

but only the first view shall be loaded completely to the power bi report , the other ones shall be used only with
where clauses reducing the data. I tried to use parameters, but was not able to get them into the where-clause of a query. From my background I am a database guy, not a power bi specialist.

Re: Run multiple queries during drilldown and use selected elements from visual in SQL-where clause

lbendlin — Fri, 29 Jul 2022 02:03:51 GMT

Only possible in Direct Query mode.

Re: Run multiple queries during drilldown and use selected elements from visual in SQL-where clause

Anonymous — Fri, 29 Jul 2022 06:51:39 GMT

Hallo Ibendin,

thank your for your answer.

Can you point me to an URL, which describes how to do it or give me a short description how to do it?

Especially I would be interested to know, where I can configure something for each query like :

Query execution time = When drilldown from page a to page b occurs / with selection

For me it looks like somehow there is a builtin limitation for all queries

Query execution time = When report is opened

Maybe I am wrong and just do not know how to do it, because of my limited Power BI knowledge.

But if it would really not be possible that would be (from my point of view) a very serious Power BI report server missing functionality and we should think about staying with Business Objects ( which is more expensive but also powerful).

Is it possible with Power BI Desktop / Power BI Report server or do I need other components?

I played around with the parameters in "Transform data part" but was not able to get parameters into my queries.

Many thanks for your answers in advance,

Horst

Re: Run multiple queries during drilldown and use selected elements from visual in SQL-where clause

lbendlin — Fri, 29 Jul 2022 16:09:00 GMT

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Docs

Re: Run multiple queries during drilldown and use selected elements from visual in SQL-where clause

Anonymous — Mon, 08 Aug 2022 10:43:50 GMT

Thanks for the link to the m-query parameter article.
I am still busy trying to find out, if it will be useful for our special case.
Maybe I will come up with more specific questions or feedback if it solved our requirements.

Re: Run multiple queries during drilldown and use selected elements from visual in SQL-where clause

Anonymous — Wed, 10 Aug 2022 09:15:10 GMT

I came a bit closer to my goal creating an m-query for our postgres database using parameter, but the Postgres.Database function complains about a string, if it was produced with Text.Combine. What am I doing wrong here or is there a problem?

see details :

This m-query ,which has no syntax erros, gives the following error, it looks like the postgres DB does not like the string made of substrings and parameter :

But if I use a string which was not a result of text.Combine then it works, the following works.

But I need Text.Combine to get my parameter in.

What am I doing wrong here ?

If some of you gurus sees my mistake, please give me a hint.

Many thanks in advance.

Re: Run multiple queries during drilldown and use selected elements from visual in SQL-where clause

lbendlin — Wed, 10 Aug 2022 11:19:46 GMT

Text.From([P_ISO_Week_Of_Year])

Re: Run multiple queries during drilldown and use selected elements from visual in SQL-where clause

Anonymous — Tue, 16 Aug 2022 13:42:52 GMT

Hallo ,

recently I found some time to continue my evaluation of Power BI .

Your Text.From([Parameter]) gives an error see screenshot.

But when I encode my parameter values with single quotes like P_ISO_WEEK_OF_YEAR = '2022-30'
then the m-query code works.

Currently I am having problems getting values into the parameter dynamically.

There are two things :

* I cannot use 'BIND to parameter' for a column, because it is not available
in modelling columns -> Advanced part in my Power BI desktop for any table and any column, even
if I am using <Direct Query>.

* Also the "preview feature" is not available in my power bi desktop version under

File -> Options and Settings -> Options

It should be between <Diagnostics and Settings> and <Auto Recovery> according to some other posts but it is not there in my version.

So my questions are :

Is this "bind to parameter feature" not availabe for Power BI Desktop Version: 2.97.802.0 64-bit (September 2021) ?

Unfortunately I receive Power BI Desktop with company software distribution, and they are always a bit behind in versions.

Is this "bind to parameter feature" not availabe for Datasource = PostgreSQL 13 with mode = <Direct Query> ?

Why do I not have this "Preview option" in File -> Options and Settings -> Options ?

Am I doing something wrong, which you could correct ?

Many thanks for your answers in advance, Horst