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 results - Performance Issues

Hi,
I am creating a view in the following way
CREATE VIEW TableView
AS
SELECT OriginalTable.*
FROM OriginalTable
WHERE ChangedFlag is null
UNION ALL
SELECT TableWithChanges.*
FROM TableWithChanges
OriginalTable and TableWithChanges have identical schemas. The data is in
OriginalTable and queries are run against it. However when I want to analyze
the query output with some changes I put the changed rows in
TableWithChanges and now run the queries against TableView. New and changed
rows are in TableWithChanges. Deletions and changes in OriginalTable are
handled by setting ChangedFlag = 1.
This way my earlier queries can keep running against OriginalTable and my
simulations can run against TableView.
The queries I run involve joins between many such tables, and sometimes self
joins too.
Problem: Performance is severely hit when I run queries against the view.
Specifically I observe that SQL Server does lot of processor intensive
activity. A query that was completing in 5 secs now is running for about 30
minutes (and has not completed yet). SQL Server is consuming close to 100%
CPU all this while.
Is there a better way in which I can combine the two data sets without
affecting performance to such an extent?
Please help me out here.
Thanks,
NitinPartitioned view is what you want. Though, you must follow its strict
guideline in order to get the benefits.
http://msdn.microsoft.com/library/e...des_06_17zr.asp
-oj
"Nitin M" <nitin@.nowhere.com> wrote in message
news:e7AWpMovFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am creating a view in the following way
> CREATE VIEW TableView
> AS
> SELECT OriginalTable.*
> FROM OriginalTable
> WHERE ChangedFlag is null
> UNION ALL
> SELECT TableWithChanges.*
> FROM TableWithChanges
> OriginalTable and TableWithChanges have identical schemas. The data is in
> OriginalTable and queries are run against it. However when I want to
> analyze the query output with some changes I put the changed rows in
> TableWithChanges and now run the queries against TableView. New and
> changed rows are in TableWithChanges. Deletions and changes in
> OriginalTable are handled by setting ChangedFlag = 1.
> This way my earlier queries can keep running against OriginalTable and my
> simulations can run against TableView.
> The queries I run involve joins between many such tables, and sometimes
> self joins too.
> Problem: Performance is severely hit when I run queries against the view.
> Specifically I observe that SQL Server does lot of processor intensive
> activity. A query that was completing in 5 secs now is running for about
> 30 minutes (and has not completed yet). SQL Server is consuming close to
> 100% CPU all this while.
> Is there a better way in which I can combine the two data sets without
> affecting performance to such an extent?
> Please help me out here.
> Thanks,
> Nitin
>|||Thanks OJ,
I have a explicit where clause [WHERE ChangedFlag is null] instead of the
check constraint. Will I give some better performance if I use check
constraints instead.
Also ChangedFlag column in this case is not a primary key column.
Is there any other trick to combine data?
Thanks,
Nitin
"oj" <nospam_ojngo@.home.com> wrote in message
news:eXBArSovFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Partitioned view is what you want. Though, you must follow its strict
> guideline in order to get the benefits.
> http://msdn.microsoft.com/library/e...des_06_17zr.asp
>
> --
> -oj
>
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:e7AWpMovFHA.4032@.TK2MSFTNGP15.phx.gbl...
>|||If you don't follow the guideline, you don't have a partitioned view. Thus,
sqlserver *will* be forced to scan every single table in your view
definition. PV is the trick to combine data.
-oj
"Nitin M" <nitin@.nowhere.com> wrote in message
news:OpuTpcovFHA.1996@.TK2MSFTNGP10.phx.gbl...
> Thanks OJ,
> I have a explicit where clause [WHERE ChangedFlag is null] instead of the
> check constraint. Will I give some better performance if I use check
> constraints instead.
> Also ChangedFlag column in this case is not a primary key column.
> Is there any other trick to combine data?
> Thanks,
> Nitin
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eXBArSovFHA.2556@.TK2MSFTNGP15.phx.gbl...
>|||Why did you mimic a 1950's magnetic tape file generational system in
SQL? Are yoiu really using flags in a RDBMS, as if you were writing
assembly language code?
Go back to the basics; same schema means same entity in an RDBMS. Your
data model has split a set over two tables when you should have had
only one. My guess woild be that you need to show a history, whcih
means that you will have a (start_time, end_time) pair in the table and
will get the current status by looking at (end_time IS NULL).|||Hi Celko,
I have a system in which the original tables are being used in a zillion
places. The system does some analysis using queries. Now there is a need to
do the same analysis with changes to original data, a simulation or a "what
if the data changes" sort of analysis.
In this situation if I want to keep two 'avtars' of a row in the same table
I will to think about what to do with the queries that already exist.
And all this while I do not want to disturb any reports etc. which are
accessing the original data. I want the simulation and the existing stuff to
run simulataneously.
Do tell me if there is a better way out.
Thanks,
Nitin
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127294968.322772.46020@.g49g2000cwa.googlegroups.com...
> Why did you mimic a 1950's magnetic tape file generational system in
> SQL? Are yoiu really using flags in a RDBMS, as if you were writing
> assembly language code?
>
> Go back to the basics; same schema means same entity in an RDBMS. Your
> data model has split a set over two tables when you should have had
> only one. My guess woild be that you need to show a history, whcih
> means that you will have a (start_time, end_time) pair in the table and
> will get the current status by looking at (end_time IS NULL).
>|||"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23LDbZuqvFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Hi Celko,
> I have a system in which the original tables are being used in a zillion
> places. The system does some analysis using queries. Now there is a need
> to do the same analysis with changes to original data, a simulation or a
> "what if the data changes" sort of analysis.
> In this situation if I want to keep two 'avtars' of a row in the same
> table I will to think about what to do with the queries that already
> exist.
> And all this while I do not want to disturb any reports etc. which are
> accessing the original data. I want the simulation and the existing stuff
> to run simulataneously.
> Do tell me if there is a better way out.
> Thanks,
> Nitin
Why not do the simulation on a copy of the database?|||<Why not do the simulation on a copy of the database?>
Wont this take lot of extra time and space too?
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uQCI7%23qvFHA.2728@.TK2MSFTNGP14.phx.gbl...
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:%23LDbZuqvFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Why not do the simulation on a copy of the database?
>|||Space is cheap.
I don't understand the extra time comment.
Some compagnies even do reporting on a database copy.
Sure, the data isn't up to date as this depends on the backup frequency.
"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23yk$hIrvFHA.4020@.TK2MSFTNGP10.phx.gbl...
> <Why not do the simulation on a copy of the database?>
> Wont this take lot of extra time and space too?
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uQCI7%23qvFHA.2728@.TK2MSFTNGP14.phx.gbl...
>

Combining Pass-Through Queries into a Stored Proc.

Is it possible to combine multiple Views into a Stored Procedure? Can I reference the results from one (1) View within the stored procedure? If so, how would I go about it?
Thanks!!Refer to Books online for Using Pass-Through Queries as Tables topic.

Combining numeric fields

Hello,
I use the following line in my sql view to bring 2 numeric fields together,
as one field (I'm using this to populate a listbox in vb.net).
This all works great, but I need to have the value 0 show up as 0.0. Right
now, it shows up as 0.
CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
Any suggestions?
TIA!
amberOn Tue, 20 Sep 2005 15:30:04 -0700, amber wrote:

>Hello,
>I use the following line in my sql view to bring 2 numeric fields together,
>as one field (I'm using this to populate a listbox in vb.net).
>This all works great, but I need to have the value 0 show up as 0.0. Right
>now, it shows up as 0.
>CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
>CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
>Any suggestions?
Hi amber,
Use the STR function instead of CAST. See the documentation in Books
Online for details on usage.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If the original data type of those two columns is integer (int, bigint,
smallint or tinyint) then cast it as decimal before casting it to varchar.
Look into data types in Books Online for more details, or provide DDL and
some sample data and please explain what exactly your goal is.
ML|||Thank you!
That fixed it!
Amber

Combining fields

Hello,
Within a view I've created, I have combined 2 fields to make 1.
dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
This is for display (to populate a listbox in .NET).
The problem is, if there is nothing in the STR_LAYER field, the whole field
is blank.
Is it possible to display Stratum always, and Layer when it's available?
Thanks!
AmberUse functions ISNULL or COALESCE.
Example:
coalesce(dbo.TABLE1.STR_STRATUM + N' ', N'') +
coalesce(dbo.TABLE2.STR_LAYER, '') AS StratumLayer
AMB
"amber" wrote:

> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole fiel
d
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||SELECT dbo.TABLE1.STR_STRATUM + ISNULL( N' ' + dbo.TABLE2.STR_LAYER AS
StratumLayer, '')
Jacco Schalkwijk
SQL Server MVP
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||If you concatenate a string with a null value, it will return null.
Use ISNULL function:
dbo.TABLE1.STR_STRATUM + N' ' + ISNULL(dbo.TABLE2.STR_LAYER ISNULL(), '')
Francesco Anti
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||This worked.
Thanks!
Amber

combining data on different sevvers

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

Combining 2 databases as one

Hi,
We need to combine 2 databases as one on SQL Server 2000. One database has
wickedly many View tables and the other has more wickedly many Stored
Procedures. What would be the best way to combine them.
YCScript the objects in one of the database and use the script to create the o
bjects in the other
database. And test, test, test. Also see: http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"YC" <asppsa@.hotmail.com> wrote in message news:%23I0KtzDhGHA.1276@.TK2MSFTNGP03.phx.gbl...[v
bcol=seagreen]
> Hi,
> We need to combine 2 databases as one on SQL Server 2000. One database ha
s wickedly many View
> tables and the other has more wickedly many Stored Procedures. What would
be the best way to
> combine them.
> YC
>[/vbcol]

Combining 2 databases as one

Hi,
We need to combine 2 databases as one on SQL Server 2000. One database has
wickedly many View tables and the other has more wickedly many Stored
Procedures. What would be the best way to combine them.
YCScript the objects in one of the database and use the script to create the objects in the other
database. And test, test, test. Also see: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"YC" <asppsa@.hotmail.com> wrote in message news:%23I0KtzDhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We need to combine 2 databases as one on SQL Server 2000. One database has wickedly many View
> tables and the other has more wickedly many Stored Procedures. What would be the best way to
> combine them.
> YC
>sqlsql

combine values in view?

Is there an easy way to do this?
I need to pull sales values for each store and make one record per month.
This used to be easy, as I was provided only one sales record per month per
store, but now I've been told that SOME of the stores have a "secondary" ID
from which to pull sales numbers, and these numbers need to be added to the
number from their PRIMARY number before displaying them. I'm not sure how
to do this.
Here is some sample data (cols don't align well here - sorry):
STORES:
ID1 ID2 STORE
100 AAA A-STORE
200 B-STORE
300 CCC C-STORE
400 D-STORE
(note that B-Store and D-Store have only ONE ID, not a secondary ID)
SALES:
ID SALES MONTH YEAR
100 5.00 01 2004
AAA 5.00 01 2004
200 5.00 01 2004
300 5.00 01 2004
CCC 5.00 01 2004
400 5.00 01 2004
(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
records for each month & year so those fields need to be accounted for.)
I need a view that will show:
ID SALES MONTH YEAR
100 10.00 01 2004
200 5.00 01 2004
300 10.00 01 2004
400 5.00 01 2004
(note that IDs 100 and 300 show the combined sales of both their primary AND
secondary IDs)
Is this easily doable? If yes, how'!!
Thanks!
-RThere are some data integrity problems here, and in particular,
if the ID2 values are not unique, this is something of a mess, but
if the data is not messed up, something like this should work:
select ID, sum(SALES) as SALES, [MONTH], [YEAR]
from (
select ID, SALES, [MONTH], [YEAR]
from SALES
union all
select ST.ID1, SA.SALES, SA.[MONTH], SA.[YEAR]
from SALES AS SA join STORES AS ST
on SA.ID = ST.ID2
) S
group by ID, [MONTH], [YEAR]
If the data is messed up, this query could provide completely
wrong information. You would be better off keeping track
of the store IDs differently:
-- primary IDs only, with store attributes
CREATE TABLE STORES (
ID char(3) primary key,
StoreName varchar(30),
.. other attributes of a store
)
-- all IDs, primary and alternate, for stores, with
-- the primary storeID for each
CREATE TABLE STORE_IDS (
ID char(3) primary key,
storeID char(3) references STORES(ID)
-- put an index on storeID to support the FK
)
The foreign key on SALES would now link to this
second table instead of the first. The query would
be different, too - something like this:
select ST.ID, sum(SA.SALES) as SALES, SA.[MONTH], SA.[YEAR]
from STORES as ST
join STORE_IDS as I
on I.storeID = ST.ID
join SALES as SA
on SA.ID = I.ID
group by ST.ID, SA.[MONTH], SA.[YEAR]
Steve Kass
Drew University
r wrote:

>Is there an easy way to do this?
>I need to pull sales values for each store and make one record per month.
>This used to be easy, as I was provided only one sales record per month per
>store, but now I've been told that SOME of the stores have a "secondary" ID
>from which to pull sales numbers, and these numbers need to be added to the
>number from their PRIMARY number before displaying them. I'm not sure how
>to do this.
>Here is some sample data (cols don't align well here - sorry):
>STORES:
>ID1 ID2 STORE
>100 AAA A-STORE
>200 B-STORE
>300 CCC C-STORE
>400 D-STORE
>(note that B-Store and D-Store have only ONE ID, not a secondary ID)
>SALES:
>ID SALES MONTH YEAR
>100 5.00 01 2004
>AAA 5.00 01 2004
>200 5.00 01 2004
>300 5.00 01 2004
>CCC 5.00 01 2004
>400 5.00 01 2004
>(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
>and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
>records for each month & year so those fields need to be accounted for.)
>I need a view that will show:
>ID SALES MONTH YEAR
>100 10.00 01 2004
>200 5.00 01 2004
>300 10.00 01 2004
>400 5.00 01 2004
>(note that IDs 100 and 300 show the combined sales of both their primary AN
D
>secondary IDs)
>Is this easily doable? If yes, how'!!
>Thanks!
>-R
>
>
>sqlsql

Combine record

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

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

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

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

Quote:

Originally Posted by

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

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

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

columns order in entities

Hi friends
i've report model with entities that depend on views. my question is , currently all columns in a entity in the same order as view has them. i mean if i have a view like below

create view vname
as
select name,addess,status from mytable

when i create entity based on this i get attributes in this order
name,address,status
but i want
address,name,status

how can i change it to alphabatical order ?
i know i can change manually in model designer window but there are too many fields to sort !!
is there any better way of doing this ?
Thanks for your helphi guys
so there is no way ?|||is it something for next version ?

columns order in entities

Hi friends
i've report model with entities that depend on views. my question is , currently all columns in a entity in the same order as view has them. i mean if i have a view like below

create view vname
as
select name,addess,status from mytable

when i create entity based on this i get attributes in this order
name,address,status
but i want
address,name,status

how can i change it to alphabatical order ?
i know i can change manually in model designer window but there are too many fields to sort !!
is there any better way of doing this ?
Thanks for your helphi guys
so there is no way ?|||is it something for next version ?

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan Kelly
Have a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan KellyHave a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan KellyHave a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Column labels in a View

Please bear with me, as I do not work with SQL much. I have a view set
up that compiles rolling monthly data from a historical table. The
view currently looks like this:
Server Current Month One Month Ago Two Months Ago...
Server1 <data> <data> <data>
Is there any way to set the column label for the second column to
datename(m, getdate()) in place of "Current Month"? I would obviously
then want to apply the same logic to the remaining columns.
TIA,
Dave
Hi
You would need to drop the view and re-create it, although this is really a
UI issue!
John
"davrion@.hotmail.com" wrote:

> Please bear with me, as I do not work with SQL much. I have a view set
> up that compiles rolling monthly data from a historical table. The
> view currently looks like this:
> Server Current Month One Month Ago Two Months Ago...
> Server1 <data> <data> <data>
> Is there any way to set the column label for the second column to
> datename(m, getdate()) in place of "Current Month"? I would obviously
> then want to apply the same logic to the remaining columns.
> TIA,
> Dave
>

Column labels in a View

Please bear with me, as I do not work with SQL much. I have a view set
up that compiles rolling monthly data from a historical table. The
view currently looks like this:
Server Current Month One Month Ago Two Months Ago...
Server1 <data> <data> <data>
Is there any way to set the column label for the second column to
datename(m, getdate()) in place of "Current Month"? I would obviously
then want to apply the same logic to the remaining columns.
TIA,
DaveHi
You would need to drop the view and re-create it, although this is really a
UI issue!
John
"davrion@.hotmail.com" wrote:

> Please bear with me, as I do not work with SQL much. I have a view set
> up that compiles rolling monthly data from a historical table. The
> view currently looks like this:
> Server Current Month One Month Ago Two Months Ago...
> Server1 <data> <data> <data>
> Is there any way to set the column label for the second column to
> datename(m, getdate()) in place of "Current Month"? I would obviously
> then want to apply the same logic to the remaining columns.
> TIA,
> Dave
>

Column labels in a View

Please bear with me, as I do not work with SQL much. I have a view set
up that compiles rolling monthly data from a historical table. The
view currently looks like this:
Server Current Month One Month Ago Two Months Ago...
Server1 <data> <data> <data>
Is there any way to set the column label for the second column to
datename(m, getdate()) in place of "Current Month"? I would obviously
then want to apply the same logic to the remaining columns.
TIA,
DaveHi
You would need to drop the view and re-create it, although this is really a
UI issue!
John
"davrion@.hotmail.com" wrote:
> Please bear with me, as I do not work with SQL much. I have a view set
> up that compiles rolling monthly data from a historical table. The
> view currently looks like this:
> Server Current Month One Month Ago Two Months Ago...
> Server1 <data> <data> <data>
> Is there any way to set the column label for the second column to
> datename(m, getdate()) in place of "Current Month"? I would obviously
> then want to apply the same logic to the remaining columns.
> TIA,
> Dave
>

Column chart bug?

When I am designing a column chart, the chart looks exactly how I want
it to look in Layout view, but then changes in the Preview mode and
when viewed in Report Manager.
Specifically I want to do this:
In the chart Y axis tab, for the scale options:
Minimum .6
Maximum 1.3
Cross 1
The Layout looks correct - when Previewed the chart keeps starting at
the minimum of 0 (instead of the .6 minimum). Since my chart is to
show variance from 1 (below or above), this bug makes it useless (1 is
no longer centered in view - but is up near 1.3).
Any ideas or workarounds?
Thanks!Update: looks like if I use whole numbers, the chart behaves correctly. So -
I guess the question is, how can I use fractions to specify lower and upper
ranges? If I do go with whole numbers (multiply the data by 10) to get the
chart to look OK, can I change the displayed Y axis labels (by dividing them
by 10)?
"coldfact" wrote:
> When I am designing a column chart, the chart looks exactly how I want
> it to look in Layout view, but then changes in the Preview mode and
> when viewed in Report Manager.
> Specifically I want to do this:
> In the chart Y axis tab, for the scale options:
> Minimum .6
> Maximum 1.3
> Cross 1
> The Layout looks correct - when Previewed the chart keeps starting at
> the minimum of 0 (instead of the .6 minimum). Since my chart is to
> show variance from 1 (below or above), this bug makes it useless (1 is
> no longer centered in view - but is up near 1.3).
> Any ideas or workarounds?
> Thanks!
>|||Try this:
Minimum 0.6
Maximum 1.3
CrossAt 1.0
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"coldfact" <coldfact@.discussions.microsoft.com> wrote in message
news:E16862E2-6EBB-4422-A438-DD7EE8673F3F@.microsoft.com...
> Update: looks like if I use whole numbers, the chart behaves correctly.
So -
> I guess the question is, how can I use fractions to specify lower and
upper
> ranges? If I do go with whole numbers (multiply the data by 10) to get the
> chart to look OK, can I change the displayed Y axis labels (by dividing
them
> by 10)?
> "coldfact" wrote:
> > When I am designing a column chart, the chart looks exactly how I want
> > it to look in Layout view, but then changes in the Preview mode and
> > when viewed in Report Manager.
> >
> > Specifically I want to do this:
> > In the chart Y axis tab, for the scale options:
> > Minimum .6
> > Maximum 1.3
> > Cross 1
> >
> > The Layout looks correct - when Previewed the chart keeps starting at
> > the minimum of 0 (instead of the .6 minimum). Since my chart is to
> > show variance from 1 (below or above), this bug makes it useless (1 is
> > no longer centered in view - but is up near 1.3).
> >
> > Any ideas or workarounds?
> > Thanks!
> >|||Much thanks! It is the 1.0 (vs 1) that makes the difference...
:)
"Robert Bruckner [MSFT]" wrote:
> Try this:
> Minimum 0.6
> Maximum 1.3
> CrossAt 1.0
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "coldfact" <coldfact@.discussions.microsoft.com> wrote in message
> news:E16862E2-6EBB-4422-A438-DD7EE8673F3F@.microsoft.com...
> > Update: looks like if I use whole numbers, the chart behaves correctly.
> So -
> > I guess the question is, how can I use fractions to specify lower and
> upper
> > ranges? If I do go with whole numbers (multiply the data by 10) to get the
> > chart to look OK, can I change the displayed Y axis labels (by dividing
> them
> > by 10)?
> >
> > "coldfact" wrote:
> >
> > > When I am designing a column chart, the chart looks exactly how I want
> > > it to look in Layout view, but then changes in the Preview mode and
> > > when viewed in Report Manager.
> > >
> > > Specifically I want to do this:
> > > In the chart Y axis tab, for the scale options:
> > > Minimum .6
> > > Maximum 1.3
> > > Cross 1
> > >
> > > The Layout looks correct - when Previewed the chart keeps starting at
> > > the minimum of 0 (instead of the .6 minimum). Since my chart is to
> > > show variance from 1 (below or above), this bug makes it useless (1 is
> > > no longer centered in view - but is up near 1.3).
> > >
> > > Any ideas or workarounds?
> > > Thanks!
> > >
>
>|||Explanation: If at least one value of the axis settings is an integer, we
use a mode of the chart control to generate "nice" integer labels and not
default float labels like 0.05, 1.05, 2.05, etc. (which are determined based
on the minimum and maximum data point values of the chart).
If you really want float labels, make sure to either not specify values for
certain axis settings or specify them as float values rather than integer
values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"coldfact" <coldfact@.discussions.microsoft.com> wrote in message
news:A3D8D209-6C1E-4930-B948-202466FDF30D@.microsoft.com...
> Much thanks! It is the 1.0 (vs 1) that makes the difference...
> :)
> "Robert Bruckner [MSFT]" wrote:
> > Try this:
> > Minimum 0.6
> > Maximum 1.3
> > CrossAt 1.0
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "coldfact" <coldfact@.discussions.microsoft.com> wrote in message
> > news:E16862E2-6EBB-4422-A438-DD7EE8673F3F@.microsoft.com...
> > > Update: looks like if I use whole numbers, the chart behaves
correctly.
> > So -
> > > I guess the question is, how can I use fractions to specify lower and
> > upper
> > > ranges? If I do go with whole numbers (multiply the data by 10) to get
the
> > > chart to look OK, can I change the displayed Y axis labels (by
dividing
> > them
> > > by 10)?
> > >
> > > "coldfact" wrote:
> > >
> > > > When I am designing a column chart, the chart looks exactly how I
want
> > > > it to look in Layout view, but then changes in the Preview mode and
> > > > when viewed in Report Manager.
> > > >
> > > > Specifically I want to do this:
> > > > In the chart Y axis tab, for the scale options:
> > > > Minimum .6
> > > > Maximum 1.3
> > > > Cross 1
> > > >
> > > > The Layout looks correct - when Previewed the chart keeps starting
at
> > > > the minimum of 0 (instead of the .6 minimum). Since my chart is to
> > > > show variance from 1 (below or above), this bug makes it useless (1
is
> > > > no longer centered in view - but is up near 1.3).
> > > >
> > > > Any ideas or workarounds?
> > > > Thanks!
> > > >
> >
> >
> >