Link to report with values for query parameters?

[SamTrexler]

Is it possible to pass query parameters in the URL string when using a Web URL link? I want to use query parameters (which can be applied in the generated SQL sent to the database), not filters (which filter the rows shown after the data is retrieved). The data is much to massive to retrieve it all and filter it after the fact.

I have a report that allows a user to specify various query parameters and refresh the data, in DirectQuery mode with SQL Server. The query parameters are applied by filtering on columns in the M query, so that the database restricts the rows returned based on the values in the query parameters. That works great in Desktop for users to find the rows they want in a mountain of data, but not in Service because the user cannot set the query parameters.

However, I'd like to call this report from other reports in Service, using a Web URL link and specifying the values for the query parameters. That would let me use this report to show the detail rows that support a given row in the "master" reports. So far, I have not been able to get the report to respond to a query parameter name in the URL.

Is there a way to do this?

Thanks,

Sam

[sdjensen]

Hi @SamTrexler,

Perhaps these youtube videos from 'Guy in a cube' can help you?

Dynamic filtering with Power BI: https://www.youtube.com/watch?v=EXObcA9G9Vw

Dynamic Filtering with Power BI and Analysis Services: https://www.youtube.com/watch?v=ywaTxQKchx8&t=524s

Dynamic Power BI reports using Parameters: https://www.youtube.com/watch?v=iiNDq2VrZPY

[SamTrexler]

Hi @sdjensen,

Thanks for the reply. I have already watched these videos, and in each case the result is filtering the results retrieved by the query. As I tried to explain, I want to set the value of a query parameter which I can then use in the query editor to filter the rows using M language. That way, the database returns just the necessary rows and I don't have to retrieve all 37 million rows to filter it down - which won't work anyway, it's too big a result set (limit 1 million rows for DirectQuery).

Since the query parameters are actually listed as "queries" on the left pane of the query editor, I have tried using the technique in these videos by subtituting the name of the query (parameter) for "<table>/<column>" in the URL string, but this doesn't seem to work.

Is there any way to get this to work, or any other way to set the value of a query parameter when calling the report?

Thanks,

Sam

[sdjensen]

With your explanation it's a bit unclear to me how you want your users to filter the query. The last video I linked actually use values specified in an excel sheet to filter the query against the source, hence this approach will not return all your 37 million rows. In the video he uses Excel to create a list of values that he wants to use as a filter, but it might as well be another source.

In the video Patrick gets his data by calling a stored procedured, but it could just as well be a normal SQL query. I have build many models using parameters in my querys hence I build standard models for Dynamics NAV customers and by using parameters I only have change a few values and the customer is up and running.

[SamTrexler]

Hi @sdjensen,

Thanks for sticking with this and trying to figure it out. Here's a simplified version of what I'm doing.

Consider a table EVENTS with five columns:

    StationID             varchar(10)

    CustomerID          varchar(50)

    EventDate            datetime

    EventType             varchar(10)

    Outcome               varchar(5)

There are 37 million rows in this table, containing all of the events that occured over 5 years at 10,000 stations.

I am in the pilot phase of implementing analysis and reporting on this data in Power BI, and have created a summary report that displays a tabular visual showing the count of Events by Station, EventType and Outcome for a two-year period. This report has a date slicer to limit the range of dates and list slicers to choose the particular EventType, Outcome  (e.g., Pass, Fail, NA, etc.) and the Station they are interested in. The result is a list of Stations, EventTypes and Outcomes, and the count for the range of dates. Very simple example. (My actual report is much more than that, and has several visuals to summarize on various columns.)

Currently, I have a separate detail report that has imported all of the detail rows for a given quarter of the year - that's all that will fit in the 1GB limit of a PBIX. So if the user finds something on the summary report that interests them, they need to open each of the detail reports for the date range of the summary report (up to 8 detail reports for the 2-year range) and repeat the process of selecting the filters to find the data, then somehow try to combine what they're seeing in all those reports. Sometimes all the results are small enough that they can export them to Excel and combine them there - if it doesn't result in too many rows for a worksheet. Clearly, this is way too cumbersome and is not something we want to roll out in production.

