Is it possible to merge the 2 datasets into 3 dataset/into 2nd dat

Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
dataset rows into into 2nd dataset?
Actually i don't have access to opendataset and openrowset query execution
on different server. so, i am planning to create dataset for each server and
want merge the 2 dataset output into 3 rd dataset.Check out subreports. That is how this sort of thing is handled. Or you do
the merging in a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
> dataset rows into into 2nd dataset?
> Actually i don't have access to opendataset and openrowset query execution
> on different server. so, i am planning to create dataset for each server
> and
> want merge the 2 dataset output into 3 rd dataset.|||Hi,
We have 2 servers, one in USA and another in Canada. How can i merge
without using openrowset ... ? How can i use subreport to show the all data
into one table?
Regards,
sri.
"Bruce L-C [MVP]" wrote:
> Check out subreports. That is how this sort of thing is handled. Or you do
> the merging in a stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> > Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
> > dataset rows into into 2nd dataset?
> > Actually i don't have access to opendataset and openrowset query execution
> > on different server. so, i am planning to create dataset for each server
> > and
> > want merge the 2 dataset output into 3 rd dataset.
>
>|||You can put a subreport into a cell of the table object.
Create your main report and test.
Create the report to be the subreport separately and test.
Drag and drop the subreport into a cell of the table control, right mouse
click and map the report parameter of the subreport to a field of the
dataset in the main report.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
> Hi,
> We have 2 servers, one in USA and another in Canada. How can i merge
> without using openrowset ... ? How can i use subreport to show the all
> data
> into one table?
> Regards,
> sri.
> "Bruce L-C [MVP]" wrote:
>> Check out subreports. That is how this sort of thing is handled. Or you
>> do
>> the merging in a stored procedure.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
>> > Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
>> > dataset rows into into 2nd dataset?
>> > Actually i don't have access to opendataset and openrowset query
>> > execution
>> > on different server. so, i am planning to create dataset for each
>> > server
>> > and
>> > want merge the 2 dataset output into 3 rd dataset.
>>|||Hi bruce,
thanks for the response.
i don't want to show the 2 datasets records. I have to check the 1st dataset
rows are available in 2nd dataset or not. If exists then show, otherwise
don't show . that is my task.
actually requirement is to list down the common server names from the 2
database(i.e 2 datasets).
Regards,
Sri
"Bruce L-C [MVP]" wrote:
> You can put a subreport into a cell of the table object.
> Create your main report and test.
> Create the report to be the subreport separately and test.
> Drag and drop the subreport into a cell of the table control, right mouse
> click and map the report parameter of the subreport to a field of the
> dataset in the main report.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
> > Hi,
> >
> > We have 2 servers, one in USA and another in Canada. How can i merge
> > without using openrowset ... ? How can i use subreport to show the all
> > data
> > into one table?
> >
> > Regards,
> > sri.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Check out subreports. That is how this sort of thing is handled. Or you
> >> do
> >> the merging in a stored procedure.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> >> > Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
> >> > dataset rows into into 2nd dataset?
> >> > Actually i don't have access to opendataset and openrowset query
> >> > execution
> >> > on different server. so, i am planning to create dataset for each
> >> > server
> >> > and
> >> > want merge the 2 dataset output into 3 rd dataset.
> >>
> >>
> >>
>
>|||Ahh, this is very easy from stored procedure. Bring in the first resultset
into a temp table (use linked servers). Then join the temp table with the
table(s) for the second resultset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:A30D9C21-F110-4DA0-BE54-A3D830805B5F@.microsoft.com...
> Hi bruce,
> thanks for the response.
> i don't want to show the 2 datasets records. I have to check the 1st
> dataset
> rows are available in 2nd dataset or not. If exists then show, otherwise
> don't show . that is my task.
> actually requirement is to list down the common server names from the 2
> database(i.e 2 datasets).
> Regards,
> Sri
> "Bruce L-C [MVP]" wrote:
>> You can put a subreport into a cell of the table object.
>> Create your main report and test.
>> Create the report to be the subreport separately and test.
>> Drag and drop the subreport into a cell of the table control, right mouse
>> click and map the report parameter of the subreport to a field of the
>> dataset in the main report.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
>> > Hi,
>> >
>> > We have 2 servers, one in USA and another in Canada. How can i merge
>> > without using openrowset ... ? How can i use subreport to show the all
>> > data
>> > into one table?
>> >
>> > Regards,
>> > sri.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Check out subreports. That is how this sort of thing is handled. Or
>> >> you
>> >> do
>> >> the merging in a stored procedure.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
>> >> > Is it possible to merge the 2 datasets into 3 dataset or merge the
>> >> > 1st
>> >> > dataset rows into into 2nd dataset?
>> >> > Actually i don't have access to opendataset and openrowset query
>> >> > execution
>> >> > on different server. so, i am planning to create dataset for each
>> >> > server
>> >> > and
>> >> > want merge the 2 dataset output into 3 rd dataset.
>> >>
>> >>
>> >>
>>|||Yea, i know. but don't have rights to create linkedserver,no permission to
create DTS.
"Bruce L-C [MVP]" wrote:
> Ahh, this is very easy from stored procedure. Bring in the first resultset
> into a temp table (use linked servers). Then join the temp table with the
> table(s) for the second resultset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> news:A30D9C21-F110-4DA0-BE54-A3D830805B5F@.microsoft.com...
> > Hi bruce,
> > thanks for the response.
> > i don't want to show the 2 datasets records. I have to check the 1st
> > dataset
> > rows are available in 2nd dataset or not. If exists then show, otherwise
> > don't show . that is my task.
> > actually requirement is to list down the common server names from the 2
> > database(i.e 2 datasets).
> >
> > Regards,
> > Sri
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You can put a subreport into a cell of the table object.
> >> Create your main report and test.
> >> Create the report to be the subreport separately and test.
> >> Drag and drop the subreport into a cell of the table control, right mouse
> >> click and map the report parameter of the subreport to a field of the
> >> dataset in the main report.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> >> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
> >> > Hi,
> >> >
> >> > We have 2 servers, one in USA and another in Canada. How can i merge
> >> > without using openrowset ... ? How can i use subreport to show the all
> >> > data
> >> > into one table?
> >> >
> >> > Regards,
> >> > sri.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> Check out subreports. That is how this sort of thing is handled. Or
> >> >> you
> >> >> do
> >> >> the merging in a stored procedure.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> >> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> >> >> > Is it possible to merge the 2 datasets into 3 dataset or merge the
> >> >> > 1st
> >> >> > dataset rows into into 2nd dataset?
> >> >> > Actually i don't have access to opendataset and openrowset query
> >> >> > execution
> >> >> > on different server. so, i am planning to create dataset for each
> >> >> > server
> >> >> > and
> >> >> > want merge the 2 dataset output into 3 rd dataset.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||You don't need DTS. However, you would need the DBA to create a linked
server for you and you would need rights to create a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:868070F0-4165-4CA2-A473-23D2925B2932@.microsoft.com...
> Yea, i know. but don't have rights to create linkedserver,no permission to
> create DTS.
> "Bruce L-C [MVP]" wrote:
>> Ahh, this is very easy from stored procedure. Bring in the first
>> resultset
>> into a temp table (use linked servers). Then join the temp table with the
>> table(s) for the second resultset.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> news:A30D9C21-F110-4DA0-BE54-A3D830805B5F@.microsoft.com...
>> > Hi bruce,
>> > thanks for the response.
>> > i don't want to show the 2 datasets records. I have to check the 1st
>> > dataset
>> > rows are available in 2nd dataset or not. If exists then show,
>> > otherwise
>> > don't show . that is my task.
>> > actually requirement is to list down the common server names from the 2
>> > database(i.e 2 datasets).
>> >
>> > Regards,
>> > Sri
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> You can put a subreport into a cell of the table object.
>> >> Create your main report and test.
>> >> Create the report to be the subreport separately and test.
>> >> Drag and drop the subreport into a cell of the table control, right
>> >> mouse
>> >> click and map the report parameter of the subreport to a field of the
>> >> dataset in the main report.
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> >> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > We have 2 servers, one in USA and another in Canada. How can i
>> >> > merge
>> >> > without using openrowset ... ? How can i use subreport to show the
>> >> > all
>> >> > data
>> >> > into one table?
>> >> >
>> >> > Regards,
>> >> > sri.
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> Check out subreports. That is how this sort of thing is handled. Or
>> >> >> you
>> >> >> do
>> >> >> the merging in a stored procedure.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> >> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
>> >> >> > Is it possible to merge the 2 datasets into 3 dataset or merge
>> >> >> > the
>> >> >> > 1st
>> >> >> > dataset rows into into 2nd dataset?
>> >> >> > Actually i don't have access to opendataset and openrowset query
>> >> >> > execution
>> >> >> > on different server. so, i am planning to create dataset for each
>> >> >> > server
>> >> >> > and
>> >> >> > want merge the 2 dataset output into 3 rd dataset.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi,
I have 2 datasets.
One dataset is ProdSales
in which i use the query ="with SET ATCPRODS AS '{FILTER([PRODUCT].[ATC].[PACK].members,[PRODUCT].[ATC].currentmember.parent.name = """ + parameters!pProductName.Value + """)}' member [PRODUCT].[ATC].packname as '[PRODUCT].[ATC].currentmember.uniquename' select {[REIMBURSEMENT FLAGS].[All REIMBURSEMENT FLAGS]} on 0, crossjoin({[Measures].[Values],[Measures].[Units]},
crossjoin(ATCPRODS,[BENCHMARK].[BENCHMARK TYPE].allmembers)) on 1 from Austria WHERE ([GEOGRAPHY].[" + parameters!pShop.value + "]," + parameters!pPeriod.value + ")"
and the other dataset is ProdSalesOTC in which i use query
="with SET OTCPRODS AS '{FILTER([PRODUCT].[OTC].[PACK].members,[PRODUCT].[OTC].currentmember.parent.name = """ + parameters!pProductName.Value + """)}' member [PRODUCT].[OTC].packname as '[PRODUCT].[OTC].currentmember.uniquename' select {[REIMBURSEMENT FLAGS].[All REIMBURSEMENT FLAGS]} on 0, crossjoin({[Measures].[Values],[Measures].[Units]},
crossjoin(OTCPRODS,[BENCHMARK].[BENCHMARK TYPE].allmembers)) on 1 from Austria WHERE ([GEOGRAPHY].[" + parameters!pShop.value + "]," + parameters!pPeriod.value + ")"
How to write a new datset by merging the above dataset so that i can use the fields in the new dataset in report?

Is it possible to create a fixed column report?

I was just wondering if it was possible to create a fixed column report that would export to a text file as dat file to be consumed by an outside process. By this I mean that my first column would be characters 1 to 8, column 2 would be 8 to 20, column 3 would be 21 to 40, etc...

If so, how would you do it?

Thanks in advance.

David,

I had to do this for a report. I generated an XML file and applied an XSL translation to get it to fixed field ASCII. Not real difficult, but it required a second step. Don't forget to change the mime output type. I saw somewhere that you can specify the XSL file to apply after exporting to XML within Reporting Services,I just don't remember where.

R