I have read all the threads on ReportItems and Sum within a group but still
can't solve my problem. Really appreciate any help.
I have a report with 3 groups and a report parameter called Recharge as
follows
Hours Hrly Cost/Rate Total Costs/Rates
8 67 £536
32 78 £2, 496
I have created a calculated field for the Hrly CostRate called
Fields!CostRate.value which contains the following expression =iif(
Parameters!recharge.Value =20, Fields!ERes_CostRate.Value ,
Fields!ERes_BillRate.Value)
This works fine and I have managed to get a total for all groups by
referencing the text box for Total Cost/Rates which is called CostRateTotal
out side of the table in the page footer as follows
=sum(ReportItems!CostRateTotal.value) Textbox CostRateTotal reads
=Fields!CostRate.Value *sum( Fields!TL_Hours.Value)
What I cannot get to work is the sub total for group 1. I know this is a
scope issue.
I somehow need a sum of ReportItems!CostRateTotal.
Please can any one help
W: www.optim8.com
Nirvana CRM and Workflow Management based on .netYou shouldn't need to aggregate the textbox at all. You should be able to do
a scoped aggregate on the sum, i.e. Sum(Field, "Scope").
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Stokes" <AndrewStokes@.discussions.microsoft.com> wrote in message
news:47816561-57BD-429D-AA94-FD698323DD31@.microsoft.com...
>I have read all the threads on ReportItems and Sum within a group but still
> can't solve my problem. Really appreciate any help.
> I have a report with 3 groups and a report parameter called Recharge as
> follows
> Hours Hrly Cost/Rate Total Costs/Rates
> 8 67 £536
> 32 78 £2, 496
> I have created a calculated field for the Hrly CostRate called
> Fields!CostRate.value which contains the following expression =iif(
> Parameters!recharge.Value =20, Fields!ERes_CostRate.Value ,
> Fields!ERes_BillRate.Value)
> This works fine and I have managed to get a total for all groups by
> referencing the text box for Total Cost/Rates which is called
> CostRateTotal
> out side of the table in the page footer as follows
> =sum(ReportItems!CostRateTotal.value) Textbox CostRateTotal reads
> =Fields!CostRate.Value *sum( Fields!TL_Hours.Value)
> What I cannot get to work is the sub total for group 1. I know this is a
> scope issue.
> I somehow need a sum of ReportItems!CostRateTotal.
> Please can any one help
>
>
> W: www.optim8.com
> Nirvana CRM and Workflow Management based on .net|||I tried your suggestion for another version of the same report and it worked.
If I sum the hours at the query it should work for me on my summary version.
Thanks for the suggestion and heading me in the right direction. Much
appreciated
--
W: www.optim8.com
Nirvana CRM and Workflow Management based on .net
"Brian Welcker [MSFT]" wrote:
> You shouldn't need to aggregate the textbox at all. You should be able to do
> a scoped aggregate on the sum, i.e. Sum(Field, "Scope").
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Andrew Stokes" <AndrewStokes@.discussions.microsoft.com> wrote in message
> news:47816561-57BD-429D-AA94-FD698323DD31@.microsoft.com...
> >I have read all the threads on ReportItems and Sum within a group but still
> > can't solve my problem. Really appreciate any help.
> >
> > I have a report with 3 groups and a report parameter called Recharge as
> > follows
> >
> > Hours Hrly Cost/Rate Total Costs/Rates
> >
> > 8 67 £536
> > 32 78 £2, 496
> >
> > I have created a calculated field for the Hrly CostRate called
> > Fields!CostRate.value which contains the following expression =iif(
> > Parameters!recharge.Value =20, Fields!ERes_CostRate.Value ,
> > Fields!ERes_BillRate.Value)
> >
> > This works fine and I have managed to get a total for all groups by
> > referencing the text box for Total Cost/Rates which is called
> > CostRateTotal
> > out side of the table in the page footer as follows
> > =sum(ReportItems!CostRateTotal.value) Textbox CostRateTotal reads
> > =Fields!CostRate.Value *sum( Fields!TL_Hours.Value)
> >
> > What I cannot get to work is the sub total for group 1. I know this is a
> > scope issue.
> >
> > I somehow need a sum of ReportItems!CostRateTotal.
> >
> > Please can any one help
> >
> >
> >
> >
> > W: www.optim8.com
> >
> > Nirvana CRM and Workflow Management based on .net
>
>
Showing posts with label sum. Show all posts
Showing posts with label sum. Show all posts
Wednesday, March 7, 2012
ReportItem Nightmare
Labels:
appreciate,
database,
group,
microsoft,
mysql,
nightmare,
oracle,
report,
reportitem,
reportitems,
server,
solve,
sql,
sum,
threads
ReportItem aggregate
I have some data grouped with by a number, On the group footer I have the
sum(col1) and sum(col2) displayed in textbox1, textbox2 etc.
Additionally I need to include sum(col1) - sum(col2) in textbox3.
When I tried to use (ReportItems!textbox1.value) -
(ReportItems!textbox2.value)
It gived an error "Report item expressions can only refer to other report
items within the same grouping scope or a containing grouping scope."
Any advice is welcome.
Thanks.This is probably because textbox3 is in an outer scope (containing grouping
or the table). When the report is processed, you'd get multiple instances of
a group. In an outer scope, we can't determine which inner group instance we
should get the textbox value from. Is this the case in your report?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ni" <Ni@.discussions.microsoft.com> wrote in message
news:EEA615B0-5283-425D-AA32-81E20F460904@.microsoft.com...
>I have some data grouped with by a number, On the group footer I have the
> sum(col1) and sum(col2) displayed in textbox1, textbox2 etc.
> Additionally I need to include sum(col1) - sum(col2) in textbox3.
> When I tried to use (ReportItems!textbox1.value) -
> (ReportItems!textbox2.value)
> It gived an error "Report item expressions can only refer to other report
> items within the same grouping scope or a containing grouping scope."
> Any advice is welcome.
> Thanks.
sum(col1) and sum(col2) displayed in textbox1, textbox2 etc.
Additionally I need to include sum(col1) - sum(col2) in textbox3.
When I tried to use (ReportItems!textbox1.value) -
(ReportItems!textbox2.value)
It gived an error "Report item expressions can only refer to other report
items within the same grouping scope or a containing grouping scope."
Any advice is welcome.
Thanks.This is probably because textbox3 is in an outer scope (containing grouping
or the table). When the report is processed, you'd get multiple instances of
a group. In an outer scope, we can't determine which inner group instance we
should get the textbox value from. Is this the case in your report?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ni" <Ni@.discussions.microsoft.com> wrote in message
news:EEA615B0-5283-425D-AA32-81E20F460904@.microsoft.com...
>I have some data grouped with by a number, On the group footer I have the
> sum(col1) and sum(col2) displayed in textbox1, textbox2 etc.
> Additionally I need to include sum(col1) - sum(col2) in textbox3.
> When I tried to use (ReportItems!textbox1.value) -
> (ReportItems!textbox2.value)
> It gived an error "Report item expressions can only refer to other report
> items within the same grouping scope or a containing grouping scope."
> Any advice is welcome.
> Thanks.
Subscribe to:
Posts (Atom)