Adding a subtotal column to a matrix

Can anyone direct me on how to add a subtotal column to a matrix?
I have dates in each column header and I want to add another column that has
"Total" in its header and sums up the other columns.
ThanksRight-click on the column header and check the "Subtotal" option. In order
to access the subtotal formatting properties in the VS properties window,
you have to click on the little green triangle after you added the subtotal.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"M" <M@.discussions.microsoft.com> wrote in message
news:1B939293-22C6-4B2B-A1E9-F6C0548C314B@.microsoft.com...
> Can anyone direct me on how to add a subtotal column to a matrix?
> I have dates in each column header and I want to add another column that
has
> "Total" in its header and sums up the other columns.
> Thankssql

Adding a day to a date.

Pls help if you can. I have two dates, date1 and date2.
Basically I want to set date2 = date1 + 1 day for a range of dates
that I can select out.

Is there any way to do this?"Chachu" <cpatel1@.gmail.com> wrote in message
news:d7c4c47b.0410050757.6d4bc3a1@.posting.google.c om...
> Pls help if you can. I have two dates, date1 and date2.
> Basically I want to set date2 = date1 + 1 day for a range of dates
> that I can select out.
> Is there any way to do this?

See DATEADD in Books Online:

update dbo.MyTable
set date2 = dateadd(dd, 1, date1)
where ...

Simon

added two more user parameters

I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO]

@.Question char(80)

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals]

(@.Region_Key int=null,@.QuestionCode char(5),@.Question char(80))

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo'

AND REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

AND Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Looks to me like when you're calling the SPROC, you're not passing in all of the Region_Key's or all of the possible Question_Code's. The SPROC syntax looks fine, assuming you're sure that the LEFT function is working correctly.

To find out exactly what's wrong, I would recommend isolating your changes one by one. Test the SPROC with only the Region_Key param and see what you get. If it's right, test with only the Question_Code param. Then try together. Problems like this are frequently caused by you not realizing that there are NULLs or blanks in your data, and so when you pass certain parameters, it will exclude other rows and therefore throw off your totals.

Hope that helps.
|||Just to add to above post, you have defined QuestionCode as char(5) in the param list, yet inside the proc you are doing left(question code, 2). You may be truncating some data and comparing with incorrect data.

|||

It's actually giving me a number way too big. A Sum way more then when i take the paramaters out. The Stored procedure works fine for another report i have with the same detail. But when i added these two additional parameters, for some reason it gives me a number way higher then if it didnt have any parameters at all.

|||Could you post the DDL and input params for the updated SP. I am hoping RegionKey is coming out of Qry_sales_Group and is it part of key or does it have multiple rows for the same value?
|||

I got it to work. I figured out that the sub query needed to be changed for this report because i was totaling based on region and question this time.

Code Snippet

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

INNER JOIN Qry_Sales_Group

ON Qry_Sales_Group.SalesPerson_Purchaser_Code = qq.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS

Where qq.response in ('Yes','No')and Region_Key=@.Region_Key

GROUP by qq.[Question] , qq.Date,qq.response ) B

Thanks for the help. I went and piece every little piece til i relized it wasnt seperating them by regions.

Add search variables to report header

Hi,
I'd like to make the Search variables in my reports part of the Header, so
for example if I'm searching for dates and have @.StartDate and @.EndDate in
the SQL statement, I'd like to add something like 'Report Represents Data
From 1993-01-01 To 1995-01-01' or something like that. How can I do this?
Thanks -
AlexAlex:
Place a textbox either in the Page Header or he body of your report.
Set the value of the textbox equal to:
="Report Represents Data From: " & Parameters!StartDate.Value & " to " &
Parameters!EndDate.Value
Michael C
"Alex" wrote:
> Hi,
> I'd like to make the Search variables in my reports part of the Header, so
> for example if I'm searching for dates and have @.StartDate and @.EndDate in
> the SQL statement, I'd like to add something like 'Report Represents Data
> From 1993-01-01 To 1995-01-01' or something like that. How can I do this?
> Thanks -
> Alex
>
>

add 28 years aagin

