Grouping question

I have the following query:
SELECT PR_NO,
Total = CASE Items.Use_Item_Calc_Qty
WHEN 0 THEN CONVERT(money, SUM
(items.unit_price * items.qty))
ELSE CONVERT(money, SUM(items.unit_price *
items.qty * ITEM_CALC_QTY))
END
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
The query is returning two records because a record in
the items table has a value of 0 in Use_item_calc_qty and
another record has a value of one.
What I want to return is only one record showing the
total for the Purchase Request. Can anyone help me with
this. I appreciate it.Vic,
I think this is what you wanted to do (your statement of problem is not
quite clear):
SELECT PR_NO,
Total = CONVERT(money, SUM(items.unit_price *
items.qty * (CASE Items.Use_Item_Calc_Qty when 0 then 1 else
Use_Item_Calc_Qty end)))
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
hth
Quentin
"Vic" <vduran@.specpro-inc.com> wrote in message
news:000d01c3c0dc$56b27560$a501280a@.phx.gbl...
> I have the following query:
> SELECT PR_NO,
> Total => CASE Items.Use_Item_Calc_Qty
> WHEN 0 THEN CONVERT(money, SUM
> (items.unit_price * items.qty))
> ELSE CONVERT(money, SUM(items.unit_price *
> items.qty * ITEM_CALC_QTY))
> END
> FROM Items
> where pr_no = 5816
> Group By PR_NO, Use_Item_Calc_Qty
> The query is returning two records because a record in
> the items table has a value of 0 in Use_item_calc_qty and
> another record has a value of one.
> What I want to return is only one record showing the
> total for the Purchase Request. Can anyone help me with
> this. I appreciate it.sql

Grouping problem

I need to provide a summary row at the bottom of each group that sums accounts of a specific type and subtracts it from the group total.

Acct Balance Prior Balance

A 10 5

B 4 8

C 7 6

D 9 12

Total 30 31

exp accts 13 20

Final 17 11

Is there an easy way to do this in reporting services?

So you want to perform a conditional sum based on another field (account type) in the dataset?

You need add some additional rows to the group footer and put an Iif statement inside the Sum that returns the value to aggregate for a match and 0 otherwise e.g.

Sum(Iif(Fields!account_type.Value = "exclude", Fields!Balance.value, 0))

Then in the last row subtract one row from the other.

The following is RDL code for an example:

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="master">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=.;Initial Catalog=master</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>2dd60f19-bf57-4e66-80c3-288d239d3f80</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

<PageWidth>21cm</PageWidth>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>21cm</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Table Name="table1">

<Footer>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox7">

<rd:DefaultName>textbox7</rd:DefaultName>

<ZIndex>5</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>Total</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="balance_1">

<rd:DefaultName>balance_1</rd:DefaultName>

<ZIndex>4</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="prior_balance_1">

<rd:DefaultName>prior_balance_1</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!prior_balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox4">

<rd:DefaultName>textbox4</rd:DefaultName>

<ZIndex>8</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>exc accts</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox5">

<rd:DefaultName>textbox5</rd:DefaultName>

<ZIndex>7</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox6">

<rd:DefaultName>textbox6</rd:DefaultName>

<ZIndex>6</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox8">

<rd:DefaultName>textbox8</rd:DefaultName>

<ZIndex>11</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>Final</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox9">

<rd:DefaultName>textbox9</rd:DefaultName>

<ZIndex>10</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox10">

<rd:DefaultName>textbox10</rd:DefaultName>

<ZIndex>9</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

</TableRows>

</Footer>

<DataSetName>DataSet1</DataSetName>

<Top>1.5cm</Top>

<Width>6.5cm</Width>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="account">

<rd:DefaultName>account</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!account.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="balance">

<rd:DefaultName>balance</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="prior_balance">

<rd:DefaultName>prior_balance</rd:DefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!prior_balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

</TableRows>

</Details>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>14</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>account</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

<rd:DefaultName>textbox2</rd:DefaultName>

<ZIndex>13</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>balance</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>12</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>prior balance</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

</TableRows>

</Header>

<TableColumns>

<TableColumn>

<Width>2cm</Width>

</TableColumn>

<TableColumn>

<Width>1.75cm</Width>

</TableColumn>

<TableColumn>

<Width>2.75cm</Width>

</TableColumn>

</TableColumns>

<Height>3.1746cm</Height>

</Table>

</ReportItems>

<Height>5cm</Height>

</Body>

<rd:ReportID>2e9a4c57-83bc-4a17-9dcd-287d47e72e3d</rd:ReportID>

<LeftMargin>2.5cm</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select 'A' as account, 1 as type, 10 as balance, 5 as prior_balance

union

select 'B', 2, 4, 8

union

select 'C', 1, 7, 6

union

select 'D', 2, 9, 12</CommandText>

<DataSourceName>master</DataSourceName>

</Query>

<Fields>

<Field Name="account">

<rd:TypeName>System.String</rd:TypeName>

<DataField>account</DataField>

</Field>

<Field Name="type">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>type</DataField>

</Field>

<Field Name="balance">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>balance</DataField>

</Field>

<Field Name="prior_balance">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>prior_balance</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>16cm</Width>

<InteractiveHeight>29.7cm</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

Grouping and summing

HI,
I have a group on two fields (ie SalesPersonId and CustomerID). I want
to print a total after any of this values change (ie. If CustomerId
changes, a total line for the customerID will be printed but not for
the SalesPersonId). How Can I do it with running values ?
Best regards=runningvalue(sum, Fields!xxx.value, "CustomerID")
where "CustomerID" is the name for your customer group. I would make
sure to name your group something other than "table1_Group1" for
clarification purposes.
Fab wrote:
> HI,
> I have a group on two fields (ie SalesPersonId and CustomerID). I want
> to print a total after any of this values change (ie. If CustomerId
> changes, a total line for the customerID will be printed but not for
> the SalesPersonId). How Can I do it with running values ?
> Best regards

Grouping a report

I am trying to group a report like so:
Company: ABC
Trip # Leg Amount
1 1 100
1 2 200
Sub Total 300
Trip # Leg Amount
2 1 100
2 2 200
Sub Total 300
Company: DEF
Trip # Leg Amount
3 1 100
3 2 200
Sub Total 300
Trip # Leg Amount
4 1 100
4 2 200
Sub Total 300
I get get the detail info in the report just fine with a grid. But
I'm running into problems when trying to add the company into the
grid. Currently I have a group created to get the sub total for each
trip and that works fine. But when I create the group to add the
company into the grid, it keeps putting the header columns (Trip #,
Leg, Amount, etc) above the company name. I have tried everything I
can think of to get the company name above it but can't get it to work.if you right click on the group row's headers(the grey part on the far
left when you have the table focused) you can insert a new group.
have a group with the company as the expression. then another with
the trip as the expression.
* company
* trip
** DETAILS|||I got it to work doing that and putting the column header row inside
my group row (instead of in a header row). But now I have another
question - not sure if this is possible, but since I am in a grid, my
Company name ends up in the same column as my trip number. The
company name is obviously going to be bigger than the trip number, but
if I stretch out the column to handle that, then the leg box looks
much larger than it needs to be (I'll try to show it here, but it'll
be tough):
Is there some way I can change the spacing on the company column
without affecting the leg column?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x Company: xx ABCDEFGHIJKLMNOPQ xx x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x Trip # xx Leg xx Amount x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x 1 xx 1 xx
100 x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x 1 xx 2 xx
200 x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x xx Sub Total xx 300
x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Group Wise No. of Pages.

Hi iam using count of pages for each group,
but it gives me a wrong no. of pages. i.e. for a group of 4 pages, for the first page total pages displayed is TOTAL PAGES:1 and for the other 3 pages it displays as TOTAL PAGES:3

Page 1

Group Name : xxx Page 1 of 1

------
Page 2

Group Name : xxx Page 1 of 3

------
Page 3

Group Name : xxx Page 2 of 3 so on...

any suggestions are appreciated.
thanks in advance.This is for CR 8.5 using RDC...

Make sure ResetPageNumberAfter is set to True for only the last Group Footer Section.|||Yes, Iam using CR 8.5,

Iam printing TOTAL PAGES in PAGE FOOTER, I have changed "Reset Page Number after" option. but still Iam getting the same result. anything else to do, please help me out.
thanks again.

Group Total..

Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
>
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
>
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as = > >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>
>Custom field is available when you right click on the fields window in
Report Designer and click add. Type whatever expression you want. For
recursive functions, you add the keyword recursive as the last parameter of
the aggregate, i.e. Sum(Fields!Sales.Value, "Scope", recursive).
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newmem" <""> wrote in message
news:%23FAJuQRXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Thanks Brian.
> Can you give me an example of using a custom field and using the Recusrive
> keyword? If there is a sample in BOL, then pls provide any reference/links
> (I wasn't able to locate any help on this topic)
> appreciate it.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
>> While I appreciate your eagerness to help, I think that most people would
>> prefer that you refrain from advertising other products in a forum
> dedicated
>> to SQL Server Reporting Services. If you start a SIMX newsgroup, I
>> promise
>> not to post there. :)
>> That being said, you should be able to define a custom field that does
>> the
>> calculation and then referce the custom field in a sum in the group
> footer.
>> Presumably, you only need to add values from the inner group as the outer
>> group is just summary. If you want it to do parent / child hierarcy
>> aggregates, you need to use the recursive keyword.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Brian" <Brian@.discussions.microsoft.com> wrote in message
>> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>> >I recently came across a new software, that I think you might want to
> look
>> >into.
>> >
>> > www.simx.com/simx/home_report%20manager.htm
>> >
>> > Works with SQL Server, and I was able to do reporting much like what
>> > you
>> > are describing.
>> >
>> > "newmem" <"" wrote:
>> >
>> >> I'm working on a Financial Report which contains a column "XYZ" , its
>> >> value
>> >> is calculated from a formula by passing the row's record id and
>> >> commission
>> >> rate. (the formula is inside a custom dll). The values are correctly
>> >> computed. Now, the footer should display the total of all the rows in
> the
>> >> group.
>> >> for instance:
>> >>
>> >> "Unit" "BrandName" "XYZ Total" "Comments"
>> >> Sodas
>> >> Pepsi $361,000 gfyeefyefffee
>> >> Coca Cola $475,250 djfdfjdfddddd
>> >> RCola $28,757 re8reruejreerr
>> >>
>> >> fdfsfnfsfssf
>> >> _________________________________________
>> >> Total: $ 865,007
>> >>
>> >> Each of the "XYZ Total" in the above example, uses an expression as =>> >> FindTotal(recID!value, comm_rate!value)
>> >> In this case, how do I get the total in the footer? How to recursively
>> >> add
>> >> the FindTotal expression when it contains the row's unique record id?
>> >>
>> >> Thanks
>> >>
>> >> P.S. The above data is a sample data. The actual report contains 3
>> >> different
>> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> >> Transaction Title
>> >>
>> >>
>> >>
>>
>|||I was able to add the custom field and assign the expression for the
calculation with the inner group as scope.
Hwever, I need to sum unique dollar amount for each of the first row in
the inner group. Right now , its adding all the dollar amounts in the
group1.
The sample data below would give a better overview.
The database contains different rec IDs for the "Regular" and the "Diet"
Category. But the report should display the amounts only once, if the
category exitss for a soda then only the name of the category is displayed
in the second level.
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
=> Group1
Pepsi Regular $361,000 gfyeefyefffee
=> Group2
Diet
ffrfrefegfegegeg => Group 3
_____________
Pepsi Totals: $361,000
Coca Cola Regular $475,250 djfdfjdfddddd
Diet
tefdnfdfdjgdgdgd
Vanila $20,000
dewrwrwrwrwrwr
__________
Coca Cola Totals: $495,250
RCola $28,757
re8reruejreerr
==============================================Soda Totals: $885,007
When I use the custom expression to get the group total, for example in case
of "Pepsi", i get the total as $ 722,000. whch is incorrect.
Can you suggest a way to handle this case?
Thanks. Appreciate all the help.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:uyOAmreXEHA.736@.TK2MSFTNGP10.phx.gbl...
> Custom field is available when you right click on the fields window in
> Report Designer and click add. Type whatever expression you want. For
> recursive functions, you add the keyword recursive as the last parameter
of
> the aggregate, i.e. Sum(Fields!Sales.Value, "Scope", recursive).
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "newmem" <""> wrote in message
> news:%23FAJuQRXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Thanks Brian.
> > Can you give me an example of using a custom field and using the
Recusrive
> > keyword? If there is a sample in BOL, then pls provide any
reference/links
> > (I wasn't able to locate any help on this topic)
> >
> > appreciate it.
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> >> While I appreciate your eagerness to help, I think that most people
would
> >> prefer that you refrain from advertising other products in a forum
> > dedicated
> >> to SQL Server Reporting Services. If you start a SIMX newsgroup, I
> >> promise
> >> not to post there. :)
> >>
> >> That being said, you should be able to define a custom field that does
> >> the
> >> calculation and then referce the custom field in a sum in the group
> > footer.
> >> Presumably, you only need to add values from the inner group as the
outer
> >> group is just summary. If you want it to do parent / child hierarcy
> >> aggregates, you need to use the recursive keyword.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> >> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >> >I recently came across a new software, that I think you might want to
> > look
> >> >into.
> >> >
> >> > www.simx.com/simx/home_report%20manager.htm
> >> >
> >> > Works with SQL Server, and I was able to do reporting much like what
> >> > you
> >> > are describing.
> >> >
> >> > "newmem" <"" wrote:
> >> >
> >> >> I'm working on a Financial Report which contains a column "XYZ" ,
its
> >> >> value
> >> >> is calculated from a formula by passing the row's record id and
> >> >> commission
> >> >> rate. (the formula is inside a custom dll). The values are correctly
> >> >> computed. Now, the footer should display the total of all the rows
in
> > the
> >> >> group.
> >> >> for instance:
> >> >>
> >> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> >> Sodas
> >> >> Pepsi $361,000 gfyeefyefffee
> >> >> Coca Cola $475,250 djfdfjdfddddd
> >> >> RCola $28,757 re8reruejreerr
> >> >>
> >> >> fdfsfnfsfssf
> >> >> _________________________________________
> >> >> Total: $ 865,007
> >> >>
> >> >> Each of the "XYZ Total" in the above example, uses an expression as
=> >> >> FindTotal(recID!value, comm_rate!value)
> >> >> In this case, how do I get the total in the footer? How to
recursively
> >> >> add
> >> >> the FindTotal expression when it contains the row's unique record
id?
> >> >>
> >> >> Thanks
> >> >>
> >> >> P.S. The above data is a sample data. The actual report contains 3
> >> >> different
> >> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> >> Transaction Title
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>|||Something got mangled in your posting so I don't quite understand what you
are looking for. It seems like you just need to hide the group header row
and drop the group label down to the inner group and set 'hide repeating' on
the group name. And just make sure you are not computing sums in your query.
You can let the report engine do them for you.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newmem" <""> wrote in message news:uqcyNIhXEHA.712@.TK2MSFTNGP11.phx.gbl...
>I was able to add the custom field and assign the expression for the
> calculation with the inner group as scope.
> Hwever, I need to sum unique dollar amount for each of the first row in
> the inner group. Right now , its adding all the dollar amounts in the
> group1.
> The sample data below would give a better overview.
> The database contains different rec IDs for the "Regular" and the "Diet"
> Category. But the report should display the amounts only once, if the
> category exitss for a soda then only the name of the category is displayed
> in the second level.
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> => Group1
> Pepsi Regular $361,000 gfyeefyefffee
> => Group2
> Diet
> ffrfrefegfegegeg => Group 3
> _____________
> Pepsi Totals: $361,000
> Coca Cola Regular $475,250 djfdfjdfddddd
> Diet
> tefdnfdfdjgdgdgd
> Vanila $20,000
> dewrwrwrwrwrwr
> __________
> Coca Cola Totals: $495,250
> RCola $28,757
> re8reruejreerr
> ==============================================> Soda Totals: $885,007
> When I use the custom expression to get the group total, for example in
> case
> of "Pepsi", i get the total as $ 722,000. whch is incorrect.
> Can you suggest a way to handle this case?
> Thanks. Appreciate all the help.
>
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:uyOAmreXEHA.736@.TK2MSFTNGP10.phx.gbl...
>> Custom field is available when you right click on the fields window in
>> Report Designer and click add. Type whatever expression you want. For
>> recursive functions, you add the keyword recursive as the last parameter
> of
>> the aggregate, i.e. Sum(Fields!Sales.Value, "Scope", recursive).
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "newmem" <""> wrote in message
>> news:%23FAJuQRXEHA.1656@.TK2MSFTNGP09.phx.gbl...
>> > Thanks Brian.
>> > Can you give me an example of using a custom field and using the
> Recusrive
>> > keyword? If there is a sample in BOL, then pls provide any
> reference/links
>> > (I wasn't able to locate any help on this topic)
>> >
>> > appreciate it.
>> >
>> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
>> > news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
>> >> While I appreciate your eagerness to help, I think that most people
> would
>> >> prefer that you refrain from advertising other products in a forum
>> > dedicated
>> >> to SQL Server Reporting Services. If you start a SIMX newsgroup, I
>> >> promise
>> >> not to post there. :)
>> >>
>> >> That being said, you should be able to define a custom field that does
>> >> the
>> >> calculation and then referce the custom field in a sum in the group
>> > footer.
>> >> Presumably, you only need to add values from the inner group as the
> outer
>> >> group is just summary. If you want it to do parent / child hierarcy
>> >> aggregates, you need to use the recursive keyword.
>> >>
>> >> --
>> >> Brian Welcker
>> >> Group Program Manager
>> >> SQL Server Reporting Services
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >>
>> >> "Brian" <Brian@.discussions.microsoft.com> wrote in message
>> >> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>> >> >I recently came across a new software, that I think you might want to
>> > look
>> >> >into.
>> >> >
>> >> > www.simx.com/simx/home_report%20manager.htm
>> >> >
>> >> > Works with SQL Server, and I was able to do reporting much like what
>> >> > you
>> >> > are describing.
>> >> >
>> >> > "newmem" <"" wrote:
>> >> >
>> >> >> I'm working on a Financial Report which contains a column "XYZ" ,
> its
>> >> >> value
>> >> >> is calculated from a formula by passing the row's record id and
>> >> >> commission
>> >> >> rate. (the formula is inside a custom dll). The values are
>> >> >> correctly
>> >> >> computed. Now, the footer should display the total of all the rows
> in
>> > the
>> >> >> group.
>> >> >> for instance:
>> >> >>
>> >> >> "Unit" "BrandName" "XYZ Total" "Comments"
>> >> >> Sodas
>> >> >> Pepsi $361,000 gfyeefyefffee
>> >> >> Coca Cola $475,250 djfdfjdfddddd
>> >> >> RCola $28,757 re8reruejreerr
>> >> >>
>> >> >> fdfsfnfsfssf
>> >> >> _________________________________________
>> >> >> Total: $ 865,007
>> >> >>
>> >> >> Each of the "XYZ Total" in the above example, uses an expression as
> =>> >> >> FindTotal(recID!value, comm_rate!value)
>> >> >> In this case, how do I get the total in the footer? How to
> recursively
>> >> >> add
>> >> >> the FindTotal expression when it contains the row's unique record
> id?
>> >> >>
>> >> >> Thanks
>> >> >>
>> >> >> P.S. The above data is a sample data. The actual report contains 3
>> >> >> different
>> >> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> >> >> Transaction Title
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >
>> >
>>
>

Group Total..

I'm working on a Financial Report which contains a column "XYZ" , its value
is calculated from a formula by passing the row's record id and commission
rate. (the formula is inside a custom dll). The values are correctly
computed. Now, the footer should display the total of all the rows in the
group.
for instance:
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
Pepsi $361,000 gfyeefyefffee
Coca Cola $475,250 djfdfjdfddddd
RCola $28,757 re8reruejreerr
fdfsfnfsfssf
_________________________________________
Total: $ 865,007
Each of the "XYZ Total" in the above example, uses an expression as = FindTotal(recID!value, comm_rate!value)
In this case, how do I get the total in the footer? How to recursively add
the FindTotal expression when it contains the row's unique record id?
Thanks
P.S. The above data is a sample data. The actual report contains 3 different
levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
Transaction TitleI recently came across a new software, that I think you might want to look into.
www.simx.com/simx/home_report%20manager.htm
Works with SQL Server, and I was able to do reporting much like what you are describing.
"newmem" <"" wrote:
> I'm working on a Financial Report which contains a column "XYZ" , its value
> is calculated from a formula by passing the row's record id and commission
> rate. (the formula is inside a custom dll). The values are correctly
> computed. Now, the footer should display the total of all the rows in the
> group.
> for instance:
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> Pepsi $361,000 gfyeefyefffee
> Coca Cola $475,250 djfdfjdfddddd
> RCola $28,757 re8reruejreerr
> fdfsfnfsfssf
> _________________________________________
> Total: $ 865,007
> Each of the "XYZ Total" in the above example, uses an expression as => FindTotal(recID!value, comm_rate!value)
> In this case, how do I get the total in the footer? How to recursively add
> the FindTotal expression when it contains the row's unique record id?
> Thanks
> P.S. The above data is a sample data. The actual report contains 3 different
> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> Transaction Title
>
>|||While I appreciate your eagerness to help, I think that most people would
prefer that you refrain from advertising other products in a forum dedicated
to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
not to post there. :)
That being said, you should be able to define a custom field that does the
calculation and then referce the custom field in a sum in the group footer.
Presumably, you only need to add values from the inner group as the outer
group is just summary. If you want it to do parent / child hierarcy
aggregates, you need to use the recursive keyword.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>I recently came across a new software, that I think you might want to look
>into.
> www.simx.com/simx/home_report%20manager.htm
> Works with SQL Server, and I was able to do reporting much like what you
> are describing.
> "newmem" <"" wrote:
>> I'm working on a Financial Report which contains a column "XYZ" , its
>> value
>> is calculated from a formula by passing the row's record id and
>> commission
>> rate. (the formula is inside a custom dll). The values are correctly
>> computed. Now, the footer should display the total of all the rows in the
>> group.
>> for instance:
>> "Unit" "BrandName" "XYZ Total" "Comments"
>> Sodas
>> Pepsi $361,000 gfyeefyefffee
>> Coca Cola $475,250 djfdfjdfddddd
>> RCola $28,757 re8reruejreerr
>> fdfsfnfsfssf
>> _________________________________________
>> Total: $ 865,007
>> Each of the "XYZ Total" in the above example, uses an expression as =>> FindTotal(recID!value, comm_rate!value)
>> In this case, how do I get the total in the footer? How to recursively
>> add
>> the FindTotal expression when it contains the row's unique record id?
>> Thanks
>> P.S. The above data is a sample data. The actual report contains 3
>> different
>> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> Transaction Title
>>|||Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as => >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>

Group Total Summary Help

I hope someone can help me with this one. I can't seem to find a way to solve my problem. I am converting a report from Crystal to RS. In Crystal I am using global variables to keep track of group totals for a final summary. I need a similar result from RS. Data example

Group A PK Field Summary Data Field 1 250 2 300 Group A Total 550 Group B 3 100 4 50 Group B Total 150 Grand Total 700

The underlying query contains detail data and I am using a table with two group levels. All details are hidden.

To calculate the totals at the detail level I need to know what the total value for the entire group is. This leads me to my problem, it is not possible (as far as I can tell) to summarize a summary (I get an error). I have tried using the code window to store variables but the value returns a 0. I found a suggestion here http://msdn2.microsoft.com/en-us/library/bb395166.aspx under Distinct Sum, but I can't call the function using the Sum command given that the formula to calculate the value is already using the sum command. I hope this makes sense.

Thanks,

Simone

Hello Simone,

In your example, I assume that the rows with 1, 2, 3, & 4 are the detail rows?

If so, In your Group A & B footer, put the expression =Sum(Fields!Data.Value)

Then place the same in your Grand Total row, summary column.

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your response. The rows 1, 2, 3, & 4 are actually summary rows of the details. All details are hidden. This is where my problem lies. The details are in the dataset but in the report I have them rolled up. To obtain each summary I have a formula that needs to know the summary for each group to determine the outcome. For example:

Group Summary 1 > 0 Use Formula A

Group Summary 1 <= 0 User Formula B

I can get the correct summary at this level, but I then need to total all of the summary records and obtain a second group total. Finally I need to sum all secondary group totals to obtain a grand total.

Thanks,

Simone

|||

I'm not sure I understand what you are trying to do with the formula, but...

Try using the same expression in your Grand Total summary that you are using in your Group Footer 1 and 2.

Example data:

Fruit Count Date

Apples 35 4/12/07

Apples 10 4/12/07

Apples 15 4/13/07

Apples 10 4/13/07

Pears 5 4/12/07

Pears 16 4/13/07

Pears 4 4/13/07

Plums 30 4/13/07

Here's how I am picturing your table:

GH1

GH2

Details =Fields!Count.Value

