{How to Fix} – This report requires a default or user-defined value for the report parameter. Error while executing SSRS report programmatically from plugins in Dynamics CRM

Looking at the title of the topic, you might be asking – Is this something to do with Dynamics CRM Plugins only? The answer is ‘NO’. It’s not. The error I am talking about is generic and you may get it from anywhere where it is trying to execute a SSRS report programmatically. However posting it in the hope that it might be beneficial for fellow CRM developers.

So let me give you a context of the requirement. Our Client had a HTML webresource based report with lot of tabular stuffs and input elements in it. On the webresource, they had an ‘Export To Excel’ button which when clicked would generate an excel with the same look and feel of the report. So this is what I suggested.

  • Develop a SSRS report with the same look and feel of the HTML webresource
  • Configure the SSRS report with the parameters required to generate the SSRS report.
  • When ‘Export’ to excel button is clicked, the necessary parameters would be passed to the SSRS through a plugin which would get the excel dump of the SSRS report.

All well and good this far and our client very much liked the idea. But every consultant would know that whenever you suggest an approach to your client and if they really like your idea, they would come up with something new. Well the same happened in my case. My client insisted on having this report accessible from outside the context of the HTML webresource. What it means is that the report should be visible to end-users and they should be able to run manually by selecting the appropriate parameters. No harm in this I thought, we have to build a report anyways.

So we started building the report. We created parameters and datasets for the parameter values and specified the option for ‘Available values’ for the parameter to the appropriate dataset.

image

All well this far. Ran the report from the report section with appropriate parameters. Report worked perfectly. Now integrated the code to call the SSRS report from Plugin. The moment we did that we started getting the error.

“This report requires a default or user-defined value for the report parameter. To run or subscribe to this report, you must provide a parameter value.”

Did some search and everywhere it was suggested to not select the option ‘Get Values from Query’ for the parameter values. We could have done that easily, but the point is that the report needs to be accessed independently. So we decided to develop a new report specific for export to excel from within the HTML webresource.

Before doing that, I really wanted to find the root cause of this issue. So I decided to investigate little further.

We were passing the report parameter from the plugin code like the one shown below.

var customerId =config.GetCustomer();

paramList.Add(new ReportParameter("pCustomerId", customerId));

 

The report was expecting a parameter of CustomerId. But the customer id I was passing was a string value because Microsoft.WebForms.Reporting.ReportParameter accepts only string values. But if you see from SQL perspective, it is ‘UNIQUEIDENTIFIER’ or a GUID value. So what I did is the query for the report parameters in SSRS, I cast the GUID as NVARCHAR.

Then when I ran the code again, voila! it worked for me. The reason why I was getting the error is because I was passing the GUID parameter as string.

 

Hope this helps!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s