How to hide a row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

How to hide a row

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-27 : 04:57:38

I have a table in reporting services layout and I tried adding the following expression under the visibility tab/ intial visibility:
=iif(Fields!UserCount.Value = 0, true, false)

This hides the data but the rows still appear. How can I hide rows?

The tables looks like this:
Database Name Total Number of Users
=Fields!DatabaseName.Value =Fields!UserCount.Value

I would like to hide or not return the row if the users count for the database is zero.

Here is the stored proc:

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[User_Count] Script Date: 08/27/2008 17:50:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[User_Count]
@CombinedName varchar (150),
@GroupName varchar (50),
@PermissionName varchar (50),
@FeatureDescription varchar (100),
@DBName varchar (100)

AS
---SET NOCOUNT ON
declare @sql varchar(max)
select @sql=''
select @sql=@sql+ 'SELECT COUNT(UserDetails.UserID) AS UserCount, '''+name+''' AS DatabaseName
FROM '+name+'.dbo.UserDetails INNER JOIN
'+name+'.dbo.UserGroup ON '+name+'.dbo.UserDetails.UserID = '+name+'.dbo.UserGroup.UserID INNER JOIN
'+name+'.dbo.GroupDetails ON '+name+'.dbo.UserGroup.GroupID = '+name+'.dbo.GroupDetails.GroupID INNER JOIN
'+name+'.dbo.GroupFeatures ON '+name+'.dbo.GroupDetails.GroupID = '+name+'.dbo.GroupFeatures.GroupID INNER JOIN
'+name+'.dbo.FeatureDetails ON '+name+'.dbo.GroupFeatures.FeatureID = '+name+'.dbo.FeatureDetails.FeatureID INNER JOIN
'+name+'.dbo.GroupPermissions ON '+name+'.dbo.GroupDetails.GroupID = '+name+'.dbo.GroupPermissions.GroupID INNER JOIN
'+name+'.dbo.PermissionDetails ON '+name+'.dbo.GroupPermissions.PermissionID = '+name+'.dbo.PermissionDetails.PermissionID
WHERE UserDetails.LastName + '' '' + UserDetails.MiddleName + '' '' + UserDetails.FirstName IN (Select Param From fn_MVParam ('''+@CombinedName+''','','')) AND GroupDetails.GroupName IN (Select Param From fn_MVParam ('''+@GroupName+''','','')) AND PermissionDetails.PermissionName IN (Select Param From fn_MVParam ('''+@PermissionName+''','','')) AND FeatureDetails.Description IN (Select Param From fn_MVParam ('''+@FeatureDescription+''','','')) AND UserDetails.IsDeleted = ''False'' AND '''+name+''' IN (Select Param From fn_MVParam ('''+@DBName+''','',''))
' from sys.databases where name='VC' or name like 'VCA%'

Create table #t([UserCount] int, [DatabaseName] varchar(50))
Insert into #t

exec(@sql)
---Print @sql

Select [UserCount], [DatabaseName]
from #t

Drop table #t

---SET NOCOUNT OFF

Return

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-27 : 05:03:43

So it seems you can right click on a row, select properties and then under properties > visibility > hidden select expression and then enter:
=iif(Fields!UserCount.Value = 0, true, false)

This bit Fields!UserCount.Value = 0 would be replaced with your field and expression:)

This works well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 05:29:09

You have same property available also from properties window

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources