adjusting parameter controls of a report

Hi,
I'm making use of parameters to filter data in an report.
But the parameters appear in fixed-width listboxes in the report. Some
values of the parameters are longer than the width of the listboxes. My
question is how to adjust the width of these parameter listboxes. Any ideas?
Regards, Harry.Hi Harry,
apparently this is not possible.. I had the same question yesterday and I
got the following reply:
"On Jul 17, 3:54 am, mischa <mis...@.discussions.microsoft.com> wrote:
> I have designed a few reports in VS2005 with multi-parameter controls. When I
> view these reports in VS they look fine, however once deployed and viewed in
> the webbrowser (MS Internet Explorer) the drop-down boxes are too small (or
> the font size is too big).
> Is there a way to adjust (resize) the control boxes so you can make them
> larger so it's easier for people to actually read where they can choose
> between?
> thanks,
> mischa
As far as I know, there is not. This has been a common request on this
news group. Hopefully, this will be addressed in SSRS 2008. Otherwise,
you could try to inject CSS; however, I think that styling is only
allowed at the Report Manager page and report border level (though I'm
not sure). Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
"
best regards,
mischa
"Harry2007" wrote:
> Hi,
> I'm making use of parameters to filter data in an report.
> But the parameters appear in fixed-width listboxes in the report. Some
> values of the parameters are longer than the width of the listboxes. My
> question is how to adjust the width of these parameter listboxes. Any ideas?
> Regards, Harry.sql

Adjust values in db base in the value modified on the form?

Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!

Paste your query or the tables involved and explain what you want in new one?|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Ex. #1 - Discount value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (hasn't been changed) -> I need to calculate it then. it becomes $180

Ex. #2 - Discount and TotalSellPrice values are changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (changed to) $150
Then I need to calculate TotalSellPrice since both Discount and TotalSellPrice values have been changed. TotalSellPrice becomes $180

Ex. #3 - TotalSellPrcie value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to calculate it. Discount is 25%
TotalSellPrice $0 (changed to) $150

Ex. #4 - TotalSellPrice value is changed to number bigger then it's original one then I have to keep Discount at 0% don't go negative
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to keep Discount at 0%
TotalSellPrice $0 (changed to) $250|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Whatever I make out from the above problem is, you want to adjust the data according to the changes in the other fields.I think you can easily do it at the front end and send the update query to the database according to its invoice no.
An user can see the store data and update it anytime by changing the different fields.You can do the neccessary calculation at the front end.And pass a simple update query to the database.That will do the trick..|||I think you can easily do it at the front end and send the update query to the database according to its invoice no.
The thing is it would not be efficient from a programming stand point, because I have to grab all the information from the database and store it somewhere in the session, then compare it to what user enters, then create a query dynamically instead of using stored procedure. I need a back end query that will handle all these stuff for me.

Any idea?|||Any idea?

And you need not have to create a stored procedure for such a small calculation.I think so...;)|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.
So...?? You can write the adjustment code in asp page,and simply update the record.Mind it you are not doing anything more than that..so I don't think on the point of efficiency ,use of stored procedure will make any difference...|||After user updates any of writable fields I do update and populate that form again but base on the rules I described before...You think it is efficient or good programming practice to make a call to the database every time a user changes a value on a form?

It's not, and you certainly wouldn't design scalable enterprise applications this way.

Your form should get complete recordsets from the database (even defaults), and should submit complete recordsets to the database.

And frankly, you don't have to store all the detail data to keep track of the new total. NewTotal = OldTotal - OldValue + NewValue. That is just three variables.

Adjust number of decimal places

I have a column used in my report that has values such as:
496.1000
These are money values and thus I require them to be in the format:
496.10
How do I get rid of the two extra zeroes?
(my sql statement actually does it, but they still appear in reporting
services for some reason)
Thanks!Don't worry about the above - I found the answer in the SQL 2005
documentation
For those interested, it was under:
Formatting Numeric and Date Values in a Report
Cheers
On Dec 18, 9:27 am, "David Conte" <davco...@.gmail.com> wrote:
> I have a column used in my report that has values such as:
> 496.1000
> These are money values and thus I require them to be in the format:
> 496.10
> How do I get rid of the two extra zeroes?
> (my sql statement actually does it, but they still appear in reporting
> services for some reason)
> Thanks!

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

Additive or non-additive dimension depending on the measure group

Hi,

I have a dimension called [Year of Account]. For most of my measure groups all values are additive over this dimension - no problem there.

Unfortunately I have one measure group that is NOT additive over this dimension.

I wanted to deal with this by removing the [Year of Account].[All] member from the dimension but I only want to remove it for this one measure group. As far as I know that's not possible. (Or am I wrong?)

Any other ideas about how I should deal with this? Any suggestions welcome.

Thanks

Jamie

I think you are probably right, one possible work around would be to use an MDX script assignment to override the value in the relevant subcube so that the users did not see misleading information.

eg.

([Year of Account].[All] ,MEASUREGROUPMEASURES(MeasureGroupName)) = "NA";

OR

([Year of Account].[All] ,MEASUREGROUPMEASURES(MeasureGroupName)) = null;

Additional JOIN altering values

Can anyone see why I would get different GrossSales values by adding the
JOIN on LaborJobCosts?
****************************************
******
This gives the correct GrossSales values:
DECLARE @.BeginSaleDate datetime
DECLARE @.EndSaleDate datetime
SET @.BeginSaleDate = '6/1/2004'
SET @.EndSaleDate = '6/1/2005'
SELECT
SC.ItemSaleCode,
ISNULL(SUM(ISNULL(S.TotalSaleAmount,0)),0) as GrossSales
FROM Sales S
RIGHT JOIN SaleCodes SC
ON SC.ItemSaleCode = S.ItemSaleCode
WHERE S.CompletedDate BETWEEN @.BeginSaleDate AND @.EndSaleDate
GROUP BY SC.ItemSaleCode
Results (correct):
ItemSaleCode GrossSales
C 373807.08
D 39213.52
P 113303.00
R 204072.92
S 119939.00
W 506886.13
****************************************
******
Now if I add a JOIN for the LaborCosts table, I get incorrect values for my
GrossSales (but the LaborCosts are correct!):
DECLARE @.BeginSaleDate datetime
DECLARE @.EndSaleDate datetime
SET @.BeginSaleDate = '6/1/2004'
SET @.EndSaleDate = '6/1/2005'
SELECT
SC.ItemSaleCode,
ISNULL(SUM(ISNULL(S.TotalSaleAmount,0)),0) as GrossSales,
ISNULL(SUM(ISNULL(LC.LaborCost,0)), 0) AS LaborCosts
FROM Sales S
RIGHT JOIN SaleCodes SC ON SC.ItemSaleCode = S.ItemSaleCode
LEFT OUTER JOIN LaborJobCosts LC ON LC.SalesID = S.SalesID
WHERE S.CompletedDate BETWEEN @.BeginSaleDate AND @.EndSaleDate
GROUP BY SC.ItemSaleCode
Results (inflated and incorrect):
ItemSaleCode GrossSales
C 936678.78
D 29213.52
P 300171.00
R 264072.84
S 207079.00
W 529586.13
****************************************
******
CREATE TABLE [dbo].[Sales] (
[SalesID] [int] IDENTITY (1, 1) NOT NULL ,
[CompletedDate] [smalldatetime] NULL ,
[ItemSaleCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SaleCodes] (
[SaleCodeID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemSaleCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JobType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LaborJobCosts] (
[LaborCostsID] [int] IDENTITY (1, 1) NOT NULL ,
[SalesID] [int] NULL ,
[LaborCost] [money] NULL
) ON [PRIMARY]
GOIt looks like SalesId isn't unique in the LaborJobCosts table. Try the
following query. Notice that RIGHT JOIN is redundant in your original - the
WHERE clause turns it into an INNER JOIN anyway. I've also taken out the
ISNULLs from inside the SUM functions - they don't achieve anything except
possibly slow things down.
It helps if you include keys and constraints with your DDL and also post
INSERT statements for some sample data. What you did post tells us that the
LaborJobCosts doesn't have any key other than the IDENTITY column. That's a
potentially serious design flaw.
SELECT SC.itemsalecode,
ISNULL(SUM(S.totalsaleamount),0) AS grosssales,
ISNULL(SUM(LC.laborcost),0) AS laborcosts
FROM Sales S
JOIN SaleCodes SC
ON SC.itemsalecode = S.itemsalecode
LEFT JOIN
(SELECT salesid, SUM(laborcost) AS laborcost
FROM LaborJobCosts
GROUP BY salesid) AS LC
ON S.salesid = LC.salesid
WHERE S.completeddate BETWEEN @.beginsaledate AND @.endsaledate
GROUP BY SC.itemsalecode
David Portas
SQL Server MVP
--|||hi
you might be getting a lot rows from the query and they are getting
hidden because of the SUM and GROUP BY,
please try to remove the SUM and GROUP BY and see the duplicate rows.
once the duplicate rows are eliminated then u can see the expected
results again
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||David Portas wrote:
> I've also taken out the ISNULLs from inside the SUM functions
> - they don't achieve anything except possibly slow things down.
They MAY achieve something: if there are NULL-s in that column, the
ISNULL from inside the SUM prevents the "Warning: Null value is
eliminated by an aggregate or other SET operation.". For example:
CREATE TABLE Test (
ID int primary key,
Value int NULL
)
SELECT SUM(Value) FROM Test
SELECT SUM(ISNULL(Value,0)) FROM Test
SELECT ISNULL(SUM(Value),0) FROM Test
SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
SET NOCOUNT ON
INSERT INTO Test VALUES (1, 100)
INSERT INTO Test VALUES (2, 100)
INSERT INTO Test VALUES (3, 200)
INSERT INTO Test VALUES (4, null)
SET NOCOUNT OFF
SELECT SUM(Value) FROM Test
SELECT SUM(ISNULL(Value,0)) FROM Test
SELECT ISNULL(SUM(Value),0) FROM Test
SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
I'm not saying that the original poster intended this, nor that it's a
good thing. The warning is harmless most of the times and the best way
to avoid it would be to make that column not nullable. I'm only saying
that the ISNULL-s inside the SUM can make a difference.
Razvan|||You are correct in that the NULL inside the column does prevent the warning.
And it is intended -- I want to ensure I get a value back in the event there
was no value. Alternatively, I could turn the warnings off and/or use
ArithAbort and/or ArithIgnore, but I may just go in and prevent this from
occuring :=)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1122211153.821275.237420@.o13g2000cwo.googlegroups.com...
> David Portas wrote:
> They MAY achieve something: if there are NULL-s in that column, the
> ISNULL from inside the SUM prevents the "Warning: Null value is
> eliminated by an aggregate or other SET operation.". For example:
> CREATE TABLE Test (
> ID int primary key,
> Value int NULL
> )
> SELECT SUM(Value) FROM Test
> SELECT SUM(ISNULL(Value,0)) FROM Test
> SELECT ISNULL(SUM(Value),0) FROM Test
> SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
> SET NOCOUNT ON
> INSERT INTO Test VALUES (1, 100)
> INSERT INTO Test VALUES (2, 100)
> INSERT INTO Test VALUES (3, 200)
> INSERT INTO Test VALUES (4, null)
> SET NOCOUNT OFF
> SELECT SUM(Value) FROM Test
> SELECT SUM(ISNULL(Value,0)) FROM Test
> SELECT ISNULL(SUM(Value),0) FROM Test
> SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
> I'm not saying that the original poster intended this, nor that it's a
> good thing. The warning is harmless most of the times and the best way
> to avoid it would be to make that column not nullable. I'm only saying
> that the ISNULL-s inside the SUM can make a difference.
> Razvan
>|||Yep, I could remove the SUM and the GROUP BY, thus negating the entire
reason for the query in the first place, but I'm trying to get the whole
enchilada so I can use the summed values in a report. However I believe you
are correct in thinking there are duplicate rows being created -- I'll dig
into that further. Thanks.
"Chandra" <chandra@.discussions.hotmail.com> wrote in message
news:%23Pk9DQDkFHA.1968@.TK2MSFTNGP14.phx.gbl...
> hi
> you might be getting a lot rows from the query and they are getting
> hidden because of the SUM and GROUP BY,
> please try to remove the SUM and GROUP BY and see the duplicate rows.
> once the duplicate rows are eliminated then u can see the expected
> results again
> best Regards,
> Chandra
> http://groups.msn.com/SQLResource/
> http://chanduas.blogspot.com/
> ---
> *** Sent via Developersdex http://www.examnotes.net ***|||This is why you guys are MVPs ... that was perfect, giving me precisely the
results I was looking for.
The inner ISNULLS were being used to prevent warnings (as I noted in the
post below), but I am going to clean that up so no NULLS are possible.
Thanks very much for your help.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:mKidneN_kpIx237fRVn-jQ@.giganews.com...
> It looks like SalesId isn't unique in the LaborJobCosts table. Try the
> following query. Notice that RIGHT JOIN is redundant in your original -
> the WHERE clause turns it into an INNER JOIN anyway. I've also taken out
> the ISNULLs from inside the SUM functions - they don't achieve anything
> except possibly slow things down.
> It helps if you include keys and constraints with your DDL and also post
> INSERT statements for some sample data. What you did post tells us that
> the LaborJobCosts doesn't have any key other than the IDENTITY column.
> That's a potentially serious design flaw.
> SELECT SC.itemsalecode,
> ISNULL(SUM(S.totalsaleamount),0) AS grosssales,
> ISNULL(SUM(LC.laborcost),0) AS laborcosts
> FROM Sales S
> JOIN SaleCodes SC
> ON SC.itemsalecode = S.itemsalecode
> LEFT JOIN
> (SELECT salesid, SUM(laborcost) AS laborcost
> FROM LaborJobCosts
> GROUP BY salesid) AS LC
> ON S.salesid = LC.salesid
> WHERE S.completeddate BETWEEN @.beginsaledate AND @.endsaledate
> GROUP BY SC.itemsalecode
> --
> David Portas
> SQL Server MVP
> --
>|||Earl wrote:
> I want to ensure I get a value back in the event there was no value.
As shown by my example, you will get a value even if you DON'T use
ISNULL inside the SUM, as long as you use ISNULL outside the SUM.

> Alternatively, I could turn the warnings off and/or use ArithAbort and/or ArithIgn
ore
Using ArithAbort and/or ArithIgnore will not prevent this warning.
AFAIK, there is no configuration setting to turn off this warning.
Razvan|||> The inner ISNULLS were being used to prevent warnings
Try:
SET ANSI_WARNINGS OFF
David Portas
SQL Server MVP
--

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 various fonts/weights to values in a string

HI
I am creating a form that needs to have strings of text in the text box.
The strings have numbers included and the numbers need to be a different
font. The form is built in a table, so splitting the row will not work.
Example:
Textbox 10 has the following value:
8. Fax Number
The number 8 needs to be arial narrow bold 6.96 and the fax number needs to
be arrial narrow 6.96
Any help would be appreciated.
Thank youHi Susan,
One of the ways to do what you need is to place a rectangle object right in
the textbox of the table. Then place two (or more) individual textboxed
inside of the rectangle that is inside of the table cell. You can set the
font weights on the individual textboxes at that point so:
"8" will be in its own textbox and the Fax Number data field in the other,
but they can be together within a single cell. This might add a little more
work, but it should solve the problem.
Rodney Landrum
"Susan R" <SusanR@.discussions.microsoft.com> wrote in message
news:B55B1631-A886-45A0-AFA5-5533CB87C0BE@.microsoft.com...
> HI
> I am creating a form that needs to have strings of text in the text box.
> The strings have numbers included and the numbers need to be a different
> font. The form is built in a table, so splitting the row will not work.
> Example:
> Textbox 10 has the following value:
> 8. Fax Number
> The number 8 needs to be arial narrow bold 6.96 and the fax number needs
> to
> be arrial narrow 6.96
> Any help would be appreciated.
> Thank you
>sql

Adding values to a parameter that can take multiple values

If I have a Select statement like this in my C# code:

Select * From foods Where foodgroup In (@.foodgroup)

And I want @.foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?

I tried

meat, dairy, fruit

'meat', 'dairy', 'fruit'

but neither worked. Is this possible?

Please search these forums. This question has come up probably 10 times in the last few weeks.

|||

I tried a search and couldn't find anything. Plus the search function here isn't very fast.

But I found the solution after doing a Google search. Thanks...

If anyone stumbles on this post, you can go here for some answers:

http://www.msdner.com/forum/thread144871.html

Adding values of rows in ms sql 2000

I am building a website in asp.net 1.1 with vb.net 2003 which will have the standings of the teams in our baseball league. Below is the database table I have created.

ID(int) home_team (nvarchar) away_team(nvarchar) win_teampf(nvarchar) lose_teampf(nvarchar)

1 Elmwood Murdock 7 2
2 Louisville Manley 4 3
3 Manley Elmwood 9 8

ID is the primary key. What I am attempting to do is add each instance of Elmwood from the win column to output the total number of wins from Elmwood and do the same for Elmwood in the losing team to output the total number of losses. The result will look something like this:

Elmwood: 1 Win 1 Loss. .500

Thanks for your reply.

I don't understand how to determine which team lost and which team won. You have columns for the name of the home team, the name of the away team, the winner's score and the loser's score. From what I can see, there is no indication if the home team or the away team won.|||

Oh, that's true. I can add two more columns. One for the losing team and one for the winning team. Thanks for pointing that out. However, I am still confused on how to add the total number of times a team is in the win column and have that value inside a datagrid.

|||

In SQL, you can sum up the wins and losses. If you can modify your table first with WINS and LOSSES columns, it should be a strsight SQL operation.

Something looks like this:

SELECT TeamID, TeamName, SUM(WINColumn) as Wins, SUM(LOSSColumn) AS Losses , (SUM(WINColumn)/SUM(LOSSColumn)) AS GameRatio

FROM YourTable

GROUP BY TeamID, TeamName

|||Thanks for your help. I appreciate it. I attempted this and was unable to link it to my datagrid. Is this a function I need to call from within sql and then pass to the datagrid? Thanks for your help. I haven't been doing asp.net for very long and this forum has helped a lot.|||You can treat the SUM fields as you would to other columns in your datagrid. In your case WINSColumn, LOSSESColumn and GameRatio are your new columns. If you still have problem, post your code here.

Adding Values in a Text Box

I have multiple values in text boxes based on Summed values. I would like to
add the values that are in the text boxes. What is the ref for Boxes? I
know fields are Fields!.
Here is an example of what I am running it the text boxes. They are in the
group footer. I would like to add them in the report footer, which is in a
different scope.
=IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
Fields!ItemLoss.Value), 0)
--
Thank You, LeoI would like to sum the values not just add them.
Thanks
"TrussworksLeo" wrote:
> I have multiple values in text boxes based on Summed values. I would like to
> add the values that are in the text boxes. What is the ref for Boxes? I
> know fields are Fields!.
> Here is an example of what I am running it the text boxes. They are in the
> group footer. I would like to add them in the report footer, which is in a
> different scope.
> =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> Fields!ItemLoss.Value), 0)
> --
> Thank You, Leo|||Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
you to reference values in a textbox.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> I would like to sum the values not just add them.
> Thanks
> "TrussworksLeo" wrote:
> > I have multiple values in text boxes based on Summed values. I would
like to
> > add the values that are in the text boxes. What is the ref for Boxes?
I
> > know fields are Fields!.
> >
> > Here is an example of what I am running it the text boxes. They are in
the
> > group footer. I would like to add them in the report footer, which is
in a
> > different scope.
> >
> > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > Fields!ItemLoss.Value), 0)
> > --
> > Thank You, Leo|||Yes, But it will not allow me to us a aggregate like sum against it?
Leo
"Bruce Johnson [MSFT]" wrote:
> Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
> you to reference values in a textbox.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> > I would like to sum the values not just add them.
> >
> > Thanks
> >
> > "TrussworksLeo" wrote:
> >
> > > I have multiple values in text boxes based on Summed values. I would
> like to
> > > add the values that are in the text boxes. What is the ref for Boxes?
> I
> > > know fields are Fields!.
> > >
> > > Here is an example of what I am running it the text boxes. They are in
> the
> > > group footer. I would like to add them in the report footer, which is
> in a
> > > different scope.
> > >
> > > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > > Fields!ItemLoss.Value), 0)
> > > --
> > > Thank You, Leo
>
>

