Adjusting Report width automatically

Hi All,

We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.

Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.

Any help is highly appreciated.

Regards, kart

Sizing is one of the few aspects of RS that cannot be controlled using expressions. Column widths are fixed and only row height supports auto sizing.

Paging is one of the last aspects of the rendering to be determined and it's also specific (and petentially different) to each rendering format. So HTML, Excel and PDF will all treat paging their own way. Hence I don't the possibility of there being an object that exposes a "CurrentPage" property in order to control it's properties at runtime. Maybe in future version MS will exposes some events raised by the renderers that you could hook into, but to my knowledge that's not available right now.

Hence the only way I see of achieveing what you suggest is to write your own rendering extension - which is not trivial - that handles the sizing of each page.

|||

You can try to change all the columns width to zero. (its not a good solution for export to excel but only to HTML)

Adjusting Report width automatically

Hi All,

We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.

Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.

Any help is highly appreciated.

Regards, kart

Sizing is one of the few aspects of RS that cannot be controlled using expressions. Column widths are fixed and only row height supports auto sizing.

Paging is one of the last aspects of the rendering to be determined and it's also specific (and petentially different) to each rendering format. So HTML, Excel and PDF will all treat paging their own way. Hence I don't the possibility of there being an object that exposes a "CurrentPage" property in order to control it's properties at runtime. Maybe in future version MS will exposes some events raised by the renderers that you could hook into, but to my knowledge that's not available right now.

Hence the only way I see of achieveing what you suggest is to write your own rendering extension - which is not trivial - that handles the sizing of each page.

|||

You can try to change all the columns width to zero. (its not a good solution for export to excel but only to HTML)

Adjusting column width in queried dataset

I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.

Thanks in advance.

Mark.

Did you try rtrim(ltrim(columnname) in the query?

cheers,

Andrew

|||

Thanks Andrew,

rtrim(columnname) did it.

Cheers,

Mark.

Addition of a number to an INT column

Hi,
I have several INT columns in a table that I need to update.
For example, in column 'aa' I need to add 2 to all of the values in that column.
I'm using Query Analyzer - what update statement should I write?Hmm - no answers - should I be in a different forum for this question?

I guess I could do:

UPDATE table_name SET aa = 16 WHERE aa = 14
UPDATE table_name SET aa = 15 WHERE aa = 13
UPDATE table_name SET aa = 14 WHERE aa = 12

and keep going like this until I have all the values updated.

Note that I've done it from highest number first, otherwise all of the data would get adjusted to the two highest numbers.

I was looking for a more elegant solution If anyone can think of one as I have several columns to update all with slightly different increases.|||UPDATE table_name SET aa = aa + 2

:D|||Of course - thank you - how could I miss it??

Woods for the trees and all...

adding two view columns together

I have created a new view where I joined two other views I want to use in
reporting services. I want to add two columns one from each view. The join
allows all records from both views. This creates a lot of null values in one
of the columns I want to add and therefore I get a null value as the answer
instead of the desired result of adding the two columns. Is there a way to
put zeros in place of the nulls within the select statement so that all
records will add ?
Ex: Col1 + Col2 = Col3
2 3 5
null 2 null (desired answer is 2)
I have been able to do this by using a temporary table but that makes things
more complicated.
Thank you for your help.Use the SQL statement IsNull. From books online:
USE AdventureWorks;
GO
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max
Quantity'
FROM Sales.SpecialOffer;
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JD" <jim@.microsoft.com> wrote in message
news:emMfrtvKIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have created a new view where I joined two other views I want to use in
>reporting services. I want to add two columns one from each view. The join
>allows all records from both views. This creates a lot of null values in
>one of the columns I want to add and therefore I get a null value as the
>answer instead of the desired result of adding the two columns. Is there a
>way to put zeros in place of the nulls within the select statement so that
>all records will add ?
> Ex: Col1 + Col2 = Col3
> 2 3 5
> null 2 null (desired answer is 2)
> I have been able to do this by using a temporary table but that makes
> things more complicated.
> Thank you for your help.
>|||You can also use in reporting services in your Col3 the following expression
=cint(Fields!Col1) + cint(Fields!Col2)
and of course, you can use cint function in your Col1 and Col2 columns.
Hope this helps,
Mónica
"JD" <jim@.microsoft.com> escribió en el mensaje
news:emMfrtvKIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have created a new view where I joined two other views I want to use in
>reporting services. I want to add two columns one from each view. The join
>allows all records from both views. This creates a lot of null values in
>one of the columns I want to add and therefore I get a null value as the
>answer instead of the desired result of adding the two columns. Is there a
>way to put zeros in place of the nulls within the select statement so that
>all records will add ?
> Ex: Col1 + Col2 = Col3
> 2 3 5
> null 2 null (desired answer is 2)
> I have been able to do this by using a temporary table but that makes
> things more complicated.
> Thank you for your help.
>

Adding totals to matrix report columns

Hi,

I'm creating a martix report that must have overall averages at the end of each row and column. I've added a list with a textbox to cater for the row totals but this doesn't work for the columns (unless there's some way of displaying the list horizontally...?) Adding a new item within the matrix doesn't work as it won't accept aggregate functions. There must be a way of doing this quite easily but I can't figure it out.

Any ideas or suggestions would be much appreciated.

Thanks,

Aidan

Turns out the solution is very simple - all you have to do is right click on the group header cell and select 'subtotal' from the pop-up menu. You can change the properties of the subtotal cell by clicking on the green triangle in the corner of the cell when viewing the properties window. It made more sense when I realised that the pivot cell must have an aggregate value - I was aggregating in my stored procedure so it took a while to figure it out...

Hope this helps someone at some point.

Aidan

|||

Thanks for pointing out the "SubTotal" setting for the column totals. However my question is regarding the Row Totals.

My report format is roughly like so

Category1 Category2 Category3 ... CTotal

Day1 10 20 30 60
Day2 11 22 33 66
Day3

...

RTotal 21 42 63 X

Basically I'm querying for rows by Day and have a GROUP BY for Category for certain type of records and am displaying the COUNT() on each day (e.g. 10, 20, 30)

