Parameter Value not getting passed while using SetExecutionParameters method of ReportExecutionService


I was recently working on generating pdf for a report deployed in CRM through code using ReportingExecution2005 web service.

https://nishantrana.wordpress.com/2012/01/29/sample-code-to-use-reportexecution2005-asmx-to-generate-pdf-in-crm-2011/

While writing the code, we faced a very strange issue where values for certain ReportParameters were not getting passed.

I will try to explain the scenario using a very simple report.

Suppose I have report that has 4 report parameters and I am setting values for only 3 of the parameters through code. (Note:-ReportParameter4 is set as second parameter)

Suppose they all are Boolean parameter with default value as True.

Now I am using the following code to set their values as false.

 ParameterValue[] myPVArray = new ParameterValue[3];

ParameterValue myPV1 = new ParameterValue();
 myPV1.Name = "ReportParameter1";
 myPV1.Value = "False";
 myPVArray[0] = myPV1;

ParameterValue myPV2 = new ParameterValue();
 myPV2.Name = "ReportParameter3";
 myPV2.Value = "False";
 myPVArray[1] = myPV2;

ParameterValue myPV3 = new ParameterValue();
 myPV3.Name = "ReportParameter2";
 myPV3.Value = "False";
 myPVArray[2] = myPV3;

rs.SetExecutionParameters(myPVArray, "en-us");
 

This is the output of the pdf

i.e. the value appears properly for the ReportParameter 1 to 3 as set as false. For the report parameter 4 it comes as True (i.e. its default value as we are not setting it).

Now I go forward and make a small change instead of specifying default value for ReportParameter 4 as True, I modify it to take the value from the DataSet instead.

From

To

And Data Type from Boolean to Text.

Now I run the same code. This time again we would expect the value for ReportParameter 1 to 3 as False and ReportParameter4 to be whatever value there in the DataSet.

However we will get the following pdf output


For Report Parameter 2 and 3 we see the default value (i.e. True) instead of False as we are passing through code.

The solution to this that I found was to move the reportparameter 4 as the last parameter in the report.

Currently in our report it is set as the second one.

To

The output after running the code

i.e. the one we were expecting.

So one thing we need to remember over here is that if we have any of our report parameter taking its value from dataset then we should make it the last parameter or should put it after all the other report parameters whose value we are passing through code.

Hope it helps.

Sample Code to use ReportExecution2005.asmx to generate PDF in CRM 2011.


Hi,

Just sharing a sample code that uses ReportExecution2005 web service of SSRS 2008, and generates pdf of one of the custom reports deployed in CRM.

Here we are also passing value to one of the report parameter.

<br />
 // Add web reference to following service<br />
 // http://server/reportserver/ReportExecution2005.asmx</p>
<p>ReportExecutionService rs = new ReportExecutionService();<br />
 rs.Credentials = System.Net.CredentialCache.DefaultCredentials;</p>
<p>// Render arguments<br />
 byte[] result = null;<br />
 // Get the report path from the report server<br />
 // http://reportserver/Reports/Pages/Folder.aspx?ViewMode=Detail</p>
<p>string reportPath = "/MyOrg_MSCRM/CustomReports/{837a8e7e-d949-e111-996a-00155d2a49c7}";<br />
 string format = "PDF";<br />
 string historyID = null;</p>
<p>string encoding;<br />
 string mimeType;<br />
 string extension;<br />
 Warning[] warnings = null;<br />
 string[] streamIDs = null;</p>
<p> ExecutionHeader execHeader = new ExecutionHeader();<br />
 rs.ExecutionHeaderValue = execHeader;<br />
 rs.LoadReport(reportPath, historyID);</p>
<p>// Set DataSource Credentials<br />
 // the user name is your userid and the password is the id of the organization<br />
 // get more info here<br />
 // http://ronaldlemmen.blogspot.com/2009/01/log-in-name-and-password-required-by.html<br />
 DataSourceCredentials datasetCredential = new DataSourceCredentials();<br />
 datasetCredential.DataSourceName = "DataSource1";<br />
 datasetCredential.UserName = "F45D4E20-44DE-E011-8A08-005056860004";<br />
 datasetCredential.Password = "D0E60C19-44DE-E011-8A08-005056860004";</p>
<p>// Set Report Parameter values<br />
 ParameterValue[] myPVArray = new ParameterValue[1];<br />
 ParameterValue myPV = new ParameterValue();<br />
 myPV.Name = "ReportParameter1";<br />
 myPV.Value = "True";<br />
 myPVArray[0] = myPV;</p>
<p>rs.SetExecutionParameters(myPVArray, "en-us");</p>
<p> DataSourceCredentials[] credentials = new DataSourceCredentials[1];<br />
 credentials[0] = datasetCredential;<br />
 rs.SetExecutionCredentials(credentials);</p>
<p>result = rs.Render(format, null, out extension, out encoding, out mimeType, out warnings, out streamIDs);</p>
<p>Response.Clear();<br />
 MemoryStream ms = new MemoryStream(result);<br />
 Response.ContentType = "application/pdf";<br />
 Response.AddHeader("content-disposition", "attachment;filename=labtest.pdf");<br />
 Response.Buffer = true;<br />
 ms.WriteTo(Response.OutputStream);<br />
 Response.End();<br />
 

Bye.

Advertisements

“The CRL type does not exist or you do not have permissions to access it” error while refreshing dataset in Query Designer (SSRS 2008)


I was getting the below error while trying to refresh the dataset in the query designer.

The workaround was to refresh the dataset from Report Data panel

Right click the dataset and select Dataset properties

Click on Refresh Fields button and Ok to close the dialog box.

Hope this helps.

Hiding and shrinking the width of the Column in SSRS 2008


Hi,

 We recently had a requirement wherein we were hiding few columns of our SSRS report dynamically based on value of a parameter.

The problem was that the hidden column although not visible was still consuming the space.

To solve this issue

  • Select Advanced Mode in the report Column Groups


  • Select the static column referring to the column we want to hide and set its Hidden property


The helpful post

http://blogs.msdn.com/b/robertbruckner/archive/2010/05/02/report-design-shrinking-hidden-static-columns.aspx

Bye.

Showing SSRS report in a single page


Hi,

We had a requirement to show one of our SSRS report (in CRM 2011) in a single page so that users don’t have to click on Previous Page and Next Page buttons.

.

Setting InteractiveSize’s Height property of the report to 0 solved the issue.

The helpful posts

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/5733e039-7031-454a-b9ad-981a93d97f1f/

Bye.

Error :- ‘ConvertUtcToLocalTime’ is not a member of ‘Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility” while creating report using Report Wizard – CRM 2011


Hi,

I got the following error while creating report using report wizard

“Web service request CreateReport to Report Server http://servername/ReportServer/ReportService2005.asmx failed with SoapException. Error: The Value expression for the textrun ‘txtExecutionDateTimeLabel.Paragraphs[0].TextRuns[0]’ contains an error: [BC30456] ‘ConvertUtcToLocalTime’ is not a member of ‘Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility'”

Re installing Reporting Extensions resolved the issue.

http://yellowduckguy.wordpress.com/2011/06/01/microsoft-dynamics-crm-2011-reporting-extensions-is-not-installed/

Hope it helps.

%d bloggers like this: