Paginated Report - Help With Parameters

[lg1551]

I have a basic table from a PBIX which I have brought into a paginated report that I created in Report Builder in the Power BI Service. I have now linked a paginated report visual to that report in the service. All is well here.

I am having trouble finding guidance on how to add a parameter to the top of the report so it can be filtered. The parameter would be one of the dimension columns from the table and paginated report visual. I'm not finding anything anywhere that gives me a straight forward step-by-step on how to do this. I see many nuanced videos on YouTube and elsewhere but nothing that gives me the result I want. I would think it would be as simple as adding that field to the data set in the Report Builder as a parameter.

I've been able to get it to appear at the top of the report, but it doesn't filter the table when I click "View Report". What I am doing is very straight forward and uncomplex so I just need some help to get pointed to the right place to learn how to do this.

My paginated report is comprised of about 10 columns all from the same table. I simply want one of those dimension columns to be used as parameter to filter the report by so the user can interract as they wish.

[lg1551 · Accepted solution]

I got this figured out. I had pasted the query for the paginated report from the Power BI desktop query in the performance analyzer. That creates a completely different DAX query than if you add your model and drag and drop your columns/measures into the query designer from scratch. Once I did this, the parameters were fine. I did have to include an "ORDER BY" at the end of the query because it doesn't alphabetize them.

[rnola16]

I'm using ODBC as a data source for a Teradata db and trying to biuld paginated reports using an existing sql from a template. Here is a sample query, unless I put in query parameters the query runs against billions of records, I looking to see a way I can put query parameters for users to input of their choice.

SELECT
    r.reserved_id,
    t1.summary_id,
    t2.summary_id
FROM 
    reserved r
    INNER JOIN (select a.summary_id, a.num from tests a where a.date = (@ StartDate) ) t1 ON t1.summary_id = r.reserved_summary_id
    INNER JOIN (select b.summary_id, b.claim_id from betatests b where b.claim_id = (@ Claim_id) )t2 ON t2.summary_id = r.reserved_for_summary_id

How can I use this custom sql and include query parameters ?

[bradsy]

I believe teradata expects a "?" as the parameter. This article, while old, still applies to how queries work. https://assets.teradata.com/resourceCenter/downloads/WhitePapers/SQL%20Server%202012%20Reporting%20S... 

[bradsy]

One of these links might help you. You need to add the parameter to the RDL, eithe a query parameter to filter data as it comes in our a report level parameter to filter data in the report.  Then you need to bind a field from the PBIX dataset to that parameter. Some pictures and instructions in third link.

https://learn.microsoft.com/en-us/sql/reporting-services/tutorial-add-a-parameter-to-your-report-rep...

https://learn.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-parameters

https://learn.microsoft.com/en-us/power-bi/visuals/paginated-report-visual

[lg1551]

I found my problem. I used the COPY QUERY from the Desktop table I was replicating to get the DAX for the Paginated Report in the Report Builder. This gave it a very different syntax than dragging and dropping the columns and filters in one by one. Once I did it all manually it worked fine. But now my problem is that my parameter list is not sorted alphabetically. When I run the report, my paramter drop-down appears, but the locations are sorted in some random order which make no sense. I tried adding an "ORDER BY" to the query but I got a "Cannot Connect to Model" error, which is odd because it runs fine otherwise. Still digging and scratching my head.