Changing table names. Navision 3.7 - need help

Hello
I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
Mening that for one company the GL table is named like this:
[Company X$GL] while for another company the GL table is like this:
[Company Y$GL]
Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
I have the possibility of getting the company name from the system, so thats not a problem...
hope for some good advice.David,
You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
="Select * From " & Parameter!Company.Value
You may be able to do it from one report, depending on the order in which the datasets are handled.
Hope that helps
"david" wrote:
> Hello
> I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> Mening that for one company the GL table is named like this:
> [Company X$GL] while for another company the GL table is like this:
> [Company Y$GL]
> Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> I have the possibility of getting the company name from the system, so thats not a problem...
> hope for some good advice.|||Terribly sorry for having bothered you all. I was missing a white space in my table name.... *ashamed beyond belief*
"david" wrote:
> Now I tried going another way. I found a text replacing program, that simply runs through all the rdl files and replaces the company X string with the company Y string, so as to fit the database tables names.
> Now when i open the reports and try to open a dataset in the data view:
> all tables are flat, and i can only mark the All columns. When I try running the command, i get the following error:
> ADO error: Invalid object name 'compname$Value Entry'.
> Invalid object name 'Compname@.Customer'.
> Staement(s) could not be prepared. Deferred prepare could not be completed.
>
> heeeeeeeelp
> "david" wrote:
> > Thanks for the reply.
> > I will elaborate a little more on the problem.
> >
> > a Select to get all accounts in account ledger is for example:
> >
> > SELECT No_, Name
> > FROM [Company X$G_L Account]
> >
> > There fore i need to concatenate the company name with the table name. And table names may have blank spaces, so I need the [ ]
> >
> > Besides that, in the books online for reporting services it specifically says that everything need to be inline, and I have som loong select statements... what 2 do?
> >
> >
> > "Chris McGuigan" wrote:
> >
> > > David,
> > > You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
> > > ="Select * From " & Parameter!Company.Value
> > > You may be able to do it from one report, depending on the order in which the datasets are handled.
> > >
> > > Hope that helps
> > >
> > > "david" wrote:
> > >
> > > > Hello
> > > > I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> > > >
> > > > Mening that for one company the GL table is named like this:
> > > > [Company X$GL] while for another company the GL table is like this:
> > > > [Company Y$GL]
> > > > Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> > > >
> > > > I have the possibility of getting the company name from the system, so thats not a problem...
> > > >
> > > > hope for some good advice.|||David,
The 1 line restrictions was removed with SP1. Any way by 1 line they really mean one sentence, so;
="SELECT * " &
"FROM [" & Parameters!Company.Value & " X$G_L Account]"
would work OK.
Regards
Chris McGuigan
"david" wrote:
> Thanks for the reply.
> I will elaborate a little more on the problem.
> a Select to get all accounts in account ledger is for example:
> SELECT No_, Name
> FROM [Company X$G_L Account]
> There fore i need to concatenate the company name with the table name. And table names may have blank spaces, so I need the [ ]
> Besides that, in the books online for reporting services it specifically says that everything need to be inline, and I have som loong select statements... what 2 do?
>
> "Chris McGuigan" wrote:
> > David,
> > You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
> > ="Select * From " & Parameter!Company.Value
> > You may be able to do it from one report, depending on the order in which the datasets are handled.
> >
> > Hope that helps
> >
> > "david" wrote:
> >
> > > Hello
> > > I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> > >
> > > Mening that for one company the GL table is named like this:
> > > [Company X$GL] while for another company the GL table is like this:
> > > [Company Y$GL]
> > > Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> > >
> > > I have the possibility of getting the company name from the system, so thats not a problem...
> > >
> > > hope for some good advice.sql