topic SSRS Report in Power Query

SSRS Report

simona0808 — Sun, 12 Jun 2022 12:41:16 GMT

Hello everyone.

I am working on an SSRS report(not very complicated).

In a nutshell, I have one dataset(including 4 columns I want to display in the table in the report) and a second dataset called - Parameters including 2 columns( the 2 parameters I want to render the information).

I don't want duplicated values in the dropdown list with parameters so I am adding them in the query filed using the following command line of code: SELECT DISTINCT Name FROM iCustomerNames.

It works fine with one column. However I noticed if I add an extra column to the expression: SELECT DISTINCT Name, ProductName FROM iCustomerNames. the distinct keyword is not working anymore and the duplicate values are in the dropdown.

Does someone have an idea how can I fix this?

I tried creating 2 separate datasets for each of the parameters. It works but I am not sure if this is a good approach.

I will be very happy to hear your thoughts!

Re: SSRS Report

v-kkf-msft — Wed, 15 Jun 2022 02:57:52 GMT

Hi simona0808 ,

It is feasible to use two datasets for two parameters.

If you want to use one dataset, you can use VB code to remove duplicate filter values from parameter drop-down. Please refer to this article for the exact steps.

SSRS: Remove Duplicate Filter Values from Parameter Drop-down using VB code

Here are the results of my tests. Note that you should move the DummyNamesFilter parameter to the top of the NamesFilter parameter.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: SSRS Report

v-kkf-msft — Wed, 22 Jun 2022 03:12:08 GMT

Hi @simona0808 ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz