Reporting Services with multiple companies

kfuglsang

Hi all,

I'm doing a few experiments with Reporting Services. I've stumbled upon a question regarding multiple companies in one database.

How would I, dynamically, create a SELECT statement that would give me e.g. all records from T79 "Company Information"?

Something like:
SELECT * FROM [*$Company Information]

Thanks in advance.

Best regards
Kenneth Fuglsang
Denmark

ara3n

Create a View that combines (outer Join) the tables in each company and adds a new column Company Name to the column.

That way when you run the query you can set a where clause

Where CompanyName = UserFilter

kfuglsang

Hm yeah, I had that thought. I was hoping for something easier, as I have quite a lot of companies spread throughout several databases.

Waldo

In this postI talk about a way to do it by Stored Procedures. May be it can help ...

kfuglsang

Great Waldo. I think I should be able to get something working with the concept described on your blog \:D/

Waldo

8)
Great. Let us know how you solved it .

kfuglsang

Will do. Hope I'll get some time to give it a try today.

mstallmann

Did you ever sucessfully implement dynamic table names with reporting services?

kfuglsang

I managed to get a test working with a stored procedure that on-the-fly created a view for my tables.
The view consisted of a SELECT string with UNION ALL in it.

Waldo

kfuglsang wrote:

I managed to get a test working with a stored procedure that on-the-fly created a view for my tables.
The view consisted of a SELECT string with UNION ALL in it.

That's indeed the main idea that I implemented... .
But I didn't create a view to just do a SELECT on it. My view was necessary as some kind of company-related distinct.
I just build my SELECT with UNION in my Stored Procedure, and execute that SQLString at the end of the SP.

mstallmann

Can you provide a sample SQL statement that might communicate specifically what you did?

Waldo

I would refer you to my blog, but it's offline at the moment, ... .

Just bookmark this .