Combining Table and Matrix format in one Report in RS2005

Hello,

I am using RS 2005 trying to create the following report. My report consists of the following columns: Question, Sub Question, N as Number of Responses, All as Average for all responses per given question and sub question, and Ethnicity column which is presented here in a Matrix format with ethnic group as columns and average response as Data values. It looks like my challenge is to combine Matrix format report (Ethnicity column) with a data such as N and All columns which are more like a table format. Any input how I could tackle this is greatly appreciated.

Thank you!

--

1.How often have you done each of the following?

N All F M Asian Multi-cultural a. Worked on a paper or project that required integrating ideas or information from various sources 1134 3.96 3.95 3.99 3.54 4.50 b. Used library resources 1132 4.21 4.26 4.09 4.12 4.33 c. Prepared multiple drafts of a paper or assignment before turning it in 1130 3.90 3.97 3.76 3.80 4.50

-How the source data looks like?sqlsql

Combining Reports

I'm trying to create a report that is actually 4 reports. Each has it's own page headers and Footers.

I can't use sub reports because I need the headers and footers to show for each indivual report.

My output is going to be PDF, and I know how to write code that i could use to combine the PDFs but I really want to do this in the report. I don't want to have to use some outside process to get my wanted output, If I can get away with it. Any help is appreciated!! Thanks.

There is no way to combine the reports into one PDF without an outside process.|||

Brad,

Your response not only lacks details but leaves me with the impression that combination reports will never be supported in Reporting Services. I have been developing reports for more years than I like to admit and I can say with hesitation that building a report that consolidates the output from several reports is by no means an uncommon practice. If Reporting Services wants to be an "enterprise" solution supporting common practices is going to have to become a priority.

Mark73

Combining Reports

I'm trying to create a report that is actually 4 reports. Each has it's own page headers and Footers.

I can't use sub reports because I need the headers and footers to show for each indivual report.

My output is going to be PDF, and I know how to write code that i could use to combine the PDFs but I really want to do this in the report. I don't want to have to use some outside process to get my wanted output, If I can get away with it. Any help is appreciated!! Thanks.

There is no way to combine the reports into one PDF without an outside process.|||

Brad,

Your response not only lacks details but leaves me with the impression that combination reports will never be supported in Reporting Services. I have been developing reports for more years than I like to admit and I can say with hesitation that building a report that consolidates the output from several reports is by no means an uncommon practice. If Reporting Services wants to be an "enterprise" solution supporting common practices is going to have to become a priority.

Mark73

sqlsql

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 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 Subreports in One Report

Hi,

I am trying to combine 5 different reports in one report. I am using them as subreports. The problem is, they are all different formats. For example, one of the report is rendered as 3 column report, and page length and width vary from report to report. When I tried to use table and list for this, my program crashed and closed my visual studio. When I use rectangle for it, then shows me the report but report is no more multiple column and page sizes were also different from the one I did in the report. Please guide me how to combine all these reports and not loose the formatting of the reports.

Thanks,

-Rohit

Multiple column layouts are currently not supported inside subreports, only as a main report.

-- Robert

|||

Hi Robert,

Do you think its going to be fixed? If yes, then how early you think it will be fixed?

Thanks,

-Rohit

|||

I am having the same problem. 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!

Combining Multiple Subreports in One Report

Hi,

I am trying to combine 5 different reports in one report. I am using them as subreports. The problem is, they are all different formats. For example, one of the report is rendered as 3 column report, and page length and width vary from report to report. When I tried to use table and list for this, my program crashed and closed my visual studio. When I use rectangle for it, then shows me the report but report is no more multiple column and page sizes were also different from the one I did in the report. Please guide me how to combine all these reports and not loose the formatting of the reports.

Thanks,

-Rohit

Multiple column layouts are currently not supported inside subreports, only as a main report.

-- Robert

|||

Hi Robert,

Do you think its going to be fixed? If yes, then how early you think it will be fixed?