So theCTotal is easily accomplished using the "SubTotal" setting. The problem is I cannot get RTotal to work. I at a point where I'm thinking of doing theRTotal in my query itself using a temp table for the original data, then doing a SUM of all Category COUNTS vertically and then doing a UNION to give me the bottom row. Needless to say, this would so lame of me I will not able to show my face to anyone.

So if you can set me straight on how you managed to do the Row Totals using a List Region, I would sure appreciate it. For the life of me, I cannot get it to work.

Best.

|||

You should be able to use the same subtotal function by right-clicking the the cell with your (Day1, Day2, Day3) Date Value (usually the second cell from the top in the leftmost column).

adding text to the column data in a query result

If I had a table with 3 columns in it, named "ID", "TITLE" and "CLASS_ID" and, in a query result, I wanted to add a fourth column named "URL" that would be the result of concatenating a file name and the value in "ID", how would I do that?

so a table that looked like:
ID TITLE CLASS_ID
1 "Hello" 137
3 "Goodbye" 587
19 "Whatever" 1028

could return a result set that looked like:
ID TITLE CLASS_ID URL

1 "Hello" 137 "hardcodedfilename.aspx?id=1"

3 "Goodbye" 587 "hardcodedfilename.aspx?id=3"

19 "Whatever" 1028 "hardcodedfilename.aspx?id=19"

I have looked through my SQL book, and scanned the usual help files and google search options, but I haven't seen an example of this. Can it be done, and if so, how?

Thanks in advance for your help.
roger

There are a few approaches, two different ways are shown below.

Chris

DECLARE @.URLTemplate VARCHAR(100)

SET @.URLTemplate = '"hardcodedfilename.aspx?id=**"'

SELECT ID,

TITLE,

CLASS_ID,

REPLACE(@.URLTemplate, '**', CAST(ID AS VARCHAR(10))) AS URL

FROM ...

--or

DECLARE @.prefix VARCHAR(100)

SET @.prefix = '"hardcodedfilename.aspx?id='

SELECT ID,

TITLE,

CLASS_ID,

@.prefix + CAST(ID AS VARCHAR(10)) + '"' AS URL

FROM ...

|||Thank you for your quick response!
I inserted the code you suggested into my existing, recursive function. The original function (which does work as it is) is:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN

SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

now, it looks like:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN XML

DECLARE @.prefix VARCHAR(100)
SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + " " as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

But, I get an error:
Msg 156, Level 15, State 1, Procedure fn_WPMTREE, Line 18
Incorrect syntax near the keyword 'FOR'.

Any suggestions? And thanks again for your help.|||

I've made a couple of corrections, see the example below.

I wasn't sure whether you needed the trailing space in the following:

CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

If not, then simply replace the above with the following:

CAST(ID AS VARCHAR(10)) as "@.url",

Chris

CREATE FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)

RETURNS XML

WITH RETURNS NULL ON NULL INPUT

BEGIN

DECLARE @.prefix VARCHAR(100)

SET @.prefix = 'rightframe.aspx?s='

RETURN (

SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

CASE WHEN PARENT_ID=@.SceneID

THEN dbo.fn_WPMTREE(id)

END

FROM dbo.SCENE WHERE PARENT_ID=@.SceneID

FOR XML PATH('Scene'), TYPE

)

END

|||That works beautifully!

I can't thank you enough.

Now, all I have to do is get an INNER JOIN working in this, and I am in good shape!

Again, Thank you.|||

HI Chris,

I have a similar question. I have a query that I use for a letter that I create. I'm attaching the query below. I want to add the text "CRM" next to the output result for "AgentDesc". So if the output of "AgentDesc" is "Director"; then I want it to display as CRM Director. Am sure this is easy enough for you..:

Thanks

SELECT dbo.tblOffer.*, CRM_SQL_ADMIN.DtFormat(dbo.tblOffer.OfferDt, 'mm dd, yyyy') AS OfferDate, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.LName)) AS AnalName, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.LName)) AS AgentName, dbo.l_tblAgent.AgentDesc AS AgentDesc,
CRM_SQL_ADMIN.vwOfferAgent_Names.DCAName AS DCAName, CRM_SQL_ADMIN.vwOfferAgent_Names.MCAName AS MCAName,
CRM_SQL_ADMIN.vwOfferAgent_Names.DIRName AS DIRName, CRM_SQL_ADMIN.vwOfferAgent_Names.RMName AS RMName,
CRM_SQL_ADMIN.vwOfferAgent_Names.SReps AS SReps,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'PayTerms' AND [OPTION] = [tblOffer].[PayTerms]) AS PayTerms_Desc, dbo.l_tblCust.CustName AS CustName,
dbo.l_tblCust.StAddress AS StAddress, dbo.l_tblCust.City AS City, dbo.l_tblCust.State AS State, dbo.l_tblCust.ZipCode AS ZipCode,
dbo.l_tblCust.GPO AS GPO, dbo.l_tblCust.IsTargetCust AS IsTargetCust,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgBen' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgBen]) AS ProgBen_Desc,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgCrit' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgCrit]) AS ProgCrit_Desc, dbo.tblOffer.SpecProgType AS SpecProg,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt), 'mm dd, yyyy') AS EndOfQtr,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.WeekDayAdd(- 4, CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt)), 'mm dd, yyyy') AS EndOfQtrLess3d,
OSS.Tot_Qty AS Tot_Qty, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Purch) AS Tot_Purch, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Savings)
AS Tot_Savings
FROM dbo.l_tblCust INNER JOIN
dbo.tblOffer ON dbo.l_tblCust.CustNum = dbo.tblOffer.CustNum LEFT OUTER JOIN
dbo.l_tblBusAnalyst ON dbo.tblOffer.BusAnalystID = dbo.l_tblBusAnalyst.EmpNum LEFT OUTER JOIN
CRM_SQL_ADMIN.vwOfferAgent_Names ON dbo.tblOffer.OfferID = CRM_SQL_ADMIN.vwOfferAgent_Names.OfferID LEFT OUTER JOIN
dbo.l_tblAgent ON dbo.tblOffer.AgentID = dbo.l_tblAgent.AgentID INNER JOIN
CRM_SQL_ADMIN.vwOfferSums_Simple OSS ON dbo.tblOffer.OfferID = OSS.OfferID|||

Change:

dbo.l_tblAgent.AgentDesc AS AgentDesc,

to:

( 'CRM ' + dbo.l_tblAgent.AgentDesc ) AS AgentDesc,

|||Gr8...That works...Thanks a lot Arnie for your help...

adding text to the column data in a query result

If I had a table with 3 columns in it, named "ID", "TITLE" and "CLASS_ID" and, in a query result, I wanted to add a fourth column named "URL" that would be the result of concatenating a file name and the value in "ID", how would I do that?

so a table that looked like:
ID TITLE CLASS_ID
1 "Hello" 137
3 "Goodbye" 587
19 "Whatever" 1028

could return a result set that looked like:
ID TITLE CLASS_ID URL
1 "Hello" 137 "hardcodedfilename.aspx?id=1"
3 "Goodbye" 587 "hardcodedfilename.aspx?id=3"
19 "Whatever" 1028 "hardcodedfilename.aspx?id=19"

I have looked through my SQL book, and scanned the usual help files and google search options, but I haven't seen an example of this. Can it be done, and if so, how?

Thanks in advance for your help.
roger

There are a few approaches, two different ways are shown below.

Chris

DECLARE @.URLTemplate VARCHAR(100)

SET @.URLTemplate = '"hardcodedfilename.aspx?id=**"'

SELECT ID,

TITLE,

CLASS_ID,

REPLACE(@.URLTemplate, '**', CAST(ID AS VARCHAR(10))) AS URL

FROM ...

--or

DECLARE @.prefix VARCHAR(100)

SET @.prefix = '"hardcodedfilename.aspx?id='

SELECT ID,

TITLE,

CLASS_ID,

@.prefix + CAST(ID AS VARCHAR(10)) + '"' AS URL

FROM ...

|||Thank you for your quick response!
I inserted the code you suggested into my existing, recursive function. The original function (which does work as it is) is:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN

SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

now, it looks like:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN XML

DECLARE @.prefix VARCHAR(100)
SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + " " as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

But, I get an error:
Msg 156, Level 15, State 1, Procedure fn_WPMTREE, Line 18
Incorrect syntax near the keyword 'FOR'.

Any suggestions? And thanks again for your help.

|||

I've made a couple of corrections, see the example below.

I wasn't sure whether you needed the trailing space in the following:

CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

If not, then simply replace the above with the following:

CAST(ID AS VARCHAR(10)) as "@.url",

Chris

CREATE FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)

RETURNS XML

WITH RETURNS NULL ON NULL INPUT

BEGIN

DECLARE @.prefix VARCHAR(100)

SET @.prefix = 'rightframe.aspx?s='

RETURN (

SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

CASE WHEN PARENT_ID=@.SceneID

THEN dbo.fn_WPMTREE(id)

END

FROM dbo.SCENE WHERE PARENT_ID=@.SceneID

FOR XML PATH('Scene'), TYPE

)

END

|||That works beautifully!

I can't thank you enough.

Now, all I have to do is get an INNER JOIN working in this, and I am in good shape!

Again, Thank you.
|||

HI Chris,

I have a similar question. I have a query that I use for a letter that I create. I'm attaching the query below. I want to add the text "CRM" next to the output result for "AgentDesc". So if the output of "AgentDesc" is "Director"; then I want it to display as CRM Director. Am sure this is easy enough for you..:

Thanks

SELECT dbo.tblOffer.*, CRM_SQL_ADMIN.DtFormat(dbo.tblOffer.OfferDt, 'mm dd, yyyy') AS OfferDate, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.LName)) AS AnalName, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.LName)) AS AgentName, dbo.l_tblAgent.AgentDesc AS AgentDesc,
CRM_SQL_ADMIN.vwOfferAgent_Names.DCAName AS DCAName, CRM_SQL_ADMIN.vwOfferAgent_Names.MCAName AS MCAName,
CRM_SQL_ADMIN.vwOfferAgent_Names.DIRName AS DIRName, CRM_SQL_ADMIN.vwOfferAgent_Names.RMName AS RMName,
CRM_SQL_ADMIN.vwOfferAgent_Names.SReps AS SReps,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'PayTerms' AND [OPTION] = [tblOffer].[PayTerms]) AS PayTerms_Desc, dbo.l_tblCust.CustName AS CustName,
dbo.l_tblCust.StAddress AS StAddress, dbo.l_tblCust.City AS City, dbo.l_tblCust.State AS State, dbo.l_tblCust.ZipCode AS ZipCode,
dbo.l_tblCust.GPO AS GPO, dbo.l_tblCust.IsTargetCust AS IsTargetCust,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgBen' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgBen]) AS ProgBen_Desc,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgCrit' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgCrit]) AS ProgCrit_Desc, dbo.tblOffer.SpecProgType AS SpecProg,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt), 'mm dd, yyyy') AS EndOfQtr,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.WeekDayAdd(- 4, CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt)), 'mm dd, yyyy') AS EndOfQtrLess3d,
OSS.Tot_Qty AS Tot_Qty, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Purch) AS Tot_Purch, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Savings)
AS Tot_Savings
FROM dbo.l_tblCust INNER JOIN
dbo.tblOffer ON dbo.l_tblCust.CustNum = dbo.tblOffer.CustNum LEFT OUTER JOIN
dbo.l_tblBusAnalyst ON dbo.tblOffer.BusAnalystID = dbo.l_tblBusAnalyst.EmpNum LEFT OUTER JOIN
CRM_SQL_ADMIN.vwOfferAgent_Names ON dbo.tblOffer.OfferID = CRM_SQL_ADMIN.vwOfferAgent_Names.OfferID LEFT OUTER JOIN
dbo.l_tblAgent ON dbo.tblOffer.AgentID = dbo.l_tblAgent.AgentID INNER JOIN
CRM_SQL_ADMIN.vwOfferSums_Simple OSS ON dbo.tblOffer.OfferID = OSS.OfferID|||

Change:

dbo.l_tblAgent.AgentDesc AS AgentDesc,

to:

( 'CRM ' + dbo.l_tblAgent.AgentDesc ) AS AgentDesc,

|||Gr8...That works...Thanks a lot Arnie for your help...sql

Adding Table Columns

Any help with this will be appreciated:
I have a simple table control in a report that has values in cells, some of
the cells contain values based on values of fields in the dataset i.e.
IIF(Field1 < Field2, Field1, Field2). How would I go about adding together
the actual results of cells in a grouping rather than also specifying the IIF
logic in the group for the sum textbox? The results are not the same compared
to if you had to add the results of the cells. See the table below of what is
happening:
Field1 |Field2 |Cell Value = IIF(Field1 < Field2, Field1, Field2)
Detail |1000 |2000 |1000
Detail |2000 |1500 |1500
Group |
Footer| 3000 |3500 |3000 (I want this toreflect 2500)
Ideally I would use the ReportItems collection but you can only use that in
the page header or footer. Am I doing something stupid or missing something?
Hope this makes sense!Hey just do a sum (Field!txt.value, "Group1") which will sum that group and
need not use cell value.
Amarnath
"WvanWyk" wrote:
> Any help with this will be appreciated:
> I have a simple table control in a report that has values in cells, some of
> the cells contain values based on values of fields in the dataset i.e.
> IIF(Field1 < Field2, Field1, Field2). How would I go about adding together
> the actual results of cells in a grouping rather than also specifying the IIF
> logic in the group for the sum textbox? The results are not the same compared
> to if you had to add the results of the cells. See the table below of what is
> happening:
> Field1 |Field2 |Cell Value = IIF(Field1 < Field2, Field1, Field2)
> Detail |1000 |2000 |1000
> Detail |2000 |1500 |1500
> Group |
> Footer| 3000 |3500 |3000 (I want this toreflect 2500)
> Ideally I would use the ReportItems collection but you can only use that in
> the page header or footer. Am I doing something stupid or missing something?
> Hope this makes sense!sql

Adding subreport values

I have a _very_ simple table with just one row and three columns.
The first column of the row contains a subreport that retrieves a single
value.
The second column of the row contains a subreport that retrieves a single
value.
...and heres where I lose it... :-)
The third column should contain the sum of column1 and column2. Period.
Please help. Thanks :-)
jdjespersenIf all you are doing is returning one value from the sub-report then why
use the sub-report at all in the first place?
Secondly, you can't add two sub-reports together. Just because your
sub-reports only return one value does not mean that the main report will
see it as such. If you plugged a sub-report in to that area that returned
40 rows of data and then tried to add those together with another
sub-reports output what would you expect to see?
So in essence you can't do what you are trying to do since you can't
reference the subreport the way you are trying.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Reply-To: "Jeppe Jespersen" <jdj@.jdj.dk>
> From: "Jeppe Jespersen" <jdj@.jdj.dk>
> Subject: Adding subreport values
> Date: Fri, 26 Oct 2007 13:41:07 +0200
> Lines: 17
> Message-ID: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
> MIME-Version: 1.0
> Content-Type: text/plain;
> format=flowed;
> charset="iso-8859-1";
> reply-type=original
> Content-Transfer-Encoding: 7bit
> I have a _very_ simple table with just one row and three columns.
> The first column of the row contains a subreport that retrieves a single
> value.
> The second column of the row contains a subreport that retrieves a single
> value.
> ...and heres where I lose it... :-)
> The third column should contain the sum of column1 and column2. Period.
> Please help. Thanks :-)
> jdjespersen
>
>|||Hi Chris, and thanks for replying...
> If all you are doing is returning one value from the sub-report then why
> use the sub-report at all in the first place?
In short, 'cause i suck. :-( I'll try to explain.
Data is in an Analysis Services database, and MDX is not my strongpoint.
I was hoping that by splitting my queries into seperate datasources, I could
get away with much simpler queries. But, not being able to use data from
two datasets within a single table control, i figured i could do it with
subreports.
Imagine a desired report table layout like this. Not the most complex, i
admit:
Company Last Years Sales Current Sales
Total
Adv.Works 10000 4000
14000
Northwind 3200 2000
5200
Designing the above query may not be rocket science, but as far as MDX goes,
i'm more of a soapbox-car scientist. And not even a good one... :-/
FYI, I do have a time dimension on my datasource.
Any help greatly appreciated.
Jeppe Jespersen
Denmark
> Secondly, you can't add two sub-reports together. Just because your
> sub-reports only return one value does not mean that the main report will
> see it as such. If you plugged a sub-report in to that area that returned
> 40 rows of data and then tried to add those together with another
> sub-reports output what would you expect to see?
> So in essence you can't do what you are trying to do since you can't
> reference the subreport the way you are trying.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
>> Reply-To: "Jeppe Jespersen" <jdj@.jdj.dk>
>> From: "Jeppe Jespersen" <jdj@.jdj.dk>
>> Subject: Adding subreport values
>> Date: Fri, 26 Oct 2007 13:41:07 +0200
>> Lines: 17
>> Message-ID: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
>> MIME-Version: 1.0
>> Content-Type: text/plain;
>> format=flowed;
>> charset="iso-8859-1";
>> reply-type=original
>> Content-Transfer-Encoding: 7bit
>> I have a _very_ simple table with just one row and three columns.
>> The first column of the row contains a subreport that retrieves a single
>> value.
>> The second column of the row contains a subreport that retrieves a single
>> value.
>> ...and heres where I lose it... :-)
>> The third column should contain the sum of column1 and column2. Period.
>> Please help. Thanks :-)
>> jdjespersen
>>
>|||Trust me Analysis Services is something I try to stay away from so I feel
your pain. When it comes to MDX I have almost no idea. Try the analysis
services newsgroup and see if they can give you a hand on creating a query
that will pull back the data you need in one dataset. That way you can
avoid the subreports altogether.
Good luck. You'll need it writing those queries ;)
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Jeppe Jespersen" <jdj@.jdj.dk>
> References: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
<FQVdkw$FIHA.360@.TK2MSFTNGHUB02.phx.gbl>
> Subject: Re: Adding subreport values
> Date: Fri, 26 Oct 2007 21:31:32 +0200
> Lines: 87
>
> Hi Chris, and thanks for replying...
> > If all you are doing is returning one value from the sub-report then why
> > use the sub-report at all in the first place?
> In short, 'cause i suck. :-( I'll try to explain.
> Data is in an Analysis Services database, and MDX is not my strongpoint.
> I was hoping that by splitting my queries into seperate datasources, I
could
> get away with much simpler queries. But, not being able to use data from
> two datasets within a single table control, i figured i could do it with
> subreports.
> Imagine a desired report table layout like this. Not the most complex, i
> admit:
> Company Last Years Sales Current Sales
> Total
> Adv.Works 10000 4000
> 14000
> Northwind 3200 2000
> 5200
> Designing the above query may not be rocket science, but as far as MDX
goes,
> i'm more of a soapbox-car scientist. And not even a good one... :-/
> FYI, I do have a time dimension on my datasource.
> Any help greatly appreciated.
> Jeppe Jespersen
> Denmark
>
>
> >
> > Secondly, you can't add two sub-reports together. Just because your
> > sub-reports only return one value does not mean that the main report
will
> > see it as such. If you plugged a sub-report in to that area that
returned
> > 40 rows of data and then tried to add those together with another
> > sub-reports output what would you expect to see?
> >
> > So in essence you can't do what you are trying to do since you can't
> > reference the subreport the way you are trying.
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > --
> >> Reply-To: "Jeppe Jespersen" <jdj@.jdj.dk>
> >> From: "Jeppe Jespersen" <jdj@.jdj.dk>
> >> Subject: Adding subreport values
> >> Date: Fri, 26 Oct 2007 13:41:07 +0200
> >> Lines: 17
> >> Message-ID: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
> >> MIME-Version: 1.0
> >> Content-Type: text/plain;
> >> format=flowed;
> >> charset="iso-8859-1";
> >> reply-type=original
> >> Content-Transfer-Encoding: 7bit
> >>
> >> I have a _very_ simple table with just one row and three columns.
> >>
> >> The first column of the row contains a subreport that retrieves a
single
> >> value.
> >>
> >> The second column of the row contains a subreport that retrieves a
single
> >> value.
> >>
> >> ...and heres where I lose it... :-)
> >>
> >> The third column should contain the sum of column1 and column2. Period.
> >>
> >> Please help. Thanks :-)
> >>
> >> jdjespersen
> >>
> >>
> >>
> >
>
>|||> Trust me Analysis Services is something I try to stay away from so I feel
> your pain. When it comes to MDX I have almost no idea. Try the analysis
> services newsgroup and see if they can give you a hand on creating a query
> that will pull back the data you need in one dataset. That way you can
> avoid the subreports altogether.
> Good luck. You'll need it writing those queries ;)
Eeeek. And this is one of the simpler queries I'll need.
Thanks for trying to help.
Jeppe Jespersen
Denmark

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 static columns to a matrix report?