adding up values

Hello,
I have a query that returns some transactions I have to look at:
select t.tradeID, ABS(t.volume) as totalVolume, ABS(tr.volume) as
partialVolume, t.symbol
from transactions tr, trades t
where tr.tradeID = t.tradeID AND (tr.isMatched = 0 OR tr.isMatched IS NULL)
Sample Data:
tradeID totalVolume partialVolume Symbol
247 4000 2000 ABC
247 4000 1000 ABC
247 4000 500 ABC
247 4000 500 ABC
248 2000 1000 XYZ
248 2000 1500 XYZ
What I want to do is that add the particalVolume column up to give me the
values 4000 and 1500 in this case.
SO far what I have.
Create Table #tempTable
(
ID numeric
)
INSERT INTO #tempTable Select transactionID from transactions where moniker
IS NULL
declare @.partialVolume int
set @.partialVolume = 0
declare @.tempTradeID int
WHILE Exists(Select ID from #tempTable)
begin
Select @.partialVolume = (volume from transactions Where tradeID = @.tempTradeID + @.partialVolume
END
It doesn't work.For those curious.
I got this to work using the SUM function.
Here is the SQL statement.
select t.tradeID, t.symbol, tr.[transaction], t.volume, sum(tr.volume),
t.accountNumber
from transactions tr, trades t
where tr.symbol = t.symbol AND LEFT(tr.[transaction], 1) =LEFT(t.[transaction], 1) AND tr.date = t.date AND tr.moniker is NULL
group by t.tradeid, t.symbol, tr.[transaction], t.volume, t.accountNumber
"Won Lee" <noemail> wrote in message
news:%23Ytzp90aDHA.2932@.tk2msftngp13.phx.gbl...
> Hello,
> I have a query that returns some transactions I have to look at:
> select t.tradeID, ABS(t.volume) as totalVolume, ABS(tr.volume) as
> partialVolume, t.symbol
> from transactions tr, trades t
> where tr.tradeID = t.tradeID AND (tr.isMatched = 0 OR tr.isMatched IS
NULL)
> Sample Data:
> tradeID totalVolume partialVolume Symbol
> 247 4000 2000 ABC
> 247 4000 1000 ABC
> 247 4000 500 ABC
> 247 4000 500 ABC
> 248 2000 1000 XYZ
> 248 2000 1500 XYZ
>
> What I want to do is that add the particalVolume column up to give me the
> values 4000 and 1500 in this case.
> SO far what I have.
> Create Table #tempTable
> (
> ID numeric
> )
> INSERT INTO #tempTable Select transactionID from transactions where
moniker
> IS NULL
> declare @.partialVolume int
> set @.partialVolume = 0
> declare @.tempTradeID int
> WHILE Exists(Select ID from #tempTable)
> begin
> Select @.partialVolume = (volume from transactions Where tradeID => @.tempTradeID + @.partialVolume
>
> END
> It doesn't work.
>

adding up column values (asp & access 2000)

Hi

I've got a quandry - I have a detailed database that handles advert
orders between a design agency and printers / magazines etc.

I want to add up the total spent by the client and put the results to a

field.

I've actually done that using a query table in access - it should be
quite simple as i can bind the 'total amount' to my table - the only
thing it does not currently do is filter the total based on the month
selected.

For example if you look at
http://www.daneverton.com/dg2data/months/2006-12.asp
The data here is filtered by the issue equaling Dec-2006

The actual order total is 13,622 but the column is showing the total
for all entries to date (a years worth = 422,048)

I'm sure that there is only a basic tweak required but i'm banging my
head over what to do

The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"

Any help gladly received.Hi Dan,

What is the SQL behind: q_monodraught_total ?
Quote:
The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"
I'm thinking you might benefit from a SELECT SUM... query
SELECT SUM(fieldname) FROM table WHERE condition ORDER BY fieldname; SELECT SUM(Age) FROM Persons WHERE Age>20good luck ;o)

Cheers,
Douglas

------------------------

"Dan" <mail@.daneverton.comwrote in message news:1164792444.626863.242620@.h54g2000cwb.googlegr oups.com...
Hi

I've got a quandry - I have a detailed database that handles advert
orders between a design agency and printers / magazines etc.

I want to add up the total spent by the client and put the results to a

field.

I've actually done that using a query table in access - it should be
quite simple as i can bind the 'total amount' to my table - the only
thing it does not currently do is filter the total based on the month
selected.

For example if you look at
http://www.daneverton.com/dg2data/months/2006-12.asp
The data here is filtered by the issue equaling Dec-2006

The actual order total is 13,622 but the column is showing the total
for all entries to date (a years worth = 422,048)

I'm sure that there is only a basic tweak required but i'm banging my
head over what to do

The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"

Any help gladly received.

adding two sum() 'ed values

Hi all,
if I have
select sum(ammount) from claimfinancialpayment where claimid = 10
select sum(ammount) from claimpaymenthistory where claimid = 10
How can I add the two summed values together, when
select sum(ammount) from claimfinancialpayment where claimid = 10
sums three rows,
and
select sum(ammount) from claimpaymenthistory where claimid = 10
sums 5 rows
I was thinking of
declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
set @.tot = @.sum1+@.sum2
Would this be correct or is there an easier or faster way of doing this
THanks
RObertRobert
SELECT SUM(bblala)
FROM
(
select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
UNION ALL
select sum(ammount) from claimpaymenthistory where claimid = 10
) AS Der
"Robert Bravery" <me@.u.com> wrote in message
news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> if I have
> select sum(ammount) from claimfinancialpayment where claimid = 10
> select sum(ammount) from claimpaymenthistory where claimid = 10
> How can I add the two summed values together, when
> select sum(ammount) from claimfinancialpayment where claimid = 10
> sums three rows,
> and
> select sum(ammount) from claimpaymenthistory where claimid = 10
> sums 5 rows
> I was thinking of
> declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
> select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
> select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
> set @.tot = @.sum1+@.sum2
> Would this be correct or is there an easier or faster way of doing this
> THanks
> RObert
>
>|||You should use UNION instead of UNION ALL there.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
> Robert
> SELECT SUM(bblala)
> FROM
> (
> select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
> UNION ALL
> select sum(ammount) from claimpaymenthistory where claimid = 10
> ) AS Der
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
>|||If both SELECTs return the same value, you will lose one of them using UNION
instead of UNION ALL as a duplicate will be discarded.
BTW Uri, you appear to have mistyped the column name for the outer SUM, your
query will error due to it trying to sum column bblala when the only column
is blbla.
SELECT SUM(blabla)
FROM
(
select sum(ammount) as blabla from claimfinancialpayment where claimid =
10 UNION ALL select sum(ammount) from claimpaymenthistory where claimid =
10 ) AS Der
Dan
Roji. wrote on Mon, 6 Mar 2006 15:23:42 +0530:
> You should use UNION instead of UNION ALL there.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uahTBFQQGHA.140@.TK2M
SFTNGP12.phx.gbl...|||Hi ,Dan
Yes , I missed 'a' , bit I think the OP got the idea:-)))
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ed6hgSQQGHA.2496@.TK2MSFTNGP11.phx.gbl...
> If both SELECTs return the same value, you will lose one of them using
> UNION instead of UNION ALL as a duplicate will be discarded.
> BTW Uri, you appear to have mistyped the column name for the outer SUM,
> your query will error due to it trying to sum column bblala when the only
> column is blbla.
> SELECT SUM(blabla)
> FROM
> (
> select sum(ammount) as blabla from claimfinancialpayment where claimid =
> 10 UNION ALL select sum(ammount) from claimpaymenthistory where claimid =
> 10 ) AS Der
>
> Dan
>
> Roji. wrote on Mon, 6 Mar 2006 15:23:42 +0530:
>
>|||Roji
No , you won't get an exected result as I understood the OP
try
USE northwind
select sum(ord)
from
(
select sum(orderid) ord from orders
union --all
select sum(orderid) from orders
) as d
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23Hk5POQQGHA.5400@.TK2MSFTNGP09.phx.gbl...
> You should use UNION instead of UNION ALL there.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
>|||Sorry my mistake.
Infact you should use UNION ALL instead of UN ION :)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eRIcksQQGHA.5116@.TK2MSFTNGP10.phx.gbl...
> Roji
> No , you won't get an exected result as I understood the OP
> try
> USE northwind
> select sum(ord)
> from
> (
> select sum(orderid) ord from orders
> union --all
> select sum(orderid) from orders
> ) as d
>
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%23Hk5POQQGHA.5400@.TK2MSFTNGP09.phx.gbl...
>|||Just for variety, another alternative.
SELECT GrandTotal =
(select sum(ammount) from claimfinancialpayment
where claimid = 10) +
(select sum(ammount) from claimpaymenthistory
where claimid = 10)
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 11:38:44 +0200, "Robert Bravery" <me@.u.com> wrote:

