Combining tables from different databases

Hi.

I'm currently working on a project which involves the creation of a
web page which reports selected data to customers from two back-end
systems. I use ASP script language on IIS, the server is MS SQL 2000.
Now I'm struggling with combining two tables from the different
databases. I'm sure it's simple enough, but I'm a little short on the
SQL expertise.

I've got two databases, db1 and db2, and then two tables, db1.t1 and
db2.t2. I need to combine these two tables (both tables have a
matching key field) to make a list of all items from db1.t1, and those
who correspond from db2.t2.

I can list all items from db1.t1, but I can't seem to get the db2.t2
joined in.
Can anybody help me with the syntax for this, please ? Help !

Answers, hints & tips greatly appreciated.
Thanks in advance !
KennethHi

You will need three part naming to do this

Use DB1 -- Connected to DB1!

SELECT t.Fld, s.Fld
FROM t1 t JOIN db2..t2 s ON t.Fld = s.Fld

John

"Kenneth Fosse" <kennethfosse@.hotmail.com> wrote in message
news:a4092994.0310110658.42c8abc7@.posting.google.c om...
> Hi.
> I'm currently working on a project which involves the creation of a
> web page which reports selected data to customers from two back-end
> systems. I use ASP script language on IIS, the server is MS SQL 2000.
> Now I'm struggling with combining two tables from the different
> databases. I'm sure it's simple enough, but I'm a little short on the
> SQL expertise.
> I've got two databases, db1 and db2, and then two tables, db1.t1 and
> db2.t2. I need to combine these two tables (both tables have a
> matching key field) to make a list of all items from db1.t1, and those
> who correspond from db2.t2.
> I can list all items from db1.t1, but I can't seem to get the db2.t2
> joined in.
> Can anybody help me with the syntax for this, please ? Help !
> Answers, hints & tips greatly appreciated.
> Thanks in advance !
> Kenneth|||You need to add it as a linked server then use the fully quaklified name
server.database.ownername.tablename

Look up sp_addlinkedserver in BOL

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

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 Output PDF files

Is there a way to output reports to the same PDF file. Basicly Appending several reports to the one PDF.

Without combining the reports using subreports, I think you would use code & custom PDF components, unless you want to jump into writing a rendering extension.

Softartisians Officewriter & abcPDF.NET are two possible options. You can also try automating Acrobat.

http://www.planetpdf.com/forumarchive/84747.asp

One alternative may be to export to a different format and then convert to pdf afterwards.

cheers,

Andrew

|||

we're trying to do this on the report server with out having to add another process.

Is there any good tutorials for writing a rendering extension? I've heard its difficult.

|||

Not sure if it has changed since RS 2000, but here is a comment on how difficult...

http://blogs.msdn.com/bryanke/archive/2004/03/16/90797.aspx

If you want to develop one, it might help to travel to Microsoft's main campus and stay for a month or two while you work alongside Reporting Services developers.

cheers,

Andrew

|||

LOL, Yeah I saw that, showed it to my boss. He said no to funding that trip..Oh well...LOL

Doubt they would let me in anyways

But hey if Microsoft wants to use me as a test case, I'm all for it!!! {WINK WINK, NUDGE NUDGE}

sqlsql

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 multiple reports into one print job

I have a vb program that uses many different crystal reports. They are all slightly complex and have subreports already in them. When the user wantes to print the whole project it comes out as 10 different print jobs. I would like to make it one print job or append consecutive reports together to create one report (programaticaly). I would appreciate any help. Thank you.Did you ever get a solution to this issue? I am trying to do a similar thing. If so could you send me some info.

Thanks.sqlsql

Combining details from two datasets

Hi. I'm just starting to develop reports using Visual Studio 2005 and
Reporting Services 2005 and am still trying to get my head round things. One
immediate problem I could use some advice on is this.
I'm trying to reproduce a report that, in its original incarnation as an asp
page, displayed data returned by two Stored Procedures. The report displayed
the data returned by SP1 in the first 5 columns and for each Group returned
by SP1, SP2 returned an associated figure that was displayed as column 6.
How would I do this in a RS Report since those report objects that are
associated with Datasets - i.e. Tables, Lists and Matrices - can only be
associated with one Dataset?
I'd appreciate any pointers anyone would care to give me on this.
Regards,
YaHozna.You do this with subreport. A subreport can be put into the field of the
table control. First create two reports. The main one with all the data.
Then the report that will be a subreport. Get the second report to work
stand alone first (create it with parameters). Then drag and drop the second
report into a cell of the table object. Do a right mouse click on the sub
report, parameters and bind the parameters to a field of the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"YaHozna" <YaHozna@.discussions.microsoft.com> wrote in message
news:FE166D20-3F15-43E4-8332-2EE86DB82111@.microsoft.com...
> Hi. I'm just starting to develop reports using Visual Studio 2005 and
> Reporting Services 2005 and am still trying to get my head round things.
> One
> immediate problem I could use some advice on is this.
> I'm trying to reproduce a report that, in its original incarnation as an
> asp
> page, displayed data returned by two Stored Procedures. The report
> displayed
> the data returned by SP1 in the first 5 columns and for each Group
> returned
> by SP1, SP2 returned an associated figure that was displayed as column 6.
> How would I do this in a RS Report since those report objects that are
> associated with Datasets - i.e. Tables, Lists and Matrices - can only be
> associated with one Dataset?
> I'd appreciate any pointers anyone would care to give me on this.
> Regards,
> YaHozna.|||Bruce, many thanks. That worked perefectly. I wonder if I might presume upon
your patience once more? I can't seem to set the formatting of the subreport
cell in my table to match the rest of the row, which is a different colour
and font. I've tried changing the formatting of the subreport but to no
avail. Is there a way to do this?
Regards,
YaHozna.
"Bruce L-C [MVP]" wrote:
> You do this with subreport. A subreport can be put into the field of the
> table control. First create two reports. The main one with all the data.
> Then the report that will be a subreport. Get the second report to work
> stand alone first (create it with parameters). Then drag and drop the second
> report into a cell of the table object. Do a right mouse click on the sub
> report, parameters and bind the parameters to a field of the dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "YaHozna" <YaHozna@.discussions.microsoft.com> wrote in message
> news:FE166D20-3F15-43E4-8332-2EE86DB82111@.microsoft.com...
> > Hi. I'm just starting to develop reports using Visual Studio 2005 and
> > Reporting Services 2005 and am still trying to get my head round things.
> > One
> > immediate problem I could use some advice on is this.
> >
> > I'm trying to reproduce a report that, in its original incarnation as an
> > asp
> > page, displayed data returned by two Stored Procedures. The report
> > displayed
> > the data returned by SP1 in the first 5 columns and for each Group
> > returned
> > by SP1, SP2 returned an associated figure that was displayed as column 6.
> >
> > How would I do this in a RS Report since those report objects that are
> > associated with Datasets - i.e. Tables, Lists and Matrices - can only be
> > associated with one Dataset?
> >
> > I'd appreciate any pointers anyone would care to give me on this.
> >
> > Regards,
> >
> > YaHozna.
>
>|||You need to do the formatting in the subreport, not for the cell in the
master report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"YaHozna" <YaHozna@.discussions.microsoft.com> wrote in message
news:DE1CEE92-572C-446F-9C17-85E7C4D3F19D@.microsoft.com...
> Bruce, many thanks. That worked perefectly. I wonder if I might presume
> upon
> your patience once more? I can't seem to set the formatting of the
> subreport
> cell in my table to match the rest of the row, which is a different colour
> and font. I've tried changing the formatting of the subreport but to no
> avail. Is there a way to do this?
> Regards,
> YaHozna.
>
> "Bruce L-C [MVP]" wrote:
>> You do this with subreport. A subreport can be put into the field of the
>> table control. First create two reports. The main one with all the data.
>> Then the report that will be a subreport. Get the second report to work
>> stand alone first (create it with parameters). Then drag and drop the
>> second
>> report into a cell of the table object. Do a right mouse click on the sub
>> report, parameters and bind the parameters to a field of the dataset.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "YaHozna" <YaHozna@.discussions.microsoft.com> wrote in message
>> news:FE166D20-3F15-43E4-8332-2EE86DB82111@.microsoft.com...
>> > Hi. I'm just starting to develop reports using Visual Studio 2005 and
>> > Reporting Services 2005 and am still trying to get my head round
>> > things.
>> > One
>> > immediate problem I could use some advice on is this.
>> >
>> > I'm trying to reproduce a report that, in its original incarnation as
>> > an
>> > asp
>> > page, displayed data returned by two Stored Procedures. The report
>> > displayed
>> > the data returned by SP1 in the first 5 columns and for each Group
>> > returned
>> > by SP1, SP2 returned an associated figure that was displayed as column
>> > 6.
>> >
>> > How would I do this in a RS Report since those report objects that are
>> > associated with Datasets - i.e. Tables, Lists and Matrices - can only
>> > be
>> > associated with one Dataset?
>> >
>> > I'd appreciate any pointers anyone would care to give me on this.
>> >
>> > Regards,
>> >
>> > YaHozna.
>>|||Seems to be displaying old subreport formatting in the main report that has
subsequently been changed. However I shall persevere :)
Many thanks for the help.
Regards,
YaHozna.
"Bruce L-C [MVP]" wrote:
> You need to do the formatting in the subreport, not for the cell in the
> master report.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "YaHozna" <YaHozna@.discussions.microsoft.com> wrote in message
> news:DE1CEE92-572C-446F-9C17-85E7C4D3F19D@.microsoft.com...
> > Bruce, many thanks. That worked perefectly. I wonder if I might presume
> > upon
> > your patience once more? I can't seem to set the formatting of the
> > subreport
> > cell in my table to match the rest of the row, which is a different colour
> > and font. I've tried changing the formatting of the subreport but to no
> > avail. Is there a way to do this?
> >
> > Regards,
> >
> > YaHozna.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You do this with subreport. A subreport can be put into the field of the
> >> table control. First create two reports. The main one with all the data.
> >> Then the report that will be a subreport. Get the second report to work
> >> stand alone first (create it with parameters). Then drag and drop the
> >> second
> >> report into a cell of the table object. Do a right mouse click on the sub
> >> report, parameters and bind the parameters to a field of the dataset.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "YaHozna" <YaHozna@.discussions.microsoft.com> wrote in message
> >> news:FE166D20-3F15-43E4-8332-2EE86DB82111@.microsoft.com...
> >> > Hi. I'm just starting to develop reports using Visual Studio 2005 and
> >> > Reporting Services 2005 and am still trying to get my head round
> >> > things.
> >> > One
> >> > immediate problem I could use some advice on is this.
> >> >
> >> > I'm trying to reproduce a report that, in its original incarnation as
> >> > an
> >> > asp
> >> > page, displayed data returned by two Stored Procedures. The report
> >> > displayed
> >> > the data returned by SP1 in the first 5 columns and for each Group
> >> > returned
> >> > by SP1, SP2 returned an associated figure that was displayed as column
> >> > 6.
> >> >
> >> > How would I do this in a RS Report since those report objects that are
> >> > associated with Datasets - i.e. Tables, Lists and Matrices - can only
> >> > be
> >> > associated with one Dataset?
> >> >
> >> > I'd appreciate any pointers anyone would care to give me on this.
> >> >
> >> > Regards,
> >> >
> >> > YaHozna.
> >>
> >>
> >>
>
>

