topic Re: Report builder: (Null) values in query designer when [] brackets in column name in Report Server

Report builder: (Null) values in query designer when [] brackets in column name

SCWO — Thu, 04 Jul 2024 09:39:20 GMT

Hi,

I want to build a report with the report builder, connected to a PowerBi semantic model.

I realized, that when i try to add a dataset, some of my data values are shown as (Null) values. That happens with all data, when the column name contains [ ] brackets. (see image)

Does anyone know, how I have to change the query, so that the column and its values can be found?

Or is there any other solution that it will work and I can keep the [] brackets in the column name?

Thanks for any hint

Re: Report builder: (Null) values in query designer when [] brackets in column name

hackcrr — Fri, 05 Jul 2024 00:05:16 GMT

Hi, SCWO

The problem you are experiencing is that when connecting to the Power BI semantic model, the column names contain square brackets ( [ ]), causing (NULL) values to appear in Report Builder, which may be due to the way the Query Designer handles special characters in column names. Here are a few ways to resolve this issue:

Adding Aliases to Columns in a DAX Query
You can add aliases to columns in a DAX query to avoid the use of square brackets. The following is an example of how to add an alias to a column:

EVALUATE SUMMARIZECOLUMNS( 'fctEnergy'[Datum], 'fctEnergy'[Abriss [0/1]] AS Abriss, 'fctEnergy'[Einspeisung Gasturbine] AS Einspeisung_Gasturbine, 'fctEnergy'[Einspeisung K 07 [kW]] AS Einspeisung_K07_kW )

If possible, you can rename the columns in Power BI Desktop to remove the square brackets and then publish the updated model. This method ensures that all downstream users and tools that reference these columns do not encounter issues.

In Report Builder, you can create datasets with custom queries and rename columns within them. Here is how to define a query directly in Report Builder:
In Report Builder, go to Dataset Properties.
Select Query Designer and switch to Edit as Text mode.
Write a custom DAX query with aliased column names, similar to the following:

hackcrr

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

Re: Report builder: (Null) values in query designer when [] brackets in column name

SCWO — Fri, 05 Jul 2024 05:14:37 GMT

hackcrr

Thank you so much! That's my solution!

Great response, perfectly explained.

Re: Report builder: (Null) values in query designer when [] brackets in column name

d_gosbell — Wed, 10 Jul 2024 00:09:35 GMT

Note that you can also "fix" this by right clicking on the field and going into Field properties

And they you'll see that the Query Builder has inserted an extra closing square bracket ] - if you remove this the field should start working