0

I am using SSRS report where Main report using one subreport. I want to retrieve one cell data from main report which located on 88th row and column name is FullYear.

Main Report

Sub Report

I tried below expressions,

=LookUp(Fields!PLMapperId.Value,88,Fields!FullYear.Value,”ProfitAndLossDataset”)


Function MergeLookup(ByVal items As Object()) As String
If items Is Nothing Then
Return Nothing
End If
Dim suma As String = New String()
Dim ct as Integer = New Integer()
suma = ""
ct = 0
For Each item As Object In items
suma += Convert.ToString(item) + ","
ct += 1
Next
If (ct = 0) Then return 0 else return suma 
End Function

=Code.MergeLookup(Lookup(88,Fields!PLMapperId.Value,Fields!FullYear.Value, "ProfitAndLossDataset"))

It is giving me below errors,

Severity Code Description Project File Line Suppression State Error [rsInvalidLookupScope] The Value expression for the text box ‘Textbox8’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset.

Severity Code Description Project File Line Suppression State Error [rsInvalidLookupScope] The Value expression for the textrun ‘Textbox8.Paragraphs[0].TextRuns[10]’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset.

I want to perform some calculation on main report data which I want to render in subreport. Where do I need to change to make it work?

Keval Patel
  • 925
  • 4
  • 24
  • 46

2 Answers2

0

It may be worth a try: inserting quotation marks around 88 ie "88"

=Code.MergeLookup(Lookup(88,Fields!PLMapperId.Value,Fields!FullYear.Value, "ProfitAndLossDataset"))

or

the source that you have entered is not recognised by SSRS and may need to be an actual field equivelant ie such as parameter or even hard coded into the tablix (and visibility set to hidden?)

SuperSimmer 44
  • 964
  • 2
  • 7
  • 12
0

From the subreport, you cannot do a lookup back to a dataset in the parent report. This is why you are receiving an error indicating that the dataset is not found. Since it looks like you are just wanting to get a single value from the parent report, I would suggest creating a parameter on the subreport and then passing your value from the parent down to the subreport.

This approach will obviously get messy if you require more than a single value. In this case it might be worth considering shared datasets that you can add to both reports and keep in sync in one place.

Daniel
  • 1,364
  • 1
  • 19
  • 34
  • Yes, I need to pass multiple cell values from parent report to subreport, So as of now, I am executing parent SP in subreport SP again and sending required values in subreport SP result only. Working. – Keval Patel Mar 02 '17 at 07:45
  • Recalling the SP in the subreport is by far the easiest way to get what you need, even if it feels dirty. If this is a major performance problem, I'd need more details about exactly what you need in the subreport to comment any further. – Daniel Mar 02 '17 at 18:23