>Hi all,
>if I have
>select sum(ammount) from claimfinancialpayment where claimid = 10
>select sum(ammount) from claimpaymenthistory where claimid = 10
>How can I add the two summed values together, when
>select sum(ammount) from claimfinancialpayment where claimid = 10
>sums three rows,
>and
>select sum(ammount) from claimpaymenthistory where claimid = 10
>sums 5 rows
>I was thinking of
>declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
>select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
>select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
>set @.tot = @.sum1+@.sum2
>Would this be correct or is there an easier or faster way of doing this
>THanks
>RObert
>|||THanks Uri, just what I needed
RObert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
> Robert
> SELECT SUM(bblala)
> FROM
> (
> select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
> UNION ALL
> select sum(ammount) from claimpaymenthistory where claimid = 10
> ) AS Der
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
10
>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 sql values from 2 tables

Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following

table name: DownloadTable

fileName |DownloadSize|

file1 | 45

file2 | 50

file3 | 20

--------------

second table

table name: VideoTable

fileName | VideoSize |

file1 | 40

file2 | 60

file3 | 20

------------

Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following:

SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable

However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me?

Thanks in advance

Regards,

What is the expected output?|||in my real applicaion, the expected value was 3280. However, i got 37624.|||Based on the values you provided how do you arrive at 3280? Is there any business-forumula?|||

well thats the thing. I dont know what i need to do to arrive at the expected value. And when i try to retrieve the sum of the tables indiidually, it works, and i get the expected output. But when it comes time to add up the values of those 2 tables, i get into trouble.

And what is a business-formula?

Thanks for your replies

|||

hervens:

Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following

table name: DownloadTable

fileName |DownloadSize|

file1 | 45

file2 | 50

file3 | 20

--------------

second table

table name: VideoTable

fileName | VideoSize |

file1 | 40

file2 | 60

file3 | 20

------------

Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following:

SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable

However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me?

Is that your exact SQL statement? You've not specified which columns to join on, so your result set contains a row for every possible combination of every column in each table.

Instead, try something like this:

DECLARE @.Total BIGINT

SELECT @.Total = SUM(DownloadSize) FROM DownLoadTable
SELECT @.Total = @.Total + (SELECT SUM(VideoSize) FROM VideoTable)

|||

Hello tmorton, thx for your reply. However, when i pressed "test query" button in the dialog box to try to run it, the following error message poped up in a message box.

"The variable name "@.Total" has already been declared. Variable names must be unique within a query batch or stored procedure"

I also tried modifying the variable name total, but got the same error.

Im really sorry about this, im not much of an sql programmer. Just read a tutorial or 2 about it. Oh, and i copied the code you provided exactly

