Show table on expression using iif and countrows

Madsing
Madsing used Ask the Experts™
on
Hi,

I have a table inside a SSRS report. I have a problem writing the correct expression regarding the visibility condition.

I would like to set a condition that:

IF the column in the dataset contains more than one rows that has "DK1" in fiel valued I would like to show the table.

=iif(Countrows(Fields!DK1_DK2.Value = "DK1") > 1, TRUE, FALSE)

But with the above expression I get the error: The expression has a scope parameter that is not valid for an aggregate function.

Can you help out here?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SThayaTechnical MAnager

Commented:
i think you  have applied the expression inside the text box properties .


do the below

1. take the count from the different data set .name called "CountDataset"

2. select the entire table----> properties ....>  select Hidden  ---> set the expression

like


=iif(Sum(Fields!Count.Value, "CountDataset")>0 ,TRUE,FALSE)


this will work out

Author

Commented:
I am applying the settings to the Tablix (matrix) properties. If you select hidden you dont get the option to type in an expression. So I have selected the "show or hide based on and expression".
 

I need to check the dataset if there are any records with the value "DK1" in the field called DK1_DK2. If there are any records with the above criteria I need to show the matrix otherwise hide it.

So you contribution wont solve it.

Commented:
Have you defined the group. Since it is a aggregate function, you can't use it without group.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I have tried to change it to:


Set expression for: Hidden
=IIf((Count(Fields!DK1_DK2.Value = "DK1") < 0),True,False)

and it works I have not defined a group!?

Commented:
oh great. Thanks for sharing.
Project Leader
Commented:
First, parameter for CountRows function is "Scope" not a condition.
Second, if you return TRUE from your expression, your table will be hidden.

You mentioned,
IF the column in the dataset contains more than one rows that has "DK1" in fiel valued I would like to show the table.

Please try below expression:
=iif(Sum( IIF (Fields!DK1_DK2.Value="DK1", 1, 0)) > 1, FALSE, TRUE)

You can also simplify the expression as below:
=Sum(IIF(Fields!DK1_DK2.Value="DK1", 1, 0)) <= 1

-Harish

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial