Power BI Report Builder

[lynnma]

To Whom It May Concern:

Note: (Heatlhcare Physicians Report)

I am trying to build out a Power BI Report, using Power BI Report Builder.  We are using Power BI Report Server ( ON PREMISES).  The report is simple, pulling in Physician Contact Information.  However, I have made several attempts at building a Parameter based on the Physician's Name.  When I build the parameter out, run the report, I select a Provider, then View.  The results are not a singleton record.  I get all Physicians Names. 

Attempts:

1.) Created Parameter in Power BI Report Builder.  Did not work.

           A.) The Different Report Parameter Properties I have tried:

                  i.) Select from one of the following options:

                         a.) Tried None

                         b.) Tried Specifiy Values

                         c.) Get values from a query.

None of part 1.) Worked.  Get Values from a query was close, but pulled all records.  Its supposed to only pull Physician's Contact Info based on the Name Selected. 

2.) Created Parameter in the SQL CODE: Delcared Parameter, and applied it in the Where Clause.  No syntex errors.

       A.) Created Parameter in PBI Report Builder no succcess.

Errors I have received in the past:

1.) ExecuteReader: CommandText property has not been initialized

2.) The report parameter 'SelectPhysName1' has a DefaultValue or a ValidValue that depends on the report parameter "SelectPhysName1". Forward dependencies are not valid.

Please provide solution(s).

Thank You.

Mark Lynn.

[Poojara_D12]

Hi @lynnma 

To create a physician contact information report in Power BI Report Builder with a parameter for selecting a physician’s name, follow these steps carefully. First, ensure you have a dataset that retrieves distinct physician names, which will be used to populate the parameter dropdown. Then, create a report parameter named SelectPhysName and set its available values to come from this dataset, ensuring it is a single-selection text parameter. Next, modify the main dataset query to include a WHERE clause that filters records based on the selected physician’s name, using @SelectPhysName as the SQL parameter. After that, go to the dataset properties and correctly map the report parameter to the query parameter to ensure filtering works as expected. If you encounter the "ExecuteReader: CommandText property has not been initialized" error, verify that your dataset query is properly assigned and includes the WHERE clause with the parameter. If you receive the "Forward dependencies are not valid" error, check that your parameter does not have a circular dependency in its available or default values. By following these steps, the report should correctly filter and display only the selected physician’s contact information.

[v-jianpeng-msft · Accepted solution]

Hi, @lynnma 

Let's follow my example to learn how to use parameters to filter datasets:

First, here's my dataset:

Method 1: Create a parameter based on the cource name:

An important step is to apply this parameter to dataset1:

The results are as follows:

Method 2, pass the parameters in the query:

The results are as follows:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lynnma]

Good Morning,

Thank you very much for the feedback.  I figured it out late afternoon yesterday.  The sources I was reading and viewing did not mention creating a second data set for the parameter.  This is the major piece I was missing.  The difference between what you posted verses what I completed is I did not create the param in the sql code and setting a condition in the WHERE clause.  I got it to work correctly without having to complete that part.  However, I did create a second data set and called DataParameter, then proceeded on building out the parameter.  

I will "Accept as Solution" what you have provided.  Thank you again for responding back promptly!

Very Appreciative,

Mark Lynn. 

[Vanchy_Liao]

Hi @lynnma ,

can you describe more specifically the structure you want?

e.g. 

1. create a parameter which get name from query.

2. shows the Physician's Contact Info based on selected name in the report.