Combine two or more reports into one PDF Report

We are building a "Annual Statement" which will include a series or Reports
that will be bound into a Booklet. We will need a table of Contents (With
Page Numbers) and also need the pages to be continuous throughout.
Our question is what is the best approach to build this dynamically in
Report Server?
Build One "Master Report which includes Sub-Reports or Build the Sub Reports
and pass them into the Master?
We have a middle "Business Logic" Tier that we will be driving the creation
of these reoprts from.
Thanks!
...BryanUpdate:
I am thinking that we can programattically create a "Master RDL" that
includes the Sub Reports.
Question: would we be able to control the processing of the sub reports so
that they generate first then generate the Master Report genterating the
Table of Contents after the Sub Reports have been created (and we know the
sub Report Page Counts)
Any Suggestions would be really appreachiated
Thanks!
...Bryan
"Bryan E." wrote:
> We are building a "Annual Statement" which will include a series or Reports
> that will be bound into a Booklet. We will need a table of Contents (With
> Page Numbers) and also need the pages to be continuous throughout.
> Our question is what is the best approach to build this dynamically in
> Report Server?
> Build One "Master Report which includes Sub-Reports or Build the Sub Reports
> and pass them into the Master?
> We have a middle "Business Logic" Tier that we will be driving the creation
> of these reoprts from.
> Thanks!
> ...Bryan|||Bryan -
Did you ever get an answer? We are looking to do something similar. The
first page of one of our reports needs to be a table of contents.
Thanks
Jen
"Bryan E." wrote:
> Update:
> I am thinking that we can programattically create a "Master RDL" that
> includes the Sub Reports.
> Question: would we be able to control the processing of the sub reports so
> that they generate first then generate the Master Report genterating the
> Table of Contents after the Sub Reports have been created (and we know the
> sub Report Page Counts)
> Any Suggestions would be really appreachiated
> Thanks!
> ...Bryan
> "Bryan E." wrote:
> > We are building a "Annual Statement" which will include a series or Reports
> > that will be bound into a Booklet. We will need a table of Contents (With
> > Page Numbers) and also need the pages to be continuous throughout.
> >
> > Our question is what is the best approach to build this dynamically in
> > Report Server?
> >
> > Build One "Master Report which includes Sub-Reports or Build the Sub Reports
> > and pass them into the Master?
> >
> > We have a middle "Business Logic" Tier that we will be driving the creation
> > of these reoprts from.
> >
> > Thanks!
> > ...Bryan

