SSRS expression to Summarize by taking Max value of sub groups

• Naidu PK

  SSCrazy

  Points: 2481

  More actions

  May 11, 2018 at 9:44 am

  #338040

  Hi,
  I have the below #GroupingSSRS as my dataset to SSRS report. Amount value stays same per product code. To get the Amount total for each description, I have to pick just one value for each product code and SUM those values across the Description column. ProductCode cannot be hard coded as one Description can have either one or 10's of productcodes. Description can be hard coded in ssrs expressions. 
  I know it can be done easily in SQL but my requirement is to do this only in SSRS. 
  In SSRS, I took a table and two rows are hard coded on column 1 (row 1: Reciepts and row2: Sales). I tried the below expression for Receipts group but did not work: 
  = IIF( Fields!Description.Value="Receipts", SUM(IIF( Max(Fields!Amount.Value), "ProductCode"), Nothing))
  I need a way to group by ProductCode to pick one value under each Description group.

      DROP TABLE IF EXISTS #GroupingSSRS
    CREATE TABLE #GroupingSSRS
    (
        Description VARCHAR(25) NOT NULL,
        ProductCode TINYINT NOT NULL,
        ClientCode TINYINT NOT NULL,
        Amount INT NOT NULL

      )

      INSERT #GroupingSSRS
    (
      Description,
      ProductCode,
        ClientCode,
      Amount
    )
    VALUES
    ( 'Receipts',       1, 1,      10       ),
    ( 'Receipts',       1, 2,      10       ),
    ( 'Receipts',       1, 3,      10       ),
    ( 'Receipts',       2, 4,      20       ),
    ( 'Receipts',       2, 5,      20       ),
    ( 'Receipts',       2, 6,      20       ),
    ( 'Sales',       3, 7,      0       ),
    ( 'Sales',       3, 8,      0       ),
    ( 'Sales',       4, 9,      5       ),
    ( 'Sales',       4, 10,      5       )

  /* Below is the way to do in SQL but similar output is needed for SSRS using report expressions */ 
    
    SELECT Description, SUM(IQ.Amt) Total   /* Gets Total at Description level by picking only one amount value per productcode*/
    FROM
  (    SELECT GS.Description, GS.ProductCode, MAX(GS.Amount) Amt FROM #GroupingSSRS AS GS  /* Gets only one value per description per productcode */
        GROUP BY GS.Description, gs.ProductCode
    ) IQ
    GROUP BY IQ.Description


  Description    Total
  Receipts    30
  Sales    5

  Thanks,
  Naveen.
  Every thought is a cause and every condition an effect

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  May 16, 2018 at 9:02 am

  #1990688

  The expression you are using appears to me to be a problem:
  = IIF( Fields!Description.Value="Receipts", SUM(IIF( Max(Fields!Amount.Value), "ProductCode"), Nothing))

  The 2nd IIF function doesn't have a boolean expression, it instead expects the MAX value of the Amount field to be boolean, and then there's a closing parenthesis before you have a 2nd value expression, which again, isn't making sense to me.   I'm wondering if you perhaps need it this way:
  =IIF(Fields!Description.Value="Receipts", SUM(Max(Fields!Amount.Value), "ProductCode"), Nothing)

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)