Hello,

How can I iterate 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

As the task I want to accomplish is very simple, I want to keep it in the custom code instead of writing any custom assemblies etc.

 

Thank you!

Datasets have Tables and Tables have DataRows, which have DataColumns.  Assuming that you have only one result set, you can iterate over ds.Tables(0).DataRows with something like this (in C#, but you can translate to vb):

DataSet ds = new DataSet();
      foreach (DataRow row in ds.Tables[0].Rows)
      {
        foreach (object column in row.ItemArray)
        {
          Console.WriteLine(column.ToString());
        }
      }

Hi ,

I dont think we can pass entire dataset to custom code rather than field[s] .

But you can have a calulated field[s] which might differ  from the original dataset fields .

Thanks .

Justin,

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.

Uwe,

The only way to effectively loop through the rows of a report dataset is to call a custom function or referenced method in a report data region expression.  Using this technique, it may be possible to pass all of the the row and field information into a code structure, array or collection.

Honestly though, if you need to have this level of control, you may be better off developing a web form rather than a report.  The goal of SSRS is to simplify the reporting process rather than providing a deeply extensible programming environment.  To use Reporting Services effectively, you need to think like a report designer, working with the product as it's designed, and not think like a programmer.

Paul,

 

thank you for your answer. Is there any class reference on the SSRS classes? I did not find anything about that, to at least know whats possible.

 

Regards

Uwe

The best thing you’ll find are in the more advanced books.  Our Wrox Press book has a class references and sample code.  Also, check Teo Lachev  Bill Vaugh’s books as they focuse on programming extensions.  Teo also maintains a good blog at prologika.com and I have linkes to other resources on my blog: sqlserverbiblog.com.

Actually,

I've just found a way to do this without using external classes. I know this is an old thread but it might help for historical...

Heres how, the ideal is to "transform" the data set into a multivalued parameter (or if you need several fields, transform it in multiple multivalued parameters).

The multivalued Report Parameter must have the following characteristics:

Hidden = True

Allow Multiple Values = True

Available Values tab:

Chose 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

 

Hope it helps.

Miguel Catalão

 

• Proposed as answer by Seth Sanusi Wednesday, April 20, 2011 11:20 PM

I have followed your example and done this. It works fine when I preview the report in Visual Studio or open the deployed report directly in a browser but when I use the Render method of ReportExecution2005.ReportExecutionService to render a PDF, Excel or CSV file the error "Default value or value provided for the report parameter 'name' is not a valid value." The parameter referred to is the multi-value one.

The only difference between my implementation and your example is that both the Value and Label columns of the multi-value parameter are string values rather than one of them being an integer as shown in your example, but I don't see this as likely to be causing the problem.

Can anyone shed light on why the report might work in the viewer but not when being rendered?

Miguel,

Your technique works a treat thank you very much.  I needed to deduct business closures from start and end dates in a report tablix so I:
Created a list in SharePoint
Created a dataset that read the list
Created a parameter that used the list as a dataset
Used your technique with code to walk through the list taking 1 away from my number of days if the date in my parameter was between my start and end dates

Thanks
Andrew

I used your example in an article I wrote: http://theway4ward.wordpress.com/2011/08/19/sql-reporting-services-and-working-days-across-datasets/

Miguel.

You rock! I been trying to figure out something like this for ages. So happeeeeeeeeeeeeee! My life is now easier.

Thank you

Can someone please help me im so stuck!?!

I followed Miguels advice and I created the following function:

 

Dim public shared EntryNoList As String

dim public shared i As Integer

Public Function GetDataSetLabelFromValue( id as integer) AS Decimal

i = 1

dim RecCount as Integer

RecCount = Report.Parameters!DSFlourSales.Count() -1

 

while i <= RecCount

  if Report.Parameters!DSFlourSales.Value(i) = id then

    MonthlySalesArray(id) += Report.Parameters!DSFlourSales.Label(i)

  End if

  i = i + 1

end while

return MonthlySalesArray(id)

End Function

 

But for some silly reason my function will always adds the first value of a set. For example:

 

If id = 4 for April and April has 2 Dataset Values for Label: $100 and $200. My function will return $200 ($100+$100) and not $300 ($100 + $200).

If id = 5 for May and May has 4 Dataset Values for Label: $100, $200, $300 and $400. My function will return $400 ($100x4) and not $1000 ($100 + $200 + $300 + $400).

 

:-(

 

 I am scratching my head here I have been trying for 2 days I cannot see anything obvious. I am incrementing integer i correctly and I have tested the value for 1 increments by 1 each time. But for some reason it always adds the first value for the dataset month !!

 

 

Hi MBrodie 1979.

There are some things in your code that i don't get.

First of all i don't get where   MonthlySalesArray(id)  comes from?

Is this a variable, another parameter, a function... ? To me it would be more logical to use a simple variable and return it.

Also, and i might be wrong on this since i don't code in VB a long time, but is +=  a valid operator in vb?

Hope this helps.

Miguel Catalão

 

Hi Miguel.

Thanks for the reply. I found the issue. For my paramter Value Field I was using a calculated field. I had to use the tables primary key to search aganist and now the code works as expected.

I am still having 1 strange issue I cannot resolve. For some reason my code will always miss 1 record in the dataset for a month. So if I have 2 records in April it will only sum 1 record, if May has 17 records it will sum only 16 records. It may have to do with the fact that for RecCount I have to minus 1 from Report.Parameters!DSFlourSales.Count(), for example:

RecCount = Report.Parameters!DSFlourSales.Count() - 1

But If I don't put the -1 my function returns #Error. :-( Have you got any ideas?

 

Code:

dim public shared i As Integer

Public Function GetDataSetLabelFromValue( id as Integer) AS Decimal

i = 0

dim RecCount as Integer

RecCount = Report.Parameters!DSFlourSales.Count() - 1

while i <= RecCount

if Report.Parameters!DSFlourSales.Value(i) <= id then

MonthlySalesArray(id) += Report.Parameters!DSFlourSales.Label(i)

End if

i = i + 1

end while

return MonthlySalesArray(id)

End Function

 

PS

+= is a valid operator for vb. :)

Nevermind my previous question. I solved the problem all 100% now, thanks once again for this article and everyone who contributed!

Miguel ..... Thanks you so much.... .This helped me in finding a solution for lookup function in SSRS 2008 (just like SSRS 2008R2).

Keep up the good work. 

Regards,

Amit

How did you solve the issue?

My problem seems like yours, but sometimes i have to subtract 2, 3, 4 records in my multi value parameter and I dont know why... :(