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 BartWe 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 BartYes 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 BartPlease 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 BartOk, 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 BartNo, @@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 bartYes 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 BartJust 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
HmmmSimplest 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
0
Please sign in to leave a comment.
Comments
0 comments