Forum Discussion
Paginated Report : numeric parameter - issues with decimals
Hi,Pops1997 ,I am glad to help you.
I think your guess is probably right.
Also I have a possible guess about the problem you are experiencing.
You describe that when an expression is used, selecting one value is queried, but when multiple values are selected only the first value is returned
Like this.In fact I noticed that this may be due to the way your expression is written
=replace(Parameters!KPIMTCredit.Value(0), ‘.’ , ‘,’)
Parameters!KPIMTCredit.Value(0) will only return the first value that
You have selected .Value(0), which represents the first value in the list, and .Value(1) which represents the second value in the list
Your expression only modifies the first data in the filter condition so even if you replace ‘.’ is replaced with ‘,’ the only result that will actually be successfully filtered in the paginated reports is the first one (because you have limited the expression to index = 0).
So there will be problems when the user selects multiple values. To handle multiple values
You can try removing the subscript ‘(0)’ from the expression=replace(Parameters!SECTION.Value,',','~')//When selecting more than one value you need to use the Join function, this way of writing will report an error
After I wrote it correctly as follows:=replace(Join(Parameters!SECTION.Value, " "), ",", "~")The replace function takes a string as input.
In order to work with multiple values, you need to convert the array to a string first. You can use the Join function to concatenate all the values in the array into a single string.
But this way is not always possible in paginated reports, because paginated reports automatically add ‘,’ between multiple parametersI think creating a new calculated column for the table in Power BI Desktop and using the data from the new calculated column as a slicer field is a much more convenient and simpler solution, and it doesn't require you to modify your paginated reports too much (modifying paginated reports can easily be problematic).
You can use the fotmat function in Power BI Desktop to change the decimal point data to comma text, and use this text column for filtering (placing it as a slicer option in the paginated reports visual) instead of using the real data with the original decimal point.
URL:
FORMAT function (DAX) - DAX | Microsoft LearnModify the filtered data in the paginated reports to text type to ensure that the data types match.
It ensures that the field types on both sides are text types and that there are commas instead of decimal points in the text.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,Pops1997 .I am glad to help you.
According to your description, you are having problems displaying your report in PBI when the parameter format is in decimal form.
By PBI you mean Power BI Desktop (using paginated reports visual in Power BI Desktop, i.e. embedding the paginated reports you created in pbix reports).
Or I have a problem with the parameter display after publishing the paginated reports to Power BI Service.
Could you please tell us in detail what you did when you had the problem (what actions you performed and what settings you used)?
In Power BI Report Builder
Here are my suggestions for you
1. You need to make sure that the data types are as consistent as possible, no matter where you are displaying the data in your paginated reports.
Have you checked the language formatting settings in your browser (not just in Power BI Service/Desktop)?
You mentioned that setting the language format doesn't seem to be working.
Here's my test, I'm in Power BI Desktop and Power BI Service doesn't seem to find the ability to directly modify parameters in paginated reports
In Power BI Desktop
In Power BI Service.
Whereas in Power BI Report Server you can manage the parameters in paginated reports, the Data type cannot be modified.
Do the parameters you use participate in subsequent calculations. If the parameter is only used as a filtering condition, you could try using an expression in the paginated reports to convert the decimal data corresponding to the problem to a text format. Subsequently use the text-type parameters for query filtering.
For example, use CStr(Parameters!YourParameter.Value) to convert the parameter value to a string.
Generally there are no problems with presenting data of the wrong data type as text.
URL:
Data types in expressions in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft Learn
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ! Thank you for your help. I tried your solution to convert the parameter in string, doesn't work. I'll try to be more clear onto the issue (which happens on Desktop AND Service).
Here's my filter on PBI :
And here the properties of the field MTCredit (sorry for french) : decimal number.
Then, there is my parameter in my paginated :
Now to the demo :
- When I filter on a round number (ending with .00), it works.
My paginated :
But when I filter on a number with decimals :
It does not work :
Important information I left out (sorry about that) : I really think it's about the separator my PBI send to the Paginated.
I think it sends a point where the paginated needs a coma. Again, France.
I know that cause I tried replacing inside my parameter the points with comas and convert to text and it worked-ish. It didn't work as needed because it prevented multiple values, which I don't understand tbh. Let me show you :
Doesn't like different format but ok :
AND NOW look at this!
I select one number with decimals :
and it works, here's the result in my paginated :
BUT now when I select two numbers :
It only displays one when it should display two :
So I'm pretty sure it's about the decimal separator that's not configured the right way but I feel like I tried everything and I'm at loss.
I tried converting the data with regional parameters : fr-FR because FR uses coma as decimal separator, didn't work either..
ETA : I also tried changing the language in my paginated report in en-US, hoping he'd accept . as separator but no luck.
Hi,Pops1997 ,I am glad to help you.
I think your guess is probably right.
Also I have a possible guess about the problem you are experiencing.
You describe that when an expression is used, selecting one value is queried, but when multiple values are selected only the first value is returned
Like this.In fact I noticed that this may be due to the way your expression is written
=replace(Parameters!KPIMTCredit.Value(0), ‘.’ , ‘,’)
Parameters!KPIMTCredit.Value(0) will only return the first value that
You have selected .Value(0), which represents the first value in the list, and .Value(1) which represents the second value in the list
Your expression only modifies the first data in the filter condition so even if you replace ‘.’ is replaced with ‘,’ the only result that will actually be successfully filtered in the paginated reports is the first one (because you have limited the expression to index = 0).
So there will be problems when the user selects multiple values. To handle multiple values
You can try removing the subscript ‘(0)’ from the expression=replace(Parameters!SECTION.Value,',','~')//When selecting more than one value you need to use the Join function, this way of writing will report an error
After I wrote it correctly as follows:=replace(Join(Parameters!SECTION.Value, " "), ",", "~")The replace function takes a string as input.
In order to work with multiple values, you need to convert the array to a string first. You can use the Join function to concatenate all the values in the array into a single string.
But this way is not always possible in paginated reports, because paginated reports automatically add ‘,’ between multiple parametersI think creating a new calculated column for the table in Power BI Desktop and using the data from the new calculated column as a slicer field is a much more convenient and simpler solution, and it doesn't require you to modify your paginated reports too much (modifying paginated reports can easily be problematic).
You can use the fotmat function in Power BI Desktop to change the decimal point data to comma text, and use this text column for filtering (placing it as a slicer option in the paginated reports visual) instead of using the real data with the original decimal point.
URL:
FORMAT function (DAX) - DAX | Microsoft LearnModify the filtered data in the paginated reports to text type to ensure that the data types match.
It ensures that the field types on both sides are text types and that there are commas instead of decimal points in the text.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous !
Big Thank you for your help !!
So I tested different versions of your last suggestion, and I finally succeeded ! Here's the process :
- I duplicated my column in en-US format (not what I expected I'll be needing), with points as separators. Text.
= Table.AddColumn(Source, "MTCredit_US", each Number.ToText([MTCredit], "0.00", "en-US"),type text)
Then, I added the slicer to my report, while also keeping the old one, because I want my final users to select values by numeric order and non alphabetical.
"Credit" is my original parameter with numeric value, that's what the final user will use to select values.
"Credit US" becomes my new parameter (text) in the paginated, I'm going to hide it in the report.
And it works wonders !
Heavy process but I'm so relieved !
