"View Report" runs into error when null is selected for a parameter of decimal data type

[Thomas225]

hi everyone,

I have created a paginated report connecting to an on-prem oracle database. In power query I use multiple parameters to dynamically change the sql statement sent to the database to extract data. Everything is working in power query, whether parameters have a value or are null (null value handling is done using m code). 

In the report design view I have created the same number of parameters with the same data type as in power query. Both are connected.

When I try to generate a report with all parameters having a value, everything works file. But when null is selected for a parameter, report generation runs into an error.

I can only solve this problem by changing all parameters to text data type, but then user input will not be validated and could cause a crash.

Does anybody know a solution?

Tom

[v-venuppu]

Hi @Thomas225 ,

Thank you for the Response..!!

Great to hear that you have found a solution to the problem.I would suggest accepting your approach as the solution so that it will be helpful for others in the community who might be facing similar issues and can address them quickly.

Thank you.

[Poojara_D12]

Hi @Thomas225 

You're getting errors in your paginated report when parameters are set to null because:

• Paginated report parameters must explicitly allow nulls (set this in parameter properties).

• Your SQL query must handle nulls using logic like:
  WHERE (:Param IS NULL OR Column = :Param)
  In Power Query, nulls work fine, but in the report, both the parameter and the SQL must be designed to handle them correctly. Don't switch to text just to bypass this—allow nulls properly and adjust your SQL filters.

[v-venuppu]

Hi @Thomas225 ,

Thank you for reaching out to Microsoft Fabric Community.

You are right.Paginated reports can run into issues when a null value is passed to a decimal parameter, even if your Power Query handles nulls correctly. The issue often comes from how the report builder/runtime interprets nulls for numeric types.

Instead of changing the parameter to text (which removes validation), you can keep it as decimal and do the following:

Allow null values for your parameter (check the box in the parameter properties).

In your Power Query (M code), continue handling nulls as needed.

In your SQL query or logic, ensure you're safely handling nulls with logic like:

WHERE (:Param IS NULL OR column = :Param)
If you're dynamically generating SQL in M, ensure the logic safely skips/handles nulls when building the query.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Thomas225 · Accepted solution]

hi @v-venuppu, @Poojara_D12,

I have found a solution to my problem which is to set all parameters created in power query to text data type.

And then in Report Design View I recreated the same parameters but with the desired data type. When connecting both parameters I used format function to convert the value of report parameters to text.

Before coming up with this solution, I did some invetigation to locate the exact part in my query which extracts data from the oracle database. I found that it is not the dynamic sql statement which causes the problem, but the part of my m-code which handles the null value.

This bug can be recreated with the following steps:

1. open a blank paginated report

2. add a new dataset

1. create a blank query to enter power query

2. create a parameter of datatype decimal (suggested values: any, current value: no value inserted

3. build the parameter into the blank query and convert the query to a table

3. load the dataset to get to the report design view

4. create a report parameter of the same data type, allow null value

5. connect both parameters in the dataset settings

6. create a text box to show the value of the table

when generating the report and the current query parameter value in power query is null you get:

1. null is selected for report parameter ----> no error, text box blank

2. number is inserted into the report parameter ----> no error, text box blank

when generating the report and the current query parameter in power query has a value you get:

1. null is selected for report parameter ----> error: The evaluation request is invalid.

2. number is inserted into the report parameter ----> no error, correct value is shown in the text box

This strange behaviour also applies to date/time and probably other data types too exept text data type.

Kind Regards