GF2 (by Fruit) =Sum(Fields!Count.Value)

GF1 (by Date) =Sum(Fields!Count.Value)

Grand Total =Sum(Fields!Count.Value)

In this case, your report would look like (with the details hidden):

4/12/07

Apples 45

Pears 5

Date Total: 50

4/13/07

Apples 25

Pears 20

Plums 30

Date Total: 75

Grand Total 125

Hope this helps.

Jarret

|||

Thanks again for your detailed answer. You have the right idea with the data, but because the detail data needs the summary for the total group I am not able to carry the expression down. I get an error (can't sum expression with aggregates.). Using your example, we would need to know the summary of total fruit for the day to determine the percentage of daily fruit represented by pears (or apples or oranges). I would then need to summarize all percentages for an overall total. For now I overcame my issue by bringing back group totals in my query. I was just hoping I wouldn't need to do this. In crystal I was able to create a global variable that could be set with each group and displayed at the end. I can't find a way to do this in RS.

|||

Have you try this : RunningValue(Expression, Function, Scope)

|||

I get the same error message:

Aggregate functions cannot be nested inside other aggregate functions.

|||

Maybe this will help. Here is the expression located at the first group level:

=iif(sum(FieldA) < 0
,sum(FieldA)*FieldB
,iif(sum(FieldC) = 0
,0
,iif(sum(FieldD * FieldC)/sum(FieldC) < 0
,(sum(FieldC)-sum(FieldE))*5.25
,(sum(FieldC)-sum(FieldE))*(sum(FieldD * FieldC)/sum(FieldC))
)
)
)

I get the correct result at this level. Now I need to sum all of these values for a grand total. There are no values at the detail level.

|||

Try putting that same exact expression in your other group level, and your report footer (for your grand total).

=iif(sum(FieldA) < 0
,sum(FieldA)*FieldB
,iif(sum(FieldC) = 0
,0
,iif(sum(FieldD * FieldC)/sum(FieldC) < 0
,(sum(FieldC)-sum(FieldE))*5.25
,(sum(FieldC)-sum(FieldE))*(sum(FieldD * FieldC)/sum(FieldC))
)
)
)

Jarret

|||I don't get the right total because the formula depends on the summary of each individual group total and not the overall total. The expression above evaluates each detail record for the first group level and produces a result based on that group's total. I did try to add the scope to the expression in the second group level total, but this resulted in a scope error. Unfortunately, there doesn't seem to be an easy solution but I am extremely grateful for the advice.

Group Total Not working right

Hi,

I have a problem that i cannot figure out how to fix.

I have a sub report that i need to have the group totals in the Page header and i cannot for the life of me remember how to do this.

I have grouped by field 2 which gives me a time for Planned and unplanned events, I need to add up the time of the Planned items and put next to my Downtime Planned Text Box, and then sum the Unplanned and do the same against my unplanned downtime box

Regards SteveExample:
subreport 1 formula:
whileprintingrecords;
shared numbervar x:= sum({table.field})
subreport 2 formula:
whileprintingrecords;
shared numbervar y:= sum({table.field})
Formula in the main report:
whileprintingrecords;
shared numbervar x;
shared numbervar y;
x+y|||just to add some thing. You can use the sum function as

sum({table.field}, "Group on the field");

So you can get the planned and unplanned in the same subreport.|||Very confused with First reply, but will have a go at that, Second reply worked fine fo the same report

Regards

Steve

group subtotal and grand total

how to add group subtotal and grand total in report? i try to add formula Sum(Field!Net_Weight.Value) in group footer and unable repeat footer on each page, it return same total on every pages. I hope to get subtotal on each page by group. the expected result would be like this:

Page1




1.Group 1

Date D/no Net Weight 6/1/2007 A00000100 10.45 6/1/2007 A00000101 10.95 6/1/2007 A00000102 11.45 6/1/2007 A00000103 11.95 6/1/2007 A00000104 12.45 6/1/2007 A00000105 12.95
Subtotal 70.2


Page 2




Date D/no Net Weight 6/1/2007 A00000100 20.15 6/1/2007 A00000101 20.25 6/1/2007 A00000102 20.35 6/1/2007 A00000103 20.45 6/1/2007 A00000104 12.45 6/1/2007 A00000105 12.95
Subtotal 106.6



Grand Total= 176.8





2.Group 2

Date D/no Net Weight 6/1/2007 A00000100 10.45 6/1/2007 A00000101 10.95 6/1/2007 A00000102 11.45 6/1/2007 A00000103 11.95 6/1/2007 A00000104 12 6/1/2007 A00000105 12.95
Subtotal 69.75

anybody know how to do it?

Make sure you are in the Group Footer

FOR Subtotal:

="Display Subtotal Text" & "" & Fields!txtColumn.Value

=Sum(Fields!Balance.Value, "

=Sum(Fields!Balance.Value, "grpNameofGroup")

")

FOR Grandtotal:

=Sum(Fields!Balance.Value)

|||why the subtotal have many equal sigh? shoulnt be just one?
I put sum(Field!Balance.value,"Grpname") in group folder, it return same subtotall one each page in same group category, can the subtotal just sum up the total on textfield withinn the same group in a page?

|||

From the question that you have posted and the example that you have given I think it can be achieved by having 2 groups in 2 different group header. In the detail section put the value of the 2 nd group that you want to display. and at the 2 group footer just put the expression Sum(Field!Net_Weight.Value). For every group you have to set 'Page Break at End'.

I think it will work.

group footer and table footer sums used in % formula

I'm having a hard time understanding how to use my group footer Sum to
be used in a formula with my table footer Sum to get a % (group) of
total (table)? They are both text boxes Sum(Fields!loanamount.Value).
These are not fields that I can grab and put into an expression. How do
I proceed? Thanks for any help.Your expression would be:
=Sum(Fields!loanamount.Value, "group1NAME") / Sum(Fields!loanamount.Value,
"datasetForTABLE") * 100
Where group1NAME is the name of the grouping in which you are summing - this
is called the SCOPE. The name of the dataset that you bound to the TABLE
itself is what you will scope for "datasetForTABLE" (be sure to include the
quotes).
=-Chris
"nancy" <northtexassupply@.yahoo.com> wrote in message
news:1162396774.944155.35460@.i42g2000cwa.googlegroups.com...
> I'm having a hard time understanding how to use my group footer Sum to
> be used in a formula with my table footer Sum to get a % (group) of
> total (table)? They are both text boxes Sum(Fields!loanamount.Value).
> These are not fields that I can grab and put into an expression. How do
> I proceed? Thanks for any help.
>|||Thanks. That helped alot.
Chris Conner wrote:
> Your expression would be:
> =Sum(Fields!loanamount.Value, "group1NAME") / Sum(Fields!loanamount.Value,
> "datasetForTABLE") * 100
> Where group1NAME is the name of the grouping in which you are summing - this
> is called the SCOPE. The name of the dataset that you bound to the TABLE
> itself is what you will scope for "datasetForTABLE" (be sure to include the
> quotes).
> =-Chris
> "nancy" <northtexassupply@.yahoo.com> wrote in message
> news:1162396774.944155.35460@.i42g2000cwa.googlegroups.com...
> > I'm having a hard time understanding how to use my group footer Sum to
> > be used in a formula with my table footer Sum to get a % (group) of
> > total (table)? They are both text boxes Sum(Fields!loanamount.Value).
> > These are not fields that I can grab and put into an expression. How do
> > I proceed? Thanks for any help.
> >

Group By/sub query problems

I'm trying to list salesreps (if they have any sales for a particular
date) with their total sales amounts for a queried date, but when
running this sql string in QueryAnalyzer, it says there is an error
with syntax on Line 1 near "s" :
SELECT o.Rep_ID, o.ID, s.ID, SUM(b.orderamount) AS totalsales,
b.order_ID
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
orders o ON o.Rep_ID = s.ID INNER JOIN
b ON o.ID = b.Deal_ID
GROUP BY d.Rep_ID, d.ID, s.ID, b.order_ID
HAVING (SUM(b.orderamount) > 0)
?
NetSportsYou can only give a subquery one alias, you are trying to give it two
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s
Should be:
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') s
However, b is not in your outer query either.
The absolute best way to get a specific and useful answer is to post proper
specs (DDL, sample data, desired results). Check out
http://www.aspfaq.com/5006 which will help you get more constructive
responses.
"netsports" <ballz2wall@.cox-dot-net.no-spam.invalid> wrote in message
news:3bSdneDoYqzc2p_eRVn_vQ@.giganews.com...
> I'm trying to list salesreps (if they have any sales for a particular
> date) with their total sales amounts for a queried date, but when
> running this sql string in QueryAnalyzer, it says there is an error
> with syntax on Line 1 near "s" :
> SELECT o.Rep_ID, o.ID, s.ID, SUM(b.orderamount) AS totalsales,
> b.order_ID
> FROM (SELECT b.Deal_ID
> FROM btransactions b
> WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
> orders o ON o.Rep_ID = s.ID INNER JOIN
> b ON o.ID = b.Deal_ID
> GROUP BY d.Rep_ID, d.ID, s.ID, b.order_ID
> HAVING (SUM(b.orderamount) > 0)
> ?
> NetSports
>

GROUP BY/ HAVING CLAUSE problem

I'm trying to set up my adhoc query to return just one single record, which is aliased as 'foreign' in my sql statement (which is just the total amount of foreign overseas orders for just one day. All Sale_Type_Ids over 2 [integer datatype] are foreign orders):

SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status = 1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) >= 0)

..but my resultset is returning two records. If I remove the HAVING clause, it will return three records, with one being blank.
?
.netsports

In caculations COUNT (* ) is the only aggregate function in SQL Server that caculates NULL values, so your results will be different if you use COUNT (* ) if any of you columns allow NULLs. Try the link below for more about SQL Server NULLs. Hope this helps.
http://www.akadia.com/services/dealing_with_null_values.html|||i am using four table(ForumMain,ForumThreads,ReplyToThread,Authentication) in my forum.I have 4 asp.net pages in this forum. On the very first page, I am showing the Main category of forums.i.e all forums,last thread posted,total threads so far and the total number of replies to each thead and of course the name of the user who generated or added last thread.
To do this, i am using count function to count the total replies to each thread,RepliesToThread table is doing that(not counting total threads yet),Forum Category field from the ForumMain table,ThreadName from the ForumThreads table and the username from the Authentication table.
I am using Group By clause as well but every time a new thread is added from AddThread.aspx page, the name of the main category which the new thread is added into, is repeated on the main page.
i.e. if I add a new thread in main category DATABASE, and this main category has already one thread, the main page show me like
DATABASE already existing category.
date:25/09/2005
DATABASE new category
date:26/09/2005
rather than it should show me
DATABASE new category
date:26/09/2005
What should I do to avoid this repetition?
Thanks in advance.|||Try the link below and see if GROUP BY with CUBE or ROLLUP operator will help with you problem and some restrictions apply. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp|||Caddre, this linke you providehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp is not providing help to solve my problem.
I am looking forward to more helpful replies from you or anybody else.|||Hi,
You're summingSale_Type_Id values from Orders table. I thinks this is not what you want to get. You can Count theSale_Type_Id values to have the number of orders.

SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS [foreign]
And if your records in Orders table have Sale_Type_Id values greaterthan 2, for each distinct value of Sale_Type_Id you'll get a differentrow.
Because you group your records due to Sale_Type_Id's. Note that if itis 2 or less. You group them as nulls. And remove only the null groupby using the Having clause.
So you still have groups having Sale_Type_Id's greater than 2
I hope it is helpfull
Eralper
http://www.eralper.com

Group by Top # entered in as Parameter

Background: I have a report that groups by Item number and gives adds
up total amount for that item number. What I want to do is have the
user enter in a numeric value as a parameter such as 10, 15, 20, etc
that will then only display the TOP 10, 15, 20, etc (what they entered
in the parameter) total amounts on the report. Can anyone help me out,
Im sure this can be done but it gets tricky with the parameters thrown
in the mix. Any suggestions is much appreciated. Thanks!hi brent
you can do this w/o issue by using a stored procedure as the source dataset
(and having your 'TOP' value included as one of the parameters).
next, you are going to need to supply a dataset for the dropdown:
select '10' as topval
union
select '20' as topval
union
select '3....
if you plan on 'rolling your own' ASP.NET interface, you can preload the
values for the dropdown in HTML.
Rob
"Brent" wrote:
> Background: I have a report that groups by Item number and gives adds
> up total amount for that item number. What I want to do is have the
> user enter in a numeric value as a parameter such as 10, 15, 20, etc
> that will then only display the TOP 10, 15, 20, etc (what they entered
> in the parameter) total amounts on the report. Can anyone help me out,
> Im sure this can be done but it gets tricky with the parameters thrown
> in the mix. Any suggestions is much appreciated. Thanks!
>

