Combining two table into a single table

I have two tables with a 1 to many relationship:

tblCarCompanies
ID Company
1 Mazda
2 Nissan

tblCarModels
ID Company_fk Model
1 1 Miata
2 1 Mazda3
3 2 Sentra
4 2 Pathfinder
5 2 Maxima

What's the best way to query these two table into one result set like:
tblCars
Company Models
Mazda Miata, Mazda3
Nissan Sentra, Pathfinder, Maxima

Maybe something like this:

declare @.carCompany table
( ID integer,
Company varchar(10)
)
insert into @.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'

declare @.carModel table
( ID integer,
Company_fk integer,
Model varchar(12)
)
insert into @.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'

select company,
reverse(substring(reverse(
( select model + ', ' as [text()]
from @.carModel b
where a.id = b.company_Fk
order by model
for xml path('')
)), 3, 200)) as Models
from @.carCompany a

/*
company Models
- --
Mazda Mazda3, Miata
Nissan Maxima, Pathfinder, Sentra
*/

|||One thing I noticed is that if my Models field contains an "&" it will return "&".
I'm guessing it has to do with the for xml path('') conversion. I can simply do a replace(myOutput,'&','&') but I'm not sure if it'll affect any other characters.

What exactly does the for xml path do and is there a way to convert it back without the replace?
|||Yes, I have seen aberations before because of the path(''); you definitely need to look out for it and you might even need to choose a different solution if it becomes a significant problem. Another alternative is to use a function -- preferably an inline function -- in conjunction with the CROSS APPLY operator. Would you like to see an example of such an alternative?|||Sure, an example would be great. Thanks!
|||

I didn't come up with a good way to create an INLINE function for this. Maybe somebody else sees a straight-forward way to do this. I mocked up this test with these tables:

create table dbo.carCompany
( ID integer,
Company varchar(10)
)
go
insert into dbo.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'
go

create table dbo.carModel
( ID integer,
Company_fk integer,
Model varchar(12)
)
go
insert into dbo.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'
go

An example of a scalar function is like this:

alter function dbo.listModels
( @.prm_companyID integer
)
returns varchar(300)
as
begin

declare @.modelList varchar(300)

if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
return @.modelList

set @.modelList = ''

select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyID

set @.modelList = reverse(substring(reverse(@.modelList), 3, 300))

return @.modelList

end

go

select id,
dbo.listModels (id) as Models
from carCompany

/*
id Models
--
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/

An example with a table function and cross apply is like:

alter function dbo.companyModels
( @.prm_companyID integer
)
returns @.companyModels table
( modelList varchar(300)
)
as
begin

declare @.modelList varchar(300)

if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
return

set @.modelList = ''

select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyID

insert into @.companyModels
select reverse(substring(reverse(@.modelList), 3, 300))

return

end

go

select id,
m.modelList as Models
from carCompany
cross apply dbo.companyModels (id) m

/*
id Models
-- --
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/

There are a couple of additional things to note:

It is critical to these functions that you have an index on the MODEL table based on COMPANY_FK; otherwise, you will table scan You might be able to get away with a NOLOCK optimizer hint in these functions; if you are not sure, do NOT add the NOLOCK hint.

Combining reports into a single PDF

My company has spent the last few months developing a set of complex
financial reports using RS. The goal has always been to package them
into a single PDF, with different users receiving customized versions
of each report (eg, everyone in NY gets #s for the NY office).
We've just learned that if we combine the reports as subreports, we
lose the page headers and footers, and landscape and portrait reports
cannot be combined.
But we still have to deliver the package somehow. Does anyone have an
recommendations? We're looking at:
-Putting the page footers and headers in the report body some way.
-Writing an app to automate Adobe distiller and build the packages
ourselves (but we lose the RS delivery functionality)
-Leaving the reports as separate PDFs, and writing an app that will
print them all, creating the appearance of a single package at least
when printed.
Thanks,
BurtBurt
I had a similar problem but I could not resolve it via RS. Here is how I
solved it.
In my c# app I use the Render method for each report and save off its byte
array that is returns into a member variable pdfStream. When the report
generation was complete I then created a pdf with the byte array. Somethign
like this:
// Render Method
private byte[] m_bytResult;
m_bytResult = oRpts.Render(m_sName, GetReportFormat(false), showHideToggle,
m_sDevice, m_oParms, credentials, showHideToggle, out encoding, out mimeType,
out reportHistoryParameters, out warnings, out streamIDs);
// Generate Single PDF From Results of RS Render
string sOut = "myNewReports.pdf";
FileStream stream = File.Create( sOut, bytResult.Length );
stream.Write( bytResult, 0, bytResult.Length );
stream.close;
I hope this helps some.
Tom
"Burt" wrote:
> My company has spent the last few months developing a set of complex
> financial reports using RS. The goal has always been to package them
> into a single PDF, with different users receiving customized versions
> of each report (eg, everyone in NY gets #s for the NY office).
> We've just learned that if we combine the reports as subreports, we
> lose the page headers and footers, and landscape and portrait reports
> cannot be combined.
> But we still have to deliver the package somehow. Does anyone have an
> recommendations? We're looking at:
> -Putting the page footers and headers in the report body some way.
> -Writing an app to automate Adobe distiller and build the packages
> ourselves (but we lose the RS delivery functionality)
> -Leaving the reports as separate PDFs, and writing an app that will
> print them all, creating the appearance of a single package at least
> when printed.
> Thanks,
> Burt
>|||Thanks, Tom.
Looks like I'm going to have to resort to this- write all combinations
of the customized reports to a file structure, combine them per below,
then write a process to email them. Ug.
Burt
Tom Walls <TomWalls@.discussions.microsoft.com> wrote in message news:<E674D836-A609-4FC2-8B4C-46222FC11C1D@.microsoft.com>...
> Burt
> I had a similar problem but I could not resolve it via RS. Here is how I
> solved it.
> In my c# app I use the Render method for each report and save off its byte
> array that is returns into a member variable pdfStream. When the report
> generation was complete I then created a pdf with the byte array. Somethign
> like this:
> // Render Method
> private byte[] m_bytResult;
> m_bytResult = oRpts.Render(m_sName, GetReportFormat(false), showHideToggle,
> m_sDevice, m_oParms, credentials, showHideToggle, out encoding, out mimeType,
> out reportHistoryParameters, out warnings, out streamIDs);
> // Generate Single PDF From Results of RS Render
> string sOut = "myNewReports.pdf";
> FileStream stream = File.Create( sOut, bytResult.Length );
> stream.Write( bytResult, 0, bytResult.Length );
> stream.close;
>
> I hope this helps some.
> Tom
> "Burt" wrote:
> > My company has spent the last few months developing a set of complex
> > financial reports using RS. The goal has always been to package them
> > into a single PDF, with different users receiving customized versions
> > of each report (eg, everyone in NY gets #s for the NY office).
> >
> > We've just learned that if we combine the reports as subreports, we
> > lose the page headers and footers, and landscape and portrait reports
> > cannot be combined.
> >
> > But we still have to deliver the package somehow. Does anyone have an
> > recommendations? We're looking at:
> >
> > -Putting the page footers and headers in the report body some way.
> > -Writing an app to automate Adobe distiller and build the packages
> > ourselves (but we lose the RS delivery functionality)
> > -Leaving the reports as separate PDFs, and writing an app that will
> > print them all, creating the appearance of a single package at least
> > when printed.
> >
> > Thanks,
> >
> > Burt
> >|||Did anyone come up with a better way to do this? Seems like something a lot
of people would want to do, if you actually want to be able to log in to
Reports Manager and get a nice clean report every so often (with cover page,
TOC, et al). Otherwise it means RS is really only good for us IT guys. Anyone
from MSFT know if this kind of feature is on the horizon?
"Burt" wrote:
> Thanks, Tom.
> Looks like I'm going to have to resort to this- write all combinations
> of the customized reports to a file structure, combine them per below,
> then write a process to email them. Ug.
> Burt
>
> Tom Walls <TomWalls@.discussions.microsoft.com> wrote in message news:<E674D836-A609-4FC2-8B4C-46222FC11C1D@.microsoft.com>...
> > Burt
> >
> > I had a similar problem but I could not resolve it via RS. Here is how I
> > solved it.
> >
> > In my c# app I use the Render method for each report and save off its byte
> > array that is returns into a member variable pdfStream. When the report
> > generation was complete I then created a pdf with the byte array. Somethign
> > like this:
> >
> > // Render Method
> > private byte[] m_bytResult;
> >
> > m_bytResult = oRpts.Render(m_sName, GetReportFormat(false), showHideToggle,
> > m_sDevice, m_oParms, credentials, showHideToggle, out encoding, out mimeType,
> > out reportHistoryParameters, out warnings, out streamIDs);
> >
> > // Generate Single PDF From Results of RS Render
> > string sOut = "myNewReports.pdf";
> > FileStream stream = File.Create( sOut, bytResult.Length );
> > stream.Write( bytResult, 0, bytResult.Length );
> > stream.close;
> >
> >
> > I hope this helps some.
> >
> > Tom
> >
> > "Burt" wrote:
> >
> > > My company has spent the last few months developing a set of complex
> > > financial reports using RS. The goal has always been to package them
> > > into a single PDF, with different users receiving customized versions
> > > of each report (eg, everyone in NY gets #s for the NY office).
> > >
> > > We've just learned that if we combine the reports as subreports, we
> > > lose the page headers and footers, and landscape and portrait reports
> > > cannot be combined.
> > >
> > > But we still have to deliver the package somehow. Does anyone have an
> > > recommendations? We're looking at:
> > >
> > > -Putting the page footers and headers in the report body some way.
> > > -Writing an app to automate Adobe distiller and build the packages
> > > ourselves (but we lose the RS delivery functionality)
> > > -Leaving the reports as separate PDFs, and writing an app that will
> > > print them all, creating the appearance of a single package at least
> > > when printed.
> > >
> > > Thanks,
> > >
> > > Burt
> > >
>

Combining Reports

am currently looking for a way to potentially combine several types of
reports systematically to create a single report pack for a client.
This would need to be done on the fly so the clients can choose which bits /
Reports they wish to have and then click the Download PDF button and hey
presto here it comes?
Is there a way and if so can anyone let me know the best / easiest way for a
simple brain to do it!
Cheers alotHi, Paul
I think we'd need more information about your application/UI and method
of delivery...
but making some simple assumption, if you are dealing with your own
custom web app, you can use the SOAP api to invoke reporting services
and render the reports you need.
A nice way to present this would be to use the fileshare delivery
extension, and have the ReportServer render the selected reports to
this share, then just send an email or notification to the user with a
link to access the file share where the PDFs have been saved.
You can also create one big report, that has multiple datasets and
several dataregions, corresponding to different "reports." Then using
parameters, you can drive which datasets get to be populated with data
or hidden from the user...Note that this approach may be a bit slower,
but it will provide a better way of giving the user one single PDF
which contains multiple "reports."
I hope I've given you some ideas to get started.
Regards,
Thiago Silva
MCAD.NET
Paul Roberts wrote:
> am currently looking for a way to potentially combine several types of
> reports systematically to create a single report pack for a client.
> This would need to be done on the fly so the clients can choose which bits /
> Reports they wish to have and then click the Download PDF button and hey
> presto here it comes?
> Is there a way and if so can anyone let me know the best / easiest way for a
> simple brain to do it!
> Cheers alot|||"tafs7" <tsilva7@.gmail.com> wrote in message
news:1156518561.205312.262860@.74g2000cwt.googlegroups.com...
Thiago,
Firstly, Great Name.
Right here is the setup / Architecture, the company I am currently working
for has been using SSRS2000 to provide reports to clients via a web
interface allowing them a series of criteria to make their bespoke report.
All information is saved to a database and a GUID and Version ID is supplied
back to the interface that will then be passed to a process filter that will
then be sent off to SSRS as parameters to be disseminated to the Stored
procedure that is called from the RDL. Hey presto, SSRS send back the
desired report in the desired format.
Now, my lead developer has said, Paul you are a Genius!!!, we need you to
provide a solution for our clients to say I would like "Report 1", "Report
4" and "Report 12" [Where each of these reports is an individual report RDL
Template] to be be selected so as to be combined into one single but
brilliant report. He has also asked as a request that it has continuous
Page Numbers (I can do this bit.) and a Key / Legend bespoke to the report /
s generated and only containing the description to Icons that are contained.
Now this I know is a massive job to undertake and I have a couple of Ideas
of how it can be done. However if there is a way that I can say, "Right I
have a generated report for each individual report, 'Report 1', 'Report 4'
and 'Report 12' and then say go and get me these GENERATED reports and
output them into a single PDF or Excel.
I am guessing that this would be a bigger issue than your suggestion and it
is not something I would get SSRS to do.
Thank you for you reply and look forward to hearing a response.
Paul
> Hi, Paul
> I think we'd need more information about your application/UI and method
> of delivery...
> but making some simple assumption, if you are dealing with your own
> custom web app, you can use the SOAP api to invoke reporting services
> and render the reports you need.
> A nice way to present this would be to use the fileshare delivery
> extension, and have the ReportServer render the selected reports to
> this share, then just send an email or notification to the user with a
> link to access the file share where the PDFs have been saved.
> You can also create one big report, that has multiple datasets and
> several dataregions, corresponding to different "reports." Then using
> parameters, you can drive which datasets get to be populated with data
> or hidden from the user...Note that this approach may be a bit slower,
> but it will provide a better way of giving the user one single PDF
> which contains multiple "reports."
> I hope I've given you some ideas to get started.
> Regards,
> Thiago Silva
> MCAD.NET
> Paul Roberts wrote:
>> am currently looking for a way to potentially combine several types of
>> reports systematically to create a single report pack for a client.
>> This would need to be done on the fly so the clients can choose which
>> bits /
>> Reports they wish to have and then click the Download PDF button and hey
>> presto here it comes?
>> Is there a way and if so can anyone let me know the best / easiest way
>> for a
>> simple brain to do it!
>> Cheers alot
>|||Paul,
Firstly, thanks for the name compliment...it's Portuguese (BR), if
you're wondering.
I don't think it would be easy or even possible to generate separate
reports (different RDLs), then combine them into PDF via code, etc.
The best approach to this in my opinion, still would be to have one RDL
that contains the different report bodies in their contained rectangles
or tables or lists, and based on parameters for which report number was
selected, only execute the appropriate query and render the appropriate
RDL body/data section.
Unfortunately, RS does not allow for expression in Subreport names,
otherwise, I would recommend an entry point report with a Subreport
that would be defined based on a parameter, contained in a table. Then
you could write a little SQL to parse the entered rpt numbers as
individual rows, so you'd have the Subreport render different report
numbers in each "row" of the parent table/list. Hope this makes sense,
but it won't matter 'cause it ain't supported ;-)
Anyways, that's my 2 cents.
Cheers back at you!
Thiago Silva
MCAD.NET
Paul Roberts wrote:
> "tafs7" <tsilva7@.gmail.com> wrote in message
> news:1156518561.205312.262860@.74g2000cwt.googlegroups.com...
> Thiago,
> Firstly, Great Name.
> Right here is the setup / Architecture, the company I am currently working
> for has been using SSRS2000 to provide reports to clients via a web
> interface allowing them a series of criteria to make their bespoke report.
> All information is saved to a database and a GUID and Version ID is supplied
> back to the interface that will then be passed to a process filter that will
> then be sent off to SSRS as parameters to be disseminated to the Stored
> procedure that is called from the RDL. Hey presto, SSRS send back the
> desired report in the desired format.
> Now, my lead developer has said, Paul you are a Genius!!!, we need you to
> provide a solution for our clients to say I would like "Report 1", "Report
> 4" and "Report 12" [Where each of these reports is an individual report RDL
> Template] to be be selected so as to be combined into one single but
> brilliant report. He has also asked as a request that it has continuous
> Page Numbers (I can do this bit.) and a Key / Legend bespoke to the report /
> s generated and only containing the description to Icons that are contained.
> Now this I know is a massive job to undertake and I have a couple of Ideas
> of how it can be done. However if there is a way that I can say, "Right I
> have a generated report for each individual report, 'Report 1', 'Report 4'
> and 'Report 12' and then say go and get me these GENERATED reports and
> output them into a single PDF or Excel.
> I am guessing that this would be a bigger issue than your suggestion and it
> is not something I would get SSRS to do.
> Thank you for you reply and look forward to hearing a response.
>
> Paul
>
> > Hi, Paul
> >
> > I think we'd need more information about your application/UI and method
> > of delivery...
> >
> > but making some simple assumption, if you are dealing with your own
> > custom web app, you can use the SOAP api to invoke reporting services
> > and render the reports you need.
> >
> > A nice way to present this would be to use the fileshare delivery
> > extension, and have the ReportServer render the selected reports to
> > this share, then just send an email or notification to the user with a
> > link to access the file share where the PDFs have been saved.
> >
> > You can also create one big report, that has multiple datasets and
> > several dataregions, corresponding to different "reports." Then using
> > parameters, you can drive which datasets get to be populated with data
> > or hidden from the user...Note that this approach may be a bit slower,
> > but it will provide a better way of giving the user one single PDF
> > which contains multiple "reports."
> >
> > I hope I've given you some ideas to get started.
> >
> > Regards,
> > Thiago Silva
> > MCAD.NET
> >
> > Paul Roberts wrote:
> >> am currently looking for a way to potentially combine several types of
> >> reports systematically to create a single report pack for a client.
> >>
> >> This would need to be done on the fly so the clients can choose which
> >> bits /
> >> Reports they wish to have and then click the Download PDF button and hey
> >> presto here it comes?
> >>
> >> Is there a way and if so can anyone let me know the best / easiest way
> >> for a
> >> simple brain to do it!
> >>
> >> Cheers alot
> >

Combining reports

I want to combine several reports into a single report to print out.
I want the correct page numbering on each of the individual reports so
I don't want to use subreports on a main report. Is there a way to
combine reports into a single report to allow the user to print
without having to print out each of the individual reports?On Apr 25, 8:03 pm, jwchoi...@.gmail.com wrote:
> I want to combine several reports into a single report to print out.
> I want the correct page numbering on each of the individual reports so
> I don't want to use subreports on a main report. Is there a way to
> combine reports into a single report to allow the user to print
> without having to print out each of the individual reports?
The only thing I can think of is to create a single report that has
all the controls of each report (i.e., add x number of table controls
to a single report for x number of reports). Sorry that I could not be
of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Combining Reports

I am currently looking for a way to potentially combine several types of
reports systematically to create a single report pack for a client.
This would need to be done on the fly so the clients can choose which bits /
Reports they wish to have and then click the Download PDF button and hey
presto here it comes?
Is there a way and if so can anyone let me know the best / easiest way for a
simple brain to do it!
Cheers alotIf I understood correctly, you want a list of reports in one page and when
the client clicks on any report or download option it should download.
You can use "Action" to create a page with all of your reports and use
action to render or create a small program using asp.net and use render
method and create pdf depending on the report clicks.
Amarnath
"Paul Roberts" wrote:
> I am currently looking for a way to potentially combine several types of
> reports systematically to create a single report pack for a client.
> This would need to be done on the fly so the clients can choose which bits /
> Reports they wish to have and then click the Download PDF button and hey
> presto here it comes?
> Is there a way and if so can anyone let me know the best / easiest way for a
> simple brain to do it!
> Cheers alot
>
>

combining multiple tables into a single flat file destination

Hi,

I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS.

Does anyone have any inkling into how I would do this.

I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries.

e.g. output

personID, personForename, personSurname,
1, pf, langan

***Roles
roleID, roleName
1, developer
2, architect
3, business analyst
4, project manager
5, general manager
6, ceo

***joinPersonRoles
personID,roleID
1,1
1,2
1,3
1,4
1,5
1,6

Use Merge Joins to join your data sources. You'll need to use more than one because the Merge Join uses two inputs only.

http://msdn2.microsoft.com/en-us/library/ms141775.aspx

|||

You can do the merge join transformations as phil stated, or use a series of lookup transformations, or you could simply do a join on your initial data source query...

select out.personID, out.personForename, out.personSurname, role.roleID, role.roleName

from output as out

INNER JOIN

joinPersonRoles as pr

ON

pr.personID = out.personID

INNER JOIN

roles as role

ON

role.roleID = pr.roleID

Do you want your output to have a single row per person or are multiple rows ok? If you need it all in a single row you will also need to use a pivot transformation (or use pivot in your t-sql statement).

|||Performing the join in the source query via T-SQL as EWisdahl stated is the best route because it lets the database engine do the work.|||Ah, but you're missing my question.

I know how to do joins in order to produce a single recordset to output to a flat file.

I don't want to output a single set of records. I want to produce 3 sets, and output them all to the same file.

the example output I provided is exactly as I want the flat file.

Basically I want to run 3 data flow tasks in sequential order that appends their own output to the flat file destination.|||

So make three data flow tasks and three flat file connection managers, each referencing the same file.

Hook the data flow tasks up together in the control flow to enforce precedence.

What's the problem, I guess? It sounds like you've got it figured out: "Basically I want to run 3 data flow tasks in sequential order that appends their own output to the flat file destination."

|||

This is an odd request, however, you could potentially build up your own csv record.

Have each record be a single column text field and use a derived column transformation to string all of your current columns together for each of the sources.

After you pull these together do a union all.

If needed, you can do a select to grab the metadata information (i.e. table and column names) to push into the output as well. (i.e. select 'tablename'; select 'column1, column2, columnN'; etc...)

:edit - phil once again provided a working answer above while I was typing ... :

|||

great.. thank you so much!

Combining multiple subreports into a single report

The goal is to produce a single PDF consisting of a number of subreports. Some are landscape, others are portrait. The subreports may also be run as independent reports. The master report that contains them defaults to the width of the widest subreport, which is landscape. This causes all portrait subreports to spill over producing blank pages. Are there any work-arounds to concatenate multiple, single report PDFs into a single PDF and have page numbering too?

Thanks!

Have you tried reducing the body width to landscape? We had a similar requrement which we implemented with linked reports pointing to standalone reports and I don't recall having extra blank pages with mixed layouts.|||

I did check the landscape width for the reports both individualy and in the master report. They all render fine independently. I also tested the report rendering as I added each subreport to the master report. The moment I added the Landscape one, all portrait reports (that rendered fine before) spilled over onto subsequent pages. The subreports are embedded in a main report and not linked. Can you tell me more about how you configured your reports to be linked?

Thanks!

|||

I appologize I meant reducing the body width to portrait regardless of the fact that you have reports set to landscape. I believe at runtime the report server will expand the body width as needed.

A linked report is essentially a smart pointer to the actual report. You can create a linked reportin in the Report Manager. Go to the report properties and click on the Create Linked Report button. The advantage of having this point of indirection is that if the standalone report is moved, the linked report will automatically be redirected to the new location. Also, a linked report can have its own security policies, etc.

|||

Hi,

I was able to find information at this link:

http://msdn2.microsoft.com/en-us/library/ms155993.aspx

"Reporting Services does not provide a way to combine landscape and portrait mode pages in the same report, nor does it provide a way to create a print-based layout that replaces or exists alongside the layout of a report as rendered in a browser or other application. For most exported reports, report printouts include everything that is visible on the report, as viewed by the user on a computer monitor."

Not what I wanted to hear. Also, I was unable to find the Linked Reports option within Report Designer Report Properties. We are using Visual Studio 2005. Someone on the team provided these links for combining PDFs. This seems like a lot of work to go thru because of a missing feature. Even Word allows you to insert section breaks where you can specigy Landscapre or Portrait.

http://www.codeproject.com/cs/library/giospdfnetlibrary.asp

https://secure.codeproject.com/csharp/giospdfsplittermerger.asp

|||

Yes, this is correct. I appologize for giving you wrong information. Upon looking at our report package implementation, the master report width is set to Landscape. The Create Linked Report button is on the report properties (General Tab) assuming you use the Report Manager and have rights to create linked reports.

|||

Hi,

The mechanism that we've used to achieve this is to write some code using a PDF library to combine the reports. It goes off and renders the reports and then adds them to a master document. That way we can add page numbers, table of contents etc. and its all dynamic.

Sanjay

|||

Hi, and thanks.

That is what we ended up doing and I am posting the code for the benefit of others. We used PDFSharp (there are several others) and I modified one of their samples into the class below. It worked good and we ended up with one report that could have both landscape and portrait pages and page numbers.

#region PDFsharp - A .NET library for processing PDF

//

// Copyright (c) 2005-2006 empira Software GmbH, Cologne (Germany)

//

// http://www.pdfsharp.com

//

// http://sourceforge.net/projects/pdfsharp

//

// Permission is hereby granted, free of charge, to any person obtaining a copy

// of this software and associated documentation files (the "Software"), to deal

// in the Software without restriction, including without limitation the rights

// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

// copies of the Software, and to permit persons to whom the Software is

// furnished to do so, subject to the following conditions:

//

// The above copyright notice and this permission notice shall be included in

// all copies or substantial portions of the Software.

//

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF THIRD PARTY RIGHTS.

// IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,

// DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR

// OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE

// USE OR OTHER DEALINGS IN THE SOFTWARE.

#endregion

using System;

using System.Diagnostics;

using System.IO;

using PdfSharp;

using PdfSharp.Pdf;

using PdfSharp.Pdf.IO;

using PdfSharp.Drawing;

namespace successionManagement

{

public class CombinePdfs

{

public static PdfDocument combine(Stream[] streams, String fileName)

{

PdfDocument outputDocument = new PdfDocument();

XFont font = new XFont("arial", 8, XFontStyle.Regular);

XStringFormat format = new XStringFormat();

format.Alignment = XStringAlignment.Center;

format.LineAlignment = XLineAlignment.Far;

XGraphics gfx;

XRect box;

int totalPages = 0;

int currentPage = 0;

PdfDocument[] pdfDocuments = new PdfDocument[streams.Length];

for (int i = 0; i < streams.Length; i++)

{

Stream stream = (Stream) streams;

PdfDocument inputDocument = PdfReader.Open(stream, PdfDocumentOpenMode.Import);

totalPages = totalPages + inputDocument.PageCount;

pdfDocuments = inputDocument;

}

String pageNbrFooter;

for (int i=0; i < pdfDocuments.Length; i++)

{

PdfDocument inputDocument = (PdfDocument) pdfDocuments;

for (int idx = 0; idx < inputDocument.PageCount; idx++)

{

PdfPage page = inputDocument.Pages[idx];

currentPage = currentPage + 1;

pageNbrFooter = "Page " + currentPage + " of " + totalPages;

page = outputDocument.AddPage(page);

//Write document file name and page number on each page

gfx = XGraphics.FromPdfPage(page);

box = page.MediaBox.ToXRect();

box.Inflate(20, -10);

gfx.DrawString(String.Format( pageNbrFooter,0 ),

font, XBrushes.Black, box, format);

}

}

outputDocument.Save(fileName);

return outputDocument;

}

}

}

To invoke the class you would supply your stream in place of the pdf and the relative path. Here is a simple hard-coded path example.

Stream[] streams = new Stream[5];

streams[0] = new FileStream(@."c:/Lynette/portrait1.pdf", FileMode.Open);

streams[1] = new FileStream(@."c:/Lynette/portrait2.pdf", FileMode.Open);

streams[2] = new FileStream(@."c:/Lynette/portrait3.pdf", FileMode.Open);

streams[3] = new FileStream(@."c:/Lynette/landscape1.pdf", FileMode.Open);

streams[4] = new FileStream(@."c:/Lynette/portrait4.pdf", FileMode.Open);

PdfDocument combinedPdf = CombinePdfs.combine(streams,"c:/Lynette/myCombined.pdf");

|||

The code sample above should have a subscript in brackets but I guess that is also the symbol for an idea. How funny!

|||

Great solution to this issue. Thank you for posting the code!

Combining multiple subreports into a single report

The goal is to produce a single PDF consisting of a number of subreports. Some are landscape, others are portrait. The subreports may also be run as independent reports. The master report that contains them defaults to the width of the widest subreport, which is landscape. This causes all portrait subreports to spill over producing blank pages. Are there any work-arounds to concatenate multiple, single report PDFs into a single PDF and have page numbering too?

Thanks!

Have you tried reducing the body width to landscape? We had a similar requrement which we implemented with linked reports pointing to standalone reports and I don't recall having extra blank pages with mixed layouts.|||

I did check the landscape width for the reports both individualy and in the master report. They all render fine independently. I also tested the report rendering as I added each subreport to the master report. The moment I added the Landscape one, all portrait reports (that rendered fine before) spilled over onto subsequent pages. The subreports are embedded in a main report and not linked. Can you tell me more about how you configured your reports to be linked?

Thanks!

|||

I appologize I meant reducing the body width to portrait regardless of the fact that you have reports set to landscape. I believe at runtime the report server will expand the body width as needed.

A linked report is essentially a smart pointer to the actual report. You can create a linked reportin in the Report Manager. Go to the report properties and click on the Create Linked Report button. The advantage of having this point of indirection is that if the standalone report is moved, the linked report will automatically be redirected to the new location. Also, a linked report can have its own security policies, etc.

|||

Hi,

I was able to find information at this link:

http://msdn2.microsoft.com/en-us/library/ms155993.aspx

"Reporting Services does not provide a way to combine landscape and portrait mode pages in the same report, nor does it provide a way to create a print-based layout that replaces or exists alongside the layout of a report as rendered in a browser or other application. For most exported reports, report printouts include everything that is visible on the report, as viewed by the user on a computer monitor."

Not what I wanted to hear. Also, I was unable to find the Linked Reports option within Report Designer Report Properties. We are using Visual Studio 2005. Someone on the team provided these links for combining PDFs. This seems like a lot of work to go thru because of a missing feature. Even Word allows you to insert section breaks where you can specigy Landscapre or Portrait.

http://www.codeproject.com/cs/library/giospdfnetlibrary.asp

https://secure.codeproject.com/csharp/giospdfsplittermerger.asp

|||

Yes, this is correct. I appologize for giving you wrong information. Upon looking at our report package implementation, the master report width is set to Landscape. The Create Linked Report button is on the report properties (General Tab) assuming you use the Report Manager and have rights to create linked reports.

|||

Hi,

The mechanism that we've used to achieve this is to write some code using a PDF library to combine the reports. It goes off and renders the reports and then adds them to a master document. That way we can add page numbers, table of contents etc. and its all dynamic.

Sanjay

|||

Hi, and thanks.

That is what we ended up doing and I am posting the code for the benefit of others. We used PDFSharp (there are several others) and I modified one of their samples into the class below. It worked good and we ended up with one report that could have both landscape and portrait pages and page numbers.

#region PDFsharp - A .NET library for processing PDF

//

// Copyright (c) 2005-2006 empira Software GmbH, Cologne (Germany)

//

// http://www.pdfsharp.com

//

// http://sourceforge.net/projects/pdfsharp

//

// Permission is hereby granted, free of charge, to any person obtaining a copy

// of this software and associated documentation files (the "Software"), to deal

// in the Software without restriction, including without limitation the rights

// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

// copies of the Software, and to permit persons to whom the Software is

// furnished to do so, subject to the following conditions:

//

// The above copyright notice and this permission notice shall be included in

// all copies or substantial portions of the Software.

//

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF THIRD PARTY RIGHTS.

// IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,

// DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR

// OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE

// USE OR OTHER DEALINGS IN THE SOFTWARE.

#endregion

using System;

using System.Diagnostics;

using System.IO;

using PdfSharp;

using PdfSharp.Pdf;

using PdfSharp.Pdf.IO;

using PdfSharp.Drawing;

namespace successionManagement

{

public class CombinePdfs

{

public static PdfDocument combine(Stream[] streams, String fileName)

{

PdfDocument outputDocument = new PdfDocument();

XFont font = new XFont("arial", 8, XFontStyle.Regular);

XStringFormat format = new XStringFormat();

format.Alignment = XStringAlignment.Center;

format.LineAlignment = XLineAlignment.Far;

XGraphics gfx;

XRect box;

int totalPages = 0;

int currentPage = 0;

PdfDocument[] pdfDocuments = new PdfDocument[streams.Length];

for (int i = 0; i < streams.Length; i++)

{

Stream stream = (Stream) streams;

PdfDocument inputDocument = PdfReader.Open(stream, PdfDocumentOpenMode.Import);

totalPages = totalPages + inputDocument.PageCount;

pdfDocuments = inputDocument;

}

String pageNbrFooter;

for (int i=0; i < pdfDocuments.Length; i++)

{

PdfDocument inputDocument = (PdfDocument) pdfDocuments;

for (int idx = 0; idx < inputDocument.PageCount; idx++)

{

PdfPage page = inputDocument.Pages[idx];

currentPage = currentPage + 1;

pageNbrFooter = "Page " + currentPage + " of " + totalPages;

page = outputDocument.AddPage(page);

//Write document file name and page number on each page

gfx = XGraphics.FromPdfPage(page);

box = page.MediaBox.ToXRect();

box.Inflate(20, -10);

gfx.DrawString(String.Format( pageNbrFooter,0 ),

font, XBrushes.Black, box, format);

}

}

outputDocument.Save(fileName);

return outputDocument;

}

}

}

To invoke the class you would supply your stream in place of the pdf and the relative path. Here is a simple hard-coded path example.

Stream[] streams = new Stream[5];

streams[0] = new FileStream(@."c:/Lynette/portrait1.pdf", FileMode.Open);

streams[1] = new FileStream(@."c:/Lynette/portrait2.pdf", FileMode.Open);

streams[2] = new FileStream(@."c:/Lynette/portrait3.pdf", FileMode.Open);

streams[3] = new FileStream(@."c:/Lynette/landscape1.pdf", FileMode.Open);

streams[4] = new FileStream(@."c:/Lynette/portrait4.pdf", FileMode.Open);

PdfDocument combinedPdf = CombinePdfs.combine(streams,"c:/Lynette/myCombined.pdf");

|||

The code sample above should have a subscript in brackets but I guess that is also the symbol for an idea. How funny!

|||

Great solution to this issue. Thank you for posting the code!

Combining multiple subreports into a single report

The goal is to produce a single PDF consisting of a number of subreports. Some are landscape, others are portrait. The subreports may also be run as independent reports. The master report that contains them defaults to the width of the widest subreport, which is landscape. This causes all portrait subreports to spill over producing blank pages. Are there any work-arounds to concatenate multiple, single report PDFs into a single PDF and have page numbering too?

Thanks!

Have you tried reducing the body width to landscape? We had a similar requrement which we implemented with linked reports pointing to standalone reports and I don't recall having extra blank pages with mixed layouts.|||

I did check the landscape width for the reports both individualy and in the master report. They all render fine independently. I also tested the report rendering as I added each subreport to the master report. The moment I added the Landscape one, all portrait reports (that rendered fine before) spilled over onto subsequent pages. The subreports are embedded in a main report and not linked. Can you tell me more about how you configured your reports to be linked?

Thanks!

|||

I appologize I meant reducing the body width to portrait regardless of the fact that you have reports set to landscape. I believe at runtime the report server will expand the body width as needed.

A linked report is essentially a smart pointer to the actual report. You can create a linked reportin in the Report Manager. Go to the report properties and click on the Create Linked Report button. The advantage of having this point of indirection is that if the standalone report is moved, the linked report will automatically be redirected to the new location. Also, a linked report can have its own security policies, etc.

|||

Hi,

I was able to find information at this link:

http://msdn2.microsoft.com/en-us/library/ms155993.aspx

"Reporting Services does not provide a way to combine landscape and portrait mode pages in the same report, nor does it provide a way to create a print-based layout that replaces or exists alongside the layout of a report as rendered in a browser or other application. For most exported reports, report printouts include everything that is visible on the report, as viewed by the user on a computer monitor."

Not what I wanted to hear. Also, I was unable to find the Linked Reports option within Report Designer Report Properties. We are using Visual Studio 2005. Someone on the team provided these links for combining PDFs. This seems like a lot of work to go thru because of a missing feature. Even Word allows you to insert section breaks where you can specigy Landscapre or Portrait.

http://www.codeproject.com/cs/library/giospdfnetlibrary.asp

https://secure.codeproject.com/csharp/giospdfsplittermerger.asp

|||

Yes, this is correct. I appologize for giving you wrong information. Upon looking at our report package implementation, the master report width is set to Landscape. The Create Linked Report button is on the report properties (General Tab) assuming you use the Report Manager and have rights to create linked reports.

|||

Hi,

The mechanism that we've used to achieve this is to write some code using a PDF library to combine the reports. It goes off and renders the reports and then adds them to a master document. That way we can add page numbers, table of contents etc. and its all dynamic.

Sanjay

|||

Hi, and thanks.

That is what we ended up doing and I am posting the code for the benefit of others. We used PDFSharp (there are several others) and I modified one of their samples into the class below. It worked good and we ended up with one report that could have both landscape and portrait pages and page numbers.

#region PDFsharp - A .NET library for processing PDF

//

// Copyright (c) 2005-2006 empira Software GmbH, Cologne (Germany)

//

// http://www.pdfsharp.com

//

// http://sourceforge.net/projects/pdfsharp

//

// Permission is hereby granted, free of charge, to any person obtaining a copy

// of this software and associated documentation files (the "Software"), to deal

// in the Software without restriction, including without limitation the rights

// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

// copies of the Software, and to permit persons to whom the Software is

// furnished to do so, subject to the following conditions:

//

// The above copyright notice and this permission notice shall be included in

// all copies or substantial portions of the Software.

//

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF THIRD PARTY RIGHTS.

// IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,

// DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR

// OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE

// USE OR OTHER DEALINGS IN THE SOFTWARE.

#endregion

using System;

using System.Diagnostics;

using System.IO;

using PdfSharp;

using PdfSharp.Pdf;

using PdfSharp.Pdf.IO;

using PdfSharp.Drawing;

namespace successionManagement

{

public class CombinePdfs

{

public static PdfDocument combine(Stream[] streams, String fileName)

{

PdfDocument outputDocument = new PdfDocument();

XFont font = new XFont("arial", 8, XFontStyle.Regular);

XStringFormat format = new XStringFormat();

format.Alignment = XStringAlignment.Center;

format.LineAlignment = XLineAlignment.Far;

XGraphics gfx;

XRect box;

int totalPages = 0;

int currentPage = 0;

PdfDocument[] pdfDocuments = new PdfDocument[streams.Length];

for (int i = 0; i < streams.Length; i++)

{

Stream stream = (Stream) streams;

PdfDocument inputDocument = PdfReader.Open(stream, PdfDocumentOpenMode.Import);

totalPages = totalPages + inputDocument.PageCount;

pdfDocuments = inputDocument;

}

String pageNbrFooter;

for (int i=0; i < pdfDocuments.Length; i++)

{

PdfDocument inputDocument = (PdfDocument) pdfDocuments;

for (int idx = 0; idx < inputDocument.PageCount; idx++)

{

PdfPage page = inputDocument.Pages[idx];

currentPage = currentPage + 1;

pageNbrFooter = "Page " + currentPage + " of " + totalPages;

page = outputDocument.AddPage(page);

//Write document file name and page number on each page

gfx = XGraphics.FromPdfPage(page);

box = page.MediaBox.ToXRect();

box.Inflate(20, -10);

gfx.DrawString(String.Format( pageNbrFooter,0 ),

font, XBrushes.Black, box, format);

}

}

outputDocument.Save(fileName);

return outputDocument;

}

}

}

To invoke the class you would supply your stream in place of the pdf and the relative path. Here is a simple hard-coded path example.

Stream[] streams = new Stream[5];

streams[0] = new FileStream(@."c:/Lynette/portrait1.pdf", FileMode.Open);

streams[1] = new FileStream(@."c:/Lynette/portrait2.pdf", FileMode.Open);

streams[2] = new FileStream(@."c:/Lynette/portrait3.pdf", FileMode.Open);

streams[3] = new FileStream(@."c:/Lynette/landscape1.pdf", FileMode.Open);

streams[4] = new FileStream(@."c:/Lynette/portrait4.pdf", FileMode.Open);

PdfDocument combinedPdf = CombinePdfs.combine(streams,"c:/Lynette/myCombined.pdf");

|||

The code sample above should have a subscript in brackets but I guess that is also the symbol for an idea. How funny!

|||

Great solution to this issue. Thank you for posting the code!

combining multiple select statements in a SP

I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:

select name, age, title
from tablea

select name, age, title
from tableb

Could you combine these queries into a single result set?

Yes you can. You can use join or union to do this..

|||

Hi,

try like this:

select name, age, title
from tablea

UNION ALL //or use Union

select name, age, title
from tableb

Hope this helps

combining data on different sevvers

I need to combine data from two different tables on two MS SQL servers
running on the same LAN into a single SELECT or VIEW. Is this possible and
what would the syntax look like?
Thanks,
Charles
MTS, Inc.Yes, quite possible. Subject to the security on both servers allowing such a
ction.
Syntax is somewhat like this:
SELECT
a.Column1
, a.Column2
, b.Column5
, b.Column6
FROM Server1.MyDatabase.dbo.MyTable a
JOIN Server2.OtherDatabase.dbo.OtherTable b
ON a.KeyColumn = b.KeyColumn
WHERE ( a.CriteriaColumn = CriteriaA
AND b.CriteraColumn = CriteriaB
)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message news:XIGCg.9735$gY6.3907@.n
ewssvr11.news.prodigy.com...
>I need to combine data from two different tables on two MS SQL servers
> running on the same LAN into a single SELECT or VIEW. Is this possible an
d
> what would the syntax look like?
>
> Thanks,
> Charles
> MTS, Inc.
>
>

Combining archive tables into a single table

Hi,
I had a table in my database which will update every month ... so we used to
update the table every month and stored the archieve tables in a seperate
database.
--ID is the primary key for this table and all historical of the record will
have the same ID
Now I have to combine all those tables(Around 30 tables and each had around
3k columns) into one table based on the primary key of current version table
.
-Each Archieve table had one Unique Cycle_id
Note: The historical tables may differ very slightly in structure from the
current version,some columns may be missing that were added over the time
Now,the structure of my new table can be the same as "current version" table
(this month) with additional field cycle_id
Pls try to help me guys, which way is better to achieve this."Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:EAA5F7B1-C732-41AE-B4DE-5A0F21D46BA5@.microsoft.com...
> Hi,
> I had a table in my database which will update every month ... so we used
> to
> update the table every month and stored the archieve tables in a seperate
> database.
> --ID is the primary key for this table and all historical of the record
> will
> have the same ID
> Now I have to combine all those tables(Around 30 tables and each had
> around
> 3k columns) into one table based on the primary key of current version
> table.
> -Each Archieve table had one Unique Cycle_id
> Note: The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the time
> Now,the structure of my new table can be the same as "current version"
> table
> (this month) with additional field cycle_id
> Pls try to help me guys, which way is better to achieve this.
Take a look at the Partitioned Views topic in Books Online.
David Portas
SQL Server MVP
--|||David,
Thats a good idea ...i just went through that ,but the problem is to make
partioned view on partioned tables we need to have all smilar structure
tables.I think then only it will be possible to combine(Union) all those and
show it as One Table.
But in my case,as I said
-- The historical tables may differ very slightly in structure from the
current version,some columns may be missing that were added over the time
--And i have to add a column to uniquely represent which version it is(Is
there any other solution to ditinguish the versions)
"David Portas" wrote:

> "Kumar" <Kumar@.discussions.microsoft.com> wrote in message
> news:EAA5F7B1-C732-41AE-B4DE-5A0F21D46BA5@.microsoft.com...
> Take a look at the Partitioned Views topic in Books Online.
> --
> David Portas
> SQL Server MVP
> --
>
>|||"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:18358F29-A0FB-45F5-9586-E30A127703E9@.microsoft.com...
> David,
> Thats a good idea ...i just went through that ,but the problem is to make
> partioned view on partioned tables we need to have all smilar structure
> tables.I think then only it will be possible to combine(Union) all those
> and
> show it as One Table.
> But in my case,as I said
> -- The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the
> time
> --And i have to add a column to uniquely represent which version it is(Is
> there any other solution to ditinguish the versions)
>
> "David Portas" wrote:
>
> -- The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the
> time
That's easily fixed then - add the columns to the older tables. Why would
that be a problem?

> --And i have to add a column to uniquely represent which version it is(Is
> there any other solution to ditinguish the versions)
Yes you do have to add such a column. Without that your design is weak,
whether or not you choose to use a partitioned view. It's not generally a
good idea to have multiple tables of the same structure with duplicate data.
Any reason you didn't or don't combine them as a single table? Re-reading
your post it seems that was your actual question. The answer is just to
insert all the data to a common table using INSERT statements. Maybe I'm not
quite understanding what your problem is. Perhaps it would help if you
posted some sample DDL.
David Portas
SQL Server MVP
--|||If archived tables have less columns than the current table, simply add null
values to the union selects where the actual values are missing.
Also add a column that will contain a distinct value for each of the
partitions.
If you post some DDL we can give you a better illustration.
ML
http://milambda.blogspot.com/|||Thanks ML,David
My table is like huge one with almost 30 columns ..any way iam displaying
some of those for demonstration
--Lets say,this is my current version table and the newly creating should be
in this format
CREATE TABLE [dbo].[COPY_GLOBAL_CC_MASTER] (
[id_pk] [int] IDENTITY (1, 1) NOT NULL ,-- Primary key and all historical
versions will have same id_pk
[BATCH_ID] [int] NULL ,
[SOURCE_ID] [int] NULL ,
[LEDGER_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYABLES_ID] [int] NULL
[CLAIM_STATUS] [int] NULL--This is newly added column and which is
not there in previous versions
)
I had another table "Cycle",which maintains ids of the previous version
tables..like
Cycle_ cycle Publication data table name
name
1005 2005-11-04 22:59:18.653 dbo.GLOBAL_CC_MASTER_1005
0905 2005-10-07 13:35:15.330 dbo.GLOBAL_CC_MASTER_0905
0805 2005-09-08 02:26:43.873 dbo.GLOBAL_CC_MASTER_0805
0705 2005-08-08 22:13:04.013 dbo.GLOBAL_CC_MASTER_0705
0605 2005-07-07 19:03:43.020 dbo.GLOBAL_CC_MASTER_0605
0505 2005-06-06 17:34:03.517 dbo.GLOBAL_CC_MASTER_0505
0405 2005-05-10 12:15:12.027 dbo.GLOBAL_CC_MASTER_0405
0305 2005-04-11 23:38:59.073 dbo.GLOBAL_CC_MASTER_0305
Now I have to add all these tables into one table
"Archieve_GLOBAL_CC_MASTER" and has to include 'cycle_name' as primary key
along with 'id_pk', which should be look like:
CREATE TABLE [dbo].[Archieve_GLOBAL_CC_MASTER] (
[id_pk] [int] IDENTITY (1, 1) NOT NULL ,-- Primary key and all historical
versions will have same id_pk
[CYCLE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,--composite Primary key,New column which is not there in current table
[BATCH_ID] [int] NULL ,
[SOURCE_ID] [int] NULL ,
[LEDGER_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYABLES_ID] [int] NULL
[CLAIM_STATUS] [int] NULL
)
I think this should help you for better understanding|||It may be late and I may need glasses, but I think you've just come up with
a
solution. Create the new table and migrate all data from the old tables,
creating missing values at insert.
ML
http://milambda.blogspot.com/|||Kumar wrote:
> Thanks ML,David
> My table is like huge one with almost 30 columns ..any way iam displaying
> some of those for demonstration
> --Lets say,this is my current version table and the newly creating should
be
> in this format
>
Like this:
INSERT INTO [dbo].[archive_global_cc_master]
(id_pk, cycle_name, batch_id, source_id, ledger_id, payables_id,
claim_status)
SELECT id_pk, 1005, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_1005
UNION ALL
SELECT id_pk, 0905, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_0905
UNION ALL
SELECT id_pk, 0805, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_0805
UNION ALL ... etc
I'm not clear what you want to do with your keys. Are other tables to
reference Archive on a surrogate IDENTITY key? If so you'll want to
assign a new IDENTITY in which case id_pk won't be IDENTITY in your
archive table.
Are you sure all those other columns need to be nullable? Are you sure
you have an alternate key in each table? If not you may have
duplicates. I'm not convinced that you have a sound design here to
start with, but that could be a mistaken assumption given that this is
just a fragment.
David Portas
SQL Server MVP
--

Combine result sets

Hi,
In my project, I need to combine multiple result sets into one single result
set for further processing. However, the result sets are not having the
same table structure and therefore I can't use union. Any ideas on how I
should do it?
Thanks.Cherly
Create a temporary table , so some of the columns will contrain NULLs or
create DEFAULT constraint
"Cheryl" <justtosayhi@.excite.com> wrote in message
news:BA3FE0A3-D1C9-41C7-B10B-5DDFB0C16284@.microsoft.com...
> Hi,
> In my project, I need to combine multiple result sets into one single
> result set for further processing. However, the result sets are not
> having the same table structure and therefore I can't use union. Any
> ideas on how I should do it?
> Thanks.|||On Sep 19, 10:33 pm, "Cheryl" <justtosa...@.excite.com> wrote:
> Hi,
> In my project, I need to combine multiple result sets into one single result
> set for further processing. However, the result sets are not having the
> same table structure and therefore I can't use union. Any ideas on how I
> should do it?
> Thanks.
overall it's easier to ensure that result sets have identical
structure.
add something like this to you result sets:
CAST(NULL AS MissingColumnType) AS MissingColumnName|||How different are the recordsets ? Is it just a question of creating a
couple of columns with '' as the value, so you can
get to the point of using UNION
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Cheryl" <justtosayhi@.excite.com> wrote in message
news:BA3FE0A3-D1C9-41C7-B10B-5DDFB0C16284@.microsoft.com...
> Hi,
> In my project, I need to combine multiple result sets into one single
> result set for further processing. However, the result sets are not
> having the same table structure and therefore I can't use union. Any
> ideas on how I should do it?
> Thanks.

Combine record

Hi guys..
is there any query to do this action:
i want to combine view record into a single record.
exm.

table 1
Name A B
Jack 10 22
jack 12 21
jack ... ...
jack 1 11
ben 12 2
ben 3 2
ben ... ...

into:
View 1
Name combine
jack 10,22 and 12,21and1,11 and ....
ben 12,2 and 3,2 and.....

thx before..dede (neolempires2@.gmail.com) writes:

Quote:

Originally Posted by

is there any query to do this action:
i want to combine view record into a single record.
exm.
>
table 1
Name A B
Jack 10 22
jack 12 21
jack ... ...
jack 1 11
ben 12 2
ben 3 2
ben ... ...
>
>
into:
View 1
Name combine
jack 10,22 and 12,21and1,11 and ....
ben 12,2 and 3,2 and.....

Check out http://www.projectdmx.com/tsql/rowconcatenate.aspx for
suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

Combine Multiple Results into 1 RecordSet

Hello All
I have the following SPROC Below which I want to return the results of the 3
Querries in a single record Set which I can use in my webapp
/ ****************************************
****************
CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
AS
SELECT Count(*) As Total FROM vSQLInv_VersionString
SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
SELECT Count(*) As Valid FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
GO
****************************************
***************/
-- Desired Results --
Total Vulnerable Valid
80 5 75
Thanks
StuartSELECT Count(*) As Total,
SUM(CASE WHEN Status = 'Vulnerable' OR Status = 'EOF' THEN 1 ELSE 0 END)
As Vulnerable ,
SUM(CASE WHEN Status <> 'Vulnerable' THEN 1 ELSE 0 END) As Valid
FROM vSQLInv_VersionString
Jacco Schalkwijk
SQL Server MVP
"Stuart Shay" <sshay@.j51.com> wrote in message
news:umqEkOZMFHA.2384@.tk2msftngp13.phx.gbl...
> Hello All
> I have the following SPROC Below which I want to return the results of the
> 3 Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>|||SELECT
(
SELECT Count(*) FROM vSQLInv_VersionString
) AS Total ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
) As Vulnerable ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
) As Valid
FROM
vSQLInv_VersionString
Cheers,
Greg Jackson
PDX, Oregon|||SELECT Count(*) As Total,
Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as Vulnerable,
Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
FROM vSQLInv_VersionString
"Stuart Shay" wrote:

> Hello All
> I have the following SPROC Below which I want to return the results of the
3
> Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>
>|||Thanks & Have A GREAT Day !!!!!
Stuart
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:41A33FEB-3805-411F-A03D-459849349C05@.microsoft.com...
> SELECT Count(*) As Total,
> Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as
> Vulnerable,
> Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
> FROM vSQLInv_VersionString
>
> "Stuart Shay" wrote:
>

Combine Chart and Matrix on page

I want to combine my matrix and my chart for the current selection on
a single page.
As it is now, I'm stick with all of the results and the chart below
in. I also have parenting on the left side... however... the only
way for my chart to show the selection from the left, I have to put it
on page break. When I do that, my chart gets pushed to the very
last page of the report.
How can I get the chart to stay on the current parent selection
results and reflect the selected change?On Sep 20, 2:04 pm, Bruce Lawrence <BL32...@.gmail.com> wrote:
> I want to combine my matrix and my chart for the current selection on
> a single page.
> As it is now, I'm stick with all of the results and the chart below
> in. I also have parenting on the left side... however... the only
> way for my chart to show the selection from the left, I have to put it
> on page break. When I do that, my chart gets pushed to the very
> last page of the report.
> How can I get the chart to stay on the current parent selection
> results and reflect the selected change?
I'm not sure if I understand you, but you might try including the
chart and matrix control inside a single rectangle. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Column Splitting

