Hi all,

Regarding Query based reports, I would like to know whether it is possible to develope a report
1) on multicompany data ?
2) involving BOM explosion logic?

Regards,
Onkar.

Multicompany reports can definitely be had in any reporting system very easily . What u actually mean by query based report . Do u mean reporting tool without scripting languages. Please elaborate.

I am actually trying to design Query based reports using Reporting Studio. But while desinging the reports, we have to specify the company in the Infor ERP LN Connectivity Preferences. Due to this, only the tables from company specified in the preferences are available in data set while writing the query. Because of this, I am unable to design the reports invlolving multi company tables.
Is there any way to access data from both the companies for generating the data set?

Also, in 4GL scripting, for the BOM explosion logic, we can make use of array fields for temporary data storage. How can we tackle the similar reports in Query Based reports?

Thanks & Regards,
Onkar.

u can develope a multicompany report or complex using query based reporting tool if your tool supports dyanamic queries.
or
it can be achieved through stored procedures. but for bom eplosion, u may need to script a complex stored procedure.
or
u can use reporting tool with some developement tool like Visual Studio or Java, where u can have intermidiate database with table/s for storing tabale data across companies.
for eg. by creating table TIITM001 with additional field of BAAN_COMPANY. this table can store data of table tiitm001 across all companies. and can be used through reporting tool rather than qeuying on different tables.
Also login for exploding BOM can also be programmed/scripted and results can b stored in just one table. So reporting tool may need to use array or recursion.

In SQL server (and I believe in oracle and db2 as well) , there is way to do multi-company query without having to resort to creating a duplicate table with compnr . U may check this hint in the blog (http://www.baanboard.com/node/2187)

Thanks for the reply guys.
I will make myself a bit more clear to you. My query is specifically about "Infor Reporting Studio" and not about any other reporting tool.
I am trying to design a Query Based report using "Infor Reporting Studio (in which BIRT has been integrated with Infor ERP LN)". For this, Infor has added some plug-ins & features to the eclipse framework. Now, while carrying out report designing using this reporting studio, as per the Infor documents, we have to select the Data Source type as "Infor BDB Data Source". After selecting this data source, the next step is creating data set, where we get only one option for Data Set Type as "Infor BDB DataSet". And thus, we get to see the tables of only one company, which is specified in the Infor ERPLN Connectivity Preference, on which the query can be fired. This is why I was asking whether we can design Query Based report on multicompany data using Infor Reporting Studio.

In this case, what I observed is we can not even call a stored procedure.

OK . In that case I dont have any experience . But I am sure there must be a way to override preference setting to do the multi-company reporting bcos this compnr specification is the basic part of native SQL query (though even easy sql does not allow it ) .

Hi Onkar,
although i don't believe it will work, have you tried to use the table._compnr in where clause of query definition?

If it don't work you can try not to use the Infor jdbc driver but the native driver of your database.
Disadvantage is that you have to deal with the 'technical' tablenames, but you can also use stored procedures (if your DB support it).

Andreas

Hi,
I have similar problem.
I work in multi company environment so any report I made should work with many companies data.
When I defined in the "Infor ERPLN Connectivity Preference" some company number is it means that the report will work only for the specified company or it will work with other companies data as well (after I import it back)?
Is this company number is only for development needs?
If no so is there a way to generate report that will be suited for each company data (with Reporting Studio)?

Regards,
Daniel.

You can overrule that company number in the query:


select tcmcs015.ctyp, tcmcs015.dsca, tcmcs015.company_nr
from tcmcs015
where tcmcs015.company_nr IN (421, 422, 425)


See the screenshot for the result.

Hi JaapJD,
thanks for the replay.