GROUP BY syntax

I'm trying to group rows in ItemKey to produce a sum total of prices. Then
update ItemKeyprice. The select statement works, but I get the below error.
Where is my syntax wrong?
thanks
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
error: Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.shank
(untested)
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
Note: Without seeing your table stucture + samle data I could only guess
,it is possible yo9u get an error like
"Subquery returned more than 1 value."
"shank" <shank@.tampabay.rr.com> wrote in message
news:eTrms$l$FHA.1288@.TK2MSFTNGP09.phx.gbl...
> I'm trying to group rows in ItemKey to produce a sum total of prices. Then
> update ItemKeyprice. The select statement works, but I get the below
> error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
>|||shank (shank@.tampabay.rr.com) writes:
> I'm trying to group rows in ItemKey to produce a sum total of prices.
> Then update ItemKeyprice. The select statement works, but I get the
> below error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
The immediate error is the inclusion of ItemKey.KeyNumber in the Select
list. You are assigning RegPrice value, but you are sending it two
values.
However, when you fix that error, you will get another error saying that
subquery returned more than one value.
Here are two ways of writing what I think you want to achieve:
UPDATE ItemKeyPrice
SET RegPrice = (SELECT SUM(ItemKey.Price)
FROM ItemKey
WHERE ItemKey.KeyNumber = ItemKeyPrice.KeyNumber)
UPDATE ItemKeyPrice
SET RegPrice = ik.totprice
FROM ItemKeyPrice ikp
JOIN (SELECT KeyNumber, totprice = SUM(ItemKey.Price)
FROM ItemKey
GROUP BY KeyNumber) AS ik ON ik.KeyNumber = ikp.KeyNumber
The first uses a correlated subquery, and this syntax is by the ANSI
standard and should run on any DBMS. The second uses a FROM clause in
the UPDATE statement. This syntax is particular to MS SQL Server and
Sybase, and thus less portable. This query also includes a derived
table (which is part of ANSI SQL) to compute the sums per key.
While the first syntax is portable, my preference is very strongly
for the second, as it is easier to write and understand, and usually
also gives better performance. Furthermore, assume that you would have
more columns to set, like an average price, min price etc. In this case,
you would have to have several correlated subqueries, but the FROM
solution is very extensible in this regard.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Group By problem, how to?

Hi, The following works:
Select SUM(Amount) As Total, YEAR(TransDate) As TheYear
From SomeTable
Group By YEAR(TransDate)
The following gives an error, I have to group by Year and Month,
Select SUM(Amount) As Total,
YEAR(TransDate) As TheYear,
MONTH(TransDate) As TheMonth
From SomeTable
Group By YEAR(TransDate), MONTH(TransDate)
The problem is that TransDate is now used twice in the Group By clauseChris,
Why is this a problem? If you want one result row for
each year and month combination, you need to group on
year and month, or alternatively, use a single expression
for the year and month in both the select and group by
clauses.
The code you gave works correctly with no error
on SQL Server 2000 and 2005. An alternative with
a single group by item is given below also:
create table SomeTable (
TransDate datetime,
Amount money
)
insert into SomeTable values ('20050403', $100)
insert into SomeTable values ('20050703', $200)
insert into SomeTable values ('20060703', $300)
insert into SomeTable values ('20050708', $400)
Select SUM(Amount) As Total,
YEAR(TransDate) As TheYear,
MONTH(TransDate) As TheMonth
From SomeTable
Group By YEAR(TransDate), MONTH(TransDate)
go
Select
Total,
YEAR(TransMonth) As TheYear,
MONTH(TransMonth) As TheMonth
From (
Select
SUM(Amount) AS Total,
DATEADD(month,DATEDIFF(month,0,TransDate
),0) AS TransMonth
FROM SomeTable
GROUP BY DATEADD(month,DATEDIFF(month,0,TransDate
),0)
) M
GO
drop table SomeTable
Steve Kass
Drew University
Chris Botha wrote:

>Hi, The following works:
>Select SUM(Amount) As Total, YEAR(TransDate) As TheYear
>From SomeTable
>Group By YEAR(TransDate)
>The following gives an error, I have to group by Year and Month,
>Select SUM(Amount) As Total,
> YEAR(TransDate) As TheYear,
> MONTH(TransDate) As TheMonth
>From SomeTable
>Group By YEAR(TransDate), MONTH(TransDate)
>The problem is that TransDate is now used twice in the Group By clause
>
>|||Hi Steve, you are right, it works, must have been a typo somewhere.
Sorry for wasting your time (shrink, shrink, shrink).
Chris.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uIXBMjvOGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Chris,
> Why is this a problem? If you want one result row for
> each year and month combination, you need to group on
> year and month, or alternatively, use a single expression
> for the year and month in both the select and group by
> clauses.
> The code you gave works correctly with no error
> on SQL Server 2000 and 2005. An alternative with
> a single group by item is given below also:
> create table SomeTable (
> TransDate datetime,
> Amount money
> )
> insert into SomeTable values ('20050403', $100)
> insert into SomeTable values ('20050703', $200)
> insert into SomeTable values ('20060703', $300)
> insert into SomeTable values ('20050708', $400)
> Select SUM(Amount) As Total,
> YEAR(TransDate) As TheYear,
> MONTH(TransDate) As TheMonth
> From SomeTable
> Group By YEAR(TransDate), MONTH(TransDate)
> go
> Select
> Total,
> YEAR(TransMonth) As TheYear,
> MONTH(TransMonth) As TheMonth
> From (
> Select
> SUM(Amount) AS Total,
> DATEADD(month,DATEDIFF(month,0,TransDate
),0) AS TransMonth
> FROM SomeTable
> GROUP BY DATEADD(month,DATEDIFF(month,0,TransDate
),0)
> ) M
> GO
> drop table SomeTable
> Steve Kass
> Drew University
> Chris Botha wrote:
>

Group By Problem

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Quote:

Originally Posted by Merio

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id|||

Quote:

Originally Posted by tezza98

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id

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

The problem was solved when i changed the first line with this:
SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_no, SUM(tbl1.amount) As Amount

I needed to put the SUM on tbl1.amount. - I thought that tbl1.amount would be totaled automatically when GROUP BY is used... I was wrong. :0

