Create dynamic reports for individual values

 To create a dynamic report, you first need to write your SQL query. Then use the Get data feature in Power BI Desktop to connect to the database.

In this example, you connect to your database on SQL Server by following these steps:

  1. After you have entered your server details, in the SQL Server database window, select Advanced options.

  2. Paste the SQL query into the SQL statement box and then select OK. When the connection is made, the data is shown in the preview window.

  3. Select Edit to open the data in Power Query Editor.

Next, you create the parameter by following these steps:

  1. On the Home tab, select Manage parameters > New parameter.

  2. On the Parameters window, change the default parameter name to something more descriptive so that its purpose is clear. In this case, you change the name to SalesPerson.

  3. Select Text from the Type list and then select Any value from the Suggested value list.

  4. Select OK. A new query is shown for the parameter that you created.

Now, you need to adjust the code in SQL query to assess your new parameter:

  1. Right-click Query1 and then select Advanced editor.

  2. Replace the existing value in the execute statement with an ampersand (&) followed by your parameter name (SalesPerson), as illustrated in the following image.

  3. Make sure that no errors are shown at bottom of the window and then select Done.

Though you don't see a difference on the screen, Power BI ran the query.

  1. To confirm that the query was run, you can run a test by selecting the parameter query and entering a new value in the Current Value box.

  2. A warning icon might display next to the query. If so, select that query to view the warning message, which states that permission is required to run this native database query. Select Edit Permission and then select Run. When the query runs successfully, the parameter displays the new value.

  3. Select Close and Apply to return to the report editor.

Now, you can apply the parameter to the report:

  1. Select Edit queries > Edit parameters.

  2. On the Edit Parameters window, enter a new value and then select OK.

  3. Select Apply changes and then run the native query again.

    Now, when you view the data, you see the data for the new value that was passed through the parameter.

You can now create a report that displays data for one particular value at a time. More steps are needed to display data for multiple values at the same time.


Comments

Post a Comment

Popular posts from this blog

Azure built-in roles for tables

Azure RBAC provides built-in roles for authorizing access to table data using Microsoft Entra ID and OAuth. Built-in roles that provide permissions to tables in Azure Storage include: Storage Table Data Contributor: Use to grant read/write/delete permissions to Table storage resources. Storage Table Data Reader: Use to grant read-only permissions to Table storage resources. To learn how to assign an Azure built-in role to a security principal, see Assign an Azure role for access to table data. To learn how to list Azure RBAC roles and their permissions, see List Azure role definitions. For more information about how built-in roles are defined for Azure Storage, see Understand role definitions. For information about creating Azure custom roles, see Azure custom roles. Only roles explicitly defined for data access permit a security principal to access table data. Built-in roles such as Owner, Contributor, and Storage Account Contributor permit a security principal to manage a storage acc...
Read more

Explore Dataflows Gen2 in Microsoft Fabric

  In Microsoft Fabric, you can create a Dataflow Gen2 in the Data Factory workload or Power BI workspace, or directly in the lakehouse. Since our scenario is focused on data ingestion, let's look at the  Data Factory  workload experience. Dataflows Gen2 use Power Query Online to visualize transformations.  1. Power Query ribbon Dataflows Gen2 support a wide variety of data source connectors. Common sources include cloud and on-premises relational databases, Excel or flat files, SharePoint, SalesForce, Spark, and Fabric lakehouses. Then there are numerous data transformations possible, such as: Filter and Sort rows Pivot and Unpivot Merge and Append queries Split and Conditional split Replace values and Remove duplicates Add, Rename, Reorder, or Delete columns Rank and Percentage calculator Choose Top N and Bottom N You can also create and manage data source connections, manage parameters, and configure the default data destination in this ribbon. 2. Queries pane The ...
Read more

Select and configure an appropriate method for access to Azure Blobs

Azure Storage supports using Microsoft Entra ID to authorize requests to blob data. With Microsoft Entra ID, you can use Azure role-based access control (Azure RBAC) to grant permissions to a security principal, which may be a user, group, or application service principal. The security principal is authenticated by Microsoft Entra ID to return an OAuth 2.0 token. The token can then be used to authorize a request against the Blob service. Authorization with Microsoft Entra ID is available for all general-purpose and Blob storage accounts in all public regions and national clouds. Only storage accounts created with the Azure Resource Manager deployment model support Microsoft Entra authorization. For optimal security, Microsoft recommends using Microsoft Entra ID with managed identities to authorize requests against blob, queue, and table data, whenever possible. Authorization with Microsoft Entra ID and managed identities provides superior security and ease of use over Shared Key author...
Read more