Display count of something on dashboard

[timb4134 (Tim)]

I am working on some dashboards (not EDD dashboards or views) and would like to create some charts that show the dollar value of open orders and also the count of open orders. The dashboard is based off of a BAQ that pulls all order line details. How can I count all unique order numbers (not individual lines) that are open and display on a chart?

[Asz0ka (Adam Szoka)]

You could create a calculated field in your BAQ that determines the number of unique orders, then use it in your dashboard.

One way to get the number of unique orders would be to use the COUNT() aggregate function in a subquery (the subquery would only contain unique orders, no duplicates), then join the subquery to your top query to be able to use the field.

Another way is to use the dense_rank SQL function in a calculated field:

dense_rank() over (partition by OrderHed.Company order by OrderHed.OrderNum ) 
+ dense_rank() over (partition by OrderHed.Company order by OrderHed.OrderNum desc) 
- 1

This should work in your top level query, no subquery required.

[hkeric.wci (Haso Keric)]

Another way is something I call SubSelect

Using Count() in BAQ - Incorrect Syntax

@tmcmullen you can also do Sub-Select SubQuery. Then you wouldnt need to do GROUP BY on your Top-Query. In a Nutshell: Make a Sub-Query but DO NOT Use it on the Designer On your TopLevel Query add the Sub-Query to a Calculated Field On the Sub-Query under SubQuery Criteria you can add WHERE Clause. Example: SubQuery1: [image] [image] SubQuery2: [image] [image] Result: [image] [image] Basically in a simpler view it does SELECT (SELECT count(*) FROM table WHERe...) as YourCalculate…