BAQ Help: Using BAQ Constant CurrentUserID in the Subquery Criteria

Kinetic ERP
1

Struggling a bit, so I’m pitching it out for hive mind :honeybee: I managed to get around it by creating a calculated field that holds the value of the constant, but I thought I’d ask the group since there are many things I never knew you could do in the BAQ editor and this might be one of them.

I’ve got a query that allows various levels of sales management to see their own special quotes (that we’ve marked as Projects in dbo.quotehed.checkbox03) PLUS those of the Sales Reps who report to them. It’s perfectly dynamic, with a couple of caveats to the ‘rule’ thrown in there. The business goal is to manage special quotes in an updateable dashboard where confidence percentages, dates, and comments can be edited quickly and easily.

A new twist - I need one person to see the quotes of his coworker, who is NOT one of his direct reports. It seems this is quite easy in SQL, but I do not see a way to use the BAQ Constants in the Subquery Where clause editor - except in the expression field. My need is that I check the value of the constant, not just it to compare to another value.

I know it sounds wierd - but why cant’ you check the Constants’ values in the criteria editor? Maybe I only want the query to work if certain things are true - like only if it’s a Friday in April or something…

This is the abbreviated query and the last line of the where clause is what I need to recreate in the BAQ

declare @CurrentUserID nvarchar(10) = 'gthorpe'
 
select 
	[QuoteHed].[Company] as [QuoteHed_Company],
	[Customer].[CustID] as [Customer_CustID],
	[Customer].[Name] as [Customer_Name],
	[Customer].[City] as [Customer_City],
	[Customer].[State] as [Customer_State],
	--....
	[SalesRep].[Name] as [SalesRep_Name]
from dbo.QuoteHed as QuoteHed
   inner join Erp.Customer as Customer on QuoteHed.Company = Customer.Company and QuoteHed.CustNum = Customer.CustNum and ( Customer.GroupCode <> 'IC'  )
   inner join Erp.QSalesRP as QSalesRP on QuoteHed.Company = QSalesRP.Company and QuoteHed.QuoteNum = QSalesRP.QuoteNum and ( QSalesRP.PrimeRep = 1  )
   left outer join Erp.SalesRep as SalesRep on QSalesRP.Company = SalesRep.Company and QSalesRP.SalesRepCode = SalesRep.SalesRepCode
   left outer join  (select [OrderDtl].[Company] as [OrderDtl_Company],
						[OrderDtl].[QuoteNum] as [OrderDtl_QuoteNum],
						(max (OrderDtl.OrderNum)) as [Calculated_MaxOrder]
					from Erp.OrderDtl as OrderDtl
					group by [OrderDtl].[Company],[OrderDtl].[QuoteNum]
				)  as SubQuery2 on QuoteHed.Company = SubQuery2.OrderDtl_Company and QuoteHed.QuoteNum = SubQuery2.OrderDtl_QuoteNum
   inner join Erp.SalesTer as SalesTer on SalesTer.Company = QuoteHed.Company and SalesTer.TerritoryID = QuoteHed.TerritoryID
   inner join Erp.Region as Region on SalesTer.Company = Region.Company and SalesTer.RegionCode = Region.RegionCode
where (QuoteHed.QuoteClosed = 0  and QuoteHed.CheckBox03 = 1)
	and ((QSalesRP.SalesRepCode like substring(@CurrentUserID, 1,7)  
		or SalesRep.RepReportsTo like substring(@CurrentUserID, 1,7)  
		or QSalesRP.SalesRepCode = case when @CurrentUserID ='brasil' then 'rbarakat' else @CurrentUserID end  
		or QSalesRP.SalesRepCode = case when @CurrentUserID='gthorpe' then '2' else @CurrentUserID end  
		or SalesRep.RepReportsTo = case when @CurrentUserID='gthorpe' then '2' else @CurrentUserID end)
		or (@CurrentUserID='gthorpe' and QSalesRP.SalesRepCode in ('2','dtromans')))
order by Customer.Name
2

I’ve gotten around this with a little trick. I’ll add a criteria for a field such as QSalesRP.Company, make it equal to an expression. The expression is something like this:

(case when QSalesRP.SalesRepCode = Constants.CurrentUserID 
      then QSalesRP.Company
      else 'XXXXXX'
end)

Some, QSalesRP.Company = QSalesRP.Company if what you want to evaluate is true, otherwise it forces a false statement (QSalesRp.Company = ‘XXXXXX’) in there.

1 Like
3

A dirty secret about BAQ constants - you can reference all of them by replacing the prefix Constants. with @. For example, @CurrentUserID.

1 Like
4

This I didn’t know- thanks for that tidbit!

Yep - that’s how I’m doing my other criteria and it works great.

To be clear - I should have added a screenshot in my original post. The question is how do I use the constants where the red box is? This shows my calculated field used as the workaround.

image
image956×236 16 KB

5

Another silly BAQ trick I’ve used is,

where SalesRep.Company = case when @CurrentUserID = 'gthorpe' then SalesRep.Company else null end

If it’s controlling whether or not to return something from a specific table, you can do similar shenanigans in an outer join as well.

Apart from sneaking canned constants into unexpected places, knowing about the @-prefix notation can save a lot of frustration. You can name your own parameter @CurrentUserID or similar for any other canned constant, and then your query will do unexpected things. More importantly, last I checked, BAQ’s input sanitization doesn’t apply to those constants. User Account Security Maintenance will accept Robert'); DROP TABLE Students;-- as a new user ID.