Grouping tablix by Paramater is SSRS (#Error) and automatically adding (0) when selected in expression

1
votes

I hope this is not a stupid question, but I have searched everywhere and have tried everything.

I have a dashboard and would like to group the tablix (The dashboard is inside the tablix) by one of the Parameters (Consultant). There are a few Data sets(queries) in the report and all of the Parameters are filtered with IN in the where clause.

The problem I have is that when I go to the row group properties and select the Parameter in the expression, then it automatically adds a (0) at the end. If I take the (0) away then I get the error message:

the group expression used in grouping 'Group1' returned a data type that is not valid

I know the (0) is for getting the first value, but I am using Multi-valued Parameters.

I have tried one thing I found, but unfortunately it didn't work for me (SSRS Group By Parameter).

Edited:

This is to show you that there are multiple Data Sets(Queries) in this report

enter image description here

I have the dashboard in a tablix so that I can group for each Consultant, so when I choose 3 Consultant, I get 3 dashboards.

Expression used:

enter image description here

Then I get this error:

enter image description here

I have also tried using the CStr, but also no luck.

When I add the Parameter in any expression box it automatically put the (0) as below:

enter image description here

But then it doesn't use the parameter as I get an #Error where is should be the Consultant name.

I also used this option for page break but end up with graphs below each other:

enter image description here

This is what happens to the Charts(Sub Reports)

enter image description here

To give you an idea how the dashboard should look for each Consultant.

enter image description here

Regarding the other question I saw. I just tried exactly as they said but also no luck

I hope this isn't too much information. Just trying to help you help me.

Thank you!

UPDATE:

Parameter Properties:

enter image description here

enter image description here

enter image description here

enter image description here

1
sql-serverreporting-servicesdashboard
What exactly did you try from the other question? Show us the expression you are trying to use in the group, please. – R. Richards
It sounds like you need to JOIN the parameters, but without seeing what you are actually doing, it's hard to give you an answer. – SS_DBA
I have made the changes to the question. Hope this helps :) – Ruahl Behr

1 Answers

0
votes

Have you tried using the list tool to separate the sub reports by Consultant? A list acts like a container and will create whatever is inside for your grouping. You should also be able to apply a parameter to the list for filtering.