BAQ Multi Company - No Data

I moved my BAQs to have access to All Companies. I set the BAQ to CurCompany. Is that correct?

When I run it in either company it works for me but it is not working for the end user. It never returns any data.

Does the end user have access to the other companies? If not you can create an all company baq, but the system will still restrict the user to only the companies they have access to.

Agree with @Doug.C . It has to do with permission.

They have access to only their company and the BAQ is set for All Company. They can open and click refresh but not data returns. IT works for me and we have the same permissions.

The fact that it works means the BAQ should be ok. If you want, you can try to paste the text version of the BAQ. There are several joins and criteria that we can’t see from the picture.

Cross company BAQ?

Make sure they also have permission to the Plant… I think PartPlan is one of those tables Epicor will wrap security around.

1 Like

Right now we are in a test environment and we are both security managers. He has access to his company and plant. I have access to both.

I really think it is something simple I am missing for this.

Is this what you are looking for? (I don’t understand why some it out of the box)

    select 
	[PartTran].[TranDate] as [PartTran_TranDate],
	[DMRHead].[DMRNum] as [DMRHead_DMRNum],
	((case when  DMRHead.Checkoff3 = 1  then  'Prod'  else  (case when  DMRHead.Checkoff4 = 1  then  'Pur'  else  '' end) end)) as [Calculated_Type],
	[Reason].[ReasonCode] as [Reason_ReasonCode],
	[Reason].[Description] as [Reason_Description],
	[DMRHead].[CommentText] as [DMRHead_CommentText],
	[DMRHead].[JobNum] as [DMRHead_JobNum],
	[DMRHead].[OprSeq] as [DMRHead_OprSeq],
	[DMRHead].[AssemblySeq] as [DMRHead_AssemblySeq],
	((case when  DMRHead.MtlSeq = 0  then  DMRHead.OprSeq  else  DMRHead.MtlSeq end)) as [Calculated_JobSeq],
	[DMRHead].[PartNum] as [DMRHead_PartNum],
	[DMRHead].[PartDescription] as [DMRHead_PartDescription],
	[DMRHead].[WarehouseCode] as [DMRHead_WarehouseCode],
	[DMRHead].[BinNum] as [DMRHead_BinNum],
	[DMRHead].[VendRMANum] as [DMRHead_VendRMANum],
	[DMRHead].[TotDiscrepantQty] as [DMRHead_TotDiscrepantQty],
	[DMRHead].[TotAcceptedQty] as [DMRHead_TotAcceptedQty],
	[DMRHead].[TotRejectedQty] as [DMRHead_TotRejectedQty],
	(DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty - DMRHead.TotRejectedQty) as [Calculated_QtyRemaining],
	((DMRHead.AvgBurUnitCost + DMRHead.AvgLbrUnitCost + DMRHead.AvgMtlBurUnitCost + DMRHead.AvgMtlUnitCost + DMRHead.AvgSubUnitCost) * QtyRemaining) as [Calculated_CostRemaining],
	[PartTran].[TranType] as [PartTran_TranType],
	[Vendor].[Name] as [Vendor_Name],
	[DMRHead].[CheckOff2] as [DMRHead_CheckOff2],
	[DMRHead].[CheckBox11] as [DMRHead_CheckBox11],
	[DMRHead].[CheckBox02] as [DMRHead_CheckBox02],
	[DMRHead].[CheckBox03] as [DMRHead_CheckBox03],
	[DMRHead].[CheckBox04] as [DMRHead_CheckBox04],
	[DMRHead].[CheckBox05] as [DMRHead_CheckBox05],
	[DMRHead].[CheckBox06] as [DMRHead_CheckBox06],
	[DMRHead].[CheckBox07] as [DMRHead_CheckBox07],
	[DMRHead].[CheckBox08] as [DMRHead_CheckBox08],
	[DMRHead].[CheckBox09] as [DMRHead_CheckBox09],
	[DMRHead].[OutProcess_c] as [DMRHead_OutProcess_c],
	[PartPlant].[BuyerID] as [PartPlant_BuyerID],
	(convert(int,datediff(day,PartTran.TranDate,Constants.Today))) as [Calculated_Aging]
from Erp.DMRHead as DMRHead
inner join Erp.PartTran as PartTran on 
	DMRHead.Company = PartTran.Company
	and DMRHead.DMRNum = PartTran.DMRNum
	and ( PartTran.ActionNum = 0  )

inner join Erp.Reason as Reason on 
	PartTran.Company = Reason.Company
	and PartTran.InvAdjReason = Reason.ReasonCode
	and ( Reason.ReasonType = 'D'  )

left outer join Erp.Vendor as Vendor on 
	DMRHead.Company = Vendor.Company
	and DMRHead.VendorNum = Vendor.VendorNum
inner join Erp.PartPlant as PartPlant on 
	PartPlant.Company = DMRHead.Company
	and PartPlant.PartNum = DMRHead.PartNum
	and ( PartPlant.Plant = @CurrentPlant  )

where (DMRHead.Company = @CurComp  and DMRHead.OpenDMR = 1)

With the inner join and criteria being the plant, is he in the same plant as you when you are in his company? Is his company multi site?

@Kimberley. Please check the suggestion by @Doug.C regarding the login plant. Another option is to export the BAQ and requests the other user to import with a different name and run and check. At least you can find whether its an issue with data or BAQ.

We had to make an SSRS report which accessed the Epicor database in order for people to see data that was in a company they might not normally have access to. We didn’t report much, but it was a way for them to search on a customer name (minimum of 3 characters for the like) and return which salesrep and company was responsible for that customer. There just isn’t a way with BAQ to get access to companies they don’t have permissions for.

@MLamkin Try with External BAQ.

So we are going Multi Company and the current Company is multi plant.

I discovered they can run the BAQ if they have access to both Companies. Ok WHY? I am opening a ticket with Epicor.

Because they have access to both companies. Are you checking “Cross Company” in the BAQ. That means to pull the data across all companies. If you clear that, it should only pull data in for the logged in company.

1 Like

Ok that makes sense now. So my BAQ’s need to have Cross Company and uncheck the All companies?? correct.

All companies means the BAQ is available to use in all companies.
Cross Company means it will pull data from all of the companies the user has access to.

1 Like

“All Companies” means this query is available to each company.

“Cross Company” means the data this query gets is for all the companies the user has access to.

1 Like

In other words dont uncheck All Companies… Leave that one checked :slight_smile: at the least.

1 Like

So as Haso summarized, if you don’t want users to see (a)cross companies, uncheck that box. If you want all users to be able to run the query, check the All Companies box.

Another practice that we did is we named our BAQs to start with the company ID if it was specific for that company and added an “ALL-” prefix to Cross Company or All Company queries.