Home > MSDN, SSRS > SSRS :- Get the last previous value in Current Row Cell in Matrix

SSRS :- Get the last previous value in Current Row Cell in Matrix

I got this question from MSDN, link click here.

User questions as

i have a report with parameter is the year i can select a multiple value:

year Data 2010 2011 2012 hp 14 25 30 Dell 17 18 20

what i need when i create the report i want to have another column with the last value concatenation

with the last previous one like this :

year
Data        2010    2011   2012  2012/2011
hp          14      25     30    3025
Dell        17      18     20    2018

Its not possible me to explain in two or three steps, so I have created this blog in detail, how to achieve it.

First lets create two data-sets, one is main which have complete data, I mean data for all years selected in the parameters.

Second Data-set will have one less year data from all the years selected in the parameters.

Lets create second dataset first having one less year data as shown in the below dataset query:-

select * from
(
SELECT data, year , value AS previous,cast(year as varchar)+ data as keyvalue,row_number() over(partition by data order by year desc) yeardesc
FROM     (SELECT ‘HP’ DATA, 2010 year, 14 value
UNION
SELECT ‘HP’ DATA, 2011 year, 25 value
UNION
SELECT ‘HP’ DATA, 2012 year, 30 value
UNION
SELECT ‘Dell’ DATA, 2010 year, 17 value
UNION
SELECT ‘Dell’ DATA, 2011 year, 18 value
UNION
SELECT ‘Dell’ DATA, 2012 year, 20 value
) AS t
WHERE  year IN (@Year)

)t1

where yeardesc <> 1

order by year asc

Create Main Dataset with below sample query:-

SELECT data, year, value,cast(year as varchar)+data keyvalue
FROM     (SELECT ‘HP’ DATA, 2010 year, 14 value
UNION
SELECT ‘HP’ DATA, 2011 year, 25 value
UNION
SELECT ‘HP’ DATA, 2012 year, 30 value
UNION
SELECT ‘Dell’ DATA, 2010 year, 17 value
UNION
SELECT ‘Dell’ DATA, 2011 year, 18 value
UNION
SELECT ‘Dell’ DATA, 2012 year, 20 value) AS t
WHERE  year IN (@Year)

Now in report Properties We create some custom functions so that we can refrenece one dataset result in another dataset.

I have taken help from below link :-

https://sqlserverbiblog.wordpress.com/2011/09/29/using-custom-code-to-synchronize-different-datasets/

Copy Paste below code in Report->Properites->Code Section

Public PersSal as New System.Collections.HashTable()

Function ClearList(SomeValue as String) as String
PersSal.Clear
Return SomeValue
End Function

Function AddKeyValue(KeyValue as String, Value as Decimal) as Decimal
Try
PersSal.Add(KeyValue, Value)
Return Value
Catch ex as Exception
End Try
End Function

Function GetValueByKeyValue (ByVal Key As String) As Decimal
For Each clsPair As System.Collections.DictionaryEntry In PersSal
If clsPair.Key.ToString() = Key Then Return clsPair.Value
Next
End Function

After Completing this , add two Matrix Control for each dataset.

Note:- Matrix with dataset having less should be placed first then main dataset containing Matrix.

Create one column outside of group in first Matrix and paste the expression below:-

=Code.AddKeyValue(Fields!keyvalue.Value, last(Fields!previous.Value)))

in the column as shown below:-

ExpressionToFillDataset

After this in Main Matrix having Main Dataset result create one column out of group as created for first Matrix.

Then Write the Expression “=cstr(last(Fields!value.Value)) + cstr(Code.GetValueByKeyValue(Fields!keyvalue.Value))” as shown below:-

ExpressionToGetDatasetResult

Your are report design would look like below:-

ReportDesign

So Now we need to change the font in first matrix to white so that its not visible.

Note:- If we hide the Matrix 1 then in Main Matrix the value will not reflect for the new column.

after setting background , layout,bordercolor to white. run the report.

report look like below:-

Report

Lets select different values in parameters and run the report, Report look like below:-

Report1

So its working, For RDL code , add a comment with EmailID.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: MSDN, SSRS Tags: ,
  1. No comments yet.
  1. April 18, 2015 at 1:31 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: