SSRS: Can I exclude Report rows based on a Report calculation?

0
votes

I inherited a report where the Grouping and Aggregation is done in the Report. The Dataset is a SQL Server 2008 Query.

This raw Data:

ID           Budget Amount    Spent Amount
A              1,500            20 
A              1,500            20
A              1,500            60
B              2,000            50
B              2,000            75
B              2,000            75

Shows like this on the report:

ID           Budget Amount    Spent Amount
A              1,500            100 
B              2,000            200

I added a calculated field to the Report to get this:

ID           Budget Amount    Spent Amount      Balance
A              1,500            100              1,400
B              2,000            200              1,800

Can I do something in the report to exclude rows that have a balance less than 1,500? If not, what are my other options?

  ****************************

. . . the next day . . .

Having tried suggestions to add a Filter Exp. to the Tablix and a Row Visibility Exp. to the row - unsuccessfully - here is what I found:

My actual report has two Row Groups on the row for which I want to limit the rows that show.

I tried the same suggestions made in this thread on another report [with no Row Groups] ... and the Tablix Filter and the Row Visibility both worked fine.

I have to assume that the fact that my 'problem' report has two levels of Grouping is the cause of the Expressions not working.

BUT . . . is there a way to overcome this?

  ****************************

I just realized [while researching this same issue elsewhere] that the report I am working with does not have a 'Details' row.

What I was thinking of as a Details row is not. There is no Details Row in this report. I have been trying all your suggestions on a row that is not a Details row.

2
reporting-services
In your example above do you mean to remove the row with ID A? - JonH
You can put a row expression to hide the row if you are not interested on it. Highlight your row and in the expression place this: =IIF(Fields!MyBalance.Value < 1500, True, False) - JonH
The Balance is not a Field in the Dataset. It is an Expression in the report field. - Talay
Change your if to handle the actual label, remember the textbox is a label / property. - JonH
If it is not a calculated field in the dataset you have to use the the textbox label as Jon says or use the expression to get the balance. =IIF(Fields!Budget.Value - Fields!Spent.Value < 1500, True, False) for the Row Visibility property. - alejandro zuleta

2 Answers

0
votes

You can reference a textbox field in your report, per the comments in the thread:

=IIF(ReportItems!textbox1.Value <= 1500, True, False)

Throw this expression in the row details visibility section. Or you can even use the filters section to filter this data out. Tons of ways to do this.

0
votes

Thanks for all your suggestions. They helped me arrive at a solution.

As I said in my [edited] post, my report has no Details Row.

I was adding the expression to the Visibility Property of the Row on the report Tablix which was not a Detail row.

After reading the topic here:

https://dba.stackexchange.com/questions/53727/how-to-hide-rows-in-ssrs-report

I did this:

-> Right-clicked the last/lowest of the Row Groups in the Groups pane,

-> Group Properties,

-> Visibility,

-> Show or hide based on an expression &

-> Entered this expression [does not relate exactly to my example in the post]:

=IIF((Avg(Fields!AMOUNT_1.Value) - Sum(Fields!AMOUNT_2.Value)) >= 0, True, False)

It is now working exactly as needed.

Thanks for all your input!