|||Try This Out......

select t = sum(DownloadSize) + (select sum(VideoSize) from VideoTable) from DownloadSize

I think this should work for u.|||Wow, thank a lot Dhaliwal. Everything is working perfectly right now.

Adding spaces inside text box values

I need to adding spaces inside the expression of a text box (in table header
or in page header).
For example: = "string 1" & " " & "string 2";
this expression returns only one space between string 1 and string 2.
Many thanksHi,
Just use =space(<no of spaces>) e.g space(10)
Amarnath
"Pasquale" wrote:
> I need to adding spaces inside the expression of a text box (in table header
> or in page header).
> For example: = "string 1" & " " & "string 2";
> this expression returns only one space between string 1 and string 2.
> Many thanks|||I have tried this suggest: it functions inside Visual Studio.NET environment
but then
his distribution I have seen only a single space!
How can I solve this issue?
Thanks
"Amarnath" wrote:
> Hi,
> Just use =space(<no of spaces>) e.g space(10)
> Amarnath
> "Pasquale" wrote:
> > I need to adding spaces inside the expression of a text box (in table header
> > or in page header).
> > For example: = "string 1" & " " & "string 2";
> > this expression returns only one space between string 1 and string 2.
> >
> > Many thanks|||Try adding the space inside the string that you need the space like
= "string 1 " & "string 2"; where the space is inserted aftter the 1.
"Pasquale" wrote:
> I need to adding spaces inside the expression of a text box (in table header
> or in page header).
> For example: = "string 1" & " " & "string 2";
> this expression returns only one space between string 1 and string 2.
> Many thanks|||I have used = string1 & " " & string2 and it has worked. But the room
used by the spaces is not the same as you see when you define it. Try to put
much more spaces between the strings and you will see the distance increase.
"Pasquale" wrote:
> I have tried this suggest: it functions inside Visual Studio.NET environment
> but then
> his distribution I have seen only a single space!
> How can I solve this issue?
> Thanks
>
> "Amarnath" wrote:
> > Hi,
> >
> > Just use =space(<no of spaces>) e.g space(10)
> >
> > Amarnath
> >
> > "Pasquale" wrote:
> >
> > > I need to adding spaces inside the expression of a text box (in table header
> > > or in page header).
> > > For example: = "string 1" & " " & "string 2";
> > > this expression returns only one space between string 1 and string 2.
> > >
> > > Many thanks|||I have posted this issue then executing some proofs to put some spaces in a
text box.
I have tried:
- = "string1 " & "string2";
- = "string1" & space(10) & "string2";
- = "string1" & " " & "string2";
- = "string1 " & " " & space(10) & "
string2".
These solutions function inside MS VisualStudio .NET (I see the results by
preview),
BUT NOT FUNCTION AFTER THEIR DISTRIBUTION (I see the results inside Internet
browser).
Many thanks
"PSM" wrote:
> I have used = string1 & " " & string2 and it has worked. But the room
> used by the spaces is not the same as you see when you define it. Try to put
> much more spaces between the strings and you will see the distance increase.
> "Pasquale" wrote:
> > I have tried this suggest: it functions inside Visual Studio.NET environment
> > but then
> > his distribution I have seen only a single space!
> >
> > How can I solve this issue?
> >
> > Thanks
> >
> >
> > "Amarnath" wrote:
> >
> > > Hi,
> > >
> > > Just use =space(<no of spaces>) e.g space(10)
> > >
> > > Amarnath
> > >
> > > "Pasquale" wrote:
> > >
> > > > I need to adding spaces inside the expression of a text box (in table header
> > > > or in page header).
> > > > For example: = "string 1" & " " & "string 2";
> > > > this expression returns only one space between string 1 and string 2.
> > > >
> > > > Many thanks|||The only thing it occur to me is to use non-breaking spaces. You can use
ChrW(160) as nonbreaking space and the Internet browser won't change them.
"Pasquale" wrote:
> I have posted this issue then executing some proofs to put some spaces in a
> text box.
> I have tried:
> - = "string1 " & "string2";
> - = "string1" & space(10) & "string2";
> - = "string1" & " " & "string2";
> - = "string1 " & " " & space(10) & "
> string2".
> These solutions function inside MS VisualStudio .NET (I see the results by
> preview),
> BUT NOT FUNCTION AFTER THEIR DISTRIBUTION (I see the results inside Internet
> browser).
> Many thanks
>
> "PSM" wrote:
> > I have used = string1 & " " & string2 and it has worked. But the room
> > used by the spaces is not the same as you see when you define it. Try to put
> > much more spaces between the strings and you will see the distance increase.
> >
> > "Pasquale" wrote:
> >
> > > I have tried this suggest: it functions inside Visual Studio.NET environment
> > > but then
> > > his distribution I have seen only a single space!
> > >
> > > How can I solve this issue?
> > >
> > > Thanks
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > Hi,
> > > >
> > > > Just use =space(<no of spaces>) e.g space(10)
> > > >
> > > > Amarnath
> > > >
> > > > "Pasquale" wrote:
> > > >
> > > > > I need to adding spaces inside the expression of a text box (in table header
> > > > > or in page header).
> > > > > For example: = "string 1" & " " & "string 2";
> > > > > this expression returns only one space between string 1 and string 2.
> > > > >
> > > > > Many thanks|||Excellent!
This is the solution! Many thanks
"PSM" wrote:
> The only thing it occur to me is to use non-breaking spaces. You can use
> ChrW(160) as nonbreaking space and the Internet browser won't change them.
>
> "Pasquale" wrote:
> > I have posted this issue then executing some proofs to put some spaces in a
> > text box.
> >
> > I have tried:
> > - = "string1 " & "string2";
> > - = "string1" & space(10) & "string2";
> > - = "string1" & " " & "string2";
> > - = "string1 " & " " & space(10) & "
> > string2".
> >
> > These solutions function inside MS VisualStudio .NET (I see the results by
> > preview),
> > BUT NOT FUNCTION AFTER THEIR DISTRIBUTION (I see the results inside Internet
> > browser).
> >
> > Many thanks
> >
> >
> >
> > "PSM" wrote:
> >
> > > I have used = string1 & " " & string2 and it has worked. But the room
> > > used by the spaces is not the same as you see when you define it. Try to put
> > > much more spaces between the strings and you will see the distance increase.
> > >
> > > "Pasquale" wrote:
> > >
> > > > I have tried this suggest: it functions inside Visual Studio.NET environment
> > > > but then
> > > > his distribution I have seen only a single space!
> > > >
> > > > How can I solve this issue?
> > > >
> > > > Thanks
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Just use =space(<no of spaces>) e.g space(10)
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "Pasquale" wrote:
> > > > >
> > > > > > I need to adding spaces inside the expression of a text box (in table header
> > > > > > or in page header).
> > > > > > For example: = "string 1" & " " & "string 2";
> > > > > > this expression returns only one space between string 1 and string 2.
> > > > > >
> > > > > > Many thanks|||Yeah, I just wasted 2 hours on trying to fix this, excellent :-)
In case you were wondering what is going on I can explain...
I have a SQL statement that returns a string of dates delimited by 8 spaces,
eg.
1-Mar-2006 2-Mar-2006 etc.
When in VS.Net IDE the preview window shows the spaces correctly but when
deployed to a website they are displayed as HTML and hence the multiple
spaces are ignored and displayed as a single space.
Try putting in and Reporting services sees the & and converts it to a html
&, so your source looks like , insert scream here.
CharW(160) gets past this and is rendered into HTML by RS as
Thanks again, I will be able to sleep tonight.
John
"Pasquale" wrote:
> Excellent!
> This is the solution! Many thanks
>
> "PSM" wrote:
> > The only thing it occur to me is to use non-breaking spaces. You can use
> > ChrW(160) as nonbreaking space and the Internet browser won't change them.

