March 18, 2016 at 12:54 pm
NameOrderNumWHWH_Count
Customer111111111WH1
Customer111111111WH1 1
-----------------------------------------------
Customer122222222WH1
Customer122222222WH22
-----------------------------------------------
Customer133333333WH1
Customer133333333WH2
Customer133333333WH22
-----------------------------------------------
Customer144444444WH1
Customer144444444WH22
-----------------------------------------------
Orders:4 7
Warehouse/Orders = 1.75
I'm trying to create an SSRS report that will count the distinct warehouse's for each order. I've tried using a group variable (varCount = CountDistinct(WarehouseCode) but not having any luck.
Any suggestions?
March 18, 2016 at 2:49 pm
If you're using a query, you could get the calculation there.
COUNT(DISTINCT wh) OVER(PARTITION BY Name, OrderNum) AS WH_Count
I'm not sure how to do it in SSRS.
March 21, 2016 at 11:48 am
A previous developer had all the calculations in the code section of the SSRS report but it no longer works. I'll try re-writing the query with your suggestion.
Thank you!
March 21, 2016 at 2:33 pm
You can't do what Luis suggested because you can't COUNT(DISTINCT) with an OVER clause.
That said, you may want to try to do the aggregations in SSRS because the is presentation level stuff the T-SQL is not designed for. And sorry - I don't know how to do it in SSRS.
To get the equiv of COUNT(DISTINCT WH) OVER (Name, OrderNum) you could do something like below. Using this sample data:
USE tempdb
GO
-- sample data
IF OBJECT_ID('tempdb.dbo.wh_order') IS NOT NULL DROP TABLE dbo.wh_order;
CREATE TABLE dbo.wh_order
(
OrderKey int identity primary key, -- Hopefully you have a unique key
CustomerName varchar(100), -- a better Name than "Name"
OrderNum bigint,
WH varchar(5)
);
-- A unique index such as this will really help performance
CREATE UNIQUE NONCLUSTERED INDEX uq_wh_order
ON dbo.wh_order(OrderNum, CustomerName, WH, OrderKey);
INSERT dbo.wh_order(CustomerName, OrderNum, WH)
VALUES
('Customer1', 11111111, 'WH1'),
('Customer1', 11111111, 'WH1'),
('Customer1', 22222222, 'WH1'),
('Customer1', 22222222, 'WH2'),
('Customer1', 33333333, 'WH1'),
('Customer1', 33333333, 'WH2'),
('Customer1', 33333333, 'WH2'),
('Customer1', 44444444, 'WH1'),
('Customer1', 44444444, 'WH2');
You could query like this:
-- Solution #1: if you have a unique key (OrderKey in this example)
SELECT o.CustomerName, o.OrderNum, o.WH, WH_Count
FROM dbo.wh_order o
CROSS APPLY
(
SELECT CustomerName, OrderNum, WH_Count = COUNT(DISTINCT WH)
FROM dbo.wh_order
GROUP BY CustomerName, OrderNum
) a
WHERE o.CustomerName = a.CustomerName AND o.OrderNum = a.OrderNum;
To get what you posted you would do something like below. Note this is not even a complete solution but demonstrates why you do this presentation stuff at the presentation layer...
WITH Solution1 AS
(
SELECT
x = COUNT(*) OVER (PARTITION BY o.CustomerName, o.OrderNum ) -
ROW_NUMBER() OVER (PARTITION BY o.CustomerName, o.OrderNum ORDER BY (SELECT 1)),
o.CustomerName, o.OrderNum, o.WH, WH_Count
FROM dbo.wh_order o
CROSS APPLY
(
SELECT CustomerName, OrderNum, WH_Count = COUNT(DISTINCT WH)
FROM dbo.wh_order
GROUP BY CustomerName, OrderNum
) a
WHERE o.CustomerName = a.CustomerName AND o.OrderNum = a.OrderNum
)
SELECT CustomerName, OrderNum, WH,
WH_Count = CASE x WHEN 0 THEN CAST(WH_Count as varchar(5)) ELSE '' END
FROM Solution1;
-- Itzik Ben-Gan 2001
March 21, 2016 at 2:53 pm
Alan.B (3/21/2016)
You can't do what Luis suggested because you can't COUNT(DISTINCT) with an OVER clause.
You're right, I didn't test it.
An alternative could be something like this:
WITH CTE AS(
SELECT CustomerName,
OrderNum,
WH,
DENSE_RANK() OVER(PARTITION BY CustomerName, OrderNum ORDER BY wh) AS WH_Count
FROM dbo.wh_order o
)
SELECT CustomerName,
OrderNum,
WH,
MAX(WH_Count) OVER(PARTITION BY CustomerName, OrderNum) AS WH_Count
FROM CTE;
It's certainly possible to do it in SSRS, but I don't remember how to do it.
March 22, 2016 at 7:30 am
Thank you both for the quick response. I'll give each a try.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply