Showing posts with label functions. Show all posts
Showing posts with label functions. Show all posts

Monday, March 26, 2012

How to use two aggregate functions in RS 2005

I am not able to use two aggregate functions to display a value in a
table.
Basically it is (Sum of (Sum of X)).
How do we get around this?
Please let me know.
ThanksWhat might work for you is to add a calculated field. On the field list
where you drag and drop from just do a right mouse click, add a field (or
something like that). Then you can do a sum of that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anup" <anupkm@.gmail.com> wrote in message
news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
>I am not able to use two aggregate functions to display a value in a
> table.
> Basically it is (Sum of (Sum of X)).
> How do we get around this?
> Please let me know.
> Thanks
>|||Hi Bruce,
Thank you very much for your reply.
So you mean to say that I need to add that field as a calculated field
instead of a normal field?
Thanks
Anup
Bruce L-C [MVP] wrote:
> What might work for you is to add a calculated field. On the field list
> where you drag and drop from just do a right mouse click, add a field (or
> something like that). Then you can do a sum of that.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Anup" <anupkm@.gmail.com> wrote in message
> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
> >I am not able to use two aggregate functions to display a value in a
> > table.
> > Basically it is (Sum of (Sum of X)).
> >
> > How do we get around this?
> > Please let me know.
> >
> > Thanks
> >|||If you add a calculated field to your dataset (this is from the list of
field, I am not talking about a sql statement here) you can have the
calculated field be an aggregate. Then you can aggregate the calculated
field, getting around your problem. I do this to make things simplier too.
Try it as I mentioned and see if it makes sense in your situation. Adding a
field manually to the field list returned by the query is not very
discoverable.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anup" <anupkm@.gmail.com> wrote in message
news:1165432174.432662.162930@.f1g2000cwa.googlegroups.com...
> Hi Bruce,
> Thank you very much for your reply.
> So you mean to say that I need to add that field as a calculated field
> instead of a normal field?
> Thanks
> Anup
> Bruce L-C [MVP] wrote:
>> What might work for you is to add a calculated field. On the field list
>> where you drag and drop from just do a right mouse click, add a field (or
>> something like that). Then you can do a sum of that.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Anup" <anupkm@.gmail.com> wrote in message
>> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
>> >I am not able to use two aggregate functions to display a value in a
>> > table.
>> > Basically it is (Sum of (Sum of X)).
>> >
>> > How do we get around this?
>> > Please let me know.
>> >
>> > Thanks
>> >
>|||Thanks Bruce. Here is my situation:
I need to display the "Double Sum" Value in the footer of a table.The
details of this table is in a different level of grouping and footer is
at a different level of grouping.
Thanks
Anup
Bruce L-C [MVP] wrote:
> If you add a calculated field to your dataset (this is from the list of
> field, I am not talking about a sql statement here) you can have the
> calculated field be an aggregate. Then you can aggregate the calculated
> field, getting around your problem. I do this to make things simplier too.
> Try it as I mentioned and see if it makes sense in your situation. Adding a
> field manually to the field list returned by the query is not very
> discoverable.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Anup" <anupkm@.gmail.com> wrote in message
> news:1165432174.432662.162930@.f1g2000cwa.googlegroups.com...
> > Hi Bruce,
> >
> > Thank you very much for your reply.
> >
> > So you mean to say that I need to add that field as a calculated field
> > instead of a normal field?
> >
> > Thanks
> > Anup
> >
> > Bruce L-C [MVP] wrote:
> >> What might work for you is to add a calculated field. On the field list
> >> where you drag and drop from just do a right mouse click, add a field (or
> >> something like that). Then you can do a sum of that.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Anup" <anupkm@.gmail.com> wrote in message
> >> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
> >> >I am not able to use two aggregate functions to display a value in a
> >> > table.
> >> > Basically it is (Sum of (Sum of X)).
> >> >
> >> > How do we get around this?
> >> > Please let me know.
> >> >
> >> > Thanks
> >> >
> >

Friday, March 23, 2012

How to use sum and count aggregate functions with xml data type in SQL Server 2005

Hi:
I have xml in sql server 2005 beta2 that is stored as an xml data type and I'm trying to run some aggregate functions like count and sum, but I don't know how to do it. The elements are numeric values, but 'sum' and also 'count' return errors. I have included the error message and the sp below. Any ideas how to use sum and count correctly?

Thanks,

Paul
Msg 8117, Level 16, State 1, Procedure get_vID_FormXML_summary, Line 11

Operand data type varchar is invalid for sum aggregate operator.

--

alter procedure dbo.get_vID_FormXML_summary

@.ID int

as

SELECT FormXML.query(sum('/transactionAmounts/transactionShares/value'))

AS Result

FROM _Company_Info_01_FLAT

WHERE (ID = @.ID)

--GROUP BY DateFiled, FormXML

This looks like a typing issue. Is the XML datatype typed with a schema or untyped? You may also upgrade to RTM (or at least the Sept CTP). The behaviour and error messages have been vastly improved since Beta2. Best regards Michael <kloepper@.discussions.microsoft.com> wrote in message news:032b5300-6b08-4886-8440-fcf708316b00_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi:I have xml in sql server 2005 beta2 that is stored as an xml data type and I'm trying to run some aggregate functions like count and sum, but I don't know how to do it. The elements are numeric values, but 'sum' and also 'count' return errors. I have included the error message and the sp below. Any ideas how to use sum and count correctly?Thanks,PaulMsg 8117, Level 16, State 1, Procedure get_vID_FormXML_summary, Line 11 Operand data type varchar is invalid for sum aggregate operator. --alter procedure dbo.get_vID_FormXML_summary @.ID int as SELECT FormXML.query(sum('/transactionAmounts/transactionShares/value')) AS Result FROM _Company_Info_01_FLAT WHERE (ID = @.ID) --GROUP BY DateFiled, FormXML|||I checked the version again, it is 9.00.1116.00, that might be the Sept CTP.

Anyway, the xml has no schema associted with it, except the dbo schema.

So, what do you suggest now?

Thanks,

Paul|||

Hi.

Subquery or User defined aggregate.
The subquery is easiest:

-- Snippet

USE adventureworks

GO

WITH SubQ AS (

SELECT LineTotal AS Num FROM Sales.SalesOrderDetail

)

Select Sum(Num) FROM SubQ
-- EoSnippet

Of course, LineTotal should be replaced with some XML stuff and a conversion...

Hope this helps

|||Actually, it looks like the 'conversion' (I assume that you mean the type casting error) is what I need help with.

I get the following error using the subquery method...this looks like the error that I got with the first method i tried previously.

So how do I cast this properly? Any literature on the subject?

Thanks,
Paul

USE SEC

GO
WITH SubQ
AS
(SELECT FormXML
AS '/transactionAmounts/transactionShares/value'
FROM _Company_Info_01_FLAT)
SELECT SUM('/transactionAmounts/transactionShares/value')
AS
SumOfValues
FROM SubQ
AS
SubQ_1


Operand data type varchar is invalid for sum aggregate operator.

|||You should use the XML value() method rather than query(). query() returns an XML datatype instance, which as the error implies is not compatible with SUM(). value(), on the other hand, will return a "regular" scalar SQL type.

In your case, you should rewrite the query to be:

SELECT SUM(FormXML.value('(/transactionAmounts/transactionShares/value/text())[1]', 'int'))
AS Result

If you have multiple <value /> tags within a single instance that you are trying to get the sum of, then you can use the XQuery sum() function:

SELECT SUM(FormXML.value('sum(/transactionAmounts/transactionShares/value/text())', 'int'))
AS Result

In the second case, note that the "sum" is within the string literal that is our XQuery expression, because we are invoking the XQuery sum aggregate.

|||

You have two ways to do sums: You can use the XPath/XQuery sum() function inside an XQuery expression that you call through either the query() or as John mentions through the value() method (if you want to cast the value to a SQL value). Also, it depends on whether you want to do the sum over all elements or only all elements within a row.

