SSRS 2005
I'm relatively new to RS and have a challenge I don't know how to overcome.
I have a report where I need to subtract one group total from another. I
read that this isn't possible in 2005, but that 2008 will support
aggregations on aggregations. I'm sure someone has figured out how to do
this in 2005 though, so any words of wisdom?
This is an Accounting report, where I need to get the value for the
Contribution Margin total. The aggregation I'm looking for is this:
Contribution Margin = sum(Revenue) - sum(Expenses)
The challenge is the the sum of Revenue and Expenses are the same group on
the report, just on different pages if you will. What I really need is the
ability to do a sum with the ability to add criteria, such as:
sum(myValue, where SomeField="Revenue") minus sum(myValue, where
SomeField="Expenses")
I'd appreciate any advice I can get.
Thanks, Andre
|
|
0
|
|
|
|
Reply
|
 Andre
|
4/21/2010 5:38:38 AM |
|
Andre, if you ahve not yet found a solution, try this expression in the Group
footer or table footer below your income/expenses group.
=Sum(IIF(Fields!Type.Value = "Revenue", Fields!Amount.Value,nothing)) -
Sum(IIF(Fields!Type.Value = "Expense", Fields!Amount.Value,nothing))
"Andre" wrote:
> SSRS 2005
>
> I'm relatively new to RS and have a challenge I don't know how to overcome.
> I have a report where I need to subtract one group total from another. I
> read that this isn't possible in 2005, but that 2008 will support
> aggregations on aggregations. I'm sure someone has figured out how to do
> this in 2005 though, so any words of wisdom?
>
> This is an Accounting report, where I need to get the value for the
> Contribution Margin total. The aggregation I'm looking for is this:
>
> Contribution Margin = sum(Revenue) - sum(Expenses)
>
> The challenge is the the sum of Revenue and Expenses are the same group on
> the report, just on different pages if you will. What I really need is the
> ability to do a sum with the ability to add criteria, such as:
> sum(myValue, where SomeField="Revenue") minus sum(myValue, where
> SomeField="Expenses")
>
> I'd appreciate any advice I can get.
>
> Thanks, Andre
>
> .
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
4/26/2010 8:34:01 PM
|
|
|
1 Replies
372 Views
(page loaded in 0.029 seconds)
Similiar Articles: group totals w/criteria? - microsoft.public.sqlserver ...SSRS 2005 I'm relatively new to RS and have a challenge I don't know how to overcome. I have a report where I need to subtract one group total fr... Adding sums on the bottom of rows in Crosstab Queries - microsoft ...group totals w/criteria? - microsoft.public.sqlserver ..... is the ability to do a sum with the ability to add ... Colour Rows in Datasheet based on three criteria ... Not equal to zero - microsoft.public.access.queries... have set up Order ID, Order Date, Cutomers Name, Total ... using the expression builder again in the criteria field ... AS [Outstanding Balance] FROM Orders GROUP BY Orders ... Max Date Query w/ Nulls - microsoft.public.access.queries ...Is there a way to do this with criteria and multiple queries? Or ... tblProperty and only matching tblInspection -- Select View: Totals from the menu bar -- Change Group ... Summing up multiple line items per invoice number (RefNumber ...... the aggregate Sum function for either it creates a grand total ... InvoiceLineItemRefFullName & InvoiceLineDesc in the GROUP BY clause. It is better to have the criteria in a ... Averaging values taken every 30 seconds into 5 minute intervals ...... As AvgOfThrouhput FROM DataRate GROUP BY CLng(288*CDbl([Time_Stamp])) WHERE <whatever criteria you want>; John W ... DateDiff suggestion, I'd try a totals ... How do I count text fields? - microsoft.public.access.queries ...... and put the two descriptions in the Criteria ... In ("PureABL","HYBRID"))) GROUP BY ... Thanks again Tony John W. Vinson wrote: >>Hi Bob that gives me a total of 28 ... I need to sum up some values in a column of a query - microsoft ...I have tried using a total query but I dont have anything for it to group by. ... If you need to exclude some records, you can add criteria to ... Counts number of entries per day in query - microsoft.public ...... AS DayCount FROM [Alarm Data Filtered by Date] GROUP ... You should also move the criteria to a WHERE clause ... Count Unique Values in a Totals Query - microsoft.public ... Creating a sub report with variable number of fields to print ...More details: Crosstab query techniques - row totals ... users - http://allenbrowne.com/tips.html Reply to group ... Issue creating a report from criteria - microsoft.public ... group totals w/criteria? - microsoft.public.sqlserver ...SSRS 2005 I'm relatively new to RS and have a challenge I don't know how to overcome. I have a report where I need to subtract one group total fr... Sum data by using a query - Access - Office.com... want to subtotal all sales by city or by quarter, you use a totals query to group ... Switch back to Design view and, in the Criteria row of the Total Sales Value column ... Find the records with the top or bottom values in a group or field ...To continue with the sample data, in the Criteria row of the Birth Date field ... On the Design tab, in the Show/Hide group, click Totals. The Total row appears in the design ... Total Quality Management--Harrington - Central Washington UniversityUser-based criteria is criteria that is based on what the ... And rounding out the group, Crosby was likened to an ... Instead, minimize total cost. Move toward a single ... Creating Microsoft Access Reports - Free Online TutorialsClick Group & Sort in the Grouping and Totals group. The Group, Sort, and Total pane appears. Add a group. Click Add A Group and then select the field by which you want to ... 7/11/2012 2:05:09 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
3 followers. 