Assistance with STAT function

Hi All,

I think I'm misunderstanding how the STAT function works and could really benefit from your expertise.

I have 3 attributes:

Warehouse = [Attribute.Warehouse]

Location = [Attribute.Location]

Container = [Attribute.Container]

The hierarchy is as follows. A Warehouse can have several Locations which in turn can have several Containers. Each Container has a value associated with it, given by the measure [Date: Sum: Quantity].

What I would like to be able to do is display the number of Containers per Location per Warehouse that have quantity greater than 0, essentially [Date: Sum: Quantity] > 0.

Based on the documentation and some posts on the community I have managed to put together the following BQL Expression:

STAT(COUNT,

[Attribute.Location],

[Attribute.Warehouse],

SELECT [Attribute.Container],

[Date: Sum: Quantity] FROM [ALL] WHERE [Date: Sum: Quantity] > 0)

This is the output:

The number of containers I am getting per location is repeated and is the total number of containers with value >0 across all locations rather than the number per location.

If anybody here could give me a second pair of eyes on this it would really help!

Thanks in advance for your help.

Kind Regards,

Usman

Find more posts tagged with

Birst - General Discussions

Comments

Diane McElhiney

STAT(COUNT,

[Attribute.Location],

[Attribute.Warehouse],

SELECT [Attribute.Container], [Attribute.Location], [Attribute.Warehouse]

[Date: Sum: Quantity] FROM [ALL] WHERE [Date: Sum: Quantity] > 0

AND [Attribute.Location] = %0 AND [Attribute.Warehouse] = %1)

Also, try the below for performance:

STAT(COUNT,

[Attribute.Location],

[Attribute.Warehouse],

SELECT [Attribute.Container], [Attribute.Location], [Attribute.Warehouse]

[Date: Sum: Quantity] FROM [ALL] WHERE [Date: Sum: Quantity] > 0 DISPLAY WHERE

[Attribute.Location] = %0 AND [Attribute.Warehouse] = %1)

usman

Hi Diane,

Thanks for the help. I definitely think its a step in the right direction as I was not previously aware how to reference columns in the select statement.

When I created the report based off your suggested expression I get the following:

Which indicates that the total number of containers across all locations and warehouses with Quantity>0 is 1,381.

However when I create a report with Warehouse, Location and Container and filter on Quantity >0 then do a count on the grand summary of the Container Column, I get the following:

Any idea what I may be doing wrong to get this large discrepancy?

Thanks for your help!

Usman

Copyright © 2025 Infor. All rights reserved.