Hi Everyone,

I've been given the painstaking project of splitting a single column into multiple columns and rows. I have a solution set up in which I will be posting further down the post but I want to see if there is a much more efficient solution to this.

sample data:
create table tbl_list
(pk_int_itmid int(5) Primary Key,
vchar_desk vchar(300));

create table tbl_test1
(fk_int_itmid int(5) references tbl_list(pk_int_itmid),
vchar_itm varchar(60));

insert into tbl_list values
(1, 'this item');

insert into tbl_list values
(2, 'that item');

insert into tbl_list values
(3, 'those items');

insert into tbl_test1 values
(1, 'A, B - C, D, E - F, G, H - I');

insert into tbl_test1 values
(2, 'J, K - L, M, N - O');

insert into tbl_test1 values
(3, 'P, Q - R');

into this table:
create table tbl_output
(fk_int_itmid int(5) references tbl_list(pk_int_itmid),
vchar_itmA varchar(60),
vchar_itmB varchar(60),
vchar_itmC varchar(60));

Output in comma delimited form:
'1', 'A', 'B', 'C'
'1', 'D', 'E', 'F'
'1', 'G', 'H', 'I'
'2', 'J', 'K', 'L'
'2', 'M', 'N', 'O'
'3', 'P', 'Q', 'R'

my current solution:
create view vw_itm_a as
select fk_int_itmid,
substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,
substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,
substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmC
from tbl_test1
where charindex(',',vchar_itm) >1
Go

create view vw_itm_b as
select fk_int_itmid,
substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,
substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,
substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmC
from vw_itm_a
where charindex(',',vchar_itmC) >1;
Go

create view vw_itm_c as
select fk_int_itmid,
substring(vchar_itmC, 0, charindex('-',vchar_itmC)) as vchar_itmA,
substring(vchar_itmC, charindex('-',vchar_itmC)+1 , charindex(',',vchar_itmC)-charindex('-',vchar_itmC)) as vchar_itmB,
substring(vchar_itmC, charindex(',',vchar_itmC)+1) as vchar_itmC
from vw_itm_b
where charindex(',',vchar_itmC) >1;
Go;

create view vw_itm_d as
select fk_int_itmid, vchar_itmA, vchar_itmB,
substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmC
from vw_itm_a ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmid
Go;

create view vw_itm_e as
select fk_int_itmid, vchar_itmA, vchar_itmB,
substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmC
from vw_itm_c ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmid
Go;

create view vw_itm as
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_a
where fk_int_itmid not in (select fk_int_itmid from vw_itm_b)
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_d
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_b
where fk_int_itmid not in (select fk_int_itmid from vw_itm_c)
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_e
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_c
Go;