In your example, you are mixing the metaphors.

And build 9.00.1116.00 is much older than the September CTP (probably the December 04 CTP).

Try the following:

create table SumT(x xml)
go

insert into SumT
select N'<a><b>1</b><b>2</b></a>'
union
select N'<a><b>3</b><b>4</b></a>'
go

select SUM(B.n.value('.', 'decimal'))
from SumT cross apply x.nodes('/a/b') B(n)

select SUM(x.value('sum(/a/b)', 'decimal'))
from SumT

select x.value('sum(/a/b)', 'decimal')
from SumT

Best regards

Michael

|||I want the sum of multiple rows, so this one below works for me.

select SUM(B.n.value('.', 'decimal'))
from SumT cross apply x.nodes('/a/b') B(n)

It does, however, give slightly different results than the first method that John demonstrated. Your second and third method, as well as John's second method return casting errors.

I feel as though I'm looking for a needle in a haystack in trying to find answers looking through the MSDN documentation. Is that the best place, or can you suggest another source or good book that includes the subject of writing xml queries in SQL Server 2005. Perhaps I just need to take a closer look at the MSDN explanations?

Thanks for your help,

Paul
|||John:

Thanks for the help. I'm beginning to understand this a little better, but it's going to take some more work.

I'm just wondering if you have any favorite books or web sites to learn xqueries for SQL Server 2005? I've just been using MSDN2, but it seems to be a struggle to find what I'm looking for.

Paul|||Dear Paul

First, both John's and my queries should work. E.g., the expression I posted I copied from my management studio window after I made sure that they would give me results. If you see errors just running the queries above, I strongly recommend that you upgrade to the September CTP or the RTM version.

Regarding books, and other resources: I recommend our team's weblogs (you can start with mine at http://www.sqljunkies.com/weblog and follow the links to others).

We also have an XQuery whitepaper on MSDN.microsoft.com (linked from me weblog) and the latest versions of Book Online also have quite a bit about the XQuery support.

I hope this helps
Michael

Friday, March 9, 2012

How to use getdate() and user_name() functions with Windows NT login

