Performance running Sales Documents

Bart De Cattelle

Dear all,

When I run the Sales Documents Report, it takes up to 15 minutes before I get the results.
Is this normal?
Can this be fixed or improved?
Our customer is at SAP B1 2007 PL42 and coresuite version 2.44.

Kind regards

Daniel Ebi

Hello,

perhaps the Coresuite Designer Version 2 does help.

Do you print a big picture?

We have a problem too, but there the SAP crashes somtimes (less than 15 Minutes), because it need's to much RAM.

I hope, this helps.

Kind regards

Daniel

Philipp Knecht

Hi Bart

We had onec a similar issue which was because of wrong DNS Settings of the client.

hth

Bart De Cattelle

Dear Philipp,

I think I have found the problem,.
On row level (Document --> Detail1) I have added a query that collects information about packaging of the article on row level.
Syntax I use :
dim val1 as string
squery = "select UnitMsr, NumPerMsr from " & tb & " where LineNum = "
squery += LD.fstr(Document("LineNum"),"0")
squery += " and DocEntry = "
squery += LD.fstr(Document("DocEntry"),"0")
val1 = LD.QueryData(squery)

This is because this info is not available in the datasource.
Can I add some fields to the datasource "SalesDocuments" so I do not have to run this query on row level? And where can I do this exactly?

Kind regards

Philipp Knecht

Hi Bart

Yes you can do that.
There is the Query1,Query2, Linkfields - Approach exactly for such purposes.
Always ensure that you restrict your query by a WHERE clause.

Then select Document:Row for Query 1 and YourQuery for Query2 and link it with the corresponding fields.

hth

Bart De Cattelle

Dear Philipp,

I have done what you told me and what I found in CoresuiteDesignerIntegration Manual.
I have attached two images. First the settings on my Sales Documents layout definition and second the error I get.

Can you tell me what I am doing wrong?

I need the "UnitofMeasure" and the "quantity per package" from the item on Document Row level.

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-file-uploads/bartdeca/Sales_Documents.rar]Sales_Documents.rar[/url]

Philipp Knecht

Hi Bart

Please be arae that cld is case sensitive. I think that U_Itemcode maybe should be U_ItemCode.
At least this is the typical Message if columns are not matching.

hth

Bart De Cattelle

Dear Philipp,

I have tried both writings, but I still get this error.
Can this have another cause?

Kind regards
Bart

Philipp Knecht

Hi Bart

Ok, my bad, i see.
The linking is by QueryName, FieldName
But since you are using the SalesDocuments" Datasource there the Queryname on the left hand side should be "SalesDocuments".

So create a dummyquery and name it "SalesDocuments" and use that one on the left hand side.

hth

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-image-uploads/philipp/datalink.JPG]datalink.JPG[/url]

Bart De Cattelle

Thans Philipp,

Now it works.

But I still have following problems:

1) how can I make my userquery so it will also know at which tables it has to look :
RDR1 and ORDR for orders
DLN1 and ODLN for deliveries ,...

Is this with O@@RDR ?

2)The problem is that my Userquery is added to the datasource as a sublevel of SalesDocuments while it's only the purpose of getting some extra fields that are not available. Can this be done somehow?
Kind regards
Bart

Philipp Knecht

Hi Bart

No, @@RDR - Replacements will work only Formtypes which have this replacement defined (mostly marketing documents formtypes).

But maybe it helps if you also use DocType and Uinion Select all the possible Values.

You can use multiple LinkFields separated by ";" in the Layoutdefinition like "ItemCode;DocType"

hth

Bart De Cattelle

Thanks Philipp,

But I just get an idea, it is not possible to add these extra fields that i Need into the Datasource SalesDocument itself?
Is this query stored in a file so I can add these extra fields?

This looks easier to me.

Kind regards
Bart

Philipp Knecht

Hi bart

Yes you may use the log queries setting in the config.
After executing the report you'll find the query in:

%temp%dbnamequeries

hth

Bart De Cattelle

Dear Philipp,

I have found the query under temp folder.
I can adjust the query, then save it as a user query and link it with Link Fields?
Or is there a file under C:Program FilesSAPSAP Business OneAddonsCoresuite...
where I can overwrite this query?
Or how do you see it?

Kind regards
Bart

Philipp Knecht

Hi Bart

Just duplicate the layout
- remove the datasource
- add your extracted and modified query in the queries tab as usual


hth

Bart De Cattelle

Dear Philipp,

Little problem : query is too long to be saved in SAP B1....

Kind regards
Bart

Philipp Knecht

Hmmm

Simplest Solution if possible: Remove comments and try again
Or save the query as a function in MS SQL Server (not supported by coresystems but maybe is worth a try) and just call this function from within this query.
Or try to separate into 2 Queries by for example saving a part in #temptable
and reuse it in the second query.

hth