January 23, 2014 at 12:40 pm
We are using SQL 2008 and SSRS 2008 R2.
I am working with the BIDS outs of Visual Studio 2008 in SQL Server 2008.
I have a dataset that has in it branches, salespersons, items, costs, and profits known as "spread".
I am making a report that will list the top 50 items by cost by salesperson or the top 50 items by spread by salesperson within a particular branch.
So far, I cannot seem to find a way to make the detail group (the only grouping in the report btw) list the Top N items by salesperson. Instead I get the Top N of the entire branch. I know how to do this in SQL (code below) and will include test data to work with.
However, for some unknown reason it is not working in the SSRS report.
I notice when I change the filters on the Grouping and leave them unfiltered; I get all the records in Branch; Salesperson;[Cost or Spread] order; it is only when I include the Top N filter that only the Top N records for each branch are displayed.
The report should look like the dataset below; the Top 50 items (by Cost or Spread) for each salesperson for each branch.
Please help me see what I need to do to correct the report.
Please let me know if I need to include more info.
Details about the report; groups; params; and data sets follow the code to get the sample data.
Query that does what I want in SQL:
-- for testing the group functions
-- Cannot use as the last line of the SP because the Items reports have two sort order demands
-- the AVG Cost reports sort by LTMCostAMT
-- the AVG Spread reports sort by LTMSpreadAVG
-- both of which do NOT equal each other.
;WITH dt AS (
SELECT
ROW_NUMBER() OVER ( PARTITION BY SPRSNSLN ORDER BY LTMCostAmt DESC ) AS 'RowNumber',
ROWGUID,SLPRSNID,SPRSNSLN,BRANCH,ITEMNMBR,ITEMDESC,
LTMSpreadAmt,LTMCostAmt,LTMSpreadAvg,LTMCostAvg,LTMGPPct,LastYear,CurrentYear,TWLV_MO_AVG_GP_PCT
FROM #_TempPartNumber_Terr_LTM_Master
)
-- and select the data from the CTE
SELECT
ROWGUID,SLPRSNID,SPRSNSLN,BRANCH,ITEMNMBR,ITEMDESC,
LTMSpreadAmt,LTMCostAmt,LTMSpreadAvg,LTMCostAvg,LTMGPPct,LastYear,CurrentYear,TWLV_MO_AVG_GP_PCT
INTO #_TempResults
FROM dt
WHERE RowNumber <= 50 AND
SPRSNSLN NOT IN ('HOUSE','Inactive Sales ID','Tech Service') AND
BRANCH = 'AL'
ORDER BY SPRSNSLN,LTMCostAmt DESC
Dataset produced by the code above:
-- it is quite long as I need the top 50 from each person (I included 3 people in the example which makes for 150 records to insert- I used the forum kb on building the statement)
SELECT 'SELECT '
+ QUOTENAME('''NEWID()''','''')+','
+ QUOTENAME(SLPRSNID,'''')+','
+ QUOTENAME(SPRSNSLN,'''')+','
+ QUOTENAME(BRANCH,'''')+','
+ QUOTENAME(ITEMNMBR,'''')+','
+ QUOTENAME(ITEMDESC,'''')+','
+ CAST(LTMSpreadAmt AS VARCHAR)+','
+ CAST(LTMCostAmt AS VARCHAR)+','
+ CAST(LTMSpreadAvg AS VARCHAR)+','
+ CAST(LTMCostAvg AS VARCHAR)+','
+ CAST(LTMGPPct AS VARCHAR)+','
+ QUOTENAME(LastYear,'''')+','
+ QUOTENAME(CurrentYear,'''')+','
+ CAST(TWLV_MO_AVG_GP_PCT AS VARCHAR)
+ ' UNION ALL'
FROM #_TempResults
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #_TempTest
(
ROWGUID UNIQUEIDENTIFIER NOT NULL,
SLPRSNID VARCHAR(15) NULL,
SPRSNSLN VARCHAR(61) NULL,
BRANCH VARCHAR(15) NOT NULL,
ITEMNMBR CHAR(31) NOT NULL,
ITEMDESC VARCHAR(101) NULL,
LTMSpreadAmt DECIMAL (14,6) NULL,
LTMCostAmt DECIMAL (14,6) NULL,
LTMSpreadAvg DECIMAL (14,6) NULL,
LTMCostAvg DECIMAL (14,6) NULL,
LTMGPPct DECIMAL (14,6) NULL,
LastYear INT NOT NULL,
CurrentYear INT NOT NULL,
TWLV_MO_AVG_GP_PCT DECIMAL (14,6) NULL
)
--===== Insert the test data into the test table
INSERT INTO #_TempTest
(ROWGUID,SLPRSNID,SPRSNSLN,BRANCH,ITEMNMBR,ITEMDESC,LTMSpreadAmt,LTMCostAmt,LTMSpreadAvg,LTMCostAvg,
LTMGPPct,LastYear,CurrentYear,TWLV_MO_AVG_GP_PCT)
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','627-2071 ','ADHESIVE 3M# 1838L EPOXY 300ML ',8573.800000,82290.920000,714.483333,6857.576667,0.094357,'2012','2013',0.094364 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','627-0092 ','ADHESIVE SPRAY RED TINT JUMBO CYLINDER ',16480.260000,64462.500000,1373.355000,5371.875000,0.203603,'2012','2013',0.203603 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','004-6037 ','AME925 69# COHESIVE LINER BOARD DBL PERF 1C PRINT ',15502.695000,55532.645000,1291.891250,4627.720417,0.218239,'2012','2013',0.217981 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0344 ','STICKUM 1 X 36YD 2045-3 144/CS ',6421.290000,48779.670000,535.107500,4064.972500,0.116325,'2012','2013',0.116786 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0345 ','FILM STICKUM 1" X 36 YD 3M5480 9RL/CS ',9326.910000,37923.090000,777.242500,3160.257500,0.197394,'2012','2013',0.196305 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0337 ','ADHESIVE TRANSFER STICKUM 3" X 60 YD F9473PC VHB ',4708.640000,32926.760000,392.386667,2743.896667,0.125111,'2012','2013',0.128744 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0321 ','TEFLON STICKUM 2 X 36YD 2045-3 ',1935.670000,28703.690000,161.305833,2391.974167,0.063175,'2012','2013',0.063240 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0325 ','STICKUM 2 X 72YD CHR730 GREEN 36RL/CS ',14695.990000,26757.050000,1224.665833,2229.754167,0.354521,'2012','2013',0.351174 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','007-6506 ','PAPER PADPAK LC 1-PLY 55# 1000FT/BNDL 24/SK SSSP3008R ',2052.365000,21611.635000,171.030417,1800.969583,0.086729,'2012','2013',0.086540 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0324 ','STICKUM 1 X 72YD GREEN CHR730 ',4844.940000,21094.260000,403.745000,1757.855000,0.186780,'2012','2013',0.184147 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0016 ','ADHESIVE STICKUM PTFE FILM BACK SILICON 1 X 36 YD 36/CS ',3536.730000,20342.070000,294.727500,1695.172500,0.148111,'2012','2013',0.149758 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-6135 ','SEALING STICKUM 48MM X 200M CLEAR 6100 36RL/CS ',3796.200000,20109.600000,316.350000,1675.800000,0.158798,'2012','2013',0.158798 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','032-9000 ','ZAMHAT RE-PACK BATTERY ',14175.600000,18362.140000,1181.300000,1530.178333,0.435666,'2012','2013',0.510956 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','900-0001 ','ZAMHAT FREIGHT FEE INBOUND/OUTBOUND ',-17891.000000,17891.000000,-1490.916667,1490.916667,0.000000,'2012','2013',0.000000 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','007-4509 ','PEANUTS TRUCK LOAD 4,000 CUBIC FT ',2009.000000,17317.580000,167.416667,1443.131667,0.103950,'2012','2013',0.103890 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','012-0131 ','NEWSPRINT 24 X 30 30# 50LB/BNDL 40 BNDL/SKID ',4205.600000,16076.500000,350.466667,1339.708333,0.207355,'2012','2013',0.213525 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','007-1158 ','5/16 X 48 X 375'' P-12 CLEAR BUBBLE ',8317.280000,15990.000000,693.106667,1332.500000,0.342172,'2012','2013',0.632063 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','009-2058 ','HAND STRETCH 18 X 1500 4RL/CS ',5370.530000,15391.310000,447.544167,1282.609167,0.258673,'2012','2013',0.252764 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0323 ','FOAM VHB STICKUM 1/2" X 72 YD WHITE 3M4930 25MIL 18/CS ',1794.420000,11388.240000,149.535000,949.020000,0.136119,'2012','2013',0.137545 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0300 ','STICKUM 9/32 X 36YD 2245-2 60RL/CS ',859.280000,11061.220000,71.606667,921.768333,0.072084,'2012','2013',0.068991 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','033-0016 ','REWORK PALLET #025-1086 REPLACE FOAM 2LB W/ 4LB ',1117.500000,10988.750000,93.125000,915.729167,0.092307,'2012','2013',0.092307 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','025-1086 ','PALLET 31.5 X 30 WOOD W/1.7 PE FOAM & HARDWARE ',3572.360000,10374.670000,297.696667,864.555833,0.256137,'2012','2013',0.153682 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0342 ','KAPTON 1/2'''' STICKUM TEMP-R ',1402.250000,10229.350000,116.854167,852.445833,0.120555,'2012','2013',0.121161 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0299 ','PAPER STICKUM #4313 BRWN 2 X 55 36RL/CS 36CS/SK ',2462.400000,9836.640000,205.200000,819.720000,0.200210,'2012','2013',0.200215 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-1801 ','VINYL STICKUM 48MM X 36 YD 3M 471 RED 24RL/CS ',1209.600000,8332.800000,100.800000,694.400000,0.126760,'2012','2013',0.126760 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','008-6946 ','POLYBAG 8 X 12 1.5MIL FLAT ',2205.800000,8044.200000,183.816667,670.350000,0.215200,'2012','2013',0.215200 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','002-6010 ','VACUUM SEALER RETRACTABLE 25" W/GAS PURGE ',524.070000,7275.930000,43.672500,606.327500,0.067188,'2012','2013',0.067188 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0309 ','TEFLON FILM STICKUM 3/4" X 36 YD 2245-2 ',485.110000,7140.650000,40.425833,595.054167,0.063614,'2012','2013',0.066290 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0329 ','SEALING STICKUM 6.14 X 55M 3M375 ',3840.000000,6672.000000,320.000000,556.000000,0.365296,'2012','2013',0.365296 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0310 ','STICKUM 1 1/8 X 36YD 2245-2 FURON ST GOBAIN ',401.520000,6559.980000,33.460000,546.665000,0.057677,'2012','2013',0.054998 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-1087 ','FOIL STICKUM 2 X 60 YD 3M 425 ALUMINUM 24RL/CS ',1992.600000,6470.760000,166.050000,539.230000,0.235438,'2012','2013',0.235438 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','017-4046 ','LABEL SAI200 WARNING PRINT 6 X 14 ',1489.490000,6456.910000,124.124167,538.075833,0.187442,'2012','2013',0.187441 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','400-8814 ','GAYLORDS T/W 40 x 40 x 38 W/ BTM 20/SK ',2611.000000,6456.000000,217.583333,538.000000,0.287967,'2012','2013',0.287390 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-1088 ','FOIL STICKUM 1 X 60 YD ALUMINUM 3M 425 36RL/CS ',1074.150000,6392.250000,89.512500,532.687500,0.143864,'2012','2013',0.143863 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','009-2041 ','STRETCH 20 X 6000'' 70GA 1SC 40RL/SKID ',1214.960000,6255.440000,101.246667,521.286667,0.162636,'2012','2013',0.162636 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','002-8002 ','PRINTER DATAMAX ONEIL M-4308 ',920.000000,5235.000000,76.666667,436.250000,0.149471,'2012','2013',0.149471 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-1814 ','SEALING STICKUM 2X 110YDS 3M 311 CLEAR 36RL/CS ',1298.240000,5052.160000,108.186667,421.013333,0.204434,'2012','2013',0.618170 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0317 ','3M 94 PRIMER ',1071.360000,4656.480000,89.280000,388.040000,0.187044,'2012','2013',0.188947 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','013-5103 ','TAIL BLADE ASSEMBLY CRATE 138 X 18 X 8 ',1779.960000,4590.040000,148.330000,382.503333,0.279428,'2012','2013',0.279428 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','012-0132 ','NEWSPRINT 15 X 15 30# 25LBS/BNDL 40 BNDL/SKID ',-3327.200000,4499.200000,-277.266667,374.933333,-2.838907,'2012','2013',-2.838907 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','007-1049 ','BUBBLE 3/16 X 48 X 750 A/S S-24 P-12 ',1729.280000,4001.200000,144.106667,333.433333,0.301768,'2012','2013',0.296009 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-1660 ','STICKUM 4 X 108YD 3M 9713 XYZ-AXIS ELECTRICALLY CONDUCTIVE 1RL/CS ',662.830000,3887.170000,55.235833,323.930833,0.145676,'2012','2013',0.149014 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','007-1010 ','BUBBLE 1/2 X 48 X 250'' A/S P-12 #45009 ',2375.910000,3668.090000,197.992500,305.674167,0.393102,'2012','2013',0.419369 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','712-0320 ','2" X 36 YD YELLOW 150/200 HEBEI HUAXIA 24RL/CS ',1352.940000,3504.180000,112.745000,292.015000,0.278547,'2012','2013',0.261713 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','007-4507 ','PEANUTS LOOSE FILL 20 CU.FTBAG ',1144.120000,3425.790000,95.343333,285.482500,0.250359,'2012','2013',0.241274 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','017-3191 ','LABEL 3 X 4.5 WATERPROOF PINK 325/RL 3 INCH CORE ',3069.990000,3372.310000,255.832500,281.025833,0.476536,'2012','2013',0.353454 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','032-1029 ','GAYLORD BOX KIT 48 X 48 X 48 TW ',412.000000,3250.500000,34.333333,270.875000,0.112491,'2012','2013',0.112491 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','007-3501 ','CELL-O FILM LDPE 1.2 MIL 4 X 8 X 2132 FT 2RL/CS ',770.680000,3219.020000,64.223333,268.251667,0.193167,'2012','2013',0.193167 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','008-0925 ','POLYBAG 3.63 X 9.25 CLEAR 2MIL BOR ',1297.090000,3205.450000,108.090833,267.120833,0.288079,'2012','2013',0.295533 UNION ALL
SELECT NEWID(),'6662 ','VINCE GLORTHO','AL ','007-5526 ','POLYLAM 2 X 48 X 108 4# WHITE ',-3200.400000,3200.400000,-266.700000,266.700000,0.000000,'2012','2013',0.523876 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-9024 ','LGE175 60" TV U-CHANNEL 2" CUT 30/PLT ',12378.910000,99551.740000,1031.575833,8295.978333,0.110594,'2012','2013',0.053273 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-3842 ','FILM AIRSPEED 10 7/8 X 8 X 3600 FILM 580075 (5000) ',25344.920000,98134.930000,2112.076667,8177.910833,0.205255,'2012','2013',0.205306 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','400-9708 ','MED250 BOX 18 3/4 X 11 1/2 X 7 3/16 RSC, 44ECT GL 1C PRINT ',12349.660000,92287.990000,1259.138333,7690.665833,0.140688,'2012','2013',0.143149 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','009-2058 ','HAND STRETCH 18 X 1500 4RL/CS ',17166.000000,91824.720000,1430.500000,7652.060000,0.157499,'2012','2013',0.167579 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-3808 ','LGE175 P5000 FILM 8 X 4 X 4000 EP-FLEX 48/SKID ',36485.850000,84474.150000,3040.487500,7039.512500,0.301635,'2012','2013',0.301656 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','010-1133 ','STEEL STRAP 3/8 X .015 NON-PAINTED ',39064.990000,74430.860000,3255.415833,6202.571667,0.344197,'2012','2013',0.344153 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-1002 ','FILM AIRSPEED HC 17.25" X 1250'' ',18754.400000,68286.400000,1562.866667,5690.533333,0.215466,'2012','2013',0.215466 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-3503 ','HC12 FLEX FILM LRG 17.25 X 1750 ',21787.000000,66609.000000,1815.583333,5550.750000,0.246470,'2012','2013',0.245253 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-3507 ','AIR PILLOW FILM HC18 23.5" X 1000'' P-6 ',21799.260000,63610.740000,1816.605000,5300.895000,0.255230,'2012','2013',0.255230 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-3525 ','PILLO PKR FILM - MED 30 X 1200 ',10853.480000,53998.700000,904.456667,4499.891667,0.167357,'2012','2013',0.166247 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-9020 ','LGE175 60" U-CHANNEL 1" CUT 30/PLT ',3019.960000,49454.540000,251.663333,4121.211667,0.057551,'2012','2013',-0.192693 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-1112 ','BUBBLE 1/2 X 48 X 250'' A/S P-12 S-12 4RL/BNDL ',13603.420000,47667.380000,1133.618333,3972.281667,0.222021,'2012','2013',0.221847 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-9087 ','LGE175 55" TV CHANNEL W/1" CUT OUT 30/SK ',-1347.300000,40157.700000,-112.275000,3346.475000,-0.034714,'2012','2013',-0.101210 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-9089 ','LGE175 50" TV CHANNEL W/2" CUT OUT 30/SK ',5691.690000,38161.500000,474.307500,3180.125000,0.129789,'2012','2013',0.123159 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','712-6020 ','SEALING STICKUM 72MM X 100M 24RL/CS ',9681.120000,37419.360000,806.760000,3118.280000,0.205541,'2012','2013',0.206682 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','034-1060 ','LGE175 BOX CLAW 40 INCH ',11745.750000,36988.000000,978.812500,3082.333333,0.241018,'2012','2013',0.248731 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-3818 ','FILM AIRSPEED 8 X 4 X 1815'' P5000 FILM COMMERCIAL 48CS/PLT ',11099.520000,36516.480000,924.960000,3043.040000,0.233104,'2012','2013',0.233104 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','009-2041 ','STRETCH 20 X 6000'' 70GA 1SC 40RL/SKID ',4801.340000,32408.170000,400.111667,2700.680833,0.129035,'2012','2013',0.126878 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','025-1182 ','WOOD PALLET 60 X 24 4WAY ',11299.500000,30524.400000,941.625000,2543.700000,0.270168,'2012','2013',0.317394 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','034-1066 ','LGE175 BOX CLAW - 59 INCH ',10701.500000,30216.000000,891.791667,2518.000000,0.261538,'2012','2013',0.261538 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-9016 ','LGE175 47" U-CHANNEL 1" CUT 42/PLT ',-410.760000,29110.760000,-34.230000,2425.896667,-0.014312,'2012','2013',-0.012720 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','009-2104 ','STRETCH 20 X 5000 '' 75GA 2SC 40RL/SK ',7995.620000,28335.000000,666.301667,2361.250000,0.220079,'2012','2013',0.222889 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','009-2281 ','STRETCH 20 X 7500'' 60 GA 1SC 40RL/PLT ',4166.000000,26928.000000,347.166667,2244.000000,0.133980,'2012','2013',0.134179 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-2057 ','PE FOAM PAD 1 X 24 X 8 CHARCOAL ',16736.220000,25463.550000,1394.685000,2121.962500,0.396595,'2012','2013',0.439897 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-3819 ','FILM AIRSPEED 8 X 4 X 2600'' LD P5000 AIR CELL FILM 48CS/PLT EP-FLEX ',7059.090000,24651.490000,588.257500,2054.290833,0.222609,'2012','2013',0.225259 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','027-0102 ','RACK DIVIDERS KWIK KLIP 42 X 10 ',3126.560000,23873.440000,260.546667,1989.453333,0.115798,'2012','2013',0.115495 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','009-2247 ','STRETCH 20 X 6500'' 65GA 1SC 40RL/PALLET 7050407 ',2912.000000,23577.600000,242.666667,1964.800000,0.109929,'2012','2013',0.106206 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','012-0007 ','PAPER STOROPAK #PPD30 30# PAPER PLUS ',2132.880000,20667.120000,177.740000,1722.260000,0.093547,'2012','2013',0.093546 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-9088 ','LGE175 55" TV CHANNEL W/2" CUT OUT 30/SK ',235.500000,19734.900000,19.625000,1644.575000,0.011792,'2012','2013',-0.000879 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-9021 ','LGE175 42" U-CHANNEL 2" CUT 50/PLT ',-562.380000,19054.980000,-46.865000,1587.915000,-0.030411,'2012','2013',-0.093072 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-5516 ','POLYLAM 1 X 48 X 108 1.7 PCF WHITE ',4015.160000,18970.130000,334.596667,1580.844167,0.174683,'2012','2013',0.180471 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','027-0025 ','DOUBLE SCISSOR LIFT EHLTD 4858-3-70 ',4850.530000,17536.670000,404.210833,1461.389167,0.216665,'2012','2013',0.216665 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','400-8506 ','BOX 49 X 31 X 34 1/8 FLNG TU 48 ECT BC GLUED W/LABEL ',2512.460000,17099.430000,209.371667,1424.952500,0.128109,'2012','2013',0.126664 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','400-8597 ','AME350 24 5/8 X 16 X 32 1/2 WHITE W/LABEL 040.99.QGP6P ',7656.880000,16636.200000,638.073333,1386.350000,0.315187,'2012','2013',0.312572 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','004-7250 ','THM100 SKINBOARD BLUE WATER RANDOM ',5595.330000,16347.720000,466.277500,1362.310000,0.254993,'2012','2013',0.253057 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','032-9623 ','KDW100 #0132 SHIPPING CASE W/FOAM INSERT ',3324.210000,16050.790000,277.017500,1337.565833,0.171572,'2012','2013',0.172206 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','009-2014 ','STRETCH 20 X 3500 115 GA 1SC CAST 40/SKID ',3645.420000,15956.060000,303.785000,1329.671667,0.185976,'2012','2013',0.180204 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','025-1017 ','LGE175 BOX CLAW 59 INCH W/2 X 4 BOARDS ',3681.600000,14778.400000,306.800000,1231.533333,0.199436,'2012','2013',0.199436 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','017-0083 ','LABEL 4 X 6 TTP 4RL/CS 1000/RL 3" CORE W/O ',3611.240000,14245.160000,300.936667,1187.096667,0.202237,'2012','2013',0.207635 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','034-1048 ','LGE175 TV CORNER PAD 1.5" ',15945.600000,14118.500000,1328.800000,1176.541667,0.530386,'2012','2013',0.530386 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','013-1141 ','KDW100 CASE #0131 & #0132 BLUE ',-13350.360000,13350.360000,-1112.530000,1112.530000,0.000000,'2012','2013',0.000000 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-9013 ','LGE175 EPS FOAM 2 X 6 X 39 ',6206.700000,12976.100000,517.225000,1081.341667,0.323555,'2012','2013',0.358995 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','009-4010 ','SURLYN FILM 20 X 600 7.5MIL 37.85 LBS/RL ',3753.000000,12508.500000,312.750000,1042.375000,0.230790,'2012','2013',0.211713 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','007-5514 ','POLYLAM 5 X 48 X 108 1.7 PCF CHARCOAL ',2613.070000,12167.280000,217.755833,1013.940000,0.176793,'2012','2013',0.163166 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','017-2012 ','LABEL MED250 4 X 7.5 D/C WHT MATTE FF ',1935.900000,12098.340000,161.325000,1008.195000,0.137941,'2012','2013',0.138035 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','021-2008 ','BALING WIRE 11 GA ANNEALED AUTO TY 100LB/CTN ',5225.400000,11934.000000,435.450000,994.500000,0.304521,'2012','2013',0.304521 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','COST ADJUSTMENT ','027-1020 & 027-1021 $7.00 ',-11627.770000,11627.770000,-968.980833,968.980833,0.000000,'2012','2013',0.000000 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','032-9110 ','CA LGE 55" TV WITH 1" CUTOUT ',3516.800000,11468.800000,293.066667,955.733333,0.234678,'2012','2013',0.234678 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','032-9113 ','CA LGE 60" TV WITH 2" CUTOUT ',2783.040000,11152.960000,231.920000,929.413333,0.199701,'2012','2013',0.199701 UNION ALL
SELECT NEWID(),'1234 ','DUSTIN SNEEZER','AL ','400-8598 ','AME350 25 1/2 X 16 1/2 X 35 1/4 HSCT 44ECT-C 040.99 COVER ',5724.980000,11098.540000,477.081667,924.878333,0.340296,'2012','2013',0.339338 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','010-2153 ','STRAP PP 1/2 X 8000 WHITE 9 X 8 CORE ',13056.000000,50773.920000,1088.000000,4231.160000,0.204543,'2012','2013',0.208469 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','002-7355 ','RENTAL FOR OVAL 415 STRAPPER - FEBRUARY 2013 ',3492.000000,41400.000000,291.000000,3450.000000,0.077786,'2012','2013',0.078805 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','010-2158 ','STRAP PP 3/8 X 16,000 FT 42/SKID BULK PK ',10548.720000,41186.040000,879.060000,3432.170000,0.203900,'2012','2013',0.204007 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','002-7004 ','AUTOMATIC STRAPPER MODEL 415A 1/2" ARCH 56H X 32W ',3150.000000,18500.000000,262.500000,1541.666667,0.145496,'2012','2013',0.145496 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','008-8143 ','POLYTUBING - 6 X 1050, 4MIL, CLR NOM ',3567.300000,13597.100000,297.275000,1133.091667,0.207831,'2012','2013',0.207831 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','008-8139 ','POLYTUBING 2" 4MIL NOM CLR 1500FT 5RL/CS ',4002.000000,12630.000000,333.500000,1052.500000,0.240620,'2012','2013',0.239504 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','008-8144 ','POLYTUBING - 8 X 1050-4MIL-CLR-NOM ',2138.500000,11050.000000,178.208333,920.833333,0.162148,'2012','2013',0.162148 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','010-2150 ','STRAP PP 1/2 IVORY 8 X 8 CORE ',2790.130000,10847.470000,232.510833,903.955833,0.204590,'2012','2013',0.206021 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','008-8142 ','POLYTUBING 3 X 1050 4MIL CLR NOM ',3212.500000,7578.500000,267.708333,631.541667,0.297701,'2012','2013',0.297701 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7342 ','CENTER BELT GUIDE EXA15501B FOR 515 STRAPPER ',1523.050000,3496.700000,126.920833,291.391667,0.303411,'2012','2013',0.301442 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7341 ','PHOTO CELL E35AT11 FOR 515 STRAPPER ',752.520000,2321.880000,62.710000,193.490000,0.244769,'2012','2013',0.246878 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7162 ','CUTTER FOR 415 STRAPPER EX-560 ',758.870000,1834.450000,63.239167,152.870833,0.292624,'2012','2013',0.274886 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','010-3041 ','STRAP PE 5/8 X .030 X 3600 16 X 6 GREEN ',503.100000,1633.860000,41.925000,136.155000,0.235427,'2012','2013',0.235427 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7163 ','COMPRESSION HEAD FOR 415 STRAPPER EX-561 ',422.190000,1401.060000,35.182500,116.755000,0.231559,'2012','2013',0.233785 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-9903 ','HEATER BANK FOR C-PAK HEAT TUNNEL 1720-002 ',450.000000,1350.000000,37.500000,112.500000,0.250000,'2012','2013',0.250000 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7144 ','ROLLER #EX-403 FOR OVAL STRAP MACHINE ',428.380000,1285.000000,35.698333,107.083333,0.250020,'2012','2013',0.250016 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7161 ','PLUNGER FOR 415 STRAPPER EX-562 ',483.840000,1129.000000,40.320000,94.083333,0.299992,'2012','2013',0.299992 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','007-3180 ','MICROFOAM 1/32 48 X 2000 S24 P 12 2RL/BNDL ',288.920000,1121.480000,24.076667,93.456667,0.204849,'2012','2013',0.202614 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7065 ','CONVEYOR BELT 51-035 ',362.110000,1086.330000,30.175833,90.527500,0.250000,'2012','2013',0.250000 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7145 ','ROLLER #EX-445 FOR OVAL STRAP MACHINE ',452.230000,1018.480000,37.685833,84.873333,0.307490,'2012','2013',0.299217 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7106 ','HEATER BLADE P/N EX-553 ',295.160000,861.260000,24.596667,71.771667,0.255235,'2012','2013',0.246895 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','010-1006 ','STRAP STEEL 3/4 X .020 REGULAR DUTY ',426.980000,806.060000,35.581667,67.171667,0.346282,'2012','2013',0.348373 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7328 ','OVAL CASTERS 420S-R ',334.950000,778.650000,27.912500,64.887500,0.300781,'2012','2013',0.300781 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','712-4520 ','STICKUM DOUBLE SIDED 2" 24RL/CS ',208.320000,777.360000,17.360000,64.780000,0.211346,'2012','2013',0.215932 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','008-8004 ','POLYTUBING 12 X 1075'' 4MIL FLAT ',116.520000,740.880000,9.710000,61.740000,0.135899,'2012','2013',0.135899 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7291 ','BRAKE LEVER EX-807 FOR 415 STRAPPER ',117.180000,664.080000,9.765000,55.340000,0.149988,'2012','2013',0.149988 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7066 ','BELT 51-036 ',199.920000,599.720000,16.660000,49.976667,0.250012,'2012','2013',0.250012 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7349 ','STRAP EJECTOR FOR 515 STRAPPER ',226.350000,567.900000,18.862500,47.325000,0.284985,'2012','2013',0.284985 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7098 ','STRAP TENSION CONTROL SECONDARY VR-1 ',168.840000,566.160000,14.070000,47.180000,0.229714,'2012','2013',0.229714 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7375 ','ROCKET ROLLER #EX-6688 ',191.800000,487.060000,15.983333,40.588333,0.282532,'2012','2013',0.275658 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7159 ','PIN FOR 415 STRAPPER TPF-313A ',204.870000,418.150000,17.072500,34.845833,0.328833,'2012','2013',0.329180 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','008-8025 ','POLYTUBING 13" X 1050'' .004 NOMINAL 1050/RL ',76.260000,400.440000,6.355000,33.370000,0.159974,'2012','2013',0.159974 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7286 ','FOOT SWITCH #0710-1600-01 FOR RQ-8 STRAPPER ',97.500000,390.000000,8.125000,32.500000,0.200000,'2012','2013',0.200000 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7279 ','SEAL MOTOR FOR RQ8 #900-06-10110 ',129.010000,367.200000,10.750833,30.600000,0.259990,'2012','2013',0.259990 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7318 ','HEATER ASSEMBLY 900-01-41002-3 ',196.200000,364.200000,16.350000,30.350000,0.350107,'2012','2013',0.350107 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7157 ','SOLENOID PIN FOR 415 STRAPPER EX-429A ',120.110000,269.700000,10.009167,22.475000,0.308124,'2012','2013',0.311972 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7185 ','PIN STRAPPER #EXA-503-A ',112.940000,262.670000,9.411667,21.889167,0.300684,'2012','2013',0.302346 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7308 ','MAGNETIC CLUTCH TNC 06-12 900-01-12301 ',82.400000,259.200000,6.866667,21.600000,0.241217,'2012','2013',0.241217 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7166 ','HEATER MOUNT FOR 415 STRAPPER EX-552 ',97.280000,248.960000,8.106667,20.746667,0.280961,'2012','2013',0.280960 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7184 ','HEATER STRAPPER INSULATOR # EX-554 ',99.920000,230.680000,8.326667,19.223333,0.302238,'2012','2013',0.284774 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7078 ','BEARING #40-689 ',74.000000,221.600000,6.166667,18.466667,0.250338,'2012','2013',0.250338 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7154 ','POST FOR 415 STRAPPER EX-901 ',83.030000,218.290000,6.919167,18.190833,0.275554,'2012','2013',0.326064 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','009-5104 ','SHRINK 17 CF X 75GA 3500'' 701 ',152.380000,218.280000,12.698333,18.190000,0.411104,'2012','2013',0.411104 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7272 ','CAM ROLLER FOR 415 STRAPPER ',67.080000,214.590000,5.590000,17.882500,0.238151,'2012','2013',0.238151 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7290 ','MAIN CHUTE FOR 415 STRAPPER EX-437 ',80.780000,207.710000,6.731667,17.309167,0.280009,'2012','2013',0.280009 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','007-1020 ','BUBBLE/RENEW 1/2 X 48 X 250 P-12 S-24 #70895 ',139.550000,205.150000,11.629167,17.095833,0.404844,'2012','2013',0.404844 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7497 ','GUIDE ROLLER B #TSL1967 FOR OVAL STRAPPER ',67.290000,193.910000,5.607500,16.159167,0.257618,'2012','2013',0.260132 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7076 ','BEARING #40-690 ',63.450000,190.890000,5.287500,15.907500,0.249469,'2012','2013',0.249469 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7703 ','FIXED CASTER 0890-130284 FOR RQ8 STRAPPER ',60.240000,181.440000,5.020000,15.120000,0.249255,'2012','2013',0.249255 UNION ALL
SELECT NEWID(),'0223B ','MARY ZUUL','AL ','003-7489 ','FEED ROLLER ASSY UPPER 900-01-7300002 STRAPACK ',92.100000,171.200000,7.675000,14.266667,0.349791,'2012','2013',0.349791 UNION ALL
SSRS Report Setup details:
Groups: <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Detail Group
Name: Detail
GroupOn: [ROWGUID]
PageBreaks: none-default
Sorting: Sortby [SPRSNSLN] - Z to A
Sorting2:
Expression: =IIF(Parameters!ReportType.Value = "COST", Fields!LTMCostAmt.Value,Fields!LTMSpreadAvg.Value)
- Z to A
Visibility: show
Filters:
Expression: =INT(IIF(Parameters!ReportType.Value = "COST", Fields!LTMCostAmt.Value,Fields!LTMSpreadAvg.Value))*100
Type: Integer
Operator: Top N
Value: 50
Variables: none-default
Advanced: none-default
Parameters:
Name: ReportType
DataType: Text
Allow blank: unchecked
Allow null: unchecked
Allow multiple: unchecked
Visible
Available Values: Specify Values: Label and Value: =Split("COST,SPREAD",",")
Default Values: Specify Values: ="COST"
Advanced: default
Tablix is default settings
January 23, 2014 at 12:58 pm
I resolved the issue. Sometimes it just takes taking the time to document the issue and then posting it, because the answer came as soon as I saw it posted. I right clicked in the first grouped column; added a parent group and grouped that [SPRSNSLN] and sorted it with the same sort expressions as the Detail group and viola. The salespersons are now showing their individual top 50s within each branch.
Sometimes, I just need to see my own work from a outside perspective. 😀
I hope this helps someone in the future.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply