Hello,

I am trying to create a simple report with SSRS which will include all the Sales Orders from company 501,502,503.



I did this and got the result:-

select t_orno from ttdsls400501
UNION
select t_orno from ttdsls400502
UNION t_orno from ttdsls400503

But I want to add the company number on the report too. How should I do it?
I did not found any ._compnr in the fields shown by query designer.


Has anyone created any such reports?

Please help.

Try this:

select 501 as company, t_orno from ttdsls400501
UNION
select 502 as company, t_orno from ttdsls400502
UNION
select 503 as company, t_orno from ttdsls400503

Hi JaapJD,

That worked perfectly. But, I was thinking if I can run a while or a for loop to repeat the select query. Is it possible with SSRS? Can this script run with a cursor or something like that or everytime I will need to hardcode the company numbers?

You can define your query like this:

declare @query varchar(1000)
set @query = ''
select @query = @query + ' union select ' + cast(t_comp as char(3)) + ' as company, * from dbo.ttccom0000' + cast(t_comp as char(3)) from dbo.tttaad100000 where t_comp >= 50 and t_comp <= 90
select @query = substring(@query, 8, 1000)
exec (@query)

This one returns all rows (including company numbers) of the table tccom000 in the infor demo companies 50 - 90.

Hi JaapJD,

Thanks for the query. I have tried using Declare to declare variable in query builder of SSRS and everytime I compile query it says,

"Declare SQL construct or statement is not suupported"

I can execute this query in BIDS. Maybe there are some SQL Server / Visual Studio specialists on the board who know about restrictions in queries.