So I'm making a matrix report. It's driving me nuts, frankly. Here's what I
want to do.
__________|Item Code|Description | Column Group -->
Row Group| | |
---
| | |
|
|
\/
I can get it all working, except for the "Description" column. What I have
now is
__________|Item Code| Column Group -->
Row Group| |
---
| |
|
|
\/
Which works fine. I cannot, for the life of me, add another "static" column
for the Description (that does not get repeated with the column group). I
hope this is clear enough for someone to offer me a suggestion, and I hope
posting doesnt mess up the linbreaks... Thanks in advance!
Peter L.Try this ..
Right click on filed below column group. choose add row.
It will add new column on left. Then delete the cell below the column field.
which will delete added row below but coloum on left will remain as it is.
"plandry@.newsgroups.nospam" wrote:
> So I'm making a matrix report. It's driving me nuts, frankly. Here's what I
> want to do.
> __________|Item Code|Description | Column Group -->
> Row Group| | |
> ---
> | | |
> |
> |
> \/
> I can get it all working, except for the "Description" column. What I have
> now is
> __________|Item Code| Column Group -->
> Row Group| |
> ---
> | |
> |
> |
> \/
> Which works fine. I cannot, for the life of me, add another "static" column
> for the Description (that does not get repeated with the column group). I
> hope this is clear enough for someone to offer me a suggestion, and I hope
> posting doesnt mess up the linbreaks... Thanks in advance!
> Peter L.|||That only adds another row below... I think that's how I got the first Item
Code column. It just won't let me add another column, no matter how many rows
I add...
"Vinay" wrote:
> Try this ..
> Right click on filed below column group. choose add row.
> It will add new column on left. Then delete the cell below the column field.
> which will delete added row below but coloum on left will remain as it is.
>
> "plandry@.newsgroups.nospam" wrote:
> > So I'm making a matrix report. It's driving me nuts, frankly. Here's what I
> > want to do.
> >
> > __________|Item Code|Description | Column Group -->
> > Row Group| | |
> > ---
> > | | |
> >
> > |
> > |
> > \/
> >
> > I can get it all working, except for the "Description" column. What I have
> > now is
> >
> > __________|Item Code| Column Group -->
> > Row Group| |
> > ---
> > | |
> >
> > |
> > |
> > \/
> >
> > Which works fine. I cannot, for the life of me, add another "static" column
> > for the Description (that does not get repeated with the column group). I
> > hope this is clear enough for someone to offer me a suggestion, and I hope
> > posting doesnt mess up the linbreaks... Thanks in advance!
> >
> > Peter L.

Adding Parameters to the ORDER BY Clause