Adding row with values in script

Hi,

In a dataflow script I can Add an empty row using

OutputBuffer.AddRow().

But how can I put values into the row?

Regards,
HenkHenk,
Intellisense can help you here. If you type "Output0Buffer." a menu will appear and somewhere in there will be the names of the available columns. It makes it all very easy.
Typically the usage would be:

with Output0Buffer.
.AddRow()
.col1 = <value>
.col2 = <value>
end with

You can see an example of this here: http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1877.aspx

-Jamie

Adding Multiple Values into a row/column help

Whats the fastest easiest way to take a select that returns say 4 values for the expression into a single column on defined row

basically I mean i want to do an update to say a persons i dunno ummm places they have traveled and I want it listed like france;usa;germany etc etc and the data would always be in the tables i pull from so I can overwrite the data each time i run it but has to take 3 or more values from a query and put them in separated by say a ; into the same persons coloumn that stores the info.

I did this once before with a cursor and adding a variable to itself with colasce or whatever the command was, but was just wondering if there is a fast way to do this by chance that im not thinking about :P.

Thanks!The following example will collect the values from a column in a select statement and create a delimited list from the values.

This will denormalize the values from the source table into a single column so the destination table will not meet the requirements of first normal form. This may be best used for reporting operations, that said:

Two tables are created, one to hold values for the list, and one where the results are inserted.

