Coalesce(Sum in a Report Studio join statement

Prophet 21
1

Is it possible to use the coalesce and sum functions within a join statement in Report Studio. I can do it with a summary view, but I want to summarize the data without users having to retrieve100Ks of detail lines. Something like this SQL
“coalesce (sum(oe_line.qty_invoiced)) as page_qty_invoiced
left join oe_line on (price_page.price_page_id = oe_line.price_page_uid )”

2

Short answer: kinda depends on what you’re doing and if appropriate. I use COALESCE(SUM(Table.Column),0) sometimes and works just fine.
With the brief amount of information here hard to say if this would work in whatever scenario you are attempting. Check this out: Tips for asking a question on the forum

EDIT: Try this link see if it helps: COALESCE (Transact-SQL) - SQL Server | Microsoft Learn

3

Thanks Clint. Report Studio does not allow users to write the sql query directly. Adding related tables is limited to join statements, so this works:

  left join oe_line on (
    price_page.price_page_uid = oe_line.price_page_uid
  )

where as this fails:

coalesce (sum(oe_line.qty_invoiced,0)) as page_qty_invoiced

left join oe_line on ( price_page.price_page_uid= oe_line.price_page_uid )

I was hoping a Report Studio user may have found a workaround.
I hope I got the code block formatting correct here.

4

Apologies, guess I am unaware of Report Studio. This a P21 Item I will assume then. My assumption was you meant SSMS my apologies for the misunderstanding.

1 Like
5

No worries, Thanks for your time.

6

Hi Fred,

You cannot use SQL aggregate functions directly in the JOIN clause of a statement, and at this time it’s not possible to specify subqueries as a related table in Report Studio, unless you define them as views in MSSQL.

The Related Table box on top validates that the object name exists in MSSQL. Your best approach in this case is to create a view that is already filtered.