Addtion / Sum of Parameter Array Values - SSRS

Hi All,
I want to Sum instead of Join Parameter arrays selected values and use
it within the report to display data based on total sum of all the
values selected. Join concatanates them with comma delimited values
but i want sum of all those values.
Example as below
Value DisplayText
1 - Current Month
2 - Previoius Year
4 - Half Year
8 - Year to date
so if
current month and half year
are selected then i want to read 1 + 4 = 5 and not 1,4 as currently i
get by join function.
Code = SUM(Parameters!dropdownbox.value)
Result = #Error
Code = Join(Parameters!dropdownbox.value,",")
Result = "1,4"
Code = SUM(Join(Parameters!dropdownbox.value,","))
Result = #Error
Code = Parameters!dropdownbox.count
Result = 4 (it gives me length of array)
Code = ?
Result = 5 (This is the result is want...but can make it work)
Any help greatly appreciated
Regards
Nirav Lulla
Yotta ConsultingOn May 14, 1:02 pm, nlulla <nirav.lu...@.gmail.com> wrote:
> Hi All,
> I want to Sum instead ofJoinParameterarrays selected values and use
> it within the report to display data based on total sum of all the
> values selected.Joinconcatanates them with comma delimited values
> but i want sum of all those values.
> Example as below
> Value DisplayText
> 1 - Current Month
> 2 - Previoius Year
> 4 - Half Year
> 8 - Year to date
> so if
> current month and half year
> are selected then i want to read 1 + 4 = 5 and not 1,4 as currently i
> get byjoinfunction.
> Code = SUM(Parameters!dropdownbox.value)
> Result = #Error
> Code =Join(Parameters!dropdownbox.value,",")
> Result = "1,4"
> Code = SUM(Join(Parameters!dropdownbox.value,","))
> Result = #Error
> Code = Parameters!dropdownbox.count
> Result = 4 (it gives me length of array)
> Code = ?
> Result = 5 (This is the result is want...but can make it work)
> Any help greatly appreciated
> Regards
> Nirav Lulla
> Yotta Consulting
I'm not sure how many items are in your dropdown list but this will
work if you only have a few...
=CInt(Parameters!site.Value(0)) + CInt(Parameters!site.Value(1))|||Hi James,
Thanks for posting your reply, you suggesstio would only work if i
have set fixed length of options, but i don't know how this is going
to work in case of unknown number of options.
For now , I have Created a .net class file DLL with following code and
referenced the dll in my .rdl file, it works for me, but would be good
if it can be done within SSRS itself. Any more suggesstions welcome
Nirav Lulla
Yotta Consulting
Public Class ClsCommon
Const bDisplayColumn As Boolean = False
Const bHideColumn As Boolean = True
Shared Function SumOfArrayString(ByVal ArrayString As String) As
Integer
Dim arylist As System.Array
Dim sum As Integer
Try
arylist = ArrayString.Split(",")
For Each item As Integer In arylist
sum += CInt(item)
Next
Catch ex As Exception
Return -1
Finally
Return sum
End Try
End Function
End Class

Adding Time In DateTime Field

Hi

I'm trying to add a time from a DateTime field to provide a total. Eg:

Field1

01/02/2007 01:00:00PM

01/03/2007 01:45:00PM

01/04/2007 03:00:00PM

I want to add the time so I get a total of 05:45. The total hours could go over 24. I know I can't Sum it. I've seen several examples of how to do this but can't make any of them work. Could someone please point me in the right direction?

Thanks

set @.d1='01/02/2007 01:00:00PM'

datepart(hh,@.d1) return : 1

DATEADD ( hour, datepart(hh,@.d1), YourDate ) add 1 hour to your data

|||

Please check your objective. All of the times you list are afternoon times. The sum of the time component for all of these is the 5:45 plus an additional 36 hours. If your answer is correct, there is more to it than just summing the time components.

Code Snippet

declare @.aTable table (field1 datetime)
insert into @.aTable
select '01/02/2007 01:00:00PM' union all
select '01/03/2007 01:45:00PM' union all
select '01/04/2007 03:00:00PM'

select datediff(day, 0, sumOfTime) as Days,
convert(varchar, sumOfTime, 114) as Time
from ( select cast(sum(cast(field1 as float)
-floor(cast(field1 as float)))as datetime)
as sumOfTime
from @.aTable
) x

/*
Days Time
--
1 17:44:59:997
*/

|||

From the nature of your question, and looking at your sample data, I assume that the sample data represents 'elapsed time' on a date. And that 01:45PM means 1 hour and 45 minutes elapsed time -NOT 13:45 o'clock.

To calculate the total 'elapsed time', it would have been so much easier if you were storing the StartDateTime and EndDateTime -then it would be relatively simple date arithematic.

If my assumptions are correct, AND you cannot re-engineer the data to collect Start/End datetime values, this will be a bit more effort.

Please confirm.

|||

Sorry, I should have been clearer. The time is just a time, the date is irrelevant. It's actually a travel time, so Arnie you're correct, it is an elapsed time. I'm purely interested in adding the hours together. So in the 3 lines of sample data they travelled for 1 hour, 1 hour 45 minutes and 3 hours. AM/PM is also irrelevant. Travel time will never go over 12 hours. So the total I want is 5:45.

I'm working with someone else's data and tables here, personally I wouldn't have used a datetime field for this data but that is what I have. I also agree it would be better to have a start and end time, but I don't.

One possible way could be to extract the time, convert it to minutes, add those minutes together and then convert it back to hours and minutes...possibly? Any ideas?

|||

YOu could use this function:

Code Snippet

CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
(
@.Firstdate DATETIME,
@.Seconddate DATETIME
)
/*
Function written by Jens K. Suessmeyer, 07/22/2007
http://www.sqlserver2005.de
*/
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @.FirstdateMinutes INT
DECLARE @.SeconddateMinutes INT

SELECT @.FirstdateMinutes = DATEPART (hh,@.Firstdate)*60 + DATEPART(mi,@.Firstdate)
SELECT @.SeconddateMinutes = DATEPART (hh,@.Seconddate)*60 + DATEPART(mi,@.Seconddate)

