Sales Person in XLR reports

[Former Member]

Part of one of our XLR reports shows the quantities sold by salesperson. The problem is that the salesperson dimension is taken from the row and not the main document. We do not process salespeople at the row level but at the doc level and sometimes B1 leaves the row as u2018No sales employeeu2019.

Is there a way to get XLR to report on the header and not the row for this?

[Former Member · Accepted solution]

Hi Ralph

My appologies, I am so used to DI API, I forgot that the templates for oDocuments & oDocumentLines are split into Marketing Document folders. Refer oInvoices for invoice template files.

Kind regards

Peter Juby

[Former Member · Accepted solution]

Thanks for that. As it turned out it was quicker to run a query to find those affected and adjust them manually! I don't suppose I can do anything for cclosed orders??

Thanks again

[Former Member]

Hi Ralph

If you are still referring to the Sales Employee field, Yes you can update even if the Sales Order is closed. There are only a few fields that can be changed on marketing documents after adding them, and Sales Employee is one of them.

Kind regards

Peter Juby

[Former Member]

Although being a SU once a SO is closed the row, sales employee and owner become greyed out. Do you mean I would need to do this using DTW?

[Former Member]

Hi Ralph,

If those SO had been going through approval procedure, row level sales would be grayed out. Using DTW to update those is worrisome to me.

Only if the SO created without approval, that field can be changed even it had been closed.

Thanks,

Gordon

[Former Member]

We don't use approvals but they are greyed out?

I'm about to go on a weeks holiday so if I don't respond it's not that I've given up!!

I'll pick this up again when I get back.

Thanks again chaps.

[Former Member]

It is not grayed out completely. It should have a down arrow to let you select from the list.

[Former Member]

No the drop down box is locked and greyed out!

[Former Member]

That might be related to your PL. I am not at the same level with you.

[Former Member]

What PL do you have? When I get back I plan to start testing 2007 so perhaps things change in there.

[Former Member]

Just testing in 2007 and hope the problem will be going away.

Thanks,

Gordon

[Former Member]

Hi Ralph

You will notice that the Sales Person field on row level and header level can be updated even for closed invoices. My suggestion is to use the oDocuments & oDoucmentLines templates through DTW to run an update. I have not tried this with DTW but it is possible through the DI API. If you do not succeed, let me know and I can send you an Excel workbook with a VB Macro code to update via DI API. Just let me know your Excel version and SAP Business One version.

Kind regards

Peter Juby

[Former Member]

DTW sounds like a good idea. I don't have the document or documentlines templates though? B1 verssion is 2005A PL44 Excel 2000 SP1. Are you able to email the templates?

Thanks.

Forum doesn't let me include Email adddress but I've shown it on my profile.

[Former Member]

Hi Ralph

The quickest solution would be a Formatted Search at row level on SP that copies the header value.

Kind regards

Peter Juby

[Former Member]

Yes this would help going forward (and I have now done this), but it would be good to re do the reports to get the historical data as well.

Ta