Combine multiple RDL files into one RDL file

Hello,

I need to generate a report, which should display 4 reports. Two tables and some charts. I have all these reports (I mean the .RDL files) individually. I can render the reports separately. But, now the need is to combine these reports in the one RDL file. Is this possible? If yes, how?

Also, I tried to create a stored procedure, which would call all these 4 SP inturn and provide 4 result sets. I thought of have an RDL by calling only this SP which would give 4 result sets. But infortunately, it gave only the first SP's result set. So, I have to combine the 4 RDL files into one to show on the Reporting Console. Can anyone please help me in this? Help would be grately appreciated.

Thanks a lot. Let me know if the question is not clear.

Mannu.

Well let's see.

You would need to create 4 datasets, one for each of the original reports. You can do this on the data tab by clicking the dataset drop down and Add new dataset.

Then, copy and paste each of the individual layouts into this "super" report.

On each of the tables and charts, go to the properties for the table or chart and select the appropriate dataset as the "dataset name" that you created.

|||

Yes GregSQL gives you right approach for the problem but there is a catch.

If you create 4 datasets using same SP that will still give you the first resultset. To overcome this issue I believe you have to pass an extra parameter say ReportType with datatype as Char. Give a condition in SP to return the Dataset you want to return for that perticular ReportType.

IN StoredProcedure:

IF @.ReportType = 'A'

BEGIN

YOUR DATASET

END

IF @.ReportType = 'B'

BEGIN

YOUR DATASET

END

Comming to report , click on each Dataset and go to Parameters section and give the ReportType value for each dataresult.'A' for First Dataset, 'B' for Second Datset.

I think this will complete the whole scenario...

Hope this helps

Column width

i am using reporting services 2000 & my backend is sql 2000.
In one of my reports, i have a parameter which needs to take value more than
1400 characters but in reporting services report view i can only give 1200
characters. Kindly let me know how do i resolve this issue.WOW! A parameter with 1400 characters? Where does that value come
from? Probably not from user input I'll bet.
Anyway, if the SSRS max is 1200, I doubt there's going to be a way to
expand that. Alternatively, you're going to have to look at
abbreviating the param value. Obvious I know, but without more info,
it's going to be hard to offer helpful suggestions. Post some more
details please and we can give it a shot.
toolman
Hasan Dalwai wrote:
> i am using reporting services 2000 & my backend is sql 2000.
> In one of my reports, i have a parameter which needs to take value more than
> 1400 characters but in reporting services report view i can only give 1200
> characters. Kindly let me know how do i resolve this issue.

Column Totals / Sum by Date

