Adding static columns to the right side of a Matrix report

Hi all,
I'm trying to figure out how to add static columns to a matrix report
in reporting services 2005. i have a matrix where the columns can be
dynamic (1 to n columns) and in addition to the row columns on the far
left i also need to place columns on the right as well. So it would
look like this:
matrixcol1 matrixcol2 staticcol1, staticcoln...
row 1 # # label1 label_2_1
row 2 # # label2 label_2_2
I got the report to display the data by embedding a matrix in a list
but for each row the columns for the matrix do not line up as
matrixco1, 2.. is different for each row. -THanks!On Jun 7, 2:29 pm, dfate...@.gmail.com wrote:
> Hi all,
> I'm trying to figure out how to add static columns to a matrix report
> in reporting services 2005. i have a matrix where the columns can be
> dynamic (1 to n columns) and in addition to the row columns on the far
> left i also need to place columns on the right as well. So it would
> look like this:
> matrixcol1 matrixcol2 staticcol1, staticcoln...
> row 1 # # label1 label_2_1
> row 2 # # label2 label_2_2
> I got the report to display the data by embedding a matrix in a list
> but for each row the columns for the matrix do not line up as
> matrixco1, 2.. is different for each row. -THanks!
I normally handle this by placing an empty space in front of the
columns I want to appear first (on the left) in the matrix (i.e., (" "
+ ColumnNameToBePivoted)) and then have the columns I want at the end
(on the right) just be their regular names. That way, alphabetically,
the spaced columns will come first. Of course, you will want to do
this in the stored procedure/query that is sourcing the report. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

adding field descriptions

Hi.
I use a SQL file to create my tables. I would like to add field descriptions
to each field, but cannot figure the format:
CREATE TABLE t_012(
field1 nvarchar(22) default NULL description='my desc goes here',
field 2 integer default NULL
);
Could someone please tell me the correct way to do this?
TIA
JoeB
Joe,
When you have questions like that a good way to find out how some of them
are done is to run a profiler trace and then create a table in EM. If you
add a description in EM you will see the commands used to accomplish this.
Another and sometimes easier way is to use the table designer in Em to
create the table along with the extended properties that you want and then
before saving click on the 3rd (I think) toolbar button from the left and it
will let you script out what EM will do. Just be somewhat careful in that
EM does not always do it the most efficient way. For instance it will
usually make a new copy of a table when adding or altering columns instead
of just using ALTER TABLE.
Andrew J. Kelly SQL MVP
"JoeB" <joe@.kybert__***NO_SPAM*sdfsdfsd****___.com> wrote in message
news:umpmpiRhEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi.
> I use a SQL file to create my tables. I would like to add field
descriptions
> to each field, but cannot figure the format:
>
> CREATE TABLE t_012(
> field1 nvarchar(22) default NULL description='my desc goes here',
> field 2 integer default NULL
> );
>
> Could someone please tell me the correct way to do this?
>
> TIA
>
> JoeB
>
|||How do i run a profiller trace or see what the GUI is actually doing?
-- I didnt think it was posible to see the real scripting that the GUI uses!
As you can tell, im a newbie!
Joe
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23v4BpmRhEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Joe,
> When you have questions like that a good way to find out how some of them
> are done is to run a profiler trace and then create a table in EM. If you
> add a description in EM you will see the commands used to accomplish this.
> Another and sometimes easier way is to use the table designer in Em to
> create the table along with the extended properties that you want and then
> before saving click on the 3rd (I think) toolbar button from the left and
> it
> will let you script out what EM will do. Just be somewhat careful in that
> EM does not always do it the most efficient way. For instance it will
> usually make a new copy of a table when adding or altering columns instead
> of just using ALTER TABLE.
>
> --
> Andrew J. Kelly SQL MVP
>
> "JoeB" <joe@.kybert__***NO_SPAM*sdfsdfsd****___.com> wrote in message
> news:umpmpiRhEHA.3428@.TK2MSFTNGP11.phx.gbl...
> descriptions
>
|||In EM click on the menu for "Tools" and then select "Profiler". Then when
that tool opens choose "File - New - Trace" . Connect to your server and
when the trace properties dialog appears go to the "Events" tab. You might
want to make sure you have the "TSQL SQL:StmtStarting" selected and then
run the trace. Now what you do in EM will show up in there.
Andrew J. Kelly SQL MVP
"JoeB" <joe@.kybert__***NO_SPAM*sdfsdfsd****___.com> wrote in message
news:%235Df10RhEHA.2764@.TK2MSFTNGP11.phx.gbl...
> How do i run a profiller trace or see what the GUI is actually doing?
> -- I didnt think it was posible to see the real scripting that the GUI
uses![vbcol=seagreen]
>
> As you can tell, im a newbie!
>
> Joe
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23v4BpmRhEHA.3916@.TK2MSFTNGP11.phx.gbl...
them[vbcol=seagreen]
you[vbcol=seagreen]
this.[vbcol=seagreen]
then[vbcol=seagreen]
and[vbcol=seagreen]
that[vbcol=seagreen]
instead
>
|||Cheers,
Does anyone else have SQL server2000 SP3 lockup during EM 15-20 times /
day?
Jeo
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23KCcjIWhEHA.2544@.TK2MSFTNGP10.phx.gbl...
> In EM click on the menu for "Tools" and then select "Profiler". Then when
> that tool opens choose "File - New - Trace" . Connect to your server and
> when the trace properties dialog appears go to the "Events" tab. You
> might
> want to make sure you have the "TSQL SQL:StmtStarting" selected and then
> run the trace. Now what you do in EM will show up in there.
> --
> Andrew J. Kelly SQL MVP
>
> "JoeB" <joe@.kybert__***NO_SPAM*sdfsdfsd****___.com> wrote in message
> news:%235Df10RhEHA.2764@.TK2MSFTNGP11.phx.gbl...
> uses!
> them
> you
> this.
> then
> and
> that
> instead
>
|||The diagram editor in SQL Server Enterprise Manager (and in Access) stores a
columns description as an 'extended property' named MS_Description
These can also be seen usgin SQL Query Analyzer by navigating to the
columns, right clicking on it, selecting Extended properties
They can also be editted using these stored procs (quote from BOL):
a.. sp_addextendedproperty
Adds a new extended property to a database object.
b.. sp_updateextendedproperty
Updates the value of an existing extended property.
c.. sp_dropextendedproperty
Drops an existing extended property.
Good luck
Malcolm Cook - mec@.stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA
"JoeB" <joe@.kybert__***NO_SPAM*sdfsdfsd****___.com> wrote in message
news:umpmpiRhEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi.
> I use a SQL file to create my tables. I would like to add field
descriptions
> to each field, but cannot figure the format:
>
> CREATE TABLE t_012(
> field1 nvarchar(22) default NULL description='my desc goes here',
> field 2 integer default NULL
> );
>
> Could someone please tell me the correct way to do this?
>
> TIA
>
> JoeB
>