I am connecting to my SQL Server 2000 database with a Windows NT security
login. Several of my tables require a timestamp and user id for row added.
I use getdate() for the former default and user_name() for the latter. This
works great if I'm using any SQL logins-- however, neither of the functions
returns anything if I am logged in as an NT user.
Any ideas why not, and how to fix?
Thanks,
Randall ArnoldHi, try suser_sname() instead
select suser_sname()
Denis the SQL Menace
http://sqlservercode.blogspot.com/
select suser_sname()
Randall Arnold wrote:
> I am connecting to my SQL Server 2000 database with a Windows NT security
> login. Several of my tables require a timestamp and user id for row added
.
> I use getdate() for the former default and user_name() for the latter. Th
is
> works great if I'm using any SQL logins-- however, neither of the function
s
> returns anything if I am logged in as an NT user.
> Any ideas why not, and how to fix?
> Thanks,
> Randall Arnold|||Thanks, but I'm not using either function in a select statement; I'm placing
them in the Default Value property of the appropriate column. Besides,
getdate() is also not returning anything when using an NT login. This makes
no sense to me.
Randall Arnold
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149860914.178963.188360@.f6g2000cwb.googlegroups.com...
> Hi, try suser_sname() instead
> select suser_sname()
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> select suser_sname()
> Randall Arnold wrote:
>|||Can you post DDL?
You know defaults only get populated on inserts not on updates right?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Randall Arnold wrote:
> Thanks, but I'm not using either function in a select statement; I'm placi
ng
> them in the Default Value property of the appropriate column. Besides,
> getdate() is also not returning anything when using an NT login. This mak
es
> no sense to me.
> Randall Arnold
> "SQL Menace" <denis.gobo@.gmail.com> wrote in message
> news:1149860914.178963.188360@.f6g2000cwb.googlegroups.com...|||Yes, I know the defaults only get populated on Inserts.
As I noted, both functions work 100% as expected when logging in as a SQL
user-- the problem only occurs if I use a Windows NT security login account.
The results are the same for every table using these functions to populate
the default values.
Here's one of the tables in question:
CREATE TABLE [dbo].[L_QUALITY_AUDIT_DETAIL] (
[Detail_ID] [int] NOT NULL ,
[Audit_ID] [int] NOT NULL ,
[User_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Start_TS] [datetime] NOT NULL ,
[Complete_TS] [datetime] NULL ,
[ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Scales_Result] [real] NULL ,
[pid_fAIL_fLAG] [bit] NOT NULL ,
[Fail_Flag] [bit] NOT NULL ,
[Stamp] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Create_TS] [datetime] NOT NULL ,
[Create_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Modify_TS] [datetime] NULL ,
[Modify_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Create_TS uses getdate() to populate default value; Create_UID uses
user_name().
Thanks,
Randall Arnold
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149861709.031952.256660@.g10g2000cwb.googlegroups.com...
> Can you post DDL?
> You know defaults only get populated on inserts not on updates right?
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Randall Arnold wrote:
>|||Never mind-- for some unknown reason it's working now. Go figure!
Randall Arnold
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149861709.031952.256660@.g10g2000cwb.googlegroups.com...
> Can you post DDL?
> You know defaults only get populated on inserts not on updates right?
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Randall Arnold wrote:
>|||Perhaps sunspots? ;-)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Randall Arnold wrote:
> Never mind-- for some unknown reason it's working now. Go figure!
> Randall Arnold
> "SQL Menace" <denis.gobo@.gmail.com> wrote in message
> news:1149861709.031952.256660@.g10g2000cwb.googlegroups.com...|||I can't reproduce, I created the table using Windows Auth, inserted a row
using Windows auth, and inserted a row using sql auth, and both rows show up
populated correctly. I repeated the process by creating the table with SQL
auth, and inserted a row using both, with the same results. Here is my
simplified schema (including the formula for the default values, which you
left out!).
CREATE TABLE [dbo].[L_QUALITY_AUDIT_DETAIL]
(
[Detail_ID] [int] NOT NULL ,
[Create_TS] [datetime] NOT NULL
DEFAULT GETDATE(),
[Create_UID] [varchar] (15) NOT NULL
DEFAULT SUSER_SNAME()
)
GO
INSERT L_Quality_Audit_Detail(Detail_ID) SELECT 1;
GO
Now, in a window using the other security credentials:
INSERT L_Quality_Audit_Detail(Detail_ID) SELECT 2;
GO
SELECT * FROM L_Quality_Audit_Detail;
GO
Can you reproduce your problem with the above code? If so, then that is
peculiar. If not, then either there is a trigger attached to the table
which is messing things up, or there is a problem with the default formulae
or the table itself.
A
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:EIfig.31647$Nb2.577506@.news1.nokia.com...
> Yes, I know the defaults only get populated on Inserts.
> As I noted, both functions work 100% as expected when logging in as a SQL
> user-- the problem only occurs if I use a Windows NT security login
> account. The results are the same for every table using these functions to
> populate the default values.
> Here's one of the tables in question:
> CREATE TABLE [dbo].[L_QUALITY_AUDIT_DETAIL] (
> [Detail_ID] [int] NOT NULL ,
> [Audit_ID] [int] NOT NULL ,
> [User_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Start_TS] [datetime] NOT NULL ,
> [Complete_TS] [datetime] NULL ,
> [ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Scales_Result] [real] NULL ,
> [pid_fAIL_fLAG] [bit] NOT NULL ,
> [Fail_Flag] [bit] NOT NULL ,
> [Stamp] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Create_TS] [datetime] NOT NULL ,
> [Create_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Modify_TS] [datetime] NULL ,
> [Modify_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> Create_TS uses getdate() to populate default value; Create_UID uses
> user_name().
> Thanks,
> Randall Arnold
>
> "SQL Menace" <denis.gobo@.gmail.com> wrote in message
> news:1149861709.031952.256660@.g10g2000cwb.googlegroups.com...
>|||I don't get it myself-- it wasn't working earlier, and just suddenly decided
to play nice. First time I've seen this and hope it's the last...
Randall Arnold
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OlWuVV9iGHA.4660@.TK2MSFTNGP03.phx.gbl...
>I can't reproduce, I created the table using Windows Auth, inserted a row
>using Windows auth, and inserted a row using sql auth, and both rows show
>up populated correctly. I repeated the process by creating the table with
>SQL auth, and inserted a row using both, with the same results. Here is my
>simplified schema (including the formula for the default values, which you
>left out!).
> CREATE TABLE [dbo].[L_QUALITY_AUDIT_DETAIL]
> (
> [Detail_ID] [int] NOT NULL ,
> [Create_TS] [datetime] NOT NULL
> DEFAULT GETDATE(),
> [Create_UID] [varchar] (15) NOT NULL
> DEFAULT SUSER_SNAME()
> )
> GO
> INSERT L_Quality_Audit_Detail(Detail_ID) SELECT 1;
> GO
> Now, in a window using the other security credentials:
> INSERT L_Quality_Audit_Detail(Detail_ID) SELECT 2;
> GO
> SELECT * FROM L_Quality_Audit_Detail;
> GO
> Can you reproduce your problem with the above code? If so, then that is
> peculiar. If not, then either there is a trigger attached to the table
> which is messing things up, or there is a problem with the default
> formulae or the table itself.
> A
>
>
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:EIfig.31647$Nb2.577506@.news1.nokia.com...
>

How to use fields value in code functions

Hello,
I wrote a function in vb.net and i Sent her parameter.
The function is used in a table cell.
I want the function to use a field value,
I tried :
Fileds!myFilesName.Value

But it didn't work.
Can I do this kind of thing?

Thanks.

Are you trying to write or read from a table cell? Or is it from a control in the table cell?

|||

Hi TaTworth.

I'm trying to read the value from the current row in the dataset and write it to the table cell.

I'll will try to explain again:
I have a table in my report.
in one of the cells I have a filed. something like:

=Fields!FieldName.Value

Now, Let say I want to add him the value "2". I will use:

=Fields!FieldName.Value + 2

Now, let say that I want to call a function that will add him the
value "2" and return the result.

I can use the following call:

=Code.AddTwoFunction(Fields!FieldName.Value)

And in my code I will get the Filed value as parameter and add him the
2.

What I wanted to know is if I can call the function with out the
parameter and tell her to automaticlly get the value of the field from
the row she was called?

The reason I need this is that I have a function that need to get 5
fileds and when I call it it looks some thing like:

=Code.AddTwoFunction(Fields!FieldName.Value,Fields!
FieldName2.Value,Fields!FieldName3.Value,Fields!
FieldName4.Value,Fields!FieldName5.Value)

What I would like to do is to call the function from the cell like =Code.AddTwoFunc()

and inside the function use something like:

dim str = Fields!FieldName.Value + 2;

The problem is that it doesn't recognize the"Fields!FieldName.Value"

Hope I explain my self better

|||

Shalom Shabbat AspNetO

You can go two ways - one way is to modify the dataset prior to binding to a datagrid, the other is to set up a suitable table structure to contain all your data cells, giving each cell its own runat="server" and its own id value, you can then read and write from each cell. When reading use a NullToString functions as in http://forums.asp.net/p/1091080/1635818.aspx#1635818 and then cast the string to an integer.

Thus if the contents of tddA1, tddA2 are to be added to give tddA3, then

string sA1 = NullToString(tddA1.InnnerText);
string sA2 = NullToString(tddA2.InnnerText);
int iA1 = System.Convert.ToInt32(sA1);
int iA2 = System.Convert.ToInt32(sA2);
tddA3.InnerText = (iA1 + iA2).ToString();