SQL Queries

Bart De Cattelle

Dear All,

We have a complicated sql_query and now we have a crystal report that is showing the result of this query.
We thaught that it would be possible to save this query in SAP Business One and then make a report with Coresuite, but the query can not be executed in SAP Business One and it is not added as a datasource in our report. The query works fine in SQL.

Is there a way that we can use this query as a datasource? Or another solution?

Kind regards
Bart

Philipp Knecht

Hi Bart

Every Query you save in SAP B1 can be used as datasource.
(Please don't use the Query Groups System or General)

If the Placeholders (Parameters) in the query are following the stanbdard (eg. [%ParamXY] ) and those Parameters are defined within the Paramtable everything should work out.

Maynbe you just forgot to reopen the Layoutdefinition form after saving the Query.

hth

Bart De Cattelle

Dear all,

The query creates 2 temp tables where data is inserted and then depending of some conditions gets data out of these 2 tables and combines this data. The result of this query is the data we need to be able to use as a datasource, but i think that because of the use of declare, insert,set, ... statements, we can not use it as as datasource. Or am i wrong?
Now this query is saved as an stored procedure and there is a crystal report running to display this data. Is it possible to call this stored procedure from Coresuite (with query data) or use it as a datasource?

Kind regards
Bart

Philipp Knecht

Hi Bart

I'm not aware of any restrictions for the datasources - queries. Since we are using ADO.NET those statements should work.
As a rule of thumb : If it works in Query Analyzer it should work as datasource too.

hth

Bart De Cattelle

Dear Philipp,

The sql query is working, but now i recieve following error when calling layout :
SWISS LD : query : GetPricelist has an error : timeout expired.

My query GetPricelist is an execute of a stored procedure with some parameters. When i do not have a lot of data, everything is working fine, but when it takes longer than 1 min. to execute the query Get_PriceList i get this timeout error.

Can you set this timeout somewhere?

Kind regards
Bart

Bart De Cattelle

Dear Philipp,

This is errormessage that i found in the CoresuiteDebugLog.xml :
" <message level="1" date="23/05/2007" time="12:02:21">
<description>swiss LD : Query : Get_Prijslijst has an error.Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Warning: Null value is eliminated by an aggregate or other SET operation.
Execute stp_GetPriceList_New 'K-ANZIPL','00650', '30000', '20070523' </description>
</message>
"
Kind regards
Bart

Philipp Knecht

Hi Bart

Seems that the only opportunity to change the timeout property is in my code.
So it's maybe a good idea to make that configurable in coresuite designer config.

hth

Bart De Cattelle

Hi Philipp,

That would be very nice.
I have checked the stored procedure and the longest time that it takes to get all results in the Query Analyzer is about 12 minutes.
The problem with this customer is that this report was made in Crystal Report and now we have converted it to a coresuite layout, but because of this timeout error, they can not use the Coresuite layout yet and their Crystal Reports License runs off by the end of may.
Will it be possible to run this layout before the end of may?

Kind Regards
Bart

Martin Cerasuolo

Hello,

I've created a user defined table with the following fields: CardCode, ItemCode and DifferentDescription. Is it possible to use these user defined fields in the marketingdin lay-outs? Can you tell me how?

Best regards,

Martin

Philipp Knecht

Hi Martin

Yes this is possible.
have a look at page 40 in the full docu.

btw. We also sell support packages so if you need remote assistance let us know.

hth

Martin Cerasuolo

Dear Philip,

The question is as follows:
Our Customer wants to fill a table with alternative item descriptions per vendor. On your advise i tried to use the instructions from page 40 in the full docu. But if there are more than one descriptions, only the first is shown. Can you help me to create a solution for my problem?

Please advise?

Best Regards!

Martin Cerasuolo from ComputerPlan in the Netherlands.

Philipp Knecht

Hi Martin

Yes you can use your own query and link it to the existing ones.
You then can insert y new DataBand which then will show all descriptions to you.

hth

Bart De Cattelle

Dear Philipp,

I have been testing the beta release, do you have any idea when the official release will be released, so i can upgrade one of our clients?
I have also another problem: when i make a calculation of a column, the total of that column takes in account the visible and non_visible rows of that column, is there an easy way to make the column total of only the visible rows?

Kind regards
Bart

Philipp Knecht

Hi Bart

You may use the beta now. We still have some smaller issues but they are not critical.
If you define the aggregate on the dtail instead of the databand then only the visible details will be taken in account.

hth

Bart De Cattelle

Dear Philipp,

I have defined the aggregate on detail level and in the footer i then say row.sum("Column"). But this still takes the invisible in accounts to. Is this possible or am i doing something wrong?

Kind regards
Bart

Bart De Cattelle

Dear Philipp,

My fault, it had to be Detail.Sum and not use the databand.sum of course.
So there will be no official release in the near future, we can just install the beta at our customer site?

Kind regards
Bart