HI,
I am new to RS and I am running into some problems create reports. What I
would like to do is create a report that will count all distinct rows for a
"Users" column for every single date. I am able to get the total users from
the "Users" column but the problem is getting a running list of totals by
date. There is no date field in the database. There is a date field for
enrollment and unenroll but these are not the dates I am looking for. I
would like every single date to be totaled.
Do you know of any examples of this on the web or an example that you could
send me?
Any help would be great!
ThanksYou can't report on anything that's not in your data source. It sounds like
you're trying to get a count of users for each date. You'll have to solve
that in your source query first, then you can report on it. My suggestion
is to create a new reference date table with a record for each date in the
range you want to report on. That's only 365 records per year, so make as
many years as you want. Then join to the Users table on ReferenceDate
between EnrollDate and UnenrollDate.
A nice benefit of having a reference date table is that you can put other
data in each record as well, such as week number, quarter, fiscal year and
calendar year, for easy grouping. Yeah, it's denormalized, but makes
reporting a snap. It's easy to do this is in Excel, then import the data
into SQL.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"ACD" <ACD@.discussions.microsoft.com> wrote in message
news:66C00F58-9E89-456B-9931-5E2E5E964645@.microsoft.com...
> HI,
> I am new to RS and I am running into some problems create reports. What
> I
> would like to do is create a report that will count all distinct rows for
> a
> "Users" column for every single date. I am able to get the total users
> from
> the "Users" column but the problem is getting a running list of totals by
> date. There is no date field in the database. There is a date field for
> enrollment and unenroll but these are not the dates I am looking for. I
> would like every single date to be totaled.
> Do you know of any examples of this on the web or an example that you
> could
> send me?
> Any help would be great!
> Thanks

Column Header Problems in PDF exports

Hi. I have some matrix style reports that many customers export to PDF.
Most of these are quite large files and span numerous pages. The first 3 - 4
pages of the report look great, then after that the column header is pushed
down into the first data row of the report.
Has anyone run into this or know of a workaround etc.?
Thanks in advance.this is a known microsoft bug is what i found out
"comet61" wrote:
> Hi. I have some matrix style reports that many customers export to PDF.
> Most of these are quite large files and span numerous pages. The first 3 - 4
> pages of the report look great, then after that the column header is pushed
> down into the first data row of the report.
> Has anyone run into this or know of a workaround etc.?
> Thanks in advance.

Colspan in reports?

Is it possible to manipulate table or matrix cells with colspan in reports?
I have several reports that show Client and Project.
Currently, the only way I have to display it is like this:
Clientname
..............Project
What I want to do is like this:
Clientname
....Project
Is it possible to do this? How? Conditionally merge cells?
All help appreicated!
Kaisa M. LindahlYou are correct, merge the cells to get a colspan in a table. Simply
highlight the cells you want to merge (in the report designer) and
right click. You'll see then the option to Merge Cells.|||<cmarinella@.gmail.com> wrote in message
news:1104769372.216591.197530@.c13g2000cwb.googlegroups.com...
> You are correct, merge the cells to get a colspan in a table. Simply
> highlight the cells you want to merge (in the report designer) and
> right click. You'll see then the option to Merge Cells.
>
Ah, nice.
But I can only make it work in table view. Is it possible to do something
like it in matrix too?
Kaisa M. Lindahl

Colour Printing in CR 8.5

Hi
I'm using Crystal reports in My VB application. All but one of my reports are printing in colour. All my reports/designers are in the same project. Any ideas why this one report is printing in black & white?
any help would be appreciatedI do not know if this will apply to you because I am using Crystal 10.

I had a similar problem with users trying to print crystal reports in color, but they were printing in black and white. What I found was that since I created the crystal reports, all users who were accessing the crystal reports were printing from my printers default settings. I have a black and white printer in my office, so all the users were printing in balc and white.

What I had to do was, open the crystal report setup/creator, open the printer setup, select the color printer, and then save the report. After this was done, all users were able to print the crystal reports in color.

Let me know if this helps.

Colour of the series in legend

I have few chart based reports where I am showing different costs in series. I want to fix the colour of each type of cost so that I can maintain the same colour for each cost in different reports.

Is it possible? If yes, then please let me know.

Thanks in advance!!!

Check out my sample report on Brian Welcker's blog: http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

-- Robert