Report Server aggregation over groups by line

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.

From stackoverflow

• 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
  

• 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)