The objective with SQL reporting services is to provide a selection of reports on a citrix based platform for our customers. Security is well taken care of in citrix.
The tables in Navison, running on SQL, is named by using the Company Name, from table Company. Like this: company$customer
We need a way to build the reports in a generic way - It should not be necessary to maintaina report definition pr. Company.
Do anyone have any experience with reporting in a multicompany environment, please advise.
This is indeed an interesting question. I think the easiest way to do this is to create a stored procedure that takes the company name as a parameter and use that stored procedure as the dataset for the Report(s).
You can build the report so that the user can enter the parameter (from a populated list) and then the report will show the data for the selected company(s).
In NAV 6.0 reports in our new role tailered client, will be build on SQL reporting services. You would be able to transform you reporting from the classic NAV client and do the layout in SQL Reporting services, based on a dataset generate by NAV. That way you would be able to solve the issue you mention here.
No. If you have several companies, there is a set of (almost) all tables for each company. Each table name is prefixed with “Company Name $”, for example:
dbo.Company Name 1$G_L Entry
dbo.Company Name 2$G_L Entry and so on…
Although SQL 2005 allows you to use syntax like SELECT * FROM @company + '$G_L Entry’ in stored procedures, you loose on performance, and, it wasn’t possible in prior versions of SQL.
This approach of table sets for each company against simple additional field “Company” to each table really annoys me, because I use Crystal Reports based on SPs alot.
I had the same problem for a client. We ended up building routine that created a view of each table we needed based off all the indivdual tables and appended the company name. To do it we needed a couple of stored procs that looped through the company table and a customer table that listed the tables we needed. You then write your reports off the view and can use the CompanyName as a part of the where clause.