Test values are inserted into the test table and then a select statement collects the values. (Example supports only 4000 characters)

--Create Test Table
CREATE TABLE dbo.test (
dataField NVARCHAR(10) NOT NULL,
PRIMARY KEY (dataField)
)
GO

--Create Results Table
CREATE TABLE dbo.testResults (
resultId INT IDENTITY (1,1) NOT NULL,
result NVARCHAR(4000) NOT NULL,
PRIMARY KEY (resultId)
)
GO

--Insert Test Data
INSERT dbo.test (dataField) values ('here')
INSERT dbo.test (dataField) values ('there')
INSERT dbo.test (dataField) values ('everywhere')

--Verify Test Data
SELECT dataField FROM dbo.test

--Retrieve colon delimited list of dataField without a cursor
DECLARE @.collectValues NVARCHAR(4000)
SET @.collectValues = ('')

SELECT
@.collectValues = @.collectValues + dataField + ';'
FROM dbo.test

--Verify delimited list
SELECT @.collectValues

--Insert into result table
INSERT dbo.testResults
(result)
VALUES
(@.collectValues)

--Verify inserted data
SELECT resultId, result FROM dbo.testResults

The last select statement should return the result value:
everywhere;here;there;|||Im confused, this doesnt seem like I could get the results correctly from this, You could just use one single select to get all the data like that from that, but what if this works as above, then how would it diferentiate from members and there intrests. Let me give an exampe. Member1 has intrests of fishing,boating,camping, member2 has intrests of fising,hiking,running, how would i basically convert the below

table one

customer intrest

member1 fishing
member1 boating
member1 camping
member2 fishing
member2 hiking
member2 running

go from that data, to this data

table two

customer intrests
member1 fishing;boating;bamping
member2 fising;hiking;running

I dont think the above example can do this can it? Or Am I just missing something? Thanks! hehe|||You are absolutely correct. I misread your intention as wanting the value for a single person (as though you would add this update to a procedure for updating the base table, etc...).