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

combining datasets

I have two queries, one that brings back data from a cube and another that
returns records from a db. Is there anyway inside reporting services to
combine the two data sets?Only through subreports.
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:ucK4AE02FHA.3272@.TK2MSFTNGP09.phx.gbl...
> I have two queries, one that brings back data from a cube and another that
> returns records from a db. Is there anyway inside reporting services to
> combine the two data sets?
>

Combining data from several databases

Hello

I'm to familiar with Analysis Services. If my question is not related to this forum, please redirect me to somewhere else.

I have a number of databases of the same structure (for instance 5 databases). I would like to create some reports using data from all of them. For instance if there is a table called Customer I would like to get a report with a list of Customers with the addition of one column that contains the source database.

I'm also concerned about performance. I would prefer if the data of the original databases where copied somewhere else, since they are OLTP databases and it would be good not to be affected in terms of performance by the whole procedure.

Can you please give me some options on how to implement a solution?

Hello!

I would recommend you to build a data warehouse.

With SQL Server 2005 you have all tools you need for that.

SSIS(Integration Services) let you pump and transform data from the five source databases.

When you have a data warehouse finished you can build an Analysis Services(SSAS2005) cube on top of that or query the data warehouse tables directly with reporting services(SSRS2005).

All these poducts, the databas engine, SSIS2005, SSAS2005 and SSRS2005 are included in the same license.

HTH

Thomas Ivarsson

Combining Cross-Tab and charts

Dear all,
I'm a noob to SQL Reporting Services but have plenty of experience with
other MS applications (including excel and access). I was wondering whether
it's possible to combine a cross-tab and a chart within a single report. If
so, how do you do this? I believe the power of a report (often) exists out of
numbers combined with a graphical display.
thanks in advance,
mischaOn Apr 26, 10:22 am, mischa <mis...@.discussions.microsoft.com> wrote:
> Dear all,
> I'm a noob to SQL Reporting Services but have plenty of experience with
> other MS applications (including excel and access). I was wondering whether
> it's possible to combine a cross-tab and a chart within a single report. If
> so, how do you do this? I believe the power of a report (often) exists out of
> numbers combined with a graphical display.
> thanks in advance,
> mischa
If I understand you correctly, you should be able to use a matrix
control and a chart control.
Regards,
Enrique Martinez
Sr. Software Consultant

combine pdf with reporting services pdfs

Through another software package, I generate pdfs which I save. I now want to combine those pdf with pdf output from reporting services. how can i do this?

thks

ken

Hi Ken-

You'd need to create your own delivery extension to do that. I imagine you could add the PDF to the RS rendered PDFs in the Deliver method of your custom delivery provider. Check out the PrinterDeliverySample in the Samples folder where you installed RS. This example can give you an idea of what you need to do to set this up.

Scott

Combination Graphs?

Are combination graphs possible in reporting services?yes, you can create a Column chart that includes a Line. There is an excellent paper on MSDN showing how|||

Thanks.

I think I phrased my question poorly.

I meant Bar-Chart with multiple Y-axis, like:

http://www.swiftchart.com/example_1.htm#mbar_ex1

columns dont work in subreport reporting services

