Editing RDL programatically using C#
Recently i was working on an issue.
In Reporting Service 2005 you set FixedHeader property to a table control. When the dataset bound with the table returns no data in combination with Internet Explorer -> Tools -> Internet Options -> Advanced -> Uncheck Disable Script Debugging (Internet Explorer), you'll receive a Javascript error.
---------------------------
Error
---------------------------
A Runtime Error has occurred.
Do you wish to Debug?
Line: 12
Error: Object required
---------------------------
Yes No
---------------------------
This happens with both IE 6 and IE 7.
WORKAROUND:
=============
1. Set the No Rows property of the table with some value like "Sorry no data returned"
OR
2. Add the expression for the visiblity of the table which holds the data.
=IIF((RowNumber("DataSet")=0),True,False)
But consider the situation where you've 100's of reports already deployed in the report server. You'll not be in favor of editing each and every report and re deploy the same.
For that i wrote a piece of C# code which will read each and every deployed report from the report server, scan through the table element and where ever the FixedHeader property is set, it inserts an element <NoRows> and puts the report defintion back at the report server. I've tested the code and it works great.
CODE:
=====
Below is a console application code written in C# which accepts two parameters, one the complete Report Server URL and the other is the message that need to displayed when there are No Rows. (You can leave it blank as well)
Like this: somename.exe "https://machinename/reportserver" "Query didn't return any data "
I've commented the code for easier understanding.
Additional references,
using System.Xml;
using System.Xml.XPath;
using System.IO;
Make sure to add a Web Reference for ReportService.asmx (2005 end point) and include it in the project with the using statement.
class Program
{
static void Main(string[] args)
{
if (args.Length == 2)//Make sure we pass exactly two parameters.
{
try
{
ReportingService rs = new ReportingService();
int totalReports = 0; //To keep track of number of reports that were processed.
int editedReports = 0; //To keep track of number of reports that were edited.
rs.Url = args[0] + "/ReportService.asmx"; //First command line argument, takes the appropriate Report Server URL.
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
//Retrieving the list of all the items deployed in the Report Server.
CatalogItem[] catalogitems = rs.ListChildren("/", true);
//Looping through each and every item.
foreach (CatalogItem item in catalogitems)
{
//We're concerned only about the reports and thats were the processing starts.
if (ItemTypeEnum.Report == item.Type)
{
bool rdlModified = false; //To make sure only the modified RDL's are written back.
bool isFixedHeader = false; //To make sure we edit only the RDL's that has Fixed Headers.
string reportPath = item.Path; //Path of the report that is currently being processed.
byte[] reportDefinition = null; //Holds the Original RDL byte array.
byte[] newReport = null; //Holds the Modified RDL byte array from the stream.
XmlDocument xmldoc = null; //To hold the RDL in the XML form.
reportDefinition = rs.GetReportDefinition(reportPath);
Console.WriteLine("Processing the report: " + reportPath);
totalReports++;
using (MemoryStream memOriginalRDL = new MemoryStream(reportDefinition))
{
xmldoc = new XmlDocument();
xmldoc.Load(memOriginalRDL);
memOriginalRDL.Close();
}
XmlNode root = xmldoc.DocumentElement;
XmlNodeList nodelist = root.SelectNodes("descendant::*"); //To make sure we've all the elements under the root are traversed.
foreach (XmlNode node in nodelist)
{
if (node.Name == "Table") //We're only concerned about table.
{
int count = 0;
XmlNodeList childList = node.ChildNodes;
foreach (XmlNode childnode in childList)
{
if (childnode.Name == "NoRows") //Checking if there is NoRows element already present.
{
count++;
}
if (childnode.Name == "Header") //Setting the flag if only we've the Fixed Header enabled.
{
XmlNodeList headerChildList = childnode.ChildNodes;
foreach (XmlNode headernode in headerChildList)
{
if (headernode.Name == "FixedHeader")
{
isFixedHeader = true;
}
}
}
}
if (count == 0 && isFixedHeader) //Only if NoRows is not available, proceed with the RDL edit.
{
Console.WriteLine("\t Editing the report: " + reportPath);
XmlElement newElement = xmldoc.CreateElement("NoRows", root.NamespaceURI.ToString());
newElement.InnerText = args[1].ToString(); //Second argument from the commandline, customized message to display.
node.AppendChild(newElement);
rdlModified = true;
}
}
}
if (rdlModified)//If only RDL is modified.
{
//Get the XML back to memory stream and Convert the memory stream in to byte array.
using (MemoryStream memModifiedRDL = new MemoryStream())
{
xmldoc.Save(memModifiedRDL);
newReport = memModifiedRDL.GetBuffer();
memModifiedRDL.Close();
}
//Put it back in to Report Server database.
ReportService2K5.Warning[] warning = rs.SetReportDefinition(item.Path, newReport);
editedReports++;
Console.WriteLine("\t finished editing the report: " + reportPath);
}
}
}
Console.WriteLine("\n\n================================");
Console.WriteLine("Completed the Processing of {0} reports. Edited {1} reports. Hit enter to QUIT.",totalReports,editedReports);
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine("Exception occured: " + ex.Message.ToString());
}
}
else
{
Console.WriteLine("Please run the EXE from the command line with the ReportServer URL like : EditRDLProgramatically.exe 'https://machinename/reportserver' 'QUERY doesn't return any data'");
Console.ReadLine();
}
}
}
Comments
Anonymous
November 13, 2008
I got the following error: Exception occured: System.Web.Services.Protocols.SoapException: Server did not r ecognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sqlse rver/2005/06/30/reporting/reportingservices/ListChildren. at System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest() at System.Web.Services.Protocols.SoapServerProtocol.RouteRequest(SoapServerMe ssage message) at System.Web.Services.Protocols.SoapServerProtocol.Initialize() at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, Http Context context, HttpRequest request, HttpResponse response, Boolean& abortProce ssing) any idea?Anonymous
May 01, 2009
I came across your post while searching for a solution to a related task - in a sharepoint site defintion I upload several "Report Builder Report" files to a reports library (RDL files). However simply uploading them doesn't do the "Report Server" tasks. I'm a newbie to reporting services so my semantics are probably off - if I open the rdl file in the sql server business intelligience studio and manually deploy it to sharepoint, it gets added to the sharepoint library and the "Report Server" tasks are performed. I've been hitting a brick wall trying to figure out how to get the "Report Server" tasks done through code. After reading your post it seems maybe the ReportService2005.asmx web service might provide the answer. Do you think it is possible, and can you offer any pointers or references that would assist in getting me started? thanksAnonymous
May 07, 2009
You've mentioned about uploading RDL files to Reports Library. How are you doing that? You want it to do that programmatically? To do that programmatically, in Sharepoint integration mode, ReportService2005.asmx web service will not help. You need to use ReportService2006.asmx web service, which is specifically designed to take care of all the operations in the Sharepoint integration mode. Here are the properties and methods exposed by the reportingservice2006 class built within reportservice2006 namespace. http://msdn.microsoft.com/en-us/library/reportservice2006.reportingservice2006_members.aspx This documentation, http://msdn.microsoft.com/en-us/library/reportservice2006.reportingservice2006.createreport.aspx will show you how to upload a report programmatically to report server which is in Sharepoint integration mode.Anonymous
May 18, 2009
Hello selvar, I am working on a requirment in which I have to save the snapshot history of .rdl on some local folder. But after restoring the save rdl it is not retaining the history of that report definition. I am using Reporting Services 2005 and using ReportingServices2005.asmx to fetch and save the report definition. Thanks and RegardsAnonymous
August 30, 2009
Your question is not very clear. But the moment you say you're restoring the RDL, how exactly you're doing? Are you calling CreateReportDefinition? This will actually over write the existing RDL which effectively means anything associated with that RDL will be lost. You need to use SetReportDefinition to retain anything associated with your report. This will actually update the RDL and not over write.Anonymous
December 26, 2010
How to Edit the DataSource of the RDL file programatically.. Waiting for ur favourable replyAnonymous
January 29, 2011
This is going to be a tricky task as data sources doesn't have a file structure like reports (RDL). However i'm providing you with the web service methods which contains a sample and that sould get you started to achieve this. To get the data source information from a report: msdn.microsoft.com/.../reportservice2005.reportingservice2005.getdatasourcecontents.aspx To set the data source for a report: msdn.microsoft.com/.../reportservice2005.reportingservice2005.setdatasourcecontents.aspx HTH! Selva. [Posted AS-IS with no warranty]