How to loop rows of dataset in Reporting services rdl custom code
Asked Answered
C

1

17

How can I loop through the rows of a dataset in the custom code?
I have a report containing a dataset. I pass the dataset as a parameter to the custom code function. But what then? Where is a reference about the available members etc.?
Here is my dummy sample code so far:

Public Function ShowParameterValues(ByVal ds as DataSet) as object()
    Dim codes() As Object
    Array.Resize(codes,dc.???.Count)
    codes(0)=ds??(field???)(row??)
    return codes
End Function

Please note: this will be a very simple script (if it'll work), so I don't want to go into custom assemblies etc.

Copious answered 31/8, 2010 at 8:12 Comment(2)
are you trying to display the data on the report?Ultramundane
Tell us what are you trying to achieve. An RDL file contains the XML schema of a report's structure, not the data. The data of a report is bound at runtime when the report has retrieved the data. You're better off making a script to scrape a webpage with the report already run, actually showing you the data. Again, without more information this is hard to decipher what you want to achieve,Legged
B
3

I think you got your answer at:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a7d59224-0ee5-491e-883b-2e5fcb3edeab/iterate-through-rows-of-dataset-in-reports-custom-code?forum=sqlreportingservices

There were two important pieces of information I was able to grasp from the above link:

First, A dataset in Reporting Services is not the same type of object as an ADO.Net dataset. A report dataset is an internal object managed by the SSRS runtime (it's actually derived from a DataReader object) and not an XML structure containing datatables, etc. and cannot be passed into the report's custom code.

Second, There was a solution posted as to how one can Iterate through rows of dataset in report's custom code by "transforming" the data set into a multivalued parameter (or if several fields are needed, transforming it in multiple multivalued parameters):

The multivalued Report Parameter must have the following characteristics:

Hidden = True, Allow Multiple Values = True

Available Values tab: Choose the desired dataset. Select the searchable id as Value id, and the field you want to expose as Label Field.

Default Values Tab: Get Values from a Query. Choose the same Dataset as choosen in the available Values Tab. Value Field the same you choose for value id.

Set the parameter to never refresh (or it will be loading the data from each iteraction of another parameter).

Now, the idea is make this Parameter "searchable". From this point you exposed the Dataset as an array in the Multi valued Parameter.

Now in a custom code insert the following code:

function GetDataSetLabelFromValue( id as integer) as String
dim i as integer
i = 0
for i = 1 to Report.Parameters!YourParameter.Count()
if Report.Parameters!YourParameter.Value(i) = id then
    GetDataSetLabelFromValue = Report.YourParameter!ReportParameter1.Label(i)
    Exit For
End if
next i
End Function

Were you able to do what you wanted?

Bachelor answered 17/8, 2016 at 14:39 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.