Hiding Empty Rows - 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

Development Tools

Reporting Services Development

Hiding Empty Rows

Author

Topic

GirlGeek
Starting Member

4 Posts

Posted - 2013-04-29 : 09:45:50

Hi, All.

New here and hoping somebody can help.

I am trying to edit a tablix in an existing report that uses a stored procedure for the dataset. I am new to both Reporting Services and Stored Procedures.

I believe the SP are dictating that the table will always show eight rows, even if the rows are empty. I would like to reduce this number to four or to only show when there is data in the row. Not having ANY luck with visibility settings (either shows all eight or nothing at all) and I have got to believe the SP must be changed to accomplish my goal.

This is the SP. Can anyone see where it is forcing the rows? I have tried commenting out, etc. to no avail. Thanks in advance!

@avcShipmentNO udt_ShipmentNumber
as
declare @nCount int
declare @fPackageTot float
declare @fWeightTot float
declare @cFlag char(1)
declare @fTotalPieces float
declare @fShipmentCOWeight float
declare @tblSBOL table
(vcCustomerPONumber varchar(30)
,fTotalPieces float
,fCOWeight float
,fTextLine varchar(100)
,cFlag char(1)
,fTolPieces float
,fShipmentCOWeight float
)
select @nCount = count(CONumber)
from FS_ShipmentHeader sh (readuncommitted)
left join FS_ShipmentCO sc (readuncommitted) on sc.ShipmentHeaderKey = sh.ShipmentHeaderKey
where sh.ShipmentNumber = @avcShipmentNO

select @fTotalPieces = sum(sc.ShipmentCOTotalPieces)
,@fShipmentCOWeight = sum(case ConvertPackageDetailToMetric
when 'Y'
then sc.ShipmentCOWeight * .454
else sc.ShipmentCOWeight
end)
from FS_ShipmentHeader sh (readuncommitted)
left join FS_ShipmentCO sc (readuncommitted) on sc.ShipmentHeaderKey = sh.ShipmentHeaderKey
left join FS_ShipmentCOText st (readuncommitted) on st.ShipmentCOKey = sc.ShipmentCOKey
cross join FS_ShippingConfig spc (readuncommitted)
where sh.ShipmentNumber = @avcShipmentNO

if @nCount > 8
begin
delete from @tblSBOL
set @nCount = 8
while @nCount > 0
begin
insert into @tblSBOL
(vcCustomerPONumber
,fTotalPieces
,fCOWeight
,fTextLine
)
values(''
,null
,null
,''
)
set @nCount = @nCount - 1
end
update @tblSBOL
set cFlag = 'Y'
,fTolPieces = @fTotalPieces
,fShipmentCOWeight = @fShipmentCOWeight
end
else
begin
insert into @tblSBOL
(vcCustomerPONumber
,fTotalPieces
,fCOWeight
,fTextLine
)
select sc.CustomerPONumber
,sc.ShipmentCOTotalPieces
,case ConvertPackageDetailToMetric
when 'Y'
then sc.ShipmentCOWeight * .454
else sc.ShipmentCOWeight
end ShipmentCOWeight
,isnull(st.TextLine1,'')
from FS_ShipmentHeader sh (readuncommitted)
-- SDR 53891. Removing the extra line during printing the line information.
join FS_ShipmentCO sc (readuncommitted) on sc.ShipmentHeaderKey = sh.ShipmentHeaderKey
left join FS_ShipmentCOText st (readuncommitted) on st.ShipmentCOKey = sc.ShipmentCOKey
cross join FS_ShippingConfig spc (readuncommitted)
where sh.ShipmentNumber = @avcShipmentNO
update @tblSBOL
set fTolPieces = @fTotalPieces
,fShipmentCOWeight = @fShipmentCOWeight
set @nCount = 8 - @nCount
while @nCount > 0
begin
insert into @tblSBOL
(vcCustomerPONumber
,fTotalPieces
,fCOWeight
,fTextLine
,fTolPieces
,fShipmentCOWeight
)
values(''
,null
,null
,''
,null
,null
)
set @nCount = @nCount - 1
end
end

select * from @tblSBOL
return 0

GO

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 09:52:52

You should be able to do this on the visibility settings of the rows by selecting the row outline (at left). But probably better to remove unwanted data from the stored procedure itself.

Looking at the stored procedure, perhaps the simplest way is to delete the rows that you don't want just before the final select. So for example, something like this:

........

set @nCount = @nCount - 1 
end
end

-- I don't know if this is the criterion for deleting; I am just showing an example
delete from @tblSBOL where vcCustomerPONumber is NULL

select * from @tblSBOL 
return 0

GO

GirlGeek
Starting Member

4 Posts

Posted - 2013-04-29 : 10:43:09

I have tried to hide these rows using the visibility settings everywhere and anywhere I can find them but it doesn't work. It either hides everything or nothing.

I have no idea how to edit this Stored Procedures as I did not write it. I have messed with it but do not even know HOW to write something like this yet. Intend to learn but am new to Report Builder 3.0 and the programming language.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 11:38:40

This page shows you how to modify a stored procedure: http://msdn.microsoft.com/en-us/library/ms345356(v=sql.90).aspx

Before you make any changes, save a copy after step 3, so in case you need to revert back you can. Also, instructions are slightly different for different versions of SQL Server, so be sure to select the correct version of SQL Server (look for Other Versions) right below the title on that web page.

The only change you should have to make is adding what I showed in red in my earlier reply.

GirlGeek
Starting Member

4 Posts

Posted - 2013-04-29 : 11:42:20

Thanks, James. I will look into the link. I have fixed the issue so that it is acceptable... though not perfect.

Your way looks much better than mine but I do not know where to put in the SP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources