Regarding Custom Code using table or list control(data set)

I have the report like this

list1

{

list2

{

table1

table header

table fields

table footer

}

}

now i want to calculate the sum of one field in table when it contains particular value using CUSTOM CODE ONLY

and i want to display it in table footer. please send me answer to this id as soon as possible.

Thanks

Sateesh Maduri

Maybe I'm missing something, but couldn't you just use a conditional aggregation expression like the following in the table footer:

=Sum(iif(Fields!A.Value = "abc", CInt(Fields!B.Value), 0))

-- Robert

|||

Hello Robert

Actually I already know that what u have given using sum().But I need Custom Code for that ,I mean if I have a dataset which fills the data in the table control How should I write Custom Code to calculate sum of one field when it contains particular value.

Thanks

sql

Regarding Custom Code

Hello

I have report like this

list1

list2(includes in list1)

table (includes in list2)

end of list2

end of list1

Now I want to calculate the sum of one column in table when it contains particular value and I want to display in table footer . I already know using sum() fuction.I want to know using custom code calculation for the above problem(How Can I repeat my data set in Custom code function for each row). So , If anyone knows Please reply.

It's Very Urgent

Thanks

Have a look at these

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=541699&SiteID=1

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Regarding Aggregate conditions ..

Hai frns small help needed.

I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.

One special condition is as follows:

For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.

Here is the query is used

select * from sample

id ssn credit flag sem
1 101 0 C9 0
2 101 4 C9 3
3 101 4.5 C9 2
4 101 3.5 C1 1
5 102 4.2 C3 3
6 103 0 C1 2

select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
group by ssn,flag

ssn flag sum_val
101 C1 3.5
103 C1 2.0
102 C3 4.2
101 C9 8.5

The above output is wrong one.

Expected output

101 4.5+3.5=8.0
102 4.2
103 2.0

Any help would be appreciated

Regards,SELECT ssn
, SUM(max_credit)
FROM --MAX credit\ sem per ssn & flag
(SELECT dbo.my_table.ssn
, max_credit = MAX(CASE WHEN credit = 0 THEN sem ELSE credit END)
FROM dbo.my_table
GROUP BY dbo.my_table.ssn
, dbo.my_table.flag) AS mc
GROUP BY ssn|||Actually I think that is flawed. I don't think your sample data is comprehensive enough.|||Thanks, Here is more sample data

101 0 C9 0
101 4 C9 3
101 4.5 C9 2
101 0 C9 2
101 3.5 C1 1
101 3.5 C2 2
104 3.5 C1 3
105 3.5 C2 0
106 3.5 C3 1
107 3.5 C4 1
109 3.5 C6 4
110 3.5 C7 1

Regards,|||SELECT ssn
, SUM(case when max_credit = 0
then sem_for_max_credit
else max_credit end) as daSum
FROM (
SELECT ssn
, flag
, sem as sem_for_max_credit
, credit as max_credit
FROM daTable as T
WHERE credit =
( SELECT MAX(credit)
FROM daTable
WHERE ssn = T.ssn
AND flag = T.flag )
) AS maxes
GROUP
BY ssn|||Thanks for the help.sql

Referencing a text box

Hello,
If I have a text box with the following value : =Sum(Fields!Sales.Value)
Can I then in another text box write something like:
=textbox30.Value + textbox50.Value
Is this possible?Try:
=ReportItems!textbox30.Value + ReportItems!textbox50.Value
--
-- John H. Miller [MSFT]
-- SQL Server BI Product Group
----
--
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
----
--
"ashvsaod" <ashvsaod@.hotmail.com> wrote in message
news:em00MfcdEHA.3412@.TK2MSFTNGP11.phx.gbl...
> Hello,
> If I have a text box with the following value : =Sum(Fields!Sales.Value)
> Can I then in another text box write something like:
> =textbox30.Value + textbox50.Value
> Is this possible?
>|||When I do this I receive the following error:
The value expression for the textbox 'WIP_4' refers to the report item
'Textbox30'. Report item expressions can only refer to other report items
within the same grouping scope or a containing grouping scope.
So what can I do now? I must admit I don't even know what this means.
"John H. Miller" <jhmiller@.online.microsoft.com> wrote in message
news:410994a0$1@.news.microsoft.com...
> Try:
> =ReportItems!textbox30.Value + ReportItems!textbox50.Value
> --
> -- John H. Miller [MSFT]
> -- SQL Server BI Product Group
> ----
--
> --
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> ----
--
> --
> "ashvsaod" <ashvsaod@.hotmail.com> wrote in message
> news:em00MfcdEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > If I have a text box with the following value : =Sum(Fields!Sales.Value)
> >
> > Can I then in another text box write something like:
> >
> > =textbox30.Value + textbox50.Value
> >
> > Is this possible?
> >
> >
>|||You can find some details on how scope works in RS BOL - see
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_1l6b.asp
--
-- John H. Miller [MSFT]
-- SQL Server BI Product Group
----
--
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
----
--
"ashvsaod" <ashvsaod@.hotmail.com> wrote in message
news:u3R3ZBddEHA.1644@.tk2msftngp13.phx.gbl...
> When I do this I receive the following error:
> The value expression for the textbox 'WIP_4' refers to the report item
> 'Textbox30'. Report item expressions can only refer to other report items
> within the same grouping scope or a containing grouping scope.
> So what can I do now? I must admit I don't even know what this means.
> "John H. Miller" <jhmiller@.online.microsoft.com> wrote in message
> news:410994a0$1@.news.microsoft.com...
> > Try:
> >
> > =ReportItems!textbox30.Value + ReportItems!textbox50.Value
> >
> > --
> > -- John H. Miller [MSFT]
> > -- SQL Server BI Product Group
> ----
> --
> > --
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> ----
> --
> > --
> >
> > "ashvsaod" <ashvsaod@.hotmail.com> wrote in message
> > news:em00MfcdEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > Hello,
> > >
> > > If I have a text box with the following value :
=Sum(Fields!Sales.Value)
> > >
> > > Can I then in another text box write something like:
> > >
> > > =textbox30.Value + textbox50.Value
> > >
> > > Is this possible?
> > >
> > >
> >
> >
>|||Yeah, I found it and all is solved. Thanks for all the assistance.
"John H. Miller" <jhmiller@.online.microsoft.com> wrote in message
news:4109b720$1@.news.microsoft.com...
> You can find some details on how scope works in RS BOL - see
>
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_1l6b.asp
> --
> -- John H. Miller [MSFT]
> -- SQL Server BI Product Group
> ----
--
> --
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> ----
--
> --
> "ashvsaod" <ashvsaod@.hotmail.com> wrote in message
> news:u3R3ZBddEHA.1644@.tk2msftngp13.phx.gbl...
> > When I do this I receive the following error:
> >
> > The value expression for the textbox 'WIP_4' refers to the report item
> > 'Textbox30'. Report item expressions can only refer to other report
items
> > within the same grouping scope or a containing grouping scope.
> >
> > So what can I do now? I must admit I don't even know what this means.
> >
> > "John H. Miller" <jhmiller@.online.microsoft.com> wrote in message
> > news:410994a0$1@.news.microsoft.com...
> > > Try:
> > >
> > > =ReportItems!textbox30.Value + ReportItems!textbox50.Value
> > >
> > > --
> > > -- John H. Miller [MSFT]
> > > -- SQL Server BI Product Group
> >
> ----
> > --
> > > --
> > > "This posting is provided 'AS IS' with no warranties, and confers
no
> > > rights."
> >
> ----
> > --
> > > --
> > >
> > > "ashvsaod" <ashvsaod@.hotmail.com> wrote in message
> > > news:em00MfcdEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > Hello,
> > > >
> > > > If I have a text box with the following value :
> =Sum(Fields!Sales.Value)
> > > >
> > > > Can I then in another text box write something like:
> > > >
> > > > =textbox30.Value + textbox50.Value
> > > >
> > > > Is this possible?
> > > >
> > > >
> > >
> > >
> >
> >
>

reference CTE more than once in a SP

Hi All ...

WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)

SELECT x,y,sum(z) from myCTE

SELECT y,z,sum(x) from myCTE

the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?

never mind the semantics of what I am SELECTing, I just want to be able to reference the CTW more than once in my SP

am I trying to use the CTE in a way that was not intended?

The scope of the CTE is a single statement. So you are almost right, second select statement cannot reference CTE defined in the previous statement. You can use CTE more than once, but only in a single statement:

Code Snippet

WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)

SELECT FirstOne.x, FirstOne.y, sum(FirstOne.z)
from myCTE as FirstOne

inner join myCTE as AnotherOne on ...

If you want to refer the resultset in several statements you'll have to create table variable (or temporary table):

Code Snippet

declare @.myTable(x int, y int, z int)
insert @.myTable(x,y,z)
SELECT x,y,z, from myTable

SELECT x, y, sum(z) from @.myTable

SELECT y, z, sum(x) from @.myTable