Thanks,

-Rohit

|||

I am having the same problem. 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!

sqlsql

Combining Data from MySql and SQL2005

Problem:
I have a MySql database that houses status information that is linked
by a primary key from a SQL2005 database
Using Report Designer in VS2005, I have created 3 datasets pointing to
the 2 datasources. I need to create a relationship between the MySql
based datasets and a dataset that is based on a SQL2005 datasource, so
that I can have all of the data available in one table...
MySQL Table1
DocID, JobID, FKeyToSQL
MySQL Table2
JobID,Status
SQL2005 Data
PKeyToMySQL,Description,etc.
I would like to be able to see a status report that shows...
PKeyToMySQL,Description,JobID,Status
Thanks for any help.Can you try adding MySQL database as a LinkedServer?
"Kevin.Ailes@.gmail.com" wrote:
> Problem:
> I have a MySql database that houses status information that is linked
> by a primary key from a SQL2005 database
> Using Report Designer in VS2005, I have created 3 datasets pointing to
> the 2 datasources. I need to create a relationship between the MySql
> based datasets and a dataset that is based on a SQL2005 datasource, so
> that I can have all of the data available in one table...
> MySQL Table1
> DocID, JobID, FKeyToSQL
> MySQL Table2
> JobID,Status
> SQL2005 Data
> PKeyToMySQL,Description,etc.
> I would like to be able to see a status report that shows...
> PKeyToMySQL,Description,JobID,Status
> Thanks for any help.
>|||Jaraba wrote:
> Can you try adding MySQL database as a LinkedServer?
>
> "Kevin.Ailes@.gmail.com" wrote:
> > Problem:
> > I have a MySql database that houses status information that is linked
> > by a primary key from a SQL2005 database
> >
> > Using Report Designer in VS2005, I have created 3 datasets pointing to
> > the 2 datasources. I need to create a relationship between the MySql
> > based datasets and a dataset that is based on a SQL2005 datasource, so
> > that I can have all of the data available in one table...
> >
> > MySQL Table1
> > DocID, JobID, FKeyToSQL
> > MySQL Table2
> > JobID,Status
> >
> > SQL2005 Data
> > PKeyToMySQL,Description,etc.
> >
> > I would like to be able to see a status report that shows...
> >
> > PKeyToMySQL,Description,JobID,Status
> >
> > Thanks for any help.
> >
> >
I was able to embed a sub-report and submit a parameter to it and it
seems to have accomplished what I wanted. It seems like a round about
way to do it though. I would have thought there would be some way to
build relationships between datasets since you can't combine data from
2 different datasources into 1 dataset. Oh well, now if I could just
get this sub-report to be centered in the column.(TextAlign=center on
column properties doesn't seem to be working)

Combining data from multiple columns or views

I am trying to combine/report on data that is in one table. Due to system
limitations I have 3 columns that store the same type of business data.
Salesperson A, B and C are separate columns but need to be combined for
reporting. Can I create another table where I can use a select into to put
Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
into Column 1? If so, how?
Here is a sample data line:
Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
SalesAmount
I thought I could combine it into the following layout:
Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.Hi Dan,
Do you want to concatenate Salespersons A, B, and C all into one column, or
do you want to create a new row for Salesperson A, Salesperson B, and
salesperson C?
To concatenate, you can do the following:
select SalespersonA+SalespersonB+salespersonC, CommissionRate, DocNumber,
SalesAmount from tbl_name
If you want to create a new table and then insert into it from the other
table where each salesperson has their own row, then it would look something
like this:
create the table with columns for Salesperson, CommissionRate, DocNumber,
SalesAmount
Insert into NewTable
select SalespersonA, commissionRate, DocNumber, SalesAmount
Insert into NewTable
select SalespersonB, CommissionRate, DocNumber, SalesAmount
ect.
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Thanks for the help... I tried the following syntax and it was not working:
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS, THRDPRTYNAME,
COMMRATE, REFERRATE) VALUES (SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW)
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Change it to
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME,
COMMRATE, REFERRATE) SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW
Regards
Amish Shah|||you can also do it with unions and renaming the columns.

Combining data from multiple columns or views

I am trying to combine/report on data that is in one table. Due to system
limitations I have 3 columns that store the same type of business data.
Salesperson A, B and C are separate columns but need to be combined for
reporting. Can I create another table where I can use a select into to put
Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
into Column 1? If so, how?
Here is a sample data line:
Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
SalesAmount
I thought I could combine it into the following layout:
Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.Hi Dan,
Do you want to concatenate Salespersons A, B, and C all into one column, or
do you want to create a new row for Salesperson A, Salesperson B, and
salesperson C?
To concatenate, you can do the following:
select SalespersonA+SalespersonB+salespersonC, CommissionRate, DocNumber,
SalesAmount from tbl_name
If you want to create a new table and then insert into it from the other
table where each salesperson has their own row, then it would look something
like this:
create the table with columns for Salesperson, CommissionRate, DocNumber,
SalesAmount
Insert into NewTable
select SalespersonA, commissionRate, DocNumber, SalesAmount
Insert into NewTable
select SalespersonB, CommissionRate, DocNumber, SalesAmount
ect.
"Dan Shepherd" wrote:

> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to pu
t
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Thanks for the help... I tried the following syntax and it was not working:
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS, THRDPRTYNAME,
COMMRATE, REFERRATE) VALUES (SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW)
"Dan Shepherd" wrote:

> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to pu
t
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Change it to
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME,
COMMRATE, REFERRATE) SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW
Regards
Amish Shah|||you can also do it with unions and renaming the columns.sqlsql

Combining a Stacked column and continous line graph

Hi All
I am trying a build a RS report which as a graph which combines two
series in to a stacked column and the remaining 6 data series as
continous line graphs.
I sort of managed to get it but it never works when compared to the
corresponding Excel graph.
Please help how do I build a graph report with combination of stacked
column and continous line graphs.
Thanks
KarenYou can combine a column and a line chart. Just set the chart type to column
and look for the "Plot data as line" checkbox on the data value appearance
tab. You may also be interested in this "how to" article:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
<karenmiddleol@.yahoo.com> wrote in message
news:1131153902.660326.3140@.g44g2000cwa.googlegroups.com...
> Hi All
> I am trying a build a RS report which as a graph which combines two
> series in to a stacked column and the remaining 6 data series as
> continous line graphs.
>
> I sort of managed to get it but it never works when compared to the
> corresponding Excel graph.
>
> Please help how do I build a graph report with combination of stacked
> column and continous line graphs.
> Thanks
> Karen
>

Combining a Line Graph and Scatter Graph

I have to make a report of a combination of salary trends of
employees. The idea is to show the salary of a employee for a certain
period of time, also showing where he stands when he compared to
salaries of other employees.
I will show the salary of the employee in a line Graph, thats fine I
have already done it. X axis -> Employment Time, Y-axis Salary The
qquery will be like
userid, date, salary
72, 01/01/2001, 1000
72, 06/01/2001, 1600
72, 01/01/2002, 6000
72, 06/01/2002, 7080
72, 01/01/2003, 8010
72, 06/01/2003, 10000
72, 01/01/2004, 10050
72, 06/01/2004, 15500
salary in the Values section
date in the Category Section
The Line Graph is done.
Assuming I have the same query with more userid values (For other
employees also). I have to plot scatter points around the Line Graph
showing the salaries of each other employee as against the employee
72.
So the Graph will have 1 line for userid 72, scatter points around
that line.
How can I do it. Working Ideas please.
Thanks !
Anand Sagar
Now I have to show scatter pointsanybody there !?

Combining 2 tables with date ranges

Hi there, I'm trying to generate a report for an old database and I'm
having trouble coming up with an elegant way of going about it. Using
cursors and other 'ugly' tools I could get the job done but 1) I don't
want the report to take ages to run, 2) I'm not a big fan of cursors!

Basically there are tables that track history and each table tends to
track only a specific value housed within a date range. I'm trying to
combine the tables to get a snap-shot of the complete history. I'm
having problems dealing with the Start/End Dates from the two tables
and building the dates in the final table to be broken down by 'history
type'.

Here are a few sample records and the results I'm trying to achieve:

Table 1:
CAgyHist (ProdID,AgyID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 5, 2006
1 2 Jan 5, 2006 Jan 25, 2006
1 1 Jan 25, 2006 NULL

Table 2:
CInvHist (ProdID, InvID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 23, 2006
1 2 Jan 23, 2006 Jan 15, 2006
1 1 Jan 15, 2006 NULL

Desired End Result:
CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
1 1 1 Jan 1, 2006 Jan 5, 2006
1 2 1 Jan 5, 2006 Jan 15, 2006
1 2 2 Jan 15, 2006 Jan 23, 2006
1 2 1 Jan 23, 2006 Jan 25, 2006
1 1 1 Jan 25, 2006 NULL

My challenge thus far has been dealing with the dates as they don't
necessarily correspond - from one table to the other.

I am by no means a database expert of any level and any help would be
greatly appreciated.

Thanks,
Frank.what do you mean by , "the dates don't correspond from 1 table to the
other"?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

"Frank" <mrpubnight@.hotmail.com> wrote in message
news:1151369612.360817.191930@.c74g2000cwc.googlegr oups.com...
> Hi there, I'm trying to generate a report for an old database and I'm
> having trouble coming up with an elegant way of going about it. Using
> cursors and other 'ugly' tools I could get the job done but 1) I don't
> want the report to take ages to run, 2) I'm not a big fan of cursors!
> Basically there are tables that track history and each table tends to
> track only a specific value housed within a date range. I'm trying to
> combine the tables to get a snap-shot of the complete history. I'm
> having problems dealing with the Start/End Dates from the two tables
> and building the dates in the final table to be broken down by 'history
> type'.
> Here are a few sample records and the results I'm trying to achieve:
> Table 1:
> CAgyHist (ProdID,AgyID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 Jan 5, 2006 Jan 25, 2006
> 1 1 Jan 25, 2006 NULL
> Table 2:
> CInvHist (ProdID, InvID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 23, 2006
> 1 2 Jan 23, 2006 Jan 15, 2006
> 1 1 Jan 15, 2006 NULL
> Desired End Result:
> CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> 1 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 1 Jan 5, 2006 Jan 15, 2006
> 1 2 2 Jan 15, 2006 Jan 23, 2006
> 1 2 1 Jan 23, 2006 Jan 25, 2006
> 1 1 1 Jan 25, 2006 NULL
> My challenge thus far has been dealing with the dates as they don't
> necessarily correspond - from one table to the other.
> I am by no means a database expert of any level and any help would be
> greatly appreciated.
> Thanks,
> Frank.|||>From your data, CInvHist has this row

CInvHist (ProdID, InvID,StartDate,EndDate)
1 2 Jan 23, 2006 Jan 15, 2006

which has StartDate *after* the EndDate. Is this what you mean?|||It looks like you want to treat the 2 tables as one so you can sort by
the start date? If so, then you can use a union query and use the order
by clause at the end of the second select statement like:
select * from table1
union
select * from table2
order by start date

Jason|||Frank (mrpubnight@.hotmail.com) writes:
> Basically there are tables that track history and each table tends to
> track only a specific value housed within a date range. I'm trying to
> combine the tables to get a snap-shot of the complete history. I'm
> having problems dealing with the Start/End Dates from the two tables
> and building the dates in the final table to be broken down by 'history
> type'.
> Here are a few sample records and the results I'm trying to achieve:
> Table 1:
> CAgyHist (ProdID,AgyID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 Jan 5, 2006 Jan 25, 2006
> 1 1 Jan 25, 2006 NULL
> Table 2:
> CInvHist (ProdID, InvID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 23, 2006
> 1 2 Jan 23, 2006 Jan 15, 2006
> 1 1 Jan 15, 2006 NULL
> Desired End Result:
> CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> 1 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 1 Jan 5, 2006 Jan 15, 2006
> 1 2 2 Jan 15, 2006 Jan 23, 2006
> 1 2 1 Jan 23, 2006 Jan 25, 2006
> 1 1 1 Jan 25, 2006 NULL
> My challenge thus far has been dealing with the dates as they don't
> necessarily correspond - from one table to the other.

There should be a fair chance to this in a query (or possibly two
with help of some temp table). But since it's bit complex, the hour
is late, and your sample data is unclear, I prefer to ask for
clarification:

1) What are the keys of these tables?
2) What do they signify?
3) What is the combined table supposed to describe?
4) Is that interval from Jan 23 to Jan 15 intentional or is a typo?
In the latter case, can you provide an updated sample?
--
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.mspx|||Sorry everyone there was a typo and I will expand a little as well.

1) The keys are as follows (both tables have primary ID keys too but
they weren't included in the original question - see brackets below)
CAgyHist:
(CAH_ID PK)
ProdID FK
AgyID FK

CInvHist:
(CIH_ID PK)
ProdID FK
InvID FK

2) ProdID = PK from the products table.
AgyID = PK from the Agency table (i.e. Supplier)
InvID = PK from the InventoryType table (categorization for products)

3) Products in our application can move from supplier to supplier and
can also change their categorization. Each of the history tables
tracks these changes as they occur and when they occur. The start date
is obviously when the product begins with the corresponding agency or
categorization, and the end date is when it finishes (a NULL value
means that the product is still with a given agency or being
categorized in a certain manner.

The problem I want/need to solve is I need a complete historical
account for a product as it moves from agency to agency and from
categorization to categorization and I need it to be on a single report
(table) and chronological, so hence the final table which shows how the
product has moved throughout time.

4) Yes, sorry that was a typo. The CInvHist table records should have
read:

Table 2:
CInvHist (ProdID, InvID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 15, 2006
1 2 Jan 15, 2006 Jan 23, 2006
1 1 Jan 23, 2006 NULL

Sorry about all that confusion. I'm really hoping that this isn't too
tough or time consuming (from an execution point of view).

Again, any help will be appreciated.

Thanks,
Frank

Erland Sommarskog wrote:
> Frank (mrpubnight@.hotmail.com) writes:
> > Basically there are tables that track history and each table tends to
> > track only a specific value housed within a date range. I'm trying to
> > combine the tables to get a snap-shot of the complete history. I'm
> > having problems dealing with the Start/End Dates from the two tables
> > and building the dates in the final table to be broken down by 'history
> > type'.
> > Here are a few sample records and the results I'm trying to achieve:
> > Table 1:
> > CAgyHist (ProdID,AgyID,StartDate,EndDate)
> > 1 1 Jan 1, 2006 Jan 5, 2006
> > 1 2 Jan 5, 2006 Jan 25, 2006
> > 1 1 Jan 25, 2006 NULL
> > Table 2:
> > CInvHist (ProdID, InvID,StartDate,EndDate)
> > 1 1 Jan 1, 2006 Jan 23, 2006
> > 1 2 Jan 23, 2006 Jan 15, 2006
> > 1 1 Jan 15, 2006 NULL
> > Desired End Result:
> > CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> > 1 1 1 Jan 1, 2006 Jan 5, 2006
> > 1 2 1 Jan 5, 2006 Jan 15, 2006
> > 1 2 2 Jan 15, 2006 Jan 23, 2006
> > 1 2 1 Jan 23, 2006 Jan 25, 2006
> > 1 1 1 Jan 25, 2006 NULL
> > My challenge thus far has been dealing with the dates as they don't
> > necessarily correspond - from one table to the other.
> There should be a fair chance to this in a query (or possibly two
> with help of some temp table). But since it's bit complex, the hour
> is late, and your sample data is unclear, I prefer to ask for
> clarification:
> 1) What are the keys of these tables?
> 2) What do they signify?
> 3) What is the combined table supposed to describe?
> 4) Is that interval from Jan 23 to Jan 15 intentional or is a typo?
> In the latter case, can you provide an updated sample?
> --
> 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.mspx|||Your sample data is a mess, but the usual way is to build a calendar
and join these improperly designed tables together with BETWEEN
predicates, something like:

SELECT C.cal_date, T1.a, T2.b, ..
FROM Calendar AS C, T1, T2
WHERE C.cal_date BETWEEN T1.start_date AND T1.end_date
AND C.cal_date BETWEEN T2.start_date AND T2.end_date
AND .. ;

MIssing or reversed data will not be shown in this query.|||Frank (mrpubnight@.hotmail.com) writes:
> 1) The keys are as follows (both tables have primary ID keys too but
> they weren't included in the original question - see brackets below)
> CAgyHist:
> (CAH_ID PK)
> ProdID FK
> AgyID FK
> CInvHist:
> (CIH_ID PK)
> ProdID FK
> InvID FK

That's a bit problematic. It s not clear whether I can trust whether
ProdID, StartDate can be unique, or whether there can be more entries for
the same day and product. In my solution below, I have assumed they are
unique. Then again, if they were there is no reason for that CAH_ID.

Here is a query that works with your sample data. I will have to admit
that I'm not fully certain on how it works, and I would recommend you
to test further. I would also suggest that you check out
http://groups.google.com/group/comp...48dda4c48fb808b
for a similar problem.

CREATE TABLE CAgyHist (ProdID int NOT NULL,
AgyID int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
PRIMARY KEY(ProdID, StartDate))

CREATE TABLE CInvHist (ProdID int NOT NULL,
InvID int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
PRIMARY KEY(ProdID, StartDate))

INSERT CAgyHist(ProdID,AgyID,StartDate,EndDate)
SELECT 1, 1, 'Jan 1, 2006', 'Jan 5, 2006'
UNION
SELECT 1, 2, 'Jan 5, 2006', 'Jan 25, 2006'
UNION
SELECT 1, 1, 'Jan 25, 2006', NULL

INSERT CInvHist (ProdID, InvID,StartDate,EndDate)
SELECT 1, 1, 'Jan 1, 2006', 'Jan 15, 2006'
UNION
SELECT 1, 2, 'Jan 15, 2006', 'Jan 23, 2006'
UNION
SELECT 1, 1, 'Jan 23, 2006', NULL

SELECT ProdID, AgyID, InvID, StartDate, EndDate
FROM (SELECT a.ProdID, a.AgyID, i.InvID,
CASE WHEN a.StartDate > i.StartDate
THEN a.StartDate
ELSE i.StartDate
END AS StartDate,
CASE WHEN coalesce(a.EndDate, '99991231') <
coalesce(i.EndDate ,'99991231')
THEN a.EndDate
ELSE i.EndDate
END AS EndDate
FROM CAgyHist a
JOIN CInvHist i ON a.ProdID = i.ProdID) AS x
WHERE StartDate < coalesce(EndDate, '99991231')
ORDER BY StartDate, EndDate
go
DROP TABLE CAgyHist
DROP TABLE CInvHist

--
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.mspx

Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,
Did you try the option of building OLAP cube from those 3 DBs and then generating a Report Model out of the cube. I think that is a clear option.
Thanks,
S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,

Did you try the option of building OLAP cube from those 3

DBs and then generating a Report Model out of the cube. I think that is

a clear option.

Thanks,

S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,

Did you try the option of building OLAP cube from those 3

DBs and then generating a Report Model out of the cube. I think that is

a clear option.

Thanks,

S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

sqlsql