SQL Reporting Services: visibility

Miklos_Hollender

Hello,

I'm currently learning reporting services and it seems fine, but one of the things I can't figure out how to do is the equivalent of Navision's CurrReport.SHOWOUTPUT: dynamically changing the visibility of a line.

Usually there is a Visible property and one can assign an Expression to set it, however, f.e. a row in a Matrix does not have such a property.

So for example if you throw a Matrix on Item Legder Entry table, showing stock (Location in columns, Item No. in Rows, Sum(Quantity) in matrix fields) how do you tell it to don't show those lines where the total quantity is zero i.e. no stock?

ara3n

Hello Miklos

You can change this in your query. So that you only get the data you need.
Here is a query that will do what you are asking.

Select * from
(
SELECT [Item No_], SUM(Quantity) AS Qty, [Location Code]
FROM  [KRONUS Canada$Item Ledger Entry]
GROUP BY [Item No_], [Location Code] 
) as ItemQtyByLocation
where ItemQtyByLocation.Qty > 0

Miklos_Hollender

Are you sure it has to be on the query level? I can imagine loads of other cases where dynamic visibility would be quote important - f.e. if you do a lot of calculations in the report and don't want to show the ones that result in 0.

Or maybe we just have to accept that the Table and the Matrix are specialized controls for special cases and a really flexible, dynamic report should rather use the List control (which is roughly equivalent to Navision's sections).

ara3n

Yes you can also change it in RS as well.

Here is how to.

Select the whole tableRow (by clicking the gray square to the left)

In the property windows you'll find a property called Visibility. Click the + sign and in the hidden property select expression.

In the exression add this code.

=(Sum(Fields!Qty.Value, "Navision_5") = 0)

the table name will be different in your case. You can find the exact value by clicking the datasets selection in the first column.


You'll have to do this to every row so that subtotals do not print as well.

kriki

ara3n wrote:

Hello Miklos

You can change this in your query. So that you only get the data you need.
Here is a query that will do what you are asking.

Select * from
(
SELECT [Item No_], SUM(Quantity) AS Qty, [Location Code]
FROM  [KRONUS Canada$Item Ledger Entry]
GROUP BY [Item No_], [Location Code] 
) as ItemQtyByLocation
where ItemQtyByLocation.Qty > 0

Wouldn't this be more readable, faster?

SELECT [Item No_], SUM(Quantity) AS Qty, [Location Code]
FROM  [KRONUS Canada$Item Ledger Entry]
GROUP BY [Item No_], [Location Code] 
HAVING SUM(Quantity) > 0

ara3n

Yes the above statement is better.

Miklos_Hollender

Thanks Ara3n,

but the whole reason I had to open this topic is that a MatrixRow object does not have a Visibility property - and this is strange because mostly everything does.

screenshot

Kriki,

yep, of course, I'm just trying to find the right balance between doing stuff in SQL and doing them in Reporting Services. And I think it's a right general rule that if something would be hard to add as a @parameter in a stored procedure then it belongs to the report (Actually, the advantage of this Common Table Expression-based approach is that it can be driven by a @parameter. But I still think it belongs to the report.)

ara3n

Hello Miklos

For MatrixRow Objects there is not visibility property, but what you are trying to do can be changed on the textbox object.

Right click on textbox object Fields!Item_
and select edit group.

on Visibility tab Select expression and add the following code.

=Fields!Qty.Value = 0