In SSRS, how can I add a row to aggregate all rows that do not match the filter?
I am working on a report that shows transactions grouped by type.
Type Total income
------- --------------
A 575
B 244
C 128
D 45
E 5
F 3
Total 1000
I only want to provide details on the types of transactions that account for more than 10% of total revenue (i.e. AC). I can do this by applying a filter to the group:
Type Total income
------- --------------
A 575
B 244
C 128
Total 1000
What I want to display is one line just above the total line that has a total for all filtered types (i.e. DF sums):
Type Total income
------- --------------
A 575
B 244
C 128
Other 53
Total 1000
Is it possible? I have tried using running totals and conditionally hidden rows inside a group. I tried Iif
inside Sum
. It doesn't seem to do anything that I need, and I'm digressing from scope issues (eg, "value expression has a nested aggregate that defines the scope of the dataset").
If anyone can give me any guidance I would be very grateful.
EDIT: Should point out, but at this point, the dataset actually returns individual transactions:
ID Type Amount
---- ------ --------
1 A 4
2 A 2
3 B 6
4 A 5
5 B 5
Grouping is done using a group of rows in a table.
source to share
One solution is to solve this in the SQL source of your dataset and not inside SSRS:
SELECT
CASE
WHEN CAST([Total income] AS FLOAT) / SUM([Total income]) OVER (PARTITION BY 1) >= 0.10 THEN [Type]
ELSE 'Other'
END AS [Type]
, [Total income]
FROM Source_Table
See also SQL Fiddle
source to share
Try to solve this problem in SQL, see SQL Fiddle .
SELECT I.*
,(
CASE
WHEN I.TotalIncome >= (SELECT Sum(I2.TotalIncome) / 10 FROM Income I2) THEN 10
ELSE 1
END
) AS TotalIncomePercent
FROM Income I
After that create two groups of sums.
- SUM (TotalIncome * TotalIncomePercent) / 10
- SUM (TotalIncome * TotalIncomePercent)
The second approach might be to use a calculated column in SSRS. Try to create a calculated column with the above case statement. If it allows you to create it, you can use it in the same way as the SQL approach.
source to share
1) To show income over 10%, use the line visibility condition, for example = iif (reportitems! Total_income.value / 10 <= I.totalincome, true, false) here reportitems! total_income.value is the sum of all the values of the income text box, which will be the total value of the detail group. and I.totalincome is the value of the current field.
2) add another line outside the detail group to achieve a different income and use an expression like = reportitems! total_income.value-sum (iif (reportitems! total_income.value / 10 <= I.totalincome, I.totalincome, nothing))
source to share