SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Hiding Empty Rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GirlGeek
Starting Member

4 Posts

Posted - 04/29/2013 :  09:45:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3717 Posts

Posted - 04/29/2013 :  09:52:52  Show Profile  Reply with Quote
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
Go to Top of Page

GirlGeek
Starting Member

4 Posts

Posted - 04/29/2013 :  10:43:09  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3717 Posts

Posted - 04/29/2013 :  11:38:40  Show Profile  Reply with Quote
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.
Go to Top of Page

GirlGeek
Starting Member

4 Posts

Posted - 04/29/2013 :  11:42:20  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000