What I'm wanting to do is create a report which allows you to sort by a
few different columns, and also choose if you want it sorted ASC or
DESC.
Basically, I'll have two parameters...
1) OrderBy (a number of different columns)
2) OrderDirection (ASC or DESC)
and I'll want to do a
SELECT -
FROM -
WHERE -
ORDER BY @.OrderBy @.OrderDirection
Obviously it doesn't work like this, or I wouldn't be asking for help!
I know you can use things like "Parameters!OrderBy.Value" but I was
unable to get this to work. Would somebody mind helping out?
Thanks!
-ScottI have always done this from within the RDL file during creation. It is in
one of the property dialog boxes, you may have to choose and advanced button
somewhere...It will be within the group header properties I think...
--
--Eric Cathell, MCSA
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||SELECT dbo.DatabaseName.*
FROM dbo.DatabaseName
WHERE (FieldName LIKE @.FieldName)
ORDER BY FieldName
Note: @.FieldName is "assigned" from the Report...Report Parameters menu bar
--
Message posted via http://www.sqlmonster.com|||I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||When using filters on a table/matrix group there's a ASC or DESC. But, can
you make that an expression? When I select it, there's no option to make it
an expression.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eeNwO3pEFHA.1188@.tk2msftngp13.phx.gbl...
> I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
> hope this helps
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.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
> <ScottWMcCarter@.gmail.com> wrote in message
> news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> > What I'm wanting to do is create a report which allows you to sort by a
> > few different columns, and also choose if you want it sorted ASC or
> > DESC.
> >
> > Basically, I'll have two parameters...
> >
> > 1) OrderBy (a number of different columns)
> > 2) OrderDirection (ASC or DESC)
> >
> > and I'll want to do a
> > SELECT -
> > FROM -
> > WHERE -
> > ORDER BY @.OrderBy @.OrderDirection
> >
> > Obviously it doesn't work like this, or I wouldn't be asking for help!
> > I know you can use things like "Parameters!OrderBy.Value" but I was
> > unable to get this to work. Would somebody mind helping out?
> >
> > Thanks!
> >
> > -Scott
> >
>|||I'm not sure if my original question has been answered - In the SQL
query, how am I going to add my parameters so that you can dynamically
change if it is being sorted by ASC or DESC? Thanks for all the help
so far, you guys are great!|||You can certainly do that in the query by using a dynamic query string
="SELECT ... " & IIF(Fields!Order.Value="Asc", "ASC", "DESC"). However, is
there any reason why you feel you need to do it in the query and not in the
report engine (post query)?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<ScottWMcCarter@.gmail.com> wrote in message
news:1108412814.884740.156270@.g14g2000cwa.googlegroups.com...
> I'm not sure if my original question has been answered - In the SQL
> query, how am I going to add my parameters so that you can dynamically
> change if it is being sorted by ASC or DESC? Thanks for all the help
> so far, you guys are great!
>|||No, I just don't know how to do it in the report engine!! That is what
I'm looking for. Thanks!

Adding new columns with check constraints using same batch

I'm using a stored procedure to add fields to an existing table.

These fields must have check constraints and I need to use one T-SQL batch.

In Sql2000 Ok. In Sql2005, if table exists, I get error "invalid column xxxxx" in Add Constraint statement before new column creation.

the code is

Declare @.Setup bit

Set @.Setup = 1

if @.Setup = 1 Begin

--Alter Table MyTable Add MyField Numeric(1, 0) Not Null Default 3

Exec mySp_Add_Column 'MyTable', 'MyField', 'Numeric (1, 0) Not Null', '3'

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0

Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)

End Else Begin

-- drop column

End

GO

If MyTable does not exist and, naturally, I add it before of check constraints (using another Sp which add tables) ok.

If I add FK to new fields, ok.

Now I have to split batch in two parts as workaround...

Can anyone tell me if this is a bug or a "fix" for previous versions?

Many thanks,

Giulio

Interesting, it probably is an undocumented "fix" that exists pretty much because it is illogical to add a check constraint when something doesn't exist, so when they parse it (before they execute it) it fails.

I would personally just add the check constraint creation to the ADD column proc, if I were you. It would just require a parameter, and it would be easy enough. The parameter I would add would be @.checkCriteria. Then pass in 'MyField >= 1 and MyField <= 3)'

Then you can formulate the name (or at worst add another parameter as override) and build the statement in the proc. You can add the constraint when you are adding the column too:

create table test
(
testId int primary key
)
go
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

|||Another workaround might be this:

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0
exec('
Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)')

Steve Kass
Drew University
www.stevekass.com

|||

Thanks Louis and Steve.

I've just resolved problem. Since in Sql 2000 this problem did not exist (using same T-Sql code) I'd like to know if it's a bug...

Really, if I use Alter Table, instead of mySp_Add_Column to add new columns, I get error too... So if I can understand parser can't know what SP wants to do, why doesn't parser understand I'll add new column before of check constraints? Why can I add FKs on new fields while I can't add CKs?

It seems a parser bug...

Bye,

Giulio

|||

Really? When I execute:

create table test
(
testId int primary key
)
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

in a single batch, it works just fine. What error are you getting?

|||

ok, in this way it works...

Only when I add a column to an existing table and, after, I add a check constraint, all in one batch, I get error...

And only after I installed SqlSrv 2k5... on SqlSrv 2k it works fine...

Adding new columns to all tables using a script

Hi, I'm trying to add a modified datetime and userid to all 72 tables in my
SQL 2000 database. I have the script to do one table, and a cursor, but it
won't run across all tables. Any help would be appreciated. Thanks...

DECLARE @.tName varchar(40)
DECLARE C1 CURSOR FOR
select name from sysobjects where type = 'U'
OPEN C1
FETCH NEXT FROM C1 INTO @.tName
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE @.tName ADD
ModifiedDT datetime NULL,
ModifiedUserID int NULL
GO
COMMIT
FETCH NEXT FROM C1
END
CLOSE C1
DEALLOCATE C1
GOHi

As this is not production code then you may want to check out the
undocumented sp_MSforeachtable

http://groups.google.co.uk/groups?h...2%40tkmsftngp03

http://groups.google.co.uk/groups?h...man%40127.0.0.1

John

