Report for all the companies

chrispaul · 2006-12-16

Dear All
I am working on a database with 3 companies , I have made a report "Sales Statistics" but now my client wants this report to cover all the data in all the companies irrespective of from hich company we are running this report, Is there any way around plz tel me..
Any Update

ovicash · 2006-12-16

You could use the function CHANGECOMPANY. Find usage in help.

chrispaul · 2006-12-16

Thx Ovicash
Well i can't find anyhelp for change company , Can u plz tel me more precisely what exactly it is & how it works...

Thx in Advance

ovicash · 2006-12-16

What version are you using?

this is what the help say

CHANGECOMPANY (Record)
Use this function to redirect references to table data from one company to another.

[Ok :=] Record.CHANGECOMPANY([CompanyName])
Ok

Data type: boolean

If you omit this optional return value, a run-time error occurs if the system cannot find the company. If you include the return value, you can handle any errors. The possible values are:

If Ok is...
 It means that the company was...
 
TRUE
 Found
 
FALSE
 Not found
 

Record

Data type: record

The record you want to access from a table in another company.

CompanyName

Data type: text or code

The name of the company you want to change to. If you omit this parameter, the system changes back to the current company.

Comments
When executing this function, the system respects the user's access rights. For example, a user cannot access data in CompanyName unless he or she already has the necessary access rights.

The CHANGECOMPANY function is not affected by RESET (Record). You can deselect a company by making a new call to CHANGECOMPANY or by using CLEAR.

Global filters always belong to a specific company. If you use

Record.CHANGECOMPANY(NewCompany);

to select the company named NewCompany, any filters assigned to Record will be transferred to Record in the new company.

Example
This example shows how to use the CHANGECOMPANY function.

"G/L Account".CHANGECOMPANY('New Company');
"G/L Account".GET('1000');
"G/L Account".CALCFIELDS(Balance);// Calculates the balance
// for account no. 1000 in
// 'New Company'
"G/L Entry".CHANGECOMPANY('New Company');
"G/L Entry".SETCURRENTKEY("No.",LedgerEntry.Date);
"G/L Entry".SETRANGE("No.",'1000');
"G/L Entry".SETRANGE(Date,010196D,013196D);
"G/L Entry".CALCSUMS(NetAmount); // Sums NetAmount from
// all G/L entries on
// account no. 1000 within
// the specified range,
// for 'New Company'
"G/L Entry".RESET;
"G/L Entry".FIND('+'); // Finds the largest "No."
// in the G/L Entry table in
// 'New Company'
"G/L Entry".DELETE; // Deletes this entry in
// 'New Company'

This example shows that once the CHANGECOMPANY function has been called, all future references to the G/L Account and G/L Entry tables will refer to the table data in New Company.

Waldo · 2006-12-16

<EDIT>
Apparently the help was already copied to this thread.
:oops:

David_Cox · 2006-12-16

If this is Company Then Items
Report Data Items
Company (New page per record)
>Item (Indented no filter)

Item - OnPreDataItem()
Item.CHANGECOMPANY(Company.Name);

If you need a total for each item for each company and all companies, then this is harder, you use variables and code, fill a buffer table and use an Integer to report the values.

DenSter · 2006-12-16

I believe flowfields work when you do CHANGECOMPANY, though I'd have to check to make sure. Remember when you do CHANGECOMPANY, that you need to do this to all variables of type record, or you will get inconsistent results. Check, double check and triple check the numbers in your report before releasing to production, and don't ever try to post across companies (bad idea.... from personal experience)

Alex_Chow · 2006-12-16

DenSter wrote:

I believe flowfields work when you do CHANGECOMPANY, though I'd have to check to make sure. Remember when you do CHANGECOMPANY, that you need to do this to all variables of type record, or you will get inconsistent results. Check, double check and triple check the numbers in your report before releasing to production, and don't ever try to post across companies (bad idea.... from personal experience)

Yes, the flowfield works with CHANGECOMPANY as well.

We did a simple mod to allow the users to report financials for different companies under one account schedule. All this is done using flowfields and CHANGECOMPANY.

Waldo · 2006-12-18

I can confirm this.

We created some kind of "hyperion consolidation interface tool" (what's in a name :roll: ) with combinations of flowfields, changecompany, ... .

With quite some coding you'll be able to achieve what you need

.

May be a little tip:
Only use CHANGECOMPANY for reporting reasons.

Mbad · 2006-12-18

Waldo wrote:

Only use CHANGECOMPANY for reporting reasons.

No problem in using changecompany in code as well if you PULL information. Problems only arise if you try and modify/insert/delete from another company since Validates/Tablerelations will still work on the company you are working in. Baiscly you can delete saleslines in your company if you run changecompany on sales header and delete(true) and have a sales document with the same No.

Waldo · 2006-12-18

Mbad wrote:

...No problem in using changecompany in code as well if you PULL information...

That's what I mean with "reporting" ... .

Updating data with CHANGECOMPANY is tricky. Wouldn't recommend it.

Mbad · 2006-12-18

Waldo wrote:

That's what I mean with "reporting" ... .

Ahh

DenSter · 2006-12-18

Waldo wrote:

That's what I mean with "reporting" ... .

I used to have a customer that interpreted that as 'so as long as I use report objects, I can do whatever I want', and proceeded to program calls to posting routines in reports and do MODIFY's and COMMIT's all over the place. ](*,)

BGI · 2006-12-18

If you are using sql server, try to create a view on sql server level, combining the data of the tables from the different companies....

Waldo · 2006-12-19

Only one disadvantage. If you have a customer that modifies/deletes/creates companies frequently in the database, and you want to report over all of them, you have to update your view frequently. We worked with a stored procedure that updates a view. Here is some sample code:

  DECLARE curCompany CURSOR FOR
  SELECT replace(Name,'.','_') FROM Company

  OPEN curCompany
  FETCH NEXT FROM curCompany INTO @Company
 

  IF @@FETCH_STATUS <> 0 
    PRINT '         <<None>>' 
  IF @@FETCH_STATUS = 0

    SET @SQLString =   'SELECT ''' + @Company + ''' as Company,Name,Address,[Post Code],City,[Phone No_],[Fax No_],'''+@Lang+''' AS Language,'''+@VATcode+''' AS VATcode,
		[VAT Registration No_],[Bank Account No_],IBAN,[Bank Name]
        FROM dbo.[' + @Company + '$Company Information]'


  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @SQLString = @SQLString + 'UNION SELECT ''' + @Company + ''' as Company,Name,Address,[Post Code],City,[Phone No_],[Fax No_],'''+@Lang+''' AS Language,'''+@VATcode+''' AS VATcode,
		[VAT Registration No_],[Bank Account No_],IBAN,[Bank Name]
        FROM dbo.[' + @Company + '$Company Information]'

    FETCH NEXT FROM curCompany INTO @Company
  END

  CLOSE curCompany
  DEALLOCATE curCompany

So we create a UNION for getting all data from each company.

Report for all the companies

Comments

Categories