5in
2.1in
5in
1.6in
1.55208in
1in
1.44792in
1in
0.22in
true
true
Mat Type
textbox2
SteelBlue
2pt
2pt
2pt
2pt
true
true
Mat Count
textbox4
SteelBlue
2pt
2pt
2pt
2pt
true
true
Textbox24
SteelBlue
2pt
2pt
2pt
2pt
true
true
Textbox25
SteelBlue
2pt
2pt
2pt
2pt
0.21in
true
true
=Fields!MatType.Value
MatType
#6e9eca
2pt
2pt
2pt
2pt
true
true
=Fields!MatCount.Value
textbox8
#6e9eca
2pt
2pt
2pt
2pt
true
true
textbox9
#6e9eca
2pt
2pt
2pt
2pt
true
true
textbox10
#6e9eca
2pt
2pt
2pt
2pt
0.21in
true
true
textbox11
SlateGray
2pt
2pt
2pt
2pt
true
true
Textbox23
SlateGray
2pt
2pt
2pt
2pt
true
true
Coll Name
textbox12
SlateGray
2pt
2pt
2pt
2pt
true
true
Coll Count
textbox13
SlateGray
2pt
2pt
2pt
2pt
0.21in
true
true
textbox3
2pt
2pt
2pt
2pt
true
true
textbox5
2pt
2pt
2pt
2pt
true
true
=Fields!CollName.Value
CollName
2pt
2pt
2pt
2pt
true
true
=Fields!CollCount.Value
CollCount
2pt
2pt
2pt
2pt
After
true
true
=Fields!MatType.Value
=Fields!MatCount.Value
After
true
=Fields!MatCount.Value
=Fields!MatCount.Value
After
true
Detail
Detail_Collection
Output
true
DataSet1
0.5in
0.85in
5in
true
true
="Total Circ: " & Fields!OrgCount.Value
OrgCount
0.24in
5in
1
true
=Fields!OrgCount.Value
Between
=Fields!OrgCount.Value
Output
true
DataSet1
End
0.5in
1.6in
5in
true
true
=Fields!Orgname.Value
Orgname
0.27in
5in
1
true
=Fields!Orgname.Value
Between
=Fields!Orgname.Value
Output
true
DataSet1
End
0.36in
2.1in
5in
true
true
IUG Report
textbox1
0.36in
5in
1
2.46in
5in
1in
1in
1in
1in
0
Polaris
None
9f81152b-a014-41be-9faf-49a9fce45a1b
Polaris
=Parameters!BDATE.Value
=Parameters!EDATE.Value
-- Initial table to hold TransactionHeader information
CREATE TABLE #TEMPTrans (
TransactionID int,
OrganizationID int
)
INSERT INTO #TEMPTrans
SELECT TH.TransactionID, TH.OrganizationID
FROM PolarisTransactions.Polaris.TransactionHeaders TH
WHERE TH.TransactionTypeID = 6001 -- checkouts only
AND TH.TranClientDate BETWEEN @BDATE AND @EDATE
-- Holds informaiton used to calculate statistics later on, including collection and materialtype
CREATE TABLE #TransStats (
TransactionID int,
OrganizationID int,
CollectionName varchar(50),
MatType varchar(50)
)
--Gathering all stats together for later processing
INSERT INTO #TransStats
SELECT TMP.TransactionID, TMP.OrganizationID,
'Collection' = ( SELECT PC.Name FROM PolarisTransactions.Polaris.TransactionDetails Col
JOIN Polaris.Polaris.Collections PC ON
PC.CollectionID = Col.numValue
WHERE Col.TransactionSubTypeID = 61 -- Assigned Collection Code
AND Col.TransactionID = TMP.TransactionID
),
'MaterialType' = ( SELECT MT.Description FROM PolarisTransactions.Polaris.TransactionDetails Mat
JOIN Polaris.Polaris.MaterialTypes MT ON
MT.MaterialTypeID = Mat.numValue
WHERE Mat.TransactionSubTypeID = 4 -- Material Type
AND Mat.TransactionID = TMP.TransactionID
)
FROM #TEMPTrans TMP
WHERE TMP.TransactionID NOT IN ( SELECT TT.TransactionID FROM #TEMPTrans TT
JOIN PolarisTransactions.Polaris.TransactionDetails TD2
ON TD2.TransactionID = TT.TransactionID
WHERE TD2.TransactionSubTypeID = 124 -- Renewal Subtype
)
ORDER BY OrganizationID, 'MaterialType', 'Collection'
DECLARE @Results TABLE (
Orgname varchar(50),
OrgID int,
OrgCount int,
MatType varchar(50),
MatCount int,
CollName varchar(50),
CollCount int
)
INSERT INTO @Results
SELECT PO.Name,
TS.OrganizationID,
NULL,
MatType,
NULL,
CollectionName,
'CollCount' = Count(*)
FROM #TransStats TS
JOIN Polaris.Polaris.Organizations PO ON
PO.OrganizationID = TS.OrganizationID
GROUP BY PO.Name, TS.OrganizationID, MatType, CollectionName
Order By PO.Name, MatType, 'CollCount' DESC
UPDATE Res
SET MatCount = ( SELECT 'MatCount' = COUNT(*)
FROM #TransStats TS
WHERE Res.MatType = TS.MatType
AND Res.OrgID = TS.OrganizationID
)
FROM @Results Res
UPDATE Res
SET OrgCount = ( SELECT 'OrgCount' = COUNT(*)
FROM #TransStats TS
WHERE Res.OrgID = TS.OrganizationID
)
FROM @Results Res
SELECT * FROM @Results
ORDER BY OrgName, MatCount DESC, CollCount DESC
DROP TABLE #TEMPTrans
DROP TABLE #TransStats
true
Orgname
System.String
OrgID
System.Int32
OrgCount
System.Int32
MatType
System.String
MatCount
System.Int32
CollName
System.String
CollCount
System.Int32
DateTime
BDATE
DateTime
EDATE
en-US
true
Inch
971d9ff1-f108-4b68-9a48-93f0a6cc2dee