HI all,
This has puzzled me all morning. I have a int field which reads
19691124 (UK dates) this is actually a date 24 November 1969 I need to
add 28 years to this making it 19971124 but Im stumped!
Any ideas anyone?
Cheers
Sean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"S G" <anonymous@.devdex.com> wrote in message news:3f05aafd$0$205$75868355@.news.frii.net...
> HI all,
> This has puzzled me all morning. I have a int field which reads
> 19691124 (UK dates) this is actually a date 24 November 1969 I need to
> add 28 years to this making it 19971124 but I'm stumped!
> Any ideas anyone?
> Cheers
> Sean
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

If you want a DATETIME returned then

SELECT DATEADD(YEAR, 28, '19691124')

If you want an INT returned then

SELECT CAST(CONVERT(CHAR(8),
DATEADD(YEAR, 28, '19691124'),
112) AS INT)

Regards,
jag|||S G wrote:
> HI all,
> This has puzzled me all morning. I have a int field which reads
> 19691124 (UK dates) this is actually a date 24 November 1969 I need
> to add 28 years to this making it 19971124 but I'm stumped!

Select (DateField + 280000) as NewDateField
From Table

By the way, I don't like too much that way of storing dates.

Anyway...

Bye

Alberto|||Sorry, forgot that your input is an INT. Replace the string '19691124'
with CAST(19691124 AS CHAR(8)).

"John Gilson" <jag@.acm.org> wrote in message news:RhiNa.6153$351.2133325@.twister.nyc.rr.com...
> "S G" <anonymous@.devdex.com> wrote in message news:3f05aafd$0$205$75868355@.news.frii.net...
> > HI all,
> > This has puzzled me all morning. I have a int field which reads
> > 19691124 (UK dates) this is actually a date 24 November 1969 I need to
> > add 28 years to this making it 19971124 but I'm stumped!
> > Any ideas anyone?
> > Cheers
> > Sean
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> If you want a DATETIME returned then
> SELECT DATEADD(YEAR, 28, '19691124')
> If you want an INT returned then
> SELECT CAST(CONVERT(CHAR(8),
> DATEADD(YEAR, 28, '19691124'),
> 112) AS INT)
> Regards,
> jag|||"Albe V" <vaccariTOGLI_QUESTO@.hotmail.com> wrote in message
news:IqiNa.147480$lK4.4100117@.twister1.libero.it.. .
> S G wrote:
> > HI all,
> > This has puzzled me all morning. I have a int field which reads
> > 19691124 (UK dates) this is actually a date 24 November 1969 I need
> > to add 28 years to this making it 19971124 but I'm stumped!
> Select (DateField + 280000) as NewDateField
> From Table

This approach will be problematic with a leap year, e.g.,
adding 1 year to 20000229.

Regards,
jag

> By the way, I don't like too much that way of storing dates.
> Anyway...
> Bye
> Alberto

add 28 years

HI all,
This has puzzled me all morning. I have an int
field which reads 19691124 (UK dates) this is actually a
date 24 November 1969 I need to add 28 years to this
making it 19971124 but I'm stumped!
Any ideas anyone?
Cheers
SeanWhat about this
SELECT CONVERT(Char(10),DATEADD(Year,28,'19691124'),112)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Sam" <sgpgpjr@.yahoo.ie> wrote in message
news:07b701c34248$97333560$a001280a@.phx.gbl...
> HI all,
> This has puzzled me all morning. I have an int
> field which reads 19691124 (UK dates) this is actually a
> date 24 November 1969 I need to add 28 years to this
> making it 19971124 but I'm stumped!
> Any ideas anyone?
> Cheers
> Sean
>|||DECLARE @.years INT
SET @.years =28
SELECT DATEADD(yy, @.years, CAST(int_column AS varchar(10)))
or if you want to keep it as an int:
SELECT int_column + (@.years * 10000)
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Sam" <sgpgpjr@.yahoo.ie> wrote in message
news:07b701c34248$97333560$a001280a@.phx.gbl...
> HI all,
> This has puzzled me all morning. I have an int
> field which reads 19691124 (UK dates) this is actually a
> date 24 November 1969 I need to add 28 years to this
> making it 19971124 but I'm stumped!
> Any ideas anyone?
> Cheers
> Sean
>