RETURN (
SELECT
CONVERT(VARCHAR(10), FLOOR(@.SeconddateMinutes-@.FirstdateMinutes) / 60) +
':' +
RIGHT('00' + CONVERT(VARCHAR(10), (@.SeconddateMinutes-@.FirstdateMinutes) - FLOOR((@.SeconddateMinutes-@.FirstdateMinutes) / 60)*60),2))
END;

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

This 'should' move you in the direction you want. (I've added another row to the sample data.)

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
TravelTime smalldatetime
)

INSERT INTO @.MyTable VALUES ( '01/02/2007 01:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/03/2007 01:45:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 03:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 01:45:00PM' )

SELECT
Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 2 ) AS int )) +
( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) / 60 ) ,
Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) % 60 )
FROM @.MyTable

Hours Mins
-- --
7 30

|||Thank you so much Arnie that seems perfect. |||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de

Adding Time In DateTime Field

Hi

I'm trying to add a time from a DateTime field to provide a total. Eg:

Field1

01/02/2007 01:00:00PM

01/03/2007 01:45:00PM

01/04/2007 03:00:00PM

I want to add the time so I get a total of 05:45. The total hours could go over 24. I know I can't Sum it. I've seen several examples of how to do this but can't make any of them work. Could someone please point me in the right direction?

Thanks

set @.d1='01/02/2007 01:00:00PM'

datepart(hh,@.d1) return : 1

DATEADD ( hour, datepart(hh,@.d1), YourDate ) add 1 hour to your data

|||

Please check your objective. All of the times you list are afternoon times. The sum of the time component for all of these is the 5:45 plus an additional 36 hours. If your answer is correct, there is more to it than just summing the time components.

Code Snippet

declare @.aTable table (field1 datetime)
insert into @.aTable
select '01/02/2007 01:00:00PM' union all
select '01/03/2007 01:45:00PM' union all
select '01/04/2007 03:00:00PM'

select datediff(day, 0, sumOfTime) as Days,
convert(varchar, sumOfTime, 114) as Time
from ( select cast(sum(cast(field1 as float)
-floor(cast(field1 as float)))as datetime)
as sumOfTime
from @.aTable
) x

/*
Days Time
--
1 17:44:59:997
*/

|||

From the nature of your question, and looking at your sample data, I assume that the sample data represents 'elapsed time' on a date. And that 01:45PM means 1 hour and 45 minutes elapsed time -NOT 13:45 o'clock.

To calculate the total 'elapsed time', it would have been so much easier if you were storing the StartDateTime and EndDateTime -then it would be relatively simple date arithematic.

If my assumptions are correct, AND you cannot re-engineer the data to collect Start/End datetime values, this will be a bit more effort.

Please confirm.

|||

Sorry, I should have been clearer. The time is just a time, the date is irrelevant. It's actually a travel time, so Arnie you're correct, it is an elapsed time. I'm purely interested in adding the hours together. So in the 3 lines of sample data they travelled for 1 hour, 1 hour 45 minutes and 3 hours. AM/PM is also irrelevant. Travel time will never go over 12 hours. So the total I want is 5:45.

I'm working with someone else's data and tables here, personally I wouldn't have used a datetime field for this data but that is what I have. I also agree it would be better to have a start and end time, but I don't.

One possible way could be to extract the time, convert it to minutes, add those minutes together and then convert it back to hours and minutes...possibly? Any ideas?

|||

YOu could use this function:

Code Snippet

CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
(
@.Firstdate DATETIME,
@.Seconddate DATETIME
)
/*
Function written by Jens K. Suessmeyer, 07/22/2007
http://www.sqlserver2005.de
*/
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @.FirstdateMinutes INT
DECLARE @.SeconddateMinutes INT

SELECT @.FirstdateMinutes = DATEPART (hh,@.Firstdate)*60 + DATEPART(mi,@.Firstdate)
SELECT @.SeconddateMinutes = DATEPART (hh,@.Seconddate)*60 + DATEPART(mi,@.Seconddate)

RETURN (
SELECT
CONVERT(VARCHAR(10), FLOOR(@.SeconddateMinutes-@.FirstdateMinutes) / 60) +
':' +
RIGHT('00' + CONVERT(VARCHAR(10), (@.SeconddateMinutes-@.FirstdateMinutes) - FLOOR((@.SeconddateMinutes-@.FirstdateMinutes) / 60)*60),2))
END;

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

This 'should' move you in the direction you want. (I've added another row to the sample data.)

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
TravelTime smalldatetime
)

INSERT INTO @.MyTable VALUES ( '01/02/2007 01:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/03/2007 01:45:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 03:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 01:45:00PM' )

SELECT
Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 2 ) AS int )) +
( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) / 60 ) ,
Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) % 60 )
FROM @.MyTable

Hours Mins
-- --
7 30

|||Thank you so much Arnie that seems perfect. |||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de

Adding time

Hi,
I have a table with process_id and process_started_at and
process_completed_at which are both datetime datatype.
I need to calculate the total time taken for a particular process for a
given date-range.
and calculate average time per day per process_id.
How can I do it I have MS SQL 2003.
If I add the 2 datetime variables it increments the day by 1 after
every 24 hrs but that is not what I want.
Example if I have
date1 = '2005-01-01 20:20:30'
date2 = '2005-01-01 20:25:20'
then I want the result as '40:45:50' I am not concerned with the date
part.
I tried creating a function which would add the two times but then how
do I get the average? Do I have to write another function which will
convert the total time into seconds and then devide by total number of
days and then convert back to hr:min:sec or is there is easier way to
do it?
Does MS SQL have any simple methode which will convert the time to
seconds and seconds to hr:min:sec?
Thanks for your time and expertise
Ashoo> Does MS SQL have any simple methode which will convert the time to
> seconds and seconds to hr:min:sec?
For the latter, see http://www.aspfaq.com/2271

Adding Subreport Total to Main Report Total

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I

have named this text box ’SubRepM1’

The name of the subreport is ‘subreport1'.

On my Main Report, again I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value)*-1, and I

have named this text box 'MainRepM1'

The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values

but I now need to total these values together for each month in order to

produce a grand total.

I have tried using the following to add the totals for Month 1 together,

=subreport1.Report.SubRepM1 + MainRepM1

but this does not work and I get the following error message ‘The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but I’m having major problems trying to get these totals to work.

Can anyone help, thanks

I have the same problem, did you find a solution yet?

Please help. thanks

|||

Hi Dalia

Sorry, no one has been able to help me with this query yet!

But if i find the answer i will let you know.

|||

Hello,

Actually, this is not doable in SQL reporting, you can use multiple datasets instead.

I hope that helps you.

|||

hiya Dailia

tried using an additional dataset and it works perfectly

many thanx

|||How did you use an additional dataset?

Adding Subreport Total to Main Report Total

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I

have named this text box ’SubRepM1’

The name of the subreport is ‘subreport1'.

On my Main Report, again I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value)*-1, and I

have named this text box 'MainRepM1'

The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values

but I now need to total these values together for each month in order to

produce a grand total.

I have tried using the following to add the totals for Month 1 together,

=subreport1.Report.SubRepM1 + MainRepM1

but this does not work and I get the following error message ‘The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but I’m having major problems trying to get these totals to work.

Can anyone help, thanks

I have the same problem, did you find a solution yet?

Please help. thanks

|||

Hi Dalia

Sorry, no one has been able to help me with this query yet!

But if i find the answer i will let you know.

|||

Hello,

Actually, this is not doable in SQL reporting, you can use multiple datasets instead.

I hope that helps you.

|||

hiya Dailia

tried using an additional dataset and it works perfectly

many thanx

|||How did you use an additional dataset?

Adding Subreport Total to Main Report Total

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I

have named this text box ’SubRepM1’

The name of the subreport is ‘subreport1'.

On my Main Report, again I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value)*-1, and I

have named this text box 'MainRepM1'

The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values

but I now need to total these values together for each month in order to

produce a grand total.

I have tried using the following to add the totals for Month 1 together,

=subreport1.Report.SubRepM1 + MainRepM1

but this does not work and I get the following error message ‘The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but I’m having major problems trying to get these totals to work.

Can anyone help, thanks

I have the same problem, did you find a solution yet?

Please help. thanks

|||

Hi Dalia

Sorry, no one has been able to help me with this query yet!

But if i find the answer i will let you know.

|||

Hello,

Actually, this is not doable in SQL reporting, you can use multiple datasets instead.

I hope that helps you.

|||

hiya Dailia

tried using an additional dataset and it works perfectly

many thanx

|||How did you use an additional dataset?

Adding staggered running total and average to query

Hi,

I am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI).

Given the sample data:

CREATE TABLE [dbo].[QtrInflation] (
[Qtr] [smalldatetime] NOT NULL ,
[CPI] [decimal](8, 4) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO QtrInflation (Qtr, CPI)
SELECT '1960-03-01', 0.7500 UNION
SELECT '1960-06-01', 1.4800 UNION
SELECT '1960-09-01', 1.4600 UNION
SELECT '1960-12-01', 0.7200 UNION
SELECT '1961-03-01', 0.7100 UNION
SELECT '1961-06-01', 0.7100 UNION
SELECT '1961-09-01',-0.7000 UNION
SELECT '1961-12-01', 0.0000 UNION
SELECT '1962-03-01', 0.0000 UNION
SELECT '1962-06-01', 0.0000 UNION
SELECT '1962-09-01', 0.0000 UNION
SELECT '1962-12-01', 0.0000 UNION
SELECT '1963-03-01', 0.0000 UNION
SELECT '1963-06-01', 0.0000 UNION
SELECT '1963-09-01', 0.7100 UNION
SELECT '1963-12-01', 0.0000 UNION
SELECT '1964-03-01', 0.7000 UNION
SELECT '1964-06-01', 0.7000 UNION
SELECT '1964-09-01', 1.3900 UNION
SELECT '1964-12-01', 0.6800 UNION
SELECT '1965-03-01', 0.6800 UNION
SELECT '1965-06-01', 1.3500 UNION
SELECT '1965-09-01', 0.6700 UNION
SELECT '1965-12-01', 1.3200

I am trying to return the following results:

Qtr CPI YrCPI AvgYrCPI
--- -- -- ---
1-Jun-60 1.48
1-Sep-60 1.46
1-Dec-60 0.72
1-Mar-61 0.71 4.37
1-Jun-61 0.71 3.60
1-Sep-61 -0.70 1.44
1-Dec-61 0.00 0.72 2.53
1-Mar-62 0.00 0.01 1.44
1-Jun-62 0.00 -0.70 0.37
1-Sep-62 0.00 0.00 0.01
1-Dec-62 0.00 0.00 -0.17
1-Mar-63 0.00 0.00 -0.18
1-Jun-63 0.00 0.00 0.00
1-Sep-63 0.71 0.71 0.18
1-Dec-63 0.00 0.71 0.36
1-Mar-64 0.70 1.41 0.71
1-Jun-64 0.70 2.11 1.24
1-Sep-64 1.39 2.79 1.76
1-Dec-64 0.68 3.47 2.45
1-Mar-65 0.68 3.45 2.96
1-Jun-65 1.35 4.10 3.45
1-Sep-65 0.67 3.38 3.60
1-Dec-65 1.32 4.02 3.74

Note, 4 data points are required to calculate a moving sum of CPI
(YrCPI) and 4 calculate YrCPI figures are required calculate the
annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
first 7 results

This sad effort is about as far as I've got:

SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
FROM QtrInflation I
JOIN (
SELECT TOP 4 Qtr, CPI
FROM QtrInflation
) S
ON S.Qtr <= I.Qtr
GROUP BY I.Qtr, I.CPI
ORDER BY I.Qtr ASC

Can anyone suggest how do achieve this result without having to resort
to cursors?

Thanks,

StephenHi

This will do it (I think!) but there may be a neater way!

SELECT S.Qtr, S.CPI, D.YrCPI, E.AvgCPI
FROM QtrInflation S LEFT JOIN
( SELECT Q.Qtr, SUM(A.CPI) AS YrCPI
FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
FROM QtrInflation
GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
GROUP BY Q.Qtr
HAVING COUNT(A.Qtr) = 4 ) D ON S.Qtr = D.Qtr
LEFT JOIN
( SELECT R.Qtr, SUM(B.CPI)/4 AS AvgCPI
FROM QtrInflation R LEFT JOIN ( SELECT Q.Qtr, SUM(A.CPI) AS CPI
FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
FROM QtrInflation
GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
GROUP BY Q.Qtr
HAVING COUNT(A.Qtr) = 4 ) B ON R.Qtr >= B.Qtr AND DATEADD(YEAR,-1,R.Qtr)
< B.Qtr
GROUP BY R.Qtr
HAVING COUNT(B.Qtr) = 4 ) E ON S.Qtr = E.Qtr
ORDER BY S.Qtr

John
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0309210139.58ffad34@.posting.google.c om...
> Hi,
> I am trying to add a staggered running total and average to a query
> returning quarterly CPI data. I need to add 4 quarterly data points
> together to calculate a moving 12-month sum (YrCPI), and then to
> complicate things, calculate a moving average of the 12-month figure
> (AvgYrCPI).
> Given the sample data:
> CREATE TABLE [dbo].[QtrInflation] (
> [Qtr] [smalldatetime] NOT NULL ,
> [CPI] [decimal](8, 4) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QtrInflation (Qtr, CPI)
> SELECT '1960-03-01', 0.7500 UNION
> SELECT '1960-06-01', 1.4800 UNION
> SELECT '1960-09-01', 1.4600 UNION
> SELECT '1960-12-01', 0.7200 UNION
> SELECT '1961-03-01', 0.7100 UNION
> SELECT '1961-06-01', 0.7100 UNION
> SELECT '1961-09-01',-0.7000 UNION
> SELECT '1961-12-01', 0.0000 UNION
> SELECT '1962-03-01', 0.0000 UNION
> SELECT '1962-06-01', 0.0000 UNION
> SELECT '1962-09-01', 0.0000 UNION
> SELECT '1962-12-01', 0.0000 UNION
> SELECT '1963-03-01', 0.0000 UNION
> SELECT '1963-06-01', 0.0000 UNION
> SELECT '1963-09-01', 0.7100 UNION
> SELECT '1963-12-01', 0.0000 UNION
> SELECT '1964-03-01', 0.7000 UNION
> SELECT '1964-06-01', 0.7000 UNION
> SELECT '1964-09-01', 1.3900 UNION
> SELECT '1964-12-01', 0.6800 UNION
> SELECT '1965-03-01', 0.6800 UNION
> SELECT '1965-06-01', 1.3500 UNION
> SELECT '1965-09-01', 0.6700 UNION
> SELECT '1965-12-01', 1.3200
>
> I am trying to return the following results:
> Qtr CPI YrCPI AvgYrCPI
> --- -- -- ---
> 1-Jun-60 1.48
> 1-Sep-60 1.46
> 1-Dec-60 0.72
> 1-Mar-61 0.71 4.37
> 1-Jun-61 0.71 3.60
> 1-Sep-61 -0.70 1.44
> 1-Dec-61 0.00 0.72 2.53
> 1-Mar-62 0.00 0.01 1.44
> 1-Jun-62 0.00 -0.70 0.37
> 1-Sep-62 0.00 0.00 0.01
> 1-Dec-62 0.00 0.00 -0.17
> 1-Mar-63 0.00 0.00 -0.18
> 1-Jun-63 0.00 0.00 0.00
> 1-Sep-63 0.71 0.71 0.18
> 1-Dec-63 0.00 0.71 0.36
> 1-Mar-64 0.70 1.41 0.71
> 1-Jun-64 0.70 2.11 1.24
> 1-Sep-64 1.39 2.79 1.76
> 1-Dec-64 0.68 3.47 2.45
> 1-Mar-65 0.68 3.45 2.96
> 1-Jun-65 1.35 4.10 3.45
> 1-Sep-65 0.67 3.38 3.60
> 1-Dec-65 1.32 4.02 3.74
> Note, 4 data points are required to calculate a moving sum of CPI
> (YrCPI) and 4 calculate YrCPI figures are required calculate the
> annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> first 7 results
> This sad effort is about as far as I've got:
> SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> FROM QtrInflation I
> JOIN (
> SELECT TOP 4 Qtr, CPI
> FROM QtrInflation
> ) S
> ON S.Qtr <= I.Qtr
> GROUP BY I.Qtr, I.CPI
> ORDER BY I.Qtr ASC
> Can anyone suggest how do achieve this result without having to resort
> to cursors?
> Thanks,
> Stephen|||Stephen,

Here is another approach that I think will work
for you:

-- alternate solution
create table Weights (
offset int,
weight decimal(3,2),
weightA decimal(3,2),
weightB decimal(3,2)
)
go

insert into Weights

select 6, 0, 0, 0.25 union all
select 5, 0, 0, 0.5 union all
select 4, 0, 0, 0.75 union all
select 3, 0, 1, 1.00 union all
select 2, 0, 1, 0.75 union all
select 1, 0, 1, 0.5 union all
select 0, 1, 1, 0.25
go

select
dateadd(month,3*Offset,Q1.Qtr) Qtr,
sum(Weight*Q1.CPI) CPI,
case when sum(WeightA) = 4 then sum(WeightA*Q1.CPI) else NULL end as YrCPI,
case when sum(WeightB) = 4 then sum(WeightB*Q1.CPI) else NULL end as MACPI
from QtrInflation Q1, Weights
group by dateadd(month,3*Offset,Q1.Qtr)
having sum(Weight) = 1
order by dateadd(month,3*Offset,Q1.Qtr)

-- Steve Kass
-- Drew University
-- Ref: 17F9A22A-8DDA-4812-A8CD-B68062BADFA1

Stephen Miller wrote:
> Hi,
> I am trying to add a staggered running total and average to a query
> returning quarterly CPI data. I need to add 4 quarterly data points
> together to calculate a moving 12-month sum (YrCPI), and then to
> complicate things, calculate a moving average of the 12-month figure
> (AvgYrCPI).
> Given the sample data:
> CREATE TABLE [dbo].[QtrInflation] (
> [Qtr] [smalldatetime] NOT NULL ,
> [CPI] [decimal](8, 4) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QtrInflation (Qtr, CPI)
> SELECT '1960-03-01', 0.7500 UNION
> SELECT '1960-06-01', 1.4800 UNION
> SELECT '1960-09-01', 1.4600 UNION
> SELECT '1960-12-01', 0.7200 UNION
> SELECT '1961-03-01', 0.7100 UNION
> SELECT '1961-06-01', 0.7100 UNION
> SELECT '1961-09-01',-0.7000 UNION
> SELECT '1961-12-01', 0.0000 UNION
> SELECT '1962-03-01', 0.0000 UNION
> SELECT '1962-06-01', 0.0000 UNION
> SELECT '1962-09-01', 0.0000 UNION
> SELECT '1962-12-01', 0.0000 UNION
> SELECT '1963-03-01', 0.0000 UNION
> SELECT '1963-06-01', 0.0000 UNION
> SELECT '1963-09-01', 0.7100 UNION
> SELECT '1963-12-01', 0.0000 UNION
> SELECT '1964-03-01', 0.7000 UNION
> SELECT '1964-06-01', 0.7000 UNION
> SELECT '1964-09-01', 1.3900 UNION
> SELECT '1964-12-01', 0.6800 UNION
> SELECT '1965-03-01', 0.6800 UNION
> SELECT '1965-06-01', 1.3500 UNION
> SELECT '1965-09-01', 0.6700 UNION
> SELECT '1965-12-01', 1.3200
>
> I am trying to return the following results:
> Qtr CPI YrCPI AvgYrCPI
> --- -- -- ---
> 1-Jun-60 1.48
> 1-Sep-60 1.46
> 1-Dec-60 0.72
> 1-Mar-61 0.71 4.37
> 1-Jun-61 0.71 3.60
> 1-Sep-61 -0.70 1.44
> 1-Dec-61 0.00 0.72 2.53
> 1-Mar-62 0.00 0.01 1.44
> 1-Jun-62 0.00 -0.70 0.37
> 1-Sep-62 0.00 0.00 0.01
> 1-Dec-62 0.00 0.00 -0.17
> 1-Mar-63 0.00 0.00 -0.18
> 1-Jun-63 0.00 0.00 0.00
> 1-Sep-63 0.71 0.71 0.18
> 1-Dec-63 0.00 0.71 0.36
> 1-Mar-64 0.70 1.41 0.71
> 1-Jun-64 0.70 2.11 1.24
> 1-Sep-64 1.39 2.79 1.76
> 1-Dec-64 0.68 3.47 2.45
> 1-Mar-65 0.68 3.45 2.96
> 1-Jun-65 1.35 4.10 3.45
> 1-Sep-65 0.67 3.38 3.60
> 1-Dec-65 1.32 4.02 3.74
> Note, 4 data points are required to calculate a moving sum of CPI
> (YrCPI) and 4 calculate YrCPI figures are required calculate the
> annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> first 7 results
> This sad effort is about as far as I've got:
> SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> FROM QtrInflation I
> JOIN (
> SELECT TOP 4 Qtr, CPI
> FROM QtrInflation
> ) S
> ON S.Qtr <= I.Qtr
> GROUP BY I.Qtr, I.CPI
> ORDER BY I.Qtr ASC
> Can anyone suggest how do achieve this result without having to resort
> to cursors?
> Thanks,
> Stephen|||John & Steve

Thank you for two very interesting (and very different) responses. You
guys are gurus! Both return the results I'm looking for and now I'm
stuck picking which one's best ;)

Thanks again,

Stephen

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f6d878f$0$10783$afc38c87@.news.easynet.co.uk>...
> Hi
> This will do it (I think!) but there may be a neater way!
> SELECT S.Qtr, S.CPI, D.YrCPI, E.AvgCPI
> FROM QtrInflation S LEFT JOIN
> ( SELECT Q.Qtr, SUM(A.CPI) AS YrCPI
> FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
> FROM QtrInflation
> GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
> GROUP BY Q.Qtr
> HAVING COUNT(A.Qtr) = 4 ) D ON S.Qtr = D.Qtr
> LEFT JOIN
> ( SELECT R.Qtr, SUM(B.CPI)/4 AS AvgCPI
> FROM QtrInflation R LEFT JOIN ( SELECT Q.Qtr, SUM(A.CPI) AS CPI
> FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
> FROM QtrInflation
> GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
> GROUP BY Q.Qtr
> HAVING COUNT(A.Qtr) = 4 ) B ON R.Qtr >= B.Qtr AND DATEADD(YEAR,-1,R.Qtr)
> < B.Qtr
> GROUP BY R.Qtr
> HAVING COUNT(B.Qtr) = 4 ) E ON S.Qtr = E.Qtr
> ORDER BY S.Qtr
> John
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0309210139.58ffad34@.posting.google.c om...
> > Hi,
> > I am trying to add a staggered running total and average to a query
> > returning quarterly CPI data. I need to add 4 quarterly data points
> > together to calculate a moving 12-month sum (YrCPI), and then to
> > complicate things, calculate a moving average of the 12-month figure
> > (AvgYrCPI).
> > Given the sample data:
> > CREATE TABLE [dbo].[QtrInflation] (
> > [Qtr] [smalldatetime] NOT NULL ,
> > [CPI] [decimal](8, 4) NOT NULL
> > ) ON [PRIMARY]
> > GO
> > INSERT INTO QtrInflation (Qtr, CPI)
> > SELECT '1960-03-01', 0.7500 UNION
> > SELECT '1960-06-01', 1.4800 UNION
> > SELECT '1960-09-01', 1.4600 UNION
> > SELECT '1960-12-01', 0.7200 UNION
> > SELECT '1961-03-01', 0.7100 UNION
> > SELECT '1961-06-01', 0.7100 UNION
> > SELECT '1961-09-01',-0.7000 UNION
> > SELECT '1961-12-01', 0.0000 UNION
> > SELECT '1962-03-01', 0.0000 UNION
> > SELECT '1962-06-01', 0.0000 UNION
> > SELECT '1962-09-01', 0.0000 UNION
> > SELECT '1962-12-01', 0.0000 UNION
> > SELECT '1963-03-01', 0.0000 UNION
> > SELECT '1963-06-01', 0.0000 UNION
> > SELECT '1963-09-01', 0.7100 UNION
> > SELECT '1963-12-01', 0.0000 UNION
> > SELECT '1964-03-01', 0.7000 UNION
> > SELECT '1964-06-01', 0.7000 UNION
> > SELECT '1964-09-01', 1.3900 UNION
> > SELECT '1964-12-01', 0.6800 UNION
> > SELECT '1965-03-01', 0.6800 UNION
> > SELECT '1965-06-01', 1.3500 UNION
> > SELECT '1965-09-01', 0.6700 UNION
> > SELECT '1965-12-01', 1.3200
> > I am trying to return the following results:
> > Qtr CPI YrCPI AvgYrCPI
> > --- -- -- ---
> > 1-Jun-60 1.48
> > 1-Sep-60 1.46
> > 1-Dec-60 0.72
> > 1-Mar-61 0.71 4.37
> > 1-Jun-61 0.71 3.60
> > 1-Sep-61 -0.70 1.44
> > 1-Dec-61 0.00 0.72 2.53
> > 1-Mar-62 0.00 0.01 1.44
> > 1-Jun-62 0.00 -0.70 0.37
> > 1-Sep-62 0.00 0.00 0.01
> > 1-Dec-62 0.00 0.00 -0.17
> > 1-Mar-63 0.00 0.00 -0.18
> > 1-Jun-63 0.00 0.00 0.00
> > 1-Sep-63 0.71 0.71 0.18
> > 1-Dec-63 0.00 0.71 0.36
> > 1-Mar-64 0.70 1.41 0.71
> > 1-Jun-64 0.70 2.11 1.24
> > 1-Sep-64 1.39 2.79 1.76
> > 1-Dec-64 0.68 3.47 2.45
> > 1-Mar-65 0.68 3.45 2.96
> > 1-Jun-65 1.35 4.10 3.45
> > 1-Sep-65 0.67 3.38 3.60
> > 1-Dec-65 1.32 4.02 3.74
> > Note, 4 data points are required to calculate a moving sum of CPI
> > (YrCPI) and 4 calculate YrCPI figures are required calculate the
> > annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> > first 7 results
> > This sad effort is about as far as I've got:
> > SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> > FROM QtrInflation I
> > JOIN (
> > SELECT TOP 4 Qtr, CPI
> > FROM QtrInflation
> > ) S
> > ON S.Qtr <= I.Qtr
> > GROUP BY I.Qtr, I.CPI
> > ORDER BY I.Qtr ASC
> > Can anyone suggest how do achieve this result without having to resort
> > to cursors?
> > Thanks,
> > Stephen|||Hi Stephen

I would expect Steve's solution to work alot better than mine under
large loads!

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> I am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI). <<

I hope you mean to have a key on this table and some contraints

CREATE TABLE QtrInflation
(qtr SMALLDATETIME NOT NULL PRIMARY KEY
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
cpi DECIMAL(8,4) NOT NULL
CHECK(cpi >= 0.0000));

CREATE TABLE QtrReportRanges
(start_date SMALLDATETIME NOT NULL
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
end_date SMALLDATETIME NOT NULL
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
CHECK (start_date < end_date),
PRIMARY KEY (start_date < end_date));

INSERT INTO QtrReportRanges VALUES ('1960-03-01', '1960-12-01');
INSERT INTO QtrReportRanges VALUES ('1960-06-01', '1961-03-01');
etc,

now you can get the report easily.

SELECT R.start_date, R.end_date, SUM(cpi) AS yr_cpi, AVG(cpi) AS
avg_yr_cpi
FROM QtrInflation AS I, QtrReportRanges AS R
WHERE I.qtr BETWEEN R.start_date AND R.end_date
GROUP BY R.start_date, R.end_date;

Adding Multiple column items for a total.

Rookie question here -
I need to have one column show up in my RS 2000 report that I am creating.
The SQL table the information is coming out of is:
Column1 Column2 Column3 Column4 Column5 Column6
Name Tuition BookFee MaterialsFee OtherFee1 OtherFee2
In my report, I only want to show a column called Total Cost (For each Field
Name)which consists of Tuition+BookFee+MaterialsFee+OtherFee1+OtherFee2. In
other words, I want to add columns 2 - 6 together and only show that total in
my final report. Obviously I can do this in Excel, but I can't seem to do
this in SQL.
I can't alter my SQL tables, but I can alter the RS query if need be or sum
them up on my report layout. My only problem is I don't know how.
Any help would be greatly appreciated
Thanks.Solution I used
In the SELECT portion of my query after selecting approriate items I included
StudentProgram.BookFee+StudentProgram.MaterialFee+StudentProgram.OtherFee1+
StudentProgram.OtherFee2+StudentProgram.TuitionFee AS TotalCost
"TTU" wrote:
> Rookie question here -
> I need to have one column show up in my RS 2000 report that I am creating.
> The SQL table the information is coming out of is:
> Column1 Column2 Column3 Column4 Column5 Column6
> Name Tuition BookFee MaterialsFee OtherFee1 OtherFee2
> In my report, I only want to show a column called Total Cost (For each Field
> Name)which consists of Tuition+BookFee+MaterialsFee+OtherFee1+OtherFee2. In
> other words, I want to add columns 2 - 6 together and only show that total in
> my final report. Obviously I can do this in Excel, but I can't seem to do
> this in SQL.
> I can't alter my SQL tables, but I can alter the RS query if need be or sum
> them up on my report layout. My only problem is I don't know how.
> Any help would be greatly appreciated
> Thanks.
>

Adding Grand Total to a Column Group in a Matrix. Please Help!

Hello Guys,

I am working on a matrix report which has several row groups and 1 column group. After execution, the column group wil end up with several columns containg numeric counts. I would like to have the grand total for each "column group" column as a last row on this report.

For row groups you can just right click "Subtotal", but that is not possible for column group. Could someone please help me to find a clever way of accomplishing this, please. Thank you so much for your help!

For column groups, you can also just right click "Subtotal". Maybe you are clicking the wrong box or it isn't truly a column group or you just aren't seeing it.

Look around a bit more.

http://i55.photobucket.com/albums/g121/Farsight38/untitled-1.jpg

|||

You're right, however that will just give me totals on the right side of the matrix (row totals). What I would like to have is a grand total column(s) at the bottom of the matrix (basically a grand total for all values in each of the "column grouping" columns). Is there a way to accomplish that? Thank you for your expertise.

|||

I'm not sure I understand what you're asking for.

In order to get row totals on the right side of the matrix, you right click a column and select subtotal.

In order to get column totals at the bottom of the matrix, you right click a row and select subtotal.

|||

You should be able to achieve this is you add a subtotal on the outermost (leftmost) row group.

The reason you sometimes cannot add a subtotal is to do with static groups which don't support subtotals. These are created when you drag more than one column (measure) from your dataset to the details portion of the matrix (rows or columns).

It would help if you put together some sample data and what you would like to achieve as output. Do this in Excel and copy paste it into a post. That way we'll be able to help more affectively.

|||

Adam, you're the greatest!

Adding a subtotal to the leftmost row group did the trick. Thank you so much.

??€?§Q? , matrix wouldn't let me add the subtotal to the bottom right cell (Data), where I needed the totals to show. Adam's suggestion worked. I thought doing that would just add the total for the values of that rowgroup - but now I know better

Thank you for your help, guys.

Adding data from fields to get a total

EX: I have a table for products, and each product has a quantity. How can I add up the QTY field in all the rows to find out the total QTY of all the products.

Any help would be greatly appreciated.

gkc

You don't say whether you're using a GridView or DataGrid (or anything in particular) -

I'm hoping you're using ASP.Net 2.0 and can use the GridView - because this code sample from ASPNet101.com shows how to create a calculated column - not the exact column you want, but the code's the same :
http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=GVFooterTotal

|||Thank you for the help!

Actually, I am not using any controls. I know there must be a way to do this in TSQL, I am just not familiar with it.|||check out the SUM function in SQL.|||Yes! That was exactly what I needed.

Thank you!

Adding data from 2 seperate tables / data sets

Hi There
is it possible to add a total field from table a dataset a to table b
dataset b & finally, is it possible to do a calculation on those results.
(like a percentage of 2 fields)
i have an offered & answered column in one table & a messages played column
in another table with different data set.
i need to add the messages played to the answered column then give the
difference between offered & answered in the form of a percentage.
thanks in advance for assistance.best to do the join and math in the query
"Tango" wrote:
> Hi There
> is it possible to add a total field from table a dataset a to table b
> dataset b & finally, is it possible to do a calculation on those results.
> (like a percentage of 2 fields)
> i have an offered & answered column in one table & a messages played column
> in another table with different data set.
> i need to add the messages played to the answered column then give the
> difference between offered & answered in the form of a percentage.
> thanks in advance for assistance.
>|||Hi, I've the same problem as Tango
But, I can't join and math the query because one has a specific condition
and the other one has an other specific condition.
There's no solution to join 2 data sets?
Thanks in advance
"Antoon" wrote:
> best to do the join and math in the query
> "Tango" wrote:
> > Hi There
> >
> > is it possible to add a total field from table a dataset a to table b
> > dataset b & finally, is it possible to do a calculation on those results.
> > (like a percentage of 2 fields)
> >
> > i have an offered & answered column in one table & a messages played column
> > in another table with different data set.
> >
> > i need to add the messages played to the answered column then give the
> > difference between offered & answered in the form of a percentage.
> >
> > thanks in advance for assistance.
> >|||Not realy, you can put somthing like Fields!x.Value = Sum(Fields!y.Value,
"scope") where x and y are different datasets. But thats probably not enough.
However IMHO, I don't think you could join anything in a report that you
couldn't join in a query.
Select * from
(query1) a,
(query2) b
where a.key = b.key
should do the trick, I would think
"Nicolas BRESSAN" wrote:
> Hi, I've the same problem as Tango
> But, I can't join and math the query because one has a specific condition
> and the other one has an other specific condition.
> There's no solution to join 2 data sets?
> Thanks in advance
> "Antoon" wrote:
> > best to do the join and math in the query
> >
> > "Tango" wrote:
> >
> > > Hi There
> > >
> > > is it possible to add a total field from table a dataset a to table b
> > > dataset b & finally, is it possible to do a calculation on those results.
> > > (like a percentage of 2 fields)
> > >
> > > i have an offered & answered column in one table & a messages played column
> > > in another table with different data set.
> > >
> > > i need to add the messages played to the answered column then give the
> > > difference between offered & answered in the form of a percentage.
> > >
> > > thanks in advance for assistance.
> > >|||thanks,
the problem it's the same database but with different conditions.
I'll try your solution
"Antoon" wrote:
> Not realy, you can put somthing like Fields!x.Value = Sum(Fields!y.Value,
> "scope") where x and y are different datasets. But thats probably not enough.
> However IMHO, I don't think you could join anything in a report that you
> couldn't join in a query.
> Select * from
> (query1) a,
> (query2) b
> where a.key = b.key
> should do the trick, I would think
> "Nicolas BRESSAN" wrote:
> > Hi, I've the same problem as Tango
> >
> > But, I can't join and math the query because one has a specific condition
> > and the other one has an other specific condition.
> >
> > There's no solution to join 2 data sets?
> >
> > Thanks in advance
> >
> > "Antoon" wrote:
> >
> > > best to do the join and math in the query
> > >
> > > "Tango" wrote:
> > >
> > > > Hi There
> > > >
> > > > is it possible to add a total field from table a dataset a to table b
> > > > dataset b & finally, is it possible to do a calculation on those results.
> > > > (like a percentage of 2 fields)
> > > >
> > > > i have an offered & answered column in one table & a messages played column
> > > > in another table with different data set.
> > > >
> > > > i need to add the messages played to the answered column then give the
> > > > difference between offered & answered in the form of a percentage.
> > > >
> > > > thanks in advance for assistance.
> > > >|||You could also try subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicolas BRESSAN" <NicolasBRESSAN@.discussions.microsoft.com> wrote in
message news:721271CD-50E6-4C20-A385-6DB090F8584D@.microsoft.com...
> Hi, I've the same problem as Tango
> But, I can't join and math the query because one has a specific condition
> and the other one has an other specific condition.
> There's no solution to join 2 data sets?
> Thanks in advance
> "Antoon" wrote:
>> best to do the join and math in the query
>> "Tango" wrote:
>> > Hi There
>> >
>> > is it possible to add a total field from table a dataset a to table b
>> > dataset b & finally, is it possible to do a calculation on those
>> > results.
>> > (like a percentage of 2 fields)
>> >
>> > i have an offered & answered column in one table & a messages played
>> > column
>> > in another table with different data set.
>> >
>> > i need to add the messages played to the answered column then give the
>> > difference between offered & answered in the form of a percentage.
>> >
>> > thanks in advance for assistance.
>> >|||How we can make a subreport?
"Bruce L-C [MVP]" wrote:
> You could also try subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Nicolas BRESSAN" <NicolasBRESSAN@.discussions.microsoft.com> wrote in
> message news:721271CD-50E6-4C20-A385-6DB090F8584D@.microsoft.com...
> > Hi, I've the same problem as Tango
> >
> > But, I can't join and math the query because one has a specific condition
> > and the other one has an other specific condition.
> >
> > There's no solution to join 2 data sets?
> >
> > Thanks in advance
> >
> > "Antoon" wrote:
> >
> >> best to do the join and math in the query
> >>
> >> "Tango" wrote:
> >>
> >> > Hi There
> >> >
> >> > is it possible to add a total field from table a dataset a to table b
> >> > dataset b & finally, is it possible to do a calculation on those
> >> > results.
> >> > (like a percentage of 2 fields)
> >> >
> >> > i have an offered & answered column in one table & a messages played
> >> > column
> >> > in another table with different data set.
> >> >
> >> > i need to add the messages played to the answered column then give the
> >> > difference between offered & answered in the form of a percentage.
> >> >
> >> > thanks in advance for assistance.
> >> >
>
>|||Subreports work great for a 1 to 1 or a 1 to many relationship of the data.
A sub report is just a regular report with parameters (first get the report
working standalone). Then you drag and drop the report onto your other
report, do a right mouse click and set the parameter mapping. Read up on
subreports in books online.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicolas BRESSAN" <NicolasBRESSAN@.discussions.microsoft.com> wrote in
message news:A9582474-CB42-40D5-A605-3455CAE81518@.microsoft.com...
> How we can make a subreport?
> "Bruce L-C [MVP]" wrote:
>> You could also try subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Nicolas BRESSAN" <NicolasBRESSAN@.discussions.microsoft.com> wrote in
>> message news:721271CD-50E6-4C20-A385-6DB090F8584D@.microsoft.com...
>> > Hi, I've the same problem as Tango
>> >
>> > But, I can't join and math the query because one has a specific
>> > condition
>> > and the other one has an other specific condition.
>> >
>> > There's no solution to join 2 data sets?
>> >
>> > Thanks in advance
>> >
>> > "Antoon" wrote:
>> >
>> >> best to do the join and math in the query
>> >>
>> >> "Tango" wrote:
>> >>
>> >> > Hi There
>> >> >
>> >> > is it possible to add a total field from table a dataset a to table
>> >> > b
>> >> > dataset b & finally, is it possible to do a calculation on those
>> >> > results.
>> >> > (like a percentage of 2 fields)
>> >> >
>> >> > i have an offered & answered column in one table & a messages played
>> >> > column
>> >> > in another table with different data set.
>> >> >
>> >> > i need to add the messages played to the answered column then give
>> >> > the
>> >> > difference between offered & answered in the form of a percentage.
>> >> >
>> >> > thanks in advance for assistance.
>> >> >
>>

Adding Computed Columns Together

I have two computed columns (subtotal1, subtotal2) and I
want to add them together to get a Total. I want to show
it on a data access page that links to a SQL DB. This is
like a Purchase Order Database. Is this possible? What is
the easiest way to do this?
Thanks,
Nate SUse a view or query. Totals and subtotals don't belong in a table.
CREATE VIEW Something_with_totals (col1, col2, total)
AS
SELECT col1, col2, col1+col2
FROM Something
--
David Portas
--
Please reply only to the newsgroup
--

Adding column after Matrix total

<P>Greetings,</P>
<P>I am new to reporting services and am struggling with trying to add a column to the end of matrix report that has totals.&nbsp; You can see a jpg of the report at http://www.catertots.com/matrix.jpg What I need to do is repeat the school code that is in the first column into another column that follows the total.&nbsp; </P>
<P>Any help would be much appreciated.</P>

Hi,

Matrices are a bit difficult when it comes to adding new columns. I can only think of two ways that you could do it (and both are a bit sleazy).

1. Add a 'Total' column within your kid group so that when you group by kid, the total will automatically display. Then when you add your total column within the matrix, name it 'School' and put an IF in the measure textbox to say display school code when not in scope of the column groups. However, there will be a problem in ordering the kid group if there is a kid group greater then 'Total'

2. Add a second matrix, the same as the first and hide alll the column groups and just put it next to the first matrix.

|||Thank you for the reply.

Adding a total column

I have been working on a website in asp.net1.1 in vb.net2003. I am using a sql2000 server. I am attempting to add a column to my datagrid that will add the total number of wins and output the number in that colum. With some help, I have been able to write the code. However, I am not sure where to put it. Is it a sql function I need to call from my code to add to the win column? Thanks for your help.

Hi, maybe you can consider to add a computed column to the table, if all data used for computing the new column is in the same table and you know how to write the function that get the computed result. For example let's say you have create a function to do the computing and return a result:

create function udf_count_KB (@.tx xml)
returns int
begin
declare @.n int
select @.n=@.tx.value('declare namespace x="http://iorijay.com/KBs";
count(/x:KBRec/x:Record)','int')

return @.n
end
go

then you can alter the table like this:

alter table KBs
add KBCount as dbo.udf_count_KB(KBRec)

Then you can access the new added computed column as other columns, and it is possible to create indexes on computed columns (seehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp)