Report Server aggregation over groups by line

Question

Maybe this is a newbie question, but

Imagine I have a report that shows the sales-order list pr sales-rep, itemized to sku level, and want to show how many percent of the total sale of 1 sku, the sales-rep has sold.

I.e.

Sales-person

• List of orders

       - List of items              no sold:  5  out of this months total 942
  

Example:

John Doe

- Order #12312

      - SKU SP1231     Sold 5 . Month total 445

      - SKU SP4141     Sold 63 .  Month total 300

Emma Doe

- Order #123324

      - SKU SP1231     Sold 65 . Month total 445

      - SKU SP4141     Sold 2 .  Month total 300

etc

The Month total figure is the number of items sold of that particular sku in the total reporting period.

How do I go about adding this number? If I use Fields!TotalAmount.Value it gives the total as the group'ed total. i.e. how many of sku Y was sold on order X by sales-rep Z.

I need the global total of sales of that particular SKU.

If i say SUM(Fields!Amount,Nothing) to set global scope, it gives the sum of ALL sku's, not just the sku in question.

How do I do this?

EDIT: The Report Server is SSRS, the report uses a shared Datasource that is a Report Model already hosted on the reporting server, which points to a SQL Server database with the contents.

Answer 1

You didn't say what DBMS you are using (not Oracle clearly from the Fields!Amount syntax). Does this work for your DBMS?:

with sku_sales as
( select sku, sum(value) as sku_value
  from   sales
  group by sku
)
select sales.salesperson, sum(sales.value), sku_sales.sku_value
from   sales
join   sku_sales on sku_sales.sku = sales.sku
group by sales.salesperson, sku_sales.sku_value

Answer 2

What would I do is select Sku total sum in your report dataset using comma separated lists:

-- testing data
DECLARE @Order TABLE (ID INT, SalesRepID INT, Date DATETIME)
INSERT INTO @Order
SELECT 1, 1, GETDATE() UNION
SELECT 2, 2, GETDATE() UNION
SELECT 3, 1, GETDATE() UNION
SELECT 4, 1, GETDATE() UNION
SELECT 5, 2, GETDATE()

DECLARE @OrderDetail TABLE (ID INT, OrderID INT, SkuID INT, SkuCount INT)
INSERT INTO @OrderDetail
SELECT 1, 1, 1, 10 UNION
SELECT 2, 1, 2, 5 UNION
SELECT 3, 1, 3, 20 UNION
SELECT 4, 1, 4, 10 UNION
SELECT 5, 2, 1, 15 UNION
SELECT 6, 2, 2, 25 UNION
SELECT 7, 2, 3, 15 UNION
SELECT 8, 3, 1, 15 UNION
SELECT 9, 3, 1, 10 UNION
SELECT 10, 3, 3, 10 UNION
SELECT 11, 3, 4, 15 UNION
SELECT 12, 4, 1, 5

DECLARE @Sku TABLE (ID INT, SkuCode VARCHAR(10))
INSERT INTO @Sku
SELECT 1, 'SP1233' UNION
SELECT 2, 'SP2262' UNION
SELECT 3, 'SP1531' UNION
SELECT 4, 'SP4235'

DECLARE @SalesRep TABLE (ID INT, SalesRepName VARCHAR(20))
INSERT INTO @SalesRep
SELECT 1, 'John Doe' UNION
SELECT 2, 'Emma Doe'

    -- filters for testing
DECLARE @StartDate DATETIME, @EndDate DATETIME
SELECT  @StartDate = GETDATE(), @EndDate = GETDATE()

DECLARE @SkuIDList VARCHAR(8000), @SkuSumList VARCHAR(8000)
SELECT @SkuIDList = '', @SkuSumList = ''
--gether all sku IDs and Sum in two comma separated list
SELECT @SkuIDList = @SkuIDList + CONVERT(VARCHAR, OD.SkuID) + ',', 
@SkuSumList = @SkuSumList + CONVERT(VARCHAR, SUM(OD.SkuCount)) + ','
FROM @Order O 
INNER JOIN @OrderDetail OD ON O.ID = OD.OrderID
WHERE O.Date BETWEEN @StartDate AND @EndDate
GROUP BY OD.SkuID
-- remove last ','
SELECT @SkuIDList = SUBSTRING(@SkuIDList, 0, LEN(@SkuIDList)),
 @SkuSumList = SUBSTRING(@SkuSumList, 0, LEN(@SkuSumList))

-- include thouse lists in the main select for your report dataset
SELECT O.ID, OD.SkuID, O.SalesRepID, SR.SalesRepName, S.SkuCode, 
    OD.SkuCount, @SkuIDList AS SkuIDs, @SkuSumList AS SkuSums
FROM @Order O 
INNER JOIN @OrderDetail OD ON O.ID = OD.OrderID
INNER JOIN @Sku S ON OD.SkuID = S.ID
INNER JOIN @SalesRep SR ON O.SalesRepID = SR.ID
WHERE O.Date BETWEEN @StartDate AND @EndDate

Then you can use some custome code to retrieve sum value by sku ID (I have to write in C# currently, you easely convert it to VB):

public int GetSkuSum(string skuSumCSV, string skuIDCSV, int searchSkuID)
{
    string[] strSkuSum = skuSumCSV.Split(',');
    string[] strSkuID = skuIDCSV.Split(',');

    for (int i = 0; i < strSkuID.Length; i++)
    {
        if (Convert.ToInt32(strSkuID[i].Trim()) == searchSkuID)
        {
            return Convert.ToInt32(strSkuSum[i]);
        }
    }
    return 0;
}

Then use it in your textbox Value expression:

=Code.GetSkuSum(Fields!SkuIDs.Value,Fields!SkuSums.Value,Fields!SkuID.Value)