CODEWITHSUNDEEP
c#reporting-services
Trevor Daniel
Trevor Daniel

Reputation: 3974

SSRS - Webservice - DataSet - Cannot read the next data row for the dataset

I am trying to use SSRS 2010 webservices to call an existing report, in html format, to embed in another web page.

The report I am calling depends on a parameter and 3 other datasets embedded in the report.

When I call the report in a browser the reports works perfectly.

But when i call the report using the webservice, sending the only parameter required I am getting the error message:

"Cannot read the next data row for the dataset MembershipRegistrationPayments."

I am confused because the report works perfectly in a browser and the only "selection" needed to make the report run is "Club ID".

Here is the code i have so far...

ReportExecutionService rsExec = new ReportExecutionService();

rsExec.Credentials = new NetworkCredential("usernamexxx", "passwordxxx", "domainxxx");

string report = "/DemoReport1";
bool forRendering = false;
string historyID = null;
string format = "MHTML";
string extension;
string mimeType;
string encoding;
string deviceInfo = null;
ReportExecution.Warning[] warnings;
string[] streamIDs;

ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();

rsExec.ExecutionHeaderValue = execHeader;

execInfo = rsExec.LoadReport(report, historyID);

var execParameters = new List<ReportExecution.ParameterValue>();
execParameters.Add(new ReportExecution.ParameterValue() { Name = "OrganisationID", Value = "1234" });

rsExec.SetExecutionParameters(execParameters.ToArray(), "en-GB");

byte[] reportBytes = rsExec.Render(
format,
deviceInfo,
out extension,
out mimeType,
out encoding,
out warnings,
out streamIDs);

It's almost as of the datasets are not being called when the report is generated and thus the it cannot move to the first record in the dataset?

I'm lost. Any help would be very much appreciated!

Trev

Upvotes: 4

Views: 8467

Answers (1)

Trevor Daniel
Trevor Daniel

Reputation: 3974

Phew! Finally fixed it!

Background:

I have a Dataset that creates a number of fixed dates that are used in another Dataset.

As an example, this is what 1 of the SQL statements ORIGINALLY looked like that generate that date..

SELECT
CASE
WHEN getdate() >= DATEADD(Day, 0, DATEADD(Month, 3, DATEADD(Year, DATEDIFF(Year, 0, getdate()), 0))) THEN DATEADD(Day, 0, DATEADD(Month, 3, DATEADD(Year, DATEDIFF(Year, 0, getdate()), 0)))
WHEN getdate() <= DATEADD(Day, 0, DATEADD(Month, 3, DATEADD(Year, DATEDIFF(Year, 0, getdate()), 0))) THEN DATEADD(Year, - 1, DATEADD(Day, 0, DATEADD(Month, 3, DATEADD(Year, DATEDIFF(Year, 0, getdate()), 0))))
END AS StartDate

I have fixed the problem by converting the date result to a varchar in the SQL statement so that the reporting server no longer tries to do it "internally"

So, my SQL now looks like this:

SELECT
CASE
WHEN getdate() >= DATEADD(Day, 0, DATEADD(Month, 3, DATEADD(Year, DATEDIFF(Year, 0, getdate()), 0))) THEN CONVERT(varchar,DATEADD(Day, 0, DATEADD(Month, 3, DATEADD(Year, DATEDIFF(Year, 0, getdate()), 0))) ,113)
WHEN getdate() <= DATEADD(Day, 0, DATEADD(Month, 3, DATEADD(Year, DATEDIFF(Year, 0, getdate()), 0))) THEN CONVERT(varchar,DATEADD(Year, - 1, DATEADD(Day, 0, DATEADD(Month, 3, DATEADD(Year, DATEDIFF(Year, 0, getdate()), 0)))) ,113)
END AS StartDate

And now the report works perfectly!

Upvotes: 4

Related Questions