Menu
My dataset currently has 12 rows of data. Each representing data for a month. I would like to have variance of a column between to rows, the rows being last & last but one i.e., latest month and previous month's data.
It could have been simple if I were to work on tablix but thats not the case. I want those values for a textbox.
Any ideas on it anyone?
391
In Report Builder, the tablix data region is a generalized layout report item that displays paginated report data in cells that are organized into rows and columns. Report data can be detail data as it is retrieved from the data source, or aggregated detail data organized into groups that you specify.
Just right click on Name column then navigate to Insert Column then select Left. Once you select left, you will see a New blank column is added before the Name column. After that, right click on newly added column then select Expression (fx) from context menu.
You need to use the ReportItems collection. This gives you access to rendered output. First find the name of the textbox that contains the data you want to show from you tablix (e.g. EmpName). note: this may not be the same as the field name, its the textbox name property you need.
I hope you are using SSRS 2008R2:
R2 introduced the Lookup function that is perfect for this scenario.
=Lookup( Fields!ProductUID.Value ,Fields!ProductID.Value,Fields!Price.Value,"PriceDataSet")
The Lookup function above will evaluate the first parameter ("Fields!ProductUID.Value") in the current dataset, then look for a matching value in the field specified in the second parameter ("Fields!ProductID.Value") in the dataset specified in the fourth parameter. The value of the third parameter is then evaluated in that row of the dataset and returned.
A little convoluted, but very helpful.
In your case, you can use this in a textbox with a calculated a static number:
=Lookup(
Month(DateAdd(DateInterval.Month, -1, GetDate())),
Fields!MonthID.Value,
Fields!Name.Value,
"DataSet1")
This should calculate a number for last month, then look for a match in DataSet1.
165
In this example I have a tablix with Statecode and name as below enter image description here
Suppose you want to display the name of state of CA, write an expression as -
=Lookup(
"CA" ,
Fields!StateCode.Value,
Fields!StateName.Value,
"ReportData"
)This will return 'California' in the text box
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
