cannot get Query Calculations (relational) to aggregate on one row of report

[Nike_BA]

Hello,

I am trying to summarize shipped quantity by custom order type groups for a fulfillment scorecard, and I have run into a snag that I cannot resolve.

The following is one example of how I am defining the shipped quantity for one custom order type group:

case when [Presentation Layer].[Order Type Grp].[SO Type Cd] = 'Z011' and [Presentation Layer].[Sales Order].[Ref Doc Nbr] starts with '0' then [Presentation Layer].[Sales Order Delivery].[DC Shpd Qty]
else 0
end

This calculation works as expected in a list report that includes the Ref Doc Nbr on the report page along with a Category attribute by which the report is grouped; but if I remove the Ref Doc Nbr from the list, the value rolls up to one line per Category but the value becomes 0. I can resolve the 0 value by checking the the Ref Doc Nbr in the List properties, but then the rows expand by the number of unique Ref Doc Nbr values per Category, even though the Ref Doc Nbr data item has not been added to the List, regardless of which aggregate or roll up aggregate function I use. I should also add that SO Type Cd must be checked in the properties of the List as well for the calculation to return a value other than 0. This does not impact the rows for this specific calculation, but it does impact my at-once calculation which involves multiple order types.

See attached for actual Excel output and example of desired excel output.

This is based on a relational data source (Teradata).

Here is some background on why I am using such a calculation:

The Z011 order type is typically a call-off order type that is used to draw from (i.e. reference) a bulk contract. However, it has also been enabled to pull from available inventory (or at-once), so I need also check for a valid reference document. I tried to validate using an "is not null" statement, but apparently the value is not truly null when no document is referenced as the statement returns true for all values even if it is blank.

[Lynn]

You could try wrapping your statement in a total function. You might need to specify a "for" clause.

Code Select Expand


total (
     case when [Presentation Layer].[Order Type Grp].[SO Type Cd] = 'Z011'
              and [Presentation Layer].[Sales Order].[Ref Doc Nbr] starts with '0'
            then [Presentation Layer].[Sales Order Delivery].[DC Shpd Qty]
            else 0
     end
)


[Nike_BA]

Thanks Lynn,

That worked and did not require the "for" clause. I was a little nervous using this solution as I did not know how that would behave with summary totals and additional percent of total calculations (i.e. percentage of units shipped that are call-offs vs at-once). However, my initial tests for these additional calculations have had positive results.

Thanks again!