adding date timestamp to xp_sendmail procedure

I am trying to figure out how to add a time datestamp to my xp_sendmail procedure:

use master;
go

CREATE PROC pr_sendmail
AS

DECLARE @.DT DATETIME
SET @.DT=GETDATE()

BEGIN

EXEC xp_sendmail @.recipients = 'me@.work.com',
@.message = 'send email from SQL Server Stored Procedure.',
@.copy_recipients = 'me@.work.com',
@.subject = 'Job Started at ', @.DT

END

How do I get this to work? Thanks!@.subject = 'Job Started at ' + cast(getdate() as varchar)

you can also use CONVERT instead of CAST to format the date in various different formats.

Adding Column AVGs on Matrix Reports

Hi,
Does anyone know how to add an AVG of a column in a matrix report? Its
obvious how to do a subtotal but I can't figure out how to add an
aggregate AVG for all columns. I also need to have the AVG below the
Column headers not at the bottom of the report as usual (see sample
below).
Column1 Column2 Column3
-- -- -- --
AVG 1.67 1.34 2
Row1 2 3 1
Row2 3 1 1
Row3 0 0 4
Any response is much appreciated.
Thanks,
ClaudeYou could write a simple expression like
=Sum(fields!Detail.Value)/Count(fields!Detail.Value) where detail is the
field pointing to 2,3...etc. instead of using subtotal. Insert a static row
at the top and use this expression. You could use tables instead of matrix (I
find it much cleaner than matrix specially when you dont have column
groupings and can do with row groupings)
"Claude" wrote:
> Hi,
> Does anyone know how to add an AVG of a column in a matrix report? Its
> obvious how to do a subtotal but I can't figure out how to add an
> aggregate AVG for all columns. I also need to have the AVG below the
> Column headers not at the bottom of the report as usual (see sample
> below).
> Column1 Column2 Column3
> -- -- -- --
> AVG 1.67 1.34 2
> Row1 2 3 1
> Row2 3 1 1
> Row3 0 0 4
> Any response is much appreciated.
> Thanks,
> Claude
>

adding CALs

I still am unable to figure out how to add additional CALs to SQL Server
2000 and I am not able to get an answer from Microsoft without paying
the $250 to talk to support. Do I actually need to tell SQL that I
purchased more CALs or is it just a paper license'Hi,
Which version of SQL Srver you are currently using ? Is that Personal
edition?
Thanks
Hari
MCDBA
"Brian Rottman" <anonymous@.anonymous.net> wrote in message
news:eyQOr0CDEHA.628@.TK2MSFTNGP10.phx.gbl...
> I still am unable to figure out how to add additional CALs to SQL Server
> 2000 and I am not able to get an answer from Microsoft without paying
> the $250 to talk to support. Do I actually need to tell SQL that I
> purchased more CALs or is it just a paper license'|||SQL Server 2000 Standard

> Hi,
> Which version of SQL Srver you are currently using ? Is that Personal
> edition?
> Thanks
> Hari
> MCDBA
> "Brian Rottman" <anonymous@.anonymous.net> wrote in message
> news:eyQOr0CDEHA.628@.TK2MSFTNGP10.phx.gbl...
>
>
>|||In Control Panel , there should be a applet called License Manager... Run
that program, and If you have installed SQL using per seat , you will see an
option to add licenses for SQL there...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brian Rottman" <anonymous@.anonymous.net> wrote in message
news:eyQOr0CDEHA.628@.TK2MSFTNGP10.phx.gbl...
> I still am unable to figure out how to add additional CALs to SQL Server
> 2000 and I am not able to get an answer from Microsoft without paying
> the $250 to talk to support. Do I actually need to tell SQL that I
> purchased more CALs or is it just a paper license'|||The only thing I am seeing in License Manager is Windows, nothing for SQL

> In Control Panel , there should be a applet called License Manager... Run
> that program, and If you have installed SQL using per seat , you will see
an
> option to add licenses for SQL there...
>|||This means that SQL Server was not installed with a "per seat" license
option. The only way I am familiar with changing that is to uninstall and
reinstall SQL Server.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Brian Rottman" <anonymous@.anonymous.net> wrote in message
news:OlHy1UDDEHA.580@.TK2MSFTNGP11.phx.gbl...
> The only thing I am seeing in License Manager is Windows, nothing for SQL
>
Run
see an