Thanks for your comment :)

GROUP BY problem

Hi, I'm trying to write a query that gives me the total number of documents that exist in a table and also the total number of
documents that were updated within the last 30 days. I need the new_documents field to contain 0 if no new documents were found. Here's what I have so far:

SELECT b.doc_type,
si_service_code_lookup.code_name,
COUNT(b.doc_type) total_documents,
COUN(b.doc_type) new_documents
FROM (SELECT DISTINCT a.doc_type
FROM (SELECT documents_by_esn_vu.doc_type
FROM documents_by_esn_vu
WHERE documents_by_esn_vu.doc_orig_date
BETWEEN SYSDATE AND (SYSDATE - 30)) a ORDER BY a.doc-type) b,
si_service_code_lookup
WHERE b.doc_type = si_service_code_lookup.code
AND si_service_code_lookup.code_type = 'Parts'
GROUP BY b.doc_type,
si_service_code_lookup.code_name;

Any ideas on this?Try this:

SELECT d.doc_type,
l.code_name,
COUNT(*) total_documents,
SUM(CASE WHEN d.doc_orig_date BETWEEN SYSDATE-30 AND SYSDATE THEN 1 ELSE 0 END) new_documents
FROM si_service_code_lookup l,
documents_by_esn_vu d
WHERE d.doc_type = l.code
AND l.code_type = 'Parts'
GROUP BY d.doc_type,
l.code_name;

Or if CASE doesn't work for your version of Oracle:

SELECT d.doc_type,
l.code_name,
COUNT(*) total_documents,
SUM(DECODE(SIGN(d.doc_orig_date-(SYSDATE-30)),1,1,0)) new_documents
FROM si_service_code_lookup l,
documents_by_esn_vu d
WHERE d.doc_type = l.code
AND l.code_type = 'Parts'
GROUP BY d.doc_type,
l.code_name;|||Thanks for the help. That's exactly what I needed.

Group by MonthEnd

I have the following query which returns a sum of hours for each month
for a specific year.
select month(tDate) as month, sum(hours) as total from tablename
where Year(tDate) = 2005
group by month(tDate)
order by month
This works ok, however, is there a way to specify the month start date
and month end date for the grouping, much like it's possible to set the
first day of the w by setting DateFirst to 1=Monday?
With the query above, it totals hours from the 1st of the month to the
last day of the month. However, our month end date is the last Sunday
of the month and it's start date is the last Sunday of the previous
month + a day (i.e. Monday)
For example, our total hours for 2005 each month should be calculated
from the following dates:-
Jan: Mon 27 Dec 04 - Sun 30 Jan 05
Feb: Mon 31 Jan 05 - Sun 27 Feb 05
Mar: Mon 28 Feb - Sun 27 Mar
Apr: Mon 28 Mar - Sun 24 Apr
May: Mon 25 Apr - Sun 29 May
Jun: Mon 30 May - Sun 26 Jun
Jul: Mon 27 Jun - Sun 31 Jul
Aug: Mon 1 Aug - Sun 28 Aug
Sep: Mon 29 Aug - Sun 25 Sep
Oct: Mon 26 Sep - Sun 30 Oct
Nov: Mon 31 Oct - Sun 27 Nov
Dec: Mon 28 Nov - Sun 25 Dec
I suppose ideally i would need some kind of query to calculate the
monthly period based on the date and group it by that.
Any suggestions are most appreciated.
Thanks in advance.
Dan Williams.dan_williams@.newcross-nursing.com wrote:
> I have the following query which returns a sum of hours for each month
> for a specific year.
> select month(tDate) as month, sum(hours) as total from tablename
> where Year(tDate) = 2005
> group by month(tDate)
> order by month
> This works ok, however, is there a way to specify the month start date
> and month end date for the grouping, much like it's possible to set
> the first day of the w by setting DateFirst to 1=Monday?
>
I think your best course of action is to use an auxiliary calendar table, as
suggested in this article:
http://www.aspfaq.com/show.asp?id=2519
The benefit of using a calendar table as opposed to calculations is that it
will allow indexes on your date fields to be used, resulting in much
better-performing queries.
An auxiliary Numbers table can also be useful in other situations:
http://www.aspfaq.com/show.asp?id=2516
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||That's great. Think this will prove useful in a lot of my existing
code.
I mangaged to create a new calendar table with additional columns to
store a target month and target year column based on each date. I can
then do an inner join on this table based on my dates and group by the
target month.
I'm not entirely convinced of using additional memory to store a
numbers table, but if I come across any additional use of it, i'll be
sure to post. (I have since dropped the numbers table!)
Thanks for you help
Dan

Group By Issue

I was asked to display two fields plus a total counts of the table but only need to group by one field and leave the other field as Display field. For example:

Select A, B, Count(*)
From Table
where ...
Group By B

It won't work on SQL unless you group both fields. Any way to get around this?

Thanks!

J827Think about it, and you'll see that it makes no logical sense.

If value A remains constant for any value B, then go ahead and group by A, as it will not affect your output.

Select A, B, Count(*)
From Table
where ...
Group By A, B

If value A varies for any given value B, then which value are you going to show in your output? You need some type of criteria for deciding. You could, for instance, use the lowest value of A:

Select min(A) as A, B, Count(*)
From Table
where ...
Group By B

I think you need to better define, or at least better explain, what your objective is.|||blindman,

Thank you for your quick response!

The value A actually is coming from different table and it is not a constant for any value B. If not grouping B, they are just part of combination running results based on the business logic but my business partner would like to display both fields in the report for no calculations should be run off of A field. Is this feasible or not?

J827|||Calculate your B totals in a subquery:

select TableA.A, Subquery.B, Subquery.RecordCount
from TableA
left outer join (select B, count(*) as RecordCount from TableB group by B) Subquery
on TableA.B = Subquery.B|||GROUP BY can return one row for each column you specify in the GROUP BY clause, plus any additional aggregates of that group as a column.

So if you have TableA, with columns Title, Sales, Price, a valid GROUP BY would be:

SELECT Title, SUM(Sales) AS Sales, MAX(PRICE) AS MaxPrice, (SUM(SALES) * (SUM(PRICE)) AS Total FROM TableA GROUP BY Title

If the values for ValueB are constant with respect to a specfic value of ValueA, then you can kinda fudge this by using a MIN or MAX function. In which case you don't need to include the column in the GROUP BY clause because MIN and MAX are aggregate functions.