"Paul" <psampson@.uecomm.com.au> wrote in message
news:1061944796.500758@.proxy.uecomm.net.au...
> Hi, I'm trying to add a modified datetime and userid to all 72 tables in
my
> SQL 2000 database. I have the script to do one table, and a cursor, but it
> won't run across all tables. Any help would be appreciated. Thanks...
> DECLARE @.tName varchar(40)
> DECLARE C1 CURSOR FOR
> select name from sysobjects where type = 'U'
> OPEN C1
> FETCH NEXT FROM C1 INTO @.tName
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- This is executed as long as the previous fetch succeeds
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE @.tName ADD
> ModifiedDT datetime NULL,
> ModifiedUserID int NULL
> GO
> COMMIT
> FETCH NEXT FROM C1
> END
> CLOSE C1
> DEALLOCATE C1
> GO|||Paul (psampson@.uecomm.com.au) writes:
> Hi, I'm trying to add a modified datetime and userid to all 72 tables in
> my SQL 2000 database. I have the script to do one table, and a cursor,
> but it won't run across all tables. Any help would be appreciated.

There are a number of errors in your script:

> DECLARE @.tName varchar(40)
> DECLARE C1 CURSOR FOR

While not an error, I recommend that you make your cursors INSENSITIVE
as a matter of routine. The default keyset-driven cursors can sometimes
give nasty surprises.

> select name from sysobjects where type = 'U'
> OPEN C1
> FETCH NEXT FROM C1 INTO @.tName
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
> WHILE @.@.FETCH_STATUS = 0

I recommend that you write cursor loops as

OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @.var1, @.var2...
IF @.@.fetch_status <> 0
BREAK
-- Real job follows here.
END
DEALLOCATE cur

By only having one FETCH statement you make your code safer, because it's
easy to change the SELECT statement, and the new column to the first
FETCH, but forget the second, which may be the screens below.

> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT

There is no point in executing the SET statements in the loop, and
there is no point to make this a transaction. Not that it is wrong
either.

> BEGIN TRANSACTION
> ALTER TABLE @.tName ADD
> ModifiedDT datetime NULL,
> ModifiedUserID int NULL
> GO

Here are two serious flaws: ALTER TABLE does not accept a variable.
You need to use dynamic SQL for this. (Or sp_MSforeachtable.)

And the GO there is completely out of place. GO is not an SQL command,
but an instruction to the query tool to separate the commands into
different batches. Thus, this batch will fail with a compilation
error, because the BEGIN after WHILE does not have an END.

> FETCH NEXT FROM C1

And if you thought what I said about FETCH above was silly, look here!
Here you don't insert into a variable, but produce a result set.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks John, I'll check it out

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f4c5f38$0$256$ed9e5944@.reading.news.pipex.ne t...
> Hi
> As this is not production code then you may want to check out the
> undocumented sp_MSforeachtable
>
http://groups.google.co.uk/groups?h...2%40tkmsftngp03
>
http://groups.google.co.uk/groups?h...man%40127.0.0.1
> John
> "Paul" <psampson@.uecomm.com.au> wrote in message
> news:1061944796.500758@.proxy.uecomm.net.au...
> > Hi, I'm trying to add a modified datetime and userid to all 72 tables in
> my
> > SQL 2000 database. I have the script to do one table, and a cursor, but
it
> > won't run across all tables. Any help would be appreciated. Thanks...
> > DECLARE @.tName varchar(40)
> > DECLARE C1 CURSOR FOR
> > select name from sysobjects where type = 'U'
> > OPEN C1
> > FETCH NEXT FROM C1 INTO @.tName
> > -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > -- This is executed as long as the previous fetch succeeds
> > BEGIN TRANSACTION
> > SET QUOTED_IDENTIFIER ON
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> > SET ARITHABORT ON
> > SET NUMERIC_ROUNDABORT OFF
> > SET CONCAT_NULL_YIELDS_NULL ON
> > SET ANSI_NULLS ON
> > SET ANSI_PADDING ON
> > SET ANSI_WARNINGS ON
> > COMMIT
> > BEGIN TRANSACTION
> > ALTER TABLE @.tName ADD
> > ModifiedDT datetime NULL,
> > ModifiedUserID int NULL
> > GO
> > COMMIT
> > FETCH NEXT FROM C1
> > END
> > CLOSE C1
> > DEALLOCATE C1
> > GO

Adding new column in published table

hi,
I wish to add a new column in published table. i know using
sp_repladdcolumn we can add columns. but i wish to know using
sp_repladdcolumn is better or drop the replication then add the column and
recreate the replication is better.
Can anyone suggest the right way ?
Thanks in advance
Petchikumar.
"Petchikumar" schrieb:
> hi,
> I wish to add a new column in published table. i know using
> sp_repladdcolumn we can add columns. but i wish to know using
> sp_repladdcolumn is better or drop the replication then add the column and
> recreate the replication is better.
> Can anyone suggest the right way ?
> Thanks in advance
> Petchikumar.
Both ways are correct. sp_repladdcolumn (with reinitialization of the
subscribers) will be a little quicker as you save the time of dropping the
replication ...

Adding new column in published table

hi,
I wish to add a new column in published table. i know using
sp_repladdcolumn we can add columns. but i wish to know using
sp_repladdcolumn is better or drop the replication then add the column and
recreate the replication is better.
Can anyone suggest the right way ?
Thanks in advance
Petchikumar."Petchikumar" schrieb:
> hi,
> I wish to add a new column in published table. i know using
> sp_repladdcolumn we can add columns. but i wish to know using
> sp_repladdcolumn is better or drop the replication then add the column and
> recreate the replication is better.
> Can anyone suggest the right way ?
> Thanks in advance
> Petchikumar.
Both ways are correct. sp_repladdcolumn (with reinitialization of the
subscribers) will be a little quicker as you save the time of dropping the
replication ...

Adding new column in published table

hi,
I wish to add a new column in published table. i know using
sp_repladdcolumn we can add columns. but i wish to know using
sp_repladdcolumn is better or drop the replication then add the column and
recreate the replication is better.
Can anyone suggest the right way ?
Thanks in advance
Petchikumar."Petchikumar" schrieb:
> hi,
> I wish to add a new column in published table. i know using
> sp_repladdcolumn we can add columns. but i wish to know using
> sp_repladdcolumn is better or drop the replication then add the column and
> recreate the replication is better.
> Can anyone suggest the right way ?
> Thanks in advance
> Petchikumar.
Both ways are correct. sp_repladdcolumn (with reinitialization of the
subscribers) will be a little quicker as you save the time of dropping the
replication ...