select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
into tbl_output
from vw_itm

Is there a much more efficient manner of handling this column splitting?

Thanks
DCyou have my sincere condolences

i would do this with application programming, not sql

column reference error

Hi,
Can anyone help me with what I should be looking for with this error?
(I have also tried putting single quotes around the search number in the
query - with the same results)
Thanks!
Rich.
An unhandled exception of type 'System.Exception' occurred in
receivingdb.dll
Additional information: Error number = 547
Error class = 16
Error state = 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_purchaseorders_receivinglog'. The conflict occurred in database
'Receiving', table 'purchaseorders', column 'delivery'.
DELETE FROM [receivinglog] WHERE [delivery] = 125Rich K wrote:
> Hi,
> Can anyone help me with what I should be looking for with this error?
> (I have also tried putting single quotes around the search number in
> the query - with the same results)
> Thanks!
> Rich.
>
> An unhandled exception of type 'System.Exception' occurred in
> receivingdb.dll
> Additional information: Error number = 547
> Error class = 16
> Error state = 1
> DELETE statement conflicted with COLUMN REFERENCE constraint
> 'FK_purchaseorders_receivinglog'. The conflict occurred in database
> 'Receiving', table 'purchaseorders', column 'delivery'.
> DELETE FROM [receivinglog] WHERE [delivery] = 125
You have a foreign key column in another table. You need to delete the rows
in that table that are controlled by the rwo you are deleting in this table.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OgZIYd47FHA.3976@.TK2MSFTNGP15.phx.gbl...