only one long column is showing up in the main report for the suyb report
part of it, but when i run the subreport by itself it shows two.I have the exact same issue. When I run the report alone it looks great but
as soon as I add it to a main report the two coulumns only show as one long
column. I have posted a question on the boeard and have asked for a help
article number so I can give that to MS support otherwise they want to charge
me for a support ticket. I believe this is a MS bug. I have also consulted
with authors and traininers of SQL RS and they can not seems to get this to
work either. I have also found NO book that actually references this issue.
Any help you find would help me out as well. jasonmr2@.hotmail.com
Jason Miller
"D Witherspoon" wrote:
> only one long column is showing up in the main report for the suyb report
> part of it, but when i run the subreport by itself it shows two.
>
>|||This is right from MS...
"The multi column functionality is ignored by subreports. It only works when
used in the main report
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights."
"D Witherspoon" wrote:
> only one long column is showing up in the main report for the suyb report
> part of it, but when i run the subreport by itself it shows two.
>
>|||... they make it sound like a feature ... :(
"Jason Miller" <JasonMiller@.discussions.microsoft.com> wrote in message
news:3AF18960-6CAF-4C6B-A56F-65BF3D0FF5F7@.microsoft.com...
> This is right from MS...
> "The multi column functionality is ignored by subreports. It only works
> when
> used in the main report
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights."
> "D Witherspoon" wrote:
>> only one long column is showing up in the main report for the suyb report
>> part of it, but when i run the subreport by itself it shows two.
>>

columns containing percantage values

Hi,
I am a newbie in reporting services with a pretty easy question.
How can display percentage values of a column depending of the column
sum?
Example:
_______________________________________________
row1 3 30%
row2 5 50%
row3 2 20%
_______________
sum 10 100%
_______________________________________________
How do I create the last column?
ThanxLast week I did this for the first time.
Created a sum total in a report footer for your 2nd col and the10 would show
for your sample. Give it a label like Total_Count. Then for the 3rd col
which is to be the percentage, use the expression =Sum(Fields!Total_Count.
Value)/(ReportItems!TotalCount.Value)
HTH!
James Ski
mickmack wrote:
>Hi,
>I am a newbie in reporting services with a pretty easy question.
>How can display percentage values of a column depending of the column
>sum?
>Example:
>_______________________________________________
>row1 3 30%
>row2 5 50%
>row3 2 20%
>_______________
>sum 10 100%
>_______________________________________________
>How do I create the last column?
>Thanx
--
Message posted via http://www.sqlmonster.com|||Thank you James for your prompt reply,
that works with one column.
But how would I create the following matrix?
_____________________________________
row1 3 30% 8 40%
row2 5 50% 8 40%
row3 2 20% 4 20%
_________________________
sum 10 100% 20 100%
_____________________________________
I dont know how to call the 10 and the 20 seperatly. Which names do
they have?
Thanx|||You could use the sum aggregate , but shouldn't the group % always be 100%..
You could also refer to the column with 10 in it using
ReportItems!textboxname.Value
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"mickmack" wrote:
> Thank you James for your prompt reply,
> that works with one column.
> But how would I create the following matrix?
> _____________________________________
> row1 3 30% 8 40%
> row2 5 50% 8 40%
> row3 2 20% 4 20%
> _________________________
> sum 10 100% 20 100%
> _____________________________________
> I dont know how to call the 10 and the 20 seperatly. Which names do
> they have?
> Thanx
>|||here is how I did:
drag a list into your report, in the list, drag in two subreports, one is
on the left and another one is on the right. then you write stored procudures
(name it sptest for now) with one parameter. In your subreport one, you call
sptest 0, and it will display the left 3 columns, subreport two, you call
sptest 1, and it will display the right 2 columns. sptest 0, and sptest 1 is
the dataset that you create when you design your subreport. The hard part for
this is the store procedure. you need to decide how many rows/columns that
you want to display. For my example, sptest 0, I display:
01/2004 5
02/2004 6
03/2004 7
.
.
.
sptest 1, then display on the second subreport
01/2005 1
02/2005 2
03/2005 3
.
.
.
finally, it ends up like this
01/2004 5 01/2005 1
02/2004 6 02/2005 2
03/2004 7 03/2005 3
. .
. .
. .
I hope this will give you some idea.
Henry
"mickmack" wrote:
> Thank you James for your prompt reply,
> that works with one column.
> But how would I create the following matrix?
> _____________________________________
> row1 3 30% 8 40%
> row2 5 50% 8 40%
> row3 2 20% 4 20%
> _________________________
> sum 10 100% 20 100%
> _____________________________________
> I dont know how to call the 10 and the 20 seperatly. Which names do
> they have?
> Thanx
>|||today I found a page at microsoft, where the problem is solved:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/semiadd2.asp

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 Ranking on SS 2000 and/or Reporting Services report

I see a Rank function on SQL Server 2005 and this is exactly what I want. I am writing a Reporting Services report on Sql Server 2000 and the Rank function is not available on either software. The following is my data and what I want...

Name Value Rank

A 5 2

B 5 2

C 6 1

D 4 4

E 2 5

F 1 6

Any ideas on how to code a rank function on SS 2000 or Reporting Services?This article describes using a table-valued function to perform ranking. http://www.devx.com/getHelpOn/10MinuteSolution/16499/1954?pf=true|||

The SQL function in the link may not provide you the result you are looking for, as in SQL Server 2005. For example, the function may return records with ranks like 1,2,3,3,4,5 but what you need is 1,2,3,3,5,6 as per sql 2005. To do that, write an update statement at the end of the function before returning.

UPDATE t1

SET Rank = Rank +

(SELECT SUM(COUNT(Rank)-1) FROM table1 t2 WHERE t2.Rank < t1.Rank

GROUP BY Rank HAVING COUNT(Rank) > 1)

FROM table1 t1

GROUP BY Rank

HAVING COUNT(*) = 1

ORDER BY Rank'

Shyam

|||Thanks. I noticed the result set from the link. Thanks for the code.|||

Can you please mark it as answer?

Shyam