Combining two rows in a view

I have created a view for reporting. Im basically just joining a few
tables. It is for a University so the results shows students names and
the credits they are currently taking and the school code (There is 3
Colleges under one ownership)
The problem is some students attend two colleges and appear twice,
one for each enrollment. For example
FName LName Credits SchoolCode
John Smith 12 1468
John Smith 4 1469
I need to combine these results so it would look like this
John Smith 16 1468
This is not for all students just certain ones. I would like to do
this in the view if possible. Any help is appreciated.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...50.h
tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=904750Looks like you want to return just one of the school codes? In that case,
just group the data by student, and aggregate the measures:
SELECT StudentID, FName, LName, SUM(Credits) AS TotalCredits,
MIN(ScheelCode) AS MinSchoolCode
FROM ViewName
GROUP BY StudentID, FName, LName;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"TheCount" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_904750_a05cfa9ea57158f694c614723c
ee26e9@.dbforumz.com...
>I have created a view for reporting. I'm basically just joining a few
> tables. It is for a University so the results shows students names and
> the credits they are currently taking and the school code (There is 3
> Colleges under one ownership)
> The problem is some students attend two colleges and appear twice,
> one for each enrollment. For example
> FName LName Credits SchoolCode
> John Smith 12 1468
> John Smith 4 1469
> I need to combine these results so it would look like this
> John Smith 16 1468
> This is not for all students just certain ones. I would like to do
> this in the view if possible. Any help is appreciated.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Programming...pict262850.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=904750|||Take a look at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML

Combining two fields

Hey everyone, I am very new to SQL Reporting, I usually work with Crystal. So I think this question is very simple. I have a field and in the expression box it says

=Sum(Fields!EstRev.Value)

Now I have another field that I want to combine in the above box, no fancy calcs or anything I just want it to appear in the same area.

=Sum(Fields!OrderCount.Value)

Basically I do not want to add the two fields together arthritically I just want them numbers to appear on the bottom of each other.

I have tried an AND Operator and that didnt work. Please help, very simple questions. It has to be very simple to just combine these two in to one field on the report?

You can do something like this:

=Sum(Fields!EstRev.Value)&" "&Sum(Fields!OrderCount.Value) <- this will show something like "23 432"

or

=Sum(Fields!EstRev.Value)&Chr(13)&Chr(10)&Sum(Fields!OrderCount.Value) <- this will show something like

23

432

Maciej

|||

Maciej

I normally do this Cstr(sum(fields!EstRev.value)) - converting number to string values

Hammer

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

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

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

Combine 2 datasources in one report?

I have loaded up the newest latests, greatest Visual Studio, SQL reporting etc. I have a need to pull in data from multiple sources. Currently I have DTS packages I have to run to dump information into 1 data source to report on. Has anybody figured out a way to have multipled data sources available when in the query builder?

I am scratching my head on this one.

Your report can have 2 datasets, each with a different datasource, but you cannot combine them into a single dataset within the report.

If you need to combine the data from 2 datasources into a single dataset, you'll need to handle this in your query/stored proc via a linked server or something similar.|||Thanks Andy for your quick response. It interesting that one can pull this off in MS Access with a simple ODBC but I cant in SQL reporting. I am not sure what you mean by linked server in this case.|||Linked Servers:
http://msdn2.microsoft.com/en-us/library/aa213778(SQL.80).aspx

Distributed Query:
http://msdn2.microsoft.com/en-us/library/aa172738(sql.80).aspx

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

Column Chart Issue in MSRS 2005

Hi,
I have report created with Reporting Service 2000 SP2 designer. This
contains a Column Chart with Simple Column type. I had selected
GraySacle Pallete for my report. I have max 4 columns in my report.
When I deployed the report on MSRS 2000 SP2 Report Server it is showing
correctly. The style of the columns in legend and that appearing in
chart are correct.
When I deploy the same report on MSRS 2005 SP1 Report Server its
showing in-correct. The style of the columns in legend and that
appearing in chart are not matching. For e.g I have a column
representing "Data Unavailable" in legend it shows as "Front
Slash" on reports in the chart this DataUnavailable appears 7 times
(representing week) first time it appears as "Front Slash" 2nd time
"Back Slash" 3rd time "Straight horizontal line", 4th
"Vertical lines" ...and so on all 7 times it appears different.
Is this a bug in 2005. Its working fine in MSRS 2000 SP2
What is a solution for making it consistent on MSRS 2005?
Thanks in advance for any kind of help.
Regards,
ManojHi
Do anyone know the reason for this behaviour?
I tried creating the same report in Reporting Service 2005 SP1
designer. Still same result. GrayScale Pallete shows wrong column graph
other than those shown in legends
Any kind of help in this regards will be very usefull.
Regards,
Manoj
Manoj wrote:
> Hi,
> I have report created with Reporting Service 2000 SP2 designer. This
> contains a Column Chart with Simple Column type. I had selected
> GraySacle Pallete for my report. I have max 4 columns in my report.
> When I deployed the report on MSRS 2000 SP2 Report Server it is showing
> correctly. The style of the columns in legend and that appearing in
> chart are correct.
> When I deploy the same report on MSRS 2005 SP1 Report Server its
> showing in-correct. The style of the columns in legend and that
> appearing in chart are not matching. For e.g I have a column
> representing "Data Unavailable" in legend it shows as "Front
> Slash" on reports in the chart this DataUnavailable appears 7 times
> (representing week) first time it appears as "Front Slash" 2nd time
> "Back Slash" 3rd time "Straight horizontal line", 4th
> "Vertical lines" ...and so on all 7 times it appears different.
> Is this a bug in 2005. Its working fine in MSRS 2000 SP2
> What is a solution for making it consistent on MSRS 2005?
> Thanks in advance for any kind of help.
> Regards,
> Manoj