Adding new column fields into a big table issue

I have an existing table which has about 70 columns with 3 million rows in it. I was asked to add additional 50 new columns into the table. I have tried to add them in through the Enterprise manager design table but experiencing some problems. The adding process seemed never going to be end. Is there any good efficient way to do it? I appreciate the help!

J8You're trying to add 150 million pieces of data (3 million rows times 50 columns) to your table, which now has 210 million pieces (3 million rows times 70 columns). That is significant growth (about 70%), so it will probably take quite a while.

If the columns are all NULL-able, then I'd create a second "child" table that had the primary key from the first table and all of the new columns in it. That would allow you to populate them much more gracefully, possibly in stages.

If you really need to add these columns to the existing table because of Referential Integrity issues or due to other reasons, then I'd strongly recommend declaring downtime (so you can force the users off of the system), then making the changes from Query Analyzer using the ALTER TABLE command. It still won't be fast, but it will be faster than any other method for doing this kind of job.

-PatP|||Pat,

For whatever reason, I have to stick those new columns into this big table. You can consider this table as kind of 'feed' table.

Thanks for the tips.

J8|||You should not have problems adding 50 columns providing they all allow NULL. If not then they must have a default, and you may bring your database down very easily (which what I suspect has happened) because every row must be updated with default value for that column or columns.|||You can script the addition of columns and run it from query analyzer

alter table mytable add col71 int null, col72 int null, ...

as long as all of the columns are nullable this should happen instantly. I would expect EM to add them instantly too unless there is some difference between the default ANSI settings of the DB, Table, and your EM Session.

Adding mutiple columns

HI,
I have two tables A and B with following Data

Table A

ID(PK) | V | W | X | Final
-
1 | 4 | 5 | 6 | output

Table B

ID | Par_ID (FK) | Cost_Per_Annum

1 | 1 | 12000
2 | 1 | 24000
3 | 1 | 14000

Output should be calculated using below the formula .

output = Column V * 1st record of par_id=1 + Column w * 2nd record of par_id=1 + Column X * 3rd record of par_id=1

output = 4* 12000 + 5* 24000 + 6 * 14000

How can i do like this i have tried several ways and failed. Please suggest me.

hi Rajesh,

Try this out

Code Snippet

declare @.TableA table(ID int,V int,W int, X int )
declare @.TableB table(ID int,Par_ID int,Cost_Per_Annum int)
insert into @.TableA select 1,4,5,6

insert into @.TableB select 1,1,12000
insert into @.TableB select 2,1,24000
insert into @.TableB select 3,1,14000

select a.*,
a.v * (
select top 1 v.Cost_Per_Annum from
( select top 1 Cost_Per_Annum,ID from @.TableB where Par_ID=a.ID
) v order by id desc )+
a.W *(
select top 1 v.Cost_Per_Annum from
( select top 2 Cost_Per_Annum,ID from @.TableB where Par_ID=a.ID
) v order by id desc)+
a.X *(
select top 1 v.Cost_Per_Annum from
( select top 3 Cost_Per_Annum,ID from @.TableB where Par_ID=a.ID
) v order by id desc ) Final
from @.TableA a

|||

This should get you started.

Code Snippet

declare @.t1 table (i int, v int, w int, x int)
insert @.t1 select 1,4,5,6

declare @.t2 table (iii int, i int, c int)
insert @.t2 select 1,1,12000
union all select 2,1,24000
union all select 3,1,14000

select a.i,sum(vv*c) as [output]
from(
select * ,case ii when 'v' then 1 when 'w' then 2 when 'x' then 3 end as iii
from (select i, v,w,x from @.t1) as p
unpivot
(vv for ii in(v,w,x)) as unpvt
)a
join @.t2 b on a.i=b.i and a.iii=b.iii
group by a.i

|||Output printing wrong values. Your query value is 202000
Actual output is 252000.

I think problem in 16th line. Can you please check.

thanks|||Please copy the query block as it is and check once more , iam getting the output 252000 with the same query.|||i am getting 202000. Please refer following link for screen shot .

http://picavo.com//images/371729err.JPG

Raj
|||It is really strange, still working for me. Which version of sql server r u using?|||I am using sqlserver 2000.

|||I had checked it on MSDE with SP3 and Sql server Express edition, working on both.|||can i report this problem to Microsoft bug team?
|||

Try:

Code Snippet

createtable dbo.t1 (

ID int,

V int,

W int,

X int

)

go

insertinto dbo.t1 values(1, 4, 5, 6)

insertinto dbo.t1 values(2, 7, 8, 9)

go

createtable dbo.t2 (

ID int,

Par_ID int,

Cost_Per_Annum int

)

go

insertinto dbo.t2 values(1, 1, 12000)

insertinto dbo.t2 values(2, 1, 24000)

insertinto dbo.t2 values(3, 1, 14000)

insertinto dbo.t2 values(4, 2, 10000)

insertinto dbo.t2 values(5, 2, 20000)

insertinto dbo.t2 values(6, 2, 30000)

go

select

x.ID,

(V * Cost_Per_Annum_V)+(W * Cost_Per_Annum_W)+(X * Cost_Per_Annum_X)as [output]

from

dbo.t1 as x

innerjoin

(

select

c.Par_ID,

max(casewhen d.rn = 1 then c.Cost_Per_Annum end)as Cost_Per_Annum_V,

max(casewhen d.rn = 2 then c.Cost_Per_Annum end)as Cost_Per_Annum_W,

max(casewhen d.rn = 3 then c.Cost_Per_Annum end)as Cost_Per_Annum_X

from

dbo.t2 as c

innerjoin

(

select

a.Par_ID,

a.ID,

count(*)as rn

from

dbo.t2 as a

innerjoin

dbo.t2 as b

on a.Par_ID = b.Par_ID

and a.ID >= b.ID

groupby

a.Par_ID,

a.ID

)as d

on c.Par_ID = d.Par_ID

and c.ID = d.ID

groupby

c.Par_ID

)as y

on x.ID = y.Par_ID

go

droptable dbo.t1, dbo.t2

go

AMB