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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >
>> >
>>
>