SSRS Adding multiple yearly values into 1 table

Question

SSRS Adding multiple yearly values into 1 table

Gekko

SSC Rookie

Points: 29
More actions
May 28, 2008 at 8:51 am

#207761

Not sure of a solution for this. I will try explaining what I need to do, and hopefully it makes sense. I need to make a report to track vendor performance over a given period of time. The user will select the vendor and the year from a drop down list. The results will display in a table, and look something like this:
Vendor Report Card for Widgets Co.
-----------------------------------------------------------------------------------------------------------------------
Year | Lots Received | Lots Rejected | Units Received | Units Rejected | Performance Index | NCMRs
-----------------------------------------------------------------------------------------------------------------------
2008 | 20 | 0 | 5000 | 0 | 100% | 0
-----------------------------------------------------------------------------------------------------------------------
2007 | 40 | 2 | 8500 | 700 | 92% | 4
-----------------------------------------------------------------------------------------------------------------------
2006 | 30 | 1 | 6500 | 250 | 96% | 1
-----------------------------------------------------------------------------------------------------------------------
Basically, it should display the select year in the first row, then the previous 2 years in the next 2 rows, as show above. To get the current year, I used this statement, and it works perfectly.
--Main statement for retrieving values
select
count(lotNo) LotsRcvd,
(select count(LotPassFail)
from (
select UPPER(lotNo) LotNo,dateRecvd,vendorcode,
CASE
WHEN QtyRej=0 THEN '0'
ELSE '1'
END AS 'LotPassFail'
from tblMain
)asd
where LotPassFail=1
and year(dateRecvd)=@pYear
and vendorcode=@pVendor) LotRej,
CAST(sum(qtyRcv) AS numeric(10,0)) UnitsRcvd,
CAST(sum(qtyRej) AS numeric(10,0)) UnitsRej,
CAST(CAST(round((1-(sum(qtyRej)/sum(qtyRcv)))*100,1) AS numeric(10,1)) AS varchar(5)) + '%' PerfIndex
from tblMain
where year(dateRecvd)=@pYear
and vendorcode=@pVendor
Now, I then tried to modify it as follows: (additions are in RED)
select
count(lotNo) LotsRcvd,
(select count(LotPassFail)
from (
select UPPER(lotNo) LotNo,dateRecvd,vendorcode,
CASE
WHEN QtyRej=0 THEN '0'
ELSE '1'
END AS 'LotPassFail'
from tblMain
)asd
where LotPassFail=1
and year(dateRecvd)=@pYear
and vendorcode=@pVendor) LotRej,
CAST(sum(qtyRcv) AS numeric(10,0)) UnitsRcvd,
CAST(sum(qtyRej) AS numeric(10,0)) UnitsRej,
CAST(CAST(round((1-(sum(qtyRej)/sum(qtyRcv)))*100,1) AS numeric(10,1)) AS varchar(5)) + '%' PerfIndex,
QQ.LotsRcvd1,
QQ.LotRej1,
QQ.UnitsRcv1,
QQ.UnitsRej1,
QQ.PerfIndex1
from tblMain join
(
select
count(lotNo) LotsRcvd1,
(select count(LotPassFail)
from (
select UPPER(lotNo) LotNo,dateRecvd,vendorcode,
CASE
WHEN QtyRej=0 THEN '0'
ELSE '1'
END AS 'LotPassFail'
from tblMain
)asf
where LotPassFail=1
and year(dateRecvd)=@pYear
and vendorcode=@pVendor) LotRej1,
CAST(sum(qtyRcv) AS numeric(10,0)) UnitsRcvd1,
CAST(sum(qtyRej) AS numeric(10,0)) UnitsRej1,
CAST(CAST(round((1-(sum(qtyRej)/sum(qtyRcv)))*100,1) AS numeric(10,1)) AS varchar(5)) + '%' PerfIndex1
from tblMain
where year(dateRecvd)=@pYear
and vendorcode=@pVendor
) AS QQ on QQ.LotNo=tblMain.LotNo
where year(dateRecvd)=@pYear
and vendorcode=@pVendor
Any ideas on how to get this to work would be great. Also, anyway to decrease the size of this would be great too. Thanks for any help.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Gekko (5/28/2008)
Not sure of a solution for this. I will try explaining what I need to do, and hopefully it makes sense. I need to make a report to track vendor performance over a given period of time. The user will select the vendor and the year from a drop down list. The results will display in a table, and look something like this:
Vendor Report Card for Widgets Co.
-----------------------------------------------------------------------------------------------------------------------
Year | Lots Received | Lots Rejected | Units Received | Units Rejected | Performance Index | NCMRs
-----------------------------------------------------------------------------------------------------------------------
2008 | 20 | 0 | 5000 | 0 | 100% | 0
-----------------------------------------------------------------------------------------------------------------------
2007 | 40 | 2 | 8500 | 700 | 92% | 4
-----------------------------------------------------------------------------------------------------------------------
2006 | 30 | 1 | 6500 | 250 | 96% | 1
-----------------------------------------------------------------------------------------------------------------------
Basically, it should display the select year in the first row, then the previous 2 years in the next 2 rows, as show above. To get the current year, I used this statement, and it works perfectly.
--Main statement for retrieving values
select
count(lotNo) LotsRcvd,
(select count(LotPassFail)
from (
select UPPER(lotNo) LotNo,dateRecvd,vendorcode,
CASE
WHEN QtyRej=0 THEN '0'
ELSE '1'
END AS 'LotPassFail'
from tblMain
)asd
where LotPassFail=1
and year(dateRecvd)=@pYear
and vendorcode=@pVendor) LotRej,
CAST(sum(qtyRcv) AS numeric(10,0)) UnitsRcvd,
CAST(sum(qtyRej) AS numeric(10,0)) UnitsRej,
CAST(CAST(round((1-(sum(qtyRej)/sum(qtyRcv)))*100,1) AS numeric(10,1)) AS varchar(5)) + '%' PerfIndex
from tblMain
where year(dateRecvd)=@pYear
and vendorcode=@pVendor
Now, I then tried to modify it as follows: (additions are in RED)
select
count(lotNo) LotsRcvd,
(select count(LotPassFail)
from (
select UPPER(lotNo) LotNo,dateRecvd,vendorcode,
CASE
WHEN QtyRej=0 THEN '0'
ELSE '1'
END AS 'LotPassFail'
from tblMain
)asd
where LotPassFail=1
and year(dateRecvd)=@pYear
and vendorcode=@pVendor) LotRej,
CAST(sum(qtyRcv) AS numeric(10,0)) UnitsRcvd,
CAST(sum(qtyRej) AS numeric(10,0)) UnitsRej,
CAST(CAST(round((1-(sum(qtyRej)/sum(qtyRcv)))*100,1) AS numeric(10,1)) AS varchar(5)) + '%' PerfIndex,
QQ.LotsRcvd1,
QQ.LotRej1,
QQ.UnitsRcv1,
QQ.UnitsRej1,
QQ.PerfIndex1
from tblMain join
(
select
count(lotNo) LotsRcvd1,
(select count(LotPassFail)
from (
select UPPER(lotNo) LotNo,dateRecvd,vendorcode,
CASE
WHEN QtyRej=0 THEN '0'
ELSE '1'
END AS 'LotPassFail'
from tblMain
)asf
where LotPassFail=1
and year(dateRecvd)=@pYear
and vendorcode=@pVendor) LotRej1,
CAST(sum(qtyRcv) AS numeric(10,0)) UnitsRcvd1,
CAST(sum(qtyRej) AS numeric(10,0)) UnitsRej1,
CAST(CAST(round((1-(sum(qtyRej)/sum(qtyRcv)))*100,1) AS numeric(10,1)) AS varchar(5)) + '%' PerfIndex1
from tblMain
where year(dateRecvd)=@pYear
and vendorcode=@pVendor
) AS QQ on QQ.LotNo=tblMain.LotNo
where year(dateRecvd)=@pYear
and vendorcode=@pVendor
Any ideas on how to get this to work would be great. Also, anyway to decrease the size of this would be great too. Thanks for any help.

How about something like (my additions in bold):

--Main statement for retrieving values

select

year(dateRecvd) [Year],

count(lotNo) LotsRcvd,

(select count(LotPassFail)

from (

select UPPER(lotNo) LotNo,dateRecvd,vendorcode,

CASE

WHEN QtyRej=0 THEN '0'

ELSE '1'

END AS 'LotPassFail'

from tblMain

)asd

where LotPassFail=1

and year(dateRecvd)=@pYear

and vendorcode=@pVendor) LotRej,

CAST(sum(qtyRcv) AS numeric(10,0)) UnitsRcvd,

CAST(sum(qtyRej) AS numeric(10,0)) UnitsRej,

CAST(CAST(round((1-(sum(qtyRej)/sum(qtyRcv)))*100,1) AS numeric(10,1)) AS varchar(5)) + '%' PerfIndex

from tblMain

where year(dateRecvd) between @pYear-2 and @pYear

and vendorcode=@pVendor

group by year(dateRecvd)

order by year(dateRecvd) DESC

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2