Reputation:
I basically have the screenshot below as my layout.
My matrix columns are DCG1 and DCG2.
At the end of the Matrix I have a Total Group, which works just find. But I want to find the difference between my first and last value of the group. I've tried everything from ReportItems! to values. I cannot get SSRS to recognize these values.
So basically in the screen shots below. Screen shot 1 is the matrix structure. I have a column group called Test1, I want the first Value of Test1 and the last Value of Test 1 and place that in the Red box.
In screenshot 2, you can see the values i want to compare. The table groupings are named the same as the column + group. So dcs1group/dcs2group
Okay here is the DDL and DML for the datasource
http://pastebin.com/1ySN701D
The pastebin has been removed. Why, not sure so here it is below.
IF EXISTS (SELECT [name] FROM tempdb.sys.tables WHERE [name] LIKE '%tmpHoldingTable%') BEGIN DROP TABLE #tmpHoldingTable; END; CREATE TABLE #tmpHoldingTable ( dcs1 NVARCHAR (50), dcs2 NVARCHAR (50), Total DECIMAL (10, 2), Test1 NVARCHAR (50) ) INSERT INTO #tmpHoldingTable (dcs1, dcs2, Total, Test1) VALUES ('Contract', 'Breach of Contract', 500.00, '01/01/2013-12/31/2013'), ('Contract', 'Breach of Contract', 300.00, '01/01/2014-12/31/2014'), ('Employment', 'Discrimination', 500.00, '01/01/2013-12/31/2013'), ('Employment', 'Discrimination', 300.00, '01/01/2014-12/31/2014'), ('Employment', 'Research', 500.00, '01/01/2013-12/31/2013'), ('Employment', 'Research', 300.00, '01/01/2014-12/31/2014') SELECT * FROM #tmpHoldingTable;
Upvotes: 8
Views: 22419
Reputation: 39606
Yes, this is possible, but as you can see it is a bit complicated.
To make this a more generic answer, I have created my own DataSet, with simplified columns but more data:
select grp = 1, val = 100, dt = cast('01-jan-2015' as date)
union all select grp = 1, val = 110, dt = cast('01-jan-2015' as date)
union all select grp = 1, val = 200, dt = cast('02-jan-2015' as date)
union all select grp = 1, val = 210, dt = cast('02-jan-2015' as date)
union all select grp = 1, val = 300, dt = cast('03-jan-2015' as date)
union all select grp = 1, val = 310, dt = cast('03-jan-2015' as date)
union all select grp = 1, val = 400, dt = cast('04-jan-2015' as date)
union all select grp = 1, val = 410, dt = cast('04-jan-2015' as date)
union all select grp = 1, val = 500, dt = cast('05-jan-2015' as date)
union all select grp = 1, val = 510, dt = cast('05-jan-2015' as date)
union all select grp = 2, val = 220, dt = cast('02-jan-2015' as date)
union all select grp = 2, val = 230, dt = cast('02-jan-2015' as date)
union all select grp = 2, val = 320, dt = cast('03-jan-2015' as date)
union all select grp = 2, val = 330, dt = cast('03-jan-2015' as date)
union all select grp = 2, val = 420, dt = cast('04-jan-2015' as date)
union all select grp = 2, val = 430, dt = cast('04-jan-2015' as date)
Note that each grp / dt combination has two values, and that grp 1
is over a longer range for dt than grp 2
.
I have created a simple Matrix based on this:
Since you are using SQL Server 2012, you can use the LookupSet
function to get the First/Last values per row group.
The expression in the First row group TextBox is:
=Code.SumLookup(
LookupSet(
First(Fields!dt.Value, "grp").ToString & Fields!grp.Value.ToString
, Fields!dt.Value.ToString & Fields!grp.Value.ToString
, Fields!val.Value
, "DataSet1"
)
)
Based on my sample data, this is giving my required results:
Note that the second grp
row has a narrower range than the first, but its first/last columns are independent for each group so are correct within each grp
.
There are quite a few things going on here.
Custom code for aggregation of LookUpSet
result
The LookupSet
expression is wrapped in a Code.SumLookup
custom function:
Function SumLookup(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
Dim suma As Decimal = New Decimal()
suma = 0
For Each item As Object In items
suma += Convert.ToDecimal(item)
Next
Return suma
End Function
This is taken from the answer at this SO question.
This assumes that each matrix cell can be the sum of multiple values, so this needs to be summed up. LookupSet
returns an array of values, which is aggregated by Code.SumLookup
.
Details for LookupSet
Next, the LoopupSet
expression itself:
LookupSet(
First(Fields!dt.Value, "grp").ToString & Fields!grp.Value.ToString
, Fields!dt.Value.ToString & Fields!grp.Value.ToString
, Fields!val.Value
, "DataSet1"
)
LookupSet
takes the following parameters:
LookupSet(source_expression, destination_expression, result_expression, dataset)
In our expression, we want get all values from DataSet1
that match the first dt
in the current grp
scope.
For source_expression
I use:
First(Fields!dt.Value, "grp").ToString & Fields!grp.Value.ToString
This gets the first dt in the row scope ("grp"
is the name of the row group), then appends this to the current grp. This creates an expression to match to a similar expression when seeking in DataSet1
.
i.e. destination_expression
:
Fields!dt.Value.ToString & Fields!grp.Value.ToString
Finally, we specify we want Fields!val.Value
as the result_expression
and DataSet1
as the dataset
parameter.
All matching Fields!val.Value
values in DataSet1
are constructed into an array by LookupSet
, then aggregated by Code.SumLookup
.
Update expression for Last values
The expression for the Last TextBox is practically the same; just change First
to Last
:
=Code.SumLookup(
LookupSet(
Last(Fields!dt.Value, "grp").ToString & Fields!grp.Value.ToString
, Fields!dt.Value.ToString & Fields!grp.Value.ToString
, Fields!val.Value
, "DataSet1"
)
)
Get the Difference
Finally, to get the difference of these, simply subtract one expression from the other in the Difference TextBox, or even reference the ReportItems
values:
=ReportItems!Last.Value - ReportItems!First.Value
Where Last
and First
are the names of the TextBoxes.
Conclusion
Obviously you will need to update for your specific case, but you can see that this can be done.
Is it worth doing this in your report? You can see there are many steps involved, and in general would be easier to address when generating the DataSet. But, if that is not an option hopefully this LookupSet
approach is useful.
Upvotes: 8
Reputation: 15037
AFAIK this is not possible in SSRS alone. Believe me I've tried. Fortunately you have a SQL datasource so I would resolve this requirement there where you have (almost) unlimited powers to shape and manipulate data.
For example I would replace your final select with:
; WITH CTE_Base AS (
SELECT * FROM #tmpHoldingTable
)
, CTE_Test1 AS (
SELECT Test1
, ROW_NUMBER () OVER ( ORDER BY Test1 ) AS Column_Number_Test1
FROM CTE_Base
GROUP BY Test1
)
SELECT CTE_Base.*
, CTE_Test1.Column_Number_Test1
, CASE WHEN CTE_Test1.Column_Number_Test1 = 1
THEN Total
WHEN CTE_Test1.Column_Number_Test1 =
( SELECT MAX ( Column_Number_Test1 ) FROM CTE_Test1 )
THEN 0 - Total
ELSE 0
END AS [Difference]
FROM CTE_Base
INNER JOIN CTE_Test1
ON CTE_Base.Test1 = CTE_Test1.Test1
This adds a [Difference] column with a copy of [Total] for the 1st column and 0 - [Total] for the last column.
The SQL could probably be made more efficient, but hopefully breaking it into CTEs is easier to follow.
Then in the SSRS Designer you can add a [Difference] column outside the [Test1] column group and let it sum (default).
BTW your test data seems a bit simplistic - it will only produce 2 columns and all cells have values. But its great you posted DDL & DML - it made it easy to extend the data and code and test this.
Upvotes: 1