I also have a detail report that uses DirectQuery against the SQL Serever database (so it can retrieve any combination of data), but it only works in Desktop because it relies on query parameters. The user presses Edit Queries -> Edit Parameters and enters the value for StartDateParam, EndDateParam, StationParam, EventTypeParam and OutcomeParam, and then refreshes the report. These parameters are used in M to filter the rows, and the values of the parameters are passed to the database, which retrieves the desired rows. This is great, but won't work in Service where most of the users live.

In the summary report they can't use "See Data" or "Export to Excel" to see the detail because the data is often too large for the limits of those features. What I am trying to do is create a column in the summary report with data type Web URL that the user can click on to call the detail report and show the detail rows that make up that total. For example, if they click on the link icon on the row for Station 57, EventType "Start" and Outcome "Fail" the detail report should show the individual events at Station 57 whose EventType was Start and Outcome was Fail. 

So the scenario is that the user would use the summary report to find the summary rows of interest - using a wide variety of visuals focusing on different operational areas - and click on the link to open the detail report and see the detail rows of data. In other words, I need to implement a "drill through" sort of capability against a SQL Server database.

I've got this Web URL link working if the detail report retrieves a smallish set of records, say 50,000 to 100,000, and the results desired can be obtained by applying a filter to the retrieved data. That's what Patrick demonstrates, and it works great. However, it requires bringing all of the data into Power BI Service - which is impossible with 37 million rows.

What I need to do when they click on the Web URL link is construct a URL that will set the value of the query parameters in the detail report - not a filter on the rows retrieved based on the value in the column.

And I have everything working, except setting the values of the query parameters in the detail report.

Does that make sense? And is there any way to do it? Or is there another approach that might deliver what I am trying to do?

Thanks again for your help.

Sam

[SamTrexler]

It looks like the new "drill-through pages" feature announced at the Data Insights Summit will do exactly what I need, and it's due "in the next quarter". 

Does anyone have a solution or work-around until that feature becomes available (and goes to GA)? I'll probably be implementing these reports before that, and need to implement something like this if at all possible.

Thanks in advance.

Sam

[vmakhija]

@SamTrexler 

I would like to know what you ended up doing eventually?

Are you using the new drillthrough across pages feature introduced recently?

Or did you use the Service URL filters explained in below link (although I haven't tried these with Direct Query yet) -

https://docs.microsoft.com/en-us/power-bi/service-url-filters

Regards

[SamTrexler]

We are in the process of implementing cross-report drill-through, even though it's only recently introduced and still in preview. It lloks like it will do exactly what we need, and works great in our early tests.

Regards,

[v-jiascu-msft]

@SamTrexler

Hi,

As far as I know, this isn’t possible by now. We can’t manipulate dataset in the Power BI Service. Maybe you can vote this idea:https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17128360-passing-parameters-via-u...

Best Regards!

Dale

[SamTrexler]

Hi @v-jiascu-msft

I have voted for the idea, it's exactly what I need. And I commented on the idea why it is so useful.

I have a large number of summary reports and visualizations and I would like to call a single "detail" report that retrieves the supporting detail rows for any of these summarizations.

I already have such a "detail" report that works in DirectQuery mode to retrieve a subset of the 37 million rows in the database. But it uses query parameters, since the entire table is too big to retrieve via DirectQuery.

And since we can't set query parameters in Service, we can't run the report there - the user has to be sophitiscated enough to run Desktop, and probably needs a Pro license. This greatly limits the target audience that could make use of this report.

However, if I could pass query parameters in a web url field, I would be able to use this "detail" report to provide the information supporting a given summary and make that available to a wide audience.

I'm glad to see you have a similar requirement. Hopefully others will see this idea and vote it up as well.