SSRS 2014 Nav2016 Calculated field Syntax

SSRS Report. Needing to add a calculated field in SSRS 2014 that would compute Field name Qty: Don’t know the correct syntax for the following calculation: This is the calculated field within NAV 2016 Sum(“Warehouse Entry”.Quantity WHERE (Location Code=FIELD(Location Code),Bin Code=FIELD(Bin Code),Item No.=FIELD(Item No.),Variant Code=FIELD(Variant Code),Unit of Measure Code=FIELD(Unit of Measure Code),Lot No.=FIELD(Lot No. Filter),Serial No.=FIELD(Serial No. Filter)))

Any help is appreciated.

Thanks

Hello, where is the calculated field/flowfield located?

I’m trying to create it within SSRS 2014. The actual field is in table 7302 field Quantity (Property) CalcFormula

maybe this helps

SELECT        sum(dbo.[ADEPLAST Go Live$Warehouse Entry].Quantity)
FROM            dbo.[ADEPLAST Go Live$Bin Content] INNER JOIN
                         dbo.[ADEPLAST Go Live$Warehouse Entry] ON 
                         dbo.[ADEPLAST Go Live$Bin Content].[Location Code] = dbo.[ADEPLAST Go Live$Warehouse Entry].[Location Code] AND 
                         dbo.[ADEPLAST Go Live$Bin Content].[Bin Code] = dbo.[ADEPLAST Go Live$Warehouse Entry].[Bin Code] AND 
                         dbo.[ADEPLAST Go Live$Bin Content].[Item No_] = dbo.[ADEPLAST Go Live$Warehouse Entry].[Item No_] AND 
                         dbo.[ADEPLAST Go Live$Bin Content].[Variant Code] = dbo.[ADEPLAST Go Live$Warehouse Entry].Quantity AND 
                         dbo.[ADEPLAST Go Live$Bin Content].[Unit of Measure Code] = dbo.[ADEPLAST Go Live$Warehouse Entry].Weight

SELECT
[Company$Bin Content].[Bin Code]
,[Company$Bin Content].[Item No_]
,[Company$Bin Content].[Default]
,[Company$Warehouse Entry$VSIFT$5].[SUM$Qty_ (Base)]
FROM
[Company$Bin Content]
INNER JOIN [Company$Warehouse Entry$VSIFT$5]
ON [Company$Bin Content].[Item No_] = [Company$Warehouse Entry$VSIFT$5].[Item No_]
dbo.[Company$Bin Content].[Location Code] = dbo.[Company$Warehouse Entry$VSIFT$5].[Location Code]AND
dbo.[Company$Bin Content].[Bin Code] = dbo.[Company$Warehouse Entry$VSIFT$5].[Bin Code] AND
dbo.[Company$Bin Content].[Item No_] = dbo.[Company$Warehouse Entry$VSIFT$5].[Item No_] AND
dbo.[Company$Bin Content].[Variant Code] = dbo.[Company$Warehouse Entry$VSIFT$5].Quantity AND
dbo.[Company$Bin Content].[Unit of Measure Code] = dbo.[Company$Warehouse Entry$VSIFT$5].Weight
WHERE
[Company$Bin Content].[Bin Code] IN (N’CONT-01’, N’CONT-02’, N’CONT-03’, N’CONT-04’, N’CONT-05’, N’CONT-06’, N’CONT-07’, N’CONT-08’, N’CONT-09’, N’CONT-10’, N’CONT-11’)

The goal is to calculate a field that has the computation below. Why displaying the following info from the Bin Contents table. Bin Code, item #, Qty(Calculated field below)

Sum(“Warehouse Entry”.Quantity WHERE (Location Code=FIELD(Location Code),Bin Code=FIELD(Bin Code),Item No.=FIELD(Item No.),Variant Code=FIELD(Variant Code),Unit of Measure Code=FIELD(Unit of Measure Code),Lot No.=FIELD(Lot No. Filter),Serial No.=FIELD(Serial No. Filter)))

Hi BKR2016A,

If your question is how to SUM with condition in SSRS. You can find some examples here: stackoverflow.com/…/carrying-out-a-sumif-like-operation-using-sql-server-report-builder

Thanks,

Just a side question to you [mention:5991527fe4b448a5a8de6aa2f8abf057:e9ed411860ed4f2ba0265705b8793d05] . Why would you need to do this in SSRS? Just wandering why a client would come up with such a requirement?

You can either group them with those specified fields or use SUM with IIF condition