Walkthrough * 2: WebService Parameterizing in SSRS, aka We Locals Also Serve

Greg from marshalltown.com asks:

Referencing blog post: https://spacefold.com/lisa/2011/10/14/Actual-Factual-JSON-data-in-SSRS

I understand this is a really old post, but it is really close to what I’m trying to do. I am trying to use an XML data source, to read data into a report.
If I create the XML datasource like this:
https://devedi/purchaseorderreport/PO0175953
I was able to create datasets from the data source and build my report.
However, when I tried to parameterize the XML connection string like below:
=”https://devedi/purchaseorderreport/” & Parameters!PONumberParameter.Value
I get a non-descript error that says “An error occurred during local report processing. Error in the application. Object reference not set to an instance of an object.”
I’m using Visual Studio Pro 2017.
Any suggestions on how to make the XML connection string take a parameter? Maybe another workaround?

In my reply, I asked whether Greg was using an RDL or an RDLC.

That was completely stupid of me (not the first time!), because what I really meant to ask was “Are you using a report server to get your data or are you using an application?” In other words, in ReportViewer lingo, “Are you using Server Mode or Local Mode”?

Because I didn’t ask my question very well, Greg may or may not have understood what I needed to know. He replied “RDLC”, but, when asked for a source report, sent an RDL.

So I’m still not sure exactly how he delivers his results to the user, but it doesn’t matter. I’ll just explain both, here, and hopefully at the end of it we’ll all be less confused.

Why two different methods and answers?

Greg wants to use a web service as a the source data for a report. But there are two ways that a report gets its data:

Embedded instructions for a datasource and a query are included in the report definition. A report server connects to the datasource, invokes the query, and fills a dataset as directed. This report must run on a report server, and you can view it in Report Manager or pull it back to your application any way you like, including using various SSRS APIs, or using a ReportViewer control, either in its WebForm or WinForm version.
When you use a ReportViewer control, but SSRS is responsible for pulling the data and rendering the report, so that the ReportViewer is passive — it does nothing more than facilitate display of the report — you’re using the control’s Server mode.
You’re using a ReportViewer control or some other engine (there are some) to read and process the RDL/RDLC instructions. There’s no SSRS server involved. Your application (or its embedded control) decides how it wants to get the data and interpret the RDL’s rendering elements. If your engine is MS’s ReportViewer control (again, whether WebForm or WinForm), any embedded instructions for data source and query in the report definition are completely ignored. Your application gets the data and shapes it into the dataset(s) contents required by the report. The ReportViewer control takes the dataset from you and reads all the rest of the report definition to actively render the report content. This is the control’s Local mode.
BTW, I don’t know for sure what the “C” in RDLC stands for. Probably “Client” or “Content” or maybe “Culpable” because the MS designers for it have gotten more and more horrendous over time. Suffice to say that you can use an RDL in Local mode just fine, so you can design your report in a more capable designer just fine, as long as you remember that the data sources and queries in your RDL are irrelevant at runtime. Maybe that’s what Greg is doing.

I know I’ve talked about using ReportViewers dynamically before, including switching between the two modes dynamically at runtime, because this is something my xmlRSDocsHarness has to do. If you (and Greg) already understand all this, I apologize. Let’s move on.

Server mode walkthrough

Greg’s attempt looks like Server mode to me. And he’s quite right, you don’t seem to be able to put an expression such as ="https://devedi/purchaseorderreport/" & Parameters!PONumberParameter.Value into the XML connection string.

However, you can make the XML connection string a variable, with certain limitations. So here’s what you do:

Go ahead and use the hardcoded version in your datasource connection string, to start. This will enable you to flesh out the contents of your dataset, and design your report. If you try to follow the result of the instructions I’ll give you here, and you don’t do this first, you’ll hit the following annoying limitation, at least in ReportBuilder, which is what I’m using here:
Presumably, you have one or more parameters you want to pass to the web service at runtime. Mine is called PO, and is of text type. Go ahead and create all the ones you need.
Then create an extra parameter, which will be hidden from the user. It should be at the bottom of your parameter list, so it has access to any user-available input values it needs. You will give it the default value of the dynamically-contructed URL you need for your XML connection string.
In my example, the name of this hidden parameter is Svc, and its default value looks like this:

In Greg’s code, it’s going to look more like the expression he’s been trying to put into the connection string.
Now you can set your XML data source connection string like this, an expression that references your parameter:
Note that if you try to preview it at this point (I happen to be using Report Builder 2016), you may or may not see the parameters the way you expect. Sometimes I did, sometimes I didn’t. However, when you deploy the RDL to a server, your report will behave reliably as expected, with your Svc (or equivalent) parameter hidden, the others available, and each value of your visible parameters reflected in the next user-push of the View Report button:

That’s pretty much the whole trick. No code or nuthin’.

Walkthrough in Local mode – a different story.

In Local mode, remember, you don’t have the full might of SSRS involved. The RDL/RDLC is a definition of what a report should be, but not of how it gets that way. So you have to write some code.

In this walkthrough, I’ll be using the WinForm ReportViewer control, but please understand this technique will be exactly the same in a WebForm application.

Very important: no wizards are involved here.

The form you see below has no generated crap like data adapters widgets and reams of hidden designer code. I didn’t use a reporting-specific template for the project. I just took an empty form and dragged a textbox and label and a ReportViewer control instance onto the design surface of the form. (You could do the same with an empty ASPX page.)

WinForm with ReportViewer

I set no properties through the form designer; you will see all the relevant code in this post. And there are more comment lines than code lines, I promise you.

Caveat: web service code not shown.

I’ll be showing you all the relevant code. That doesn’t include the web service that my example is calling, because I suck at web services in .NET, and your services are undoubtedly quite different. Obviously I have no idea what’s under Greg’s URL and his devEDI server, either.

That’s not relevant. The point is that your application, not the ReportViewer, has a web service to call, and you know how to call it.

Ready? Okay.

As I said, the ReportViewer control doesn’t have any property settings at all, although you could use some and eliminate two lines I’ll put into the Form Load, just for clarity:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
   ReportViewer1.ProcessingMode = ProcessingMode.Local
   ReportViewer1.LocalReport.ReportPath = "Report1.rdl"
End Sub

As you can see, I’m using an RDL, not an RDLC, there. Why? Because I hate the designer stuff provided for RDLCs so dang much. In this walkthrough, I’m going to use Visual Studio’s Report Designer to do the RDL, and it’s pretty straightforward that way, or in a Report Builder.
So what’s in the RDL? For starters, I’m going to be very cavalier about the Data Source, in fact I’m going to name it Temp, just to underscore the fact that I know it’s just there for design purposes — just like the hard-coded connection string I used originally in the Server mode approach:
As you can see, I basically picked any database to hand. I’ll be slightly more careful with the Dataset; it’s important for me to note what its name is, as well as the column names and their data types, for later. But the query itself is another throw-away, only needed for design-time:
… then I’ll use the placeholder contents of my query to design the layout of the report as I normally would. OK so far?

Time to do the real work, which is how we are going to retrieve the data at run-time. Under the button you see in the form, I’ll put the following code:

Private Sub Button1_Click(sender As Object, e As EventArgs)
    Handles Button1.Click
    GetReportData()
    ReportViewer1.RefreshReport() ' render the report with the data
End Sub

In GetReportData, we’re going to hand the report the data set that it needs to operate, based on whatever the user put in the textbox. Notice that we’re talking to the LocalReport member object of the ReportViewer control, I’m using the same name for the datasource I’m handing the LocalReport as I used for the dataset in the RDL. And read the rest of the comments, since it’s silly for me to repeat all that here:

Private Sub GetReportData()
    ' your form control holds the 
    ' user input for the web service parameter:
    Dim thisPO As String = TextBox1.Text

     ' this is local mode!  So we have to tell the
     ' report viewer's local report object what its
     ' data source is.  First we'll go get the data
     ' from the web service:
     Dim dt As DataTable = LoadPOData(thisPO)

     With ReportViewer1.LocalReport
         .DataSources.Clear() ' because this might be a second user request
         .DataSources.Add(New ReportDataSource("PO", dt))
         ' the name here should match the dataset name you 
         ' used in the report.  If you have multiple datasets in the RDL/RDLC,
         ' you are going to want to create and add multiple data sources here.
     End With

End Sub

Finally, we have to create that datatable used when we create the new ReportDataSource in the GetReportData method. Here’s my code, for the purposes of completeness and illustration, although it will probably bear no resemblance to yours. There are lots of ways to create a datatable object, and lots of ways to call a web service. Again, read the comments:

Private Function LoadPOData(thisPO As String) As DataTable
    ' I know less than most people about
    ' how you call a web service in .NET.
    ' do this part however you would normally do it:
    Dim wb As OrderWebReference.PurchaseOrder = New OrderWebReference.PurchaseOrder()
    Dim po As OrderWebReference.PO
    po = wb.GetOrder(thisPO)

    ' Now we're going to create the dataset
    ' according to what the RDL expects.
    ' (same fields, same datatypes).

    ' Depending upon how you call your web service,
    ' and what it returns, there is probably a less
    ' tedious way of doing this than I show here.
    ' For example, you could pull back some raw
    ' XML, transform it if it isn't dataset-shaped,
    ' and then use the dataset.loadXML method to
    ' fill the dataset.  You then return 
    ' tables(0) from the dataset...

    Dim dt As New DataTable("PO")
    dt.Rows.Add()
    Dim r As DataRow = dt.Rows(0)
    Dim s As Type = System.Type.GetType("System.String")

    dt.Columns.Add("POAmount", s)
    dt.Columns.Add("PONumber", s)
    dt.Columns.Add("POStamp", s)

    r(0) = po.POAmount
    r(1) = po.PONumber
    r(2) = po.POStamp

    Return dt
End Function

That’s really it. When you run the app, the ReportViewer control in the form starts out with no content (and no error), and when you change the value in the textbox and click the button, you get the data for the value you put in.

OK? Don’t be a stranger.

I hope this double walkthrough post is useful to Greg, and maybe to other people. If not, drop me a line, and let me know what you need.