> You have a foreign key column in another table. You need to delete the
> rows in that table that are controlled by the rwo you are deleting in this
> table.
>
As easy as that eh?
Thanks Bob it makes sense now!

column management help

I have to read 25 usernames from a single users row

than display to that user the 25 profiles.

I assume this is possible with a subquery right?

Now do i have to make 25 columns 1 for each username or

could it read user1 user2 user3 ? and how please.. Thanks much

Can you explain a little more about how these usernames are currentlystored in your database, and why you need to make them columns? Sounds like they're better suited to being rows, instead ofcolumns.
|||

i have an infinite number of rows with primary key username

each column needs to store up to 25 usernames who sent a message

than im thinking a subquery can display the info for each username for that exact person.

info to be display is picture and some things about themself

How... im still trying to figure out.

i have this in one table

Username - primary key

user1, user2 user3 as columns - for each person that made an action on that users page

store them in a column

My question is: There a simplier way to read these users instead of making a bunch of columns for each user who made an action?

|||

Guys would making subtables cause trouble in the long run?

If i make a sub table for every user?

|||

ck1mark wrote:

My question is: There a simplier way to readthese users instead of making a bunch of columns for each user who madean action?

Hi,
Yep, there is. Any time you have an inclination of creating a tablewith numbered fields like that, it is a huge warning flag that thestructure is not normalized. Almost certainly, a better way is to havea Message table (I'm guessing here on what table names will make sense)that has a MessageID and whatever other fields you need to have tostore whatever information.
Then you have a child table with a structure something like this:
UserID -- Could be the user name or a artificial primary key
MessageID -- A foreign key into the Message table
This way storage is more efficient, you don't have to worry about theone message you'll occasionally get that has 26 users, and SQL isdesigned to handle related tables like this.
Make sense?
Don
|||

help me write this please im having trouble

i need to insert the select statement values

INSERT INTO table1
VALUES username, photo1

(SELECT r.username, photo1
FROM table2 r, table3 p
WHERE r.username = '" & user.identity.name & "' AND r.username = p.username)

|||You might want to check Books Online for the syntax. But it should besomething like this (untested, so may still need some tweaking):

INSERT INTO table1 (username, photo1)
(SELECT r.username, photo1
FROM table2 r, table3 p
WHERE r.username = '" & user.identity.name & "' AND r.username = p.username)

I'm not quite sure what the context is for this statement, so the quotes might need more tweaking as well.
Does that work? If not, what troubles are you having?
By the way, this is dangerous code because of SQL injection. Usinguser.identity.name may be safe, but only if you've made sure the namedoesn't have any bad stuff in it. It's always better to useparameterized queries.
Don

column data in the single row

Hi,
I have a table such as

ID Name OS
-----------
10 Paul AIX
10 Paul SOLARIS
10 Paul NT
20 Jack NT
20 Jack SOLARIS

and I have asked to create an output as

ID NAME OS
-----------
10 Paul AIX,SOLARIS,NT
20 JAck NT,SOLARIS

How can I get this output via sql.
Also a good source for such tricky SQLs would be very fruity.Hi Faar,

If this is a one-time deal for a report, then I would go ahead and plug the dreaded cursor within a cursor. If your example table is named testing and is defined as such:

create table testing
(
ID int,
Name varchar(30),
OS varchar(100)
)

-and your values are as you provided. Then the code below should work:

declare @.id int,
@.name varchar(30),
@.OS varchar(100),
@.CurrentOS varchar(100)

create table #formatted
(
ID int,
Name varchar(30),
OS varchar(100)
)

declare person cursor for
select id, name from testing
group by id, name

open person

fetch person into @.id, @.name
while @.@.fetch_status = 0
begin
set @.OS = ''
declare OS cursor for
Select OS from testing
where ID = @.ID
group by OS

open OS
fetch OS into @.CurrentOS

while @.@.fetch_status = 0
begin
set @.OS = @.OS + @.CurrentOS + ', '
fetch OS into @.CurrentOS
end
Set @.OS = Left(@.OS,LEN(@.OS)-1)
close OS
deallocate OS

insert #formatted (id, name, os)
values (@.id, @.name, @.OS)

fetch person into @.id, @.name
end
close person
deallocate person

select * from #formatted

drop table #formatted

--This is pretty much textbook for bad sql - but if you only need to to this once I wouldn't worry about it. If you need to do this regularly, there are better performing methods than the cursors such as cycling through a table variable.

good luck.|||Warning! Untested code. May have syntax errors...
create function OSList(@.ID integer)
returns varchar(500)
as
begin
declare @.ReturnValue varchar(500)
select @.ReturnValue = isnull(@.ReturnValue + ', ', '') + OS
from [YourTable]
where ID = @.ID
order by OS
Return @.ReturnValue
end

To execute:select distinct
ID,
Name,
dbo.OSList(ID)
from [YourTable]|||hi,
"create function " suggestion works very well.
thanks everybody.

Do you know a good source for such tricky SQLs?|||Just search any of the SQL Server forum like :
SQLTeam.com
SQLServerCentral.com
SQL-Server-Performance.com
forums.microsoft.com|||Celko has written good books on SQL.