|
|
You're in the
sql server reporting services
group:Using RDL as a data source for documenting reports?
sql server reporting services:
I am not an XML guru by any stretch of the imagination and am hoping someone
out here can make this happen. I'm working on developing a specification for a web based application that will help us manage our report development. Starting with recording requests, building specifications, assigning resources, documenting reports.... yada, yada, yada. Anyway I thought to myself, "Why couldn't we use Reporting Services to develop a report that could document existing RDL files for us? Its just XML, right." Would love to be able to pull up a report, prompt for a report name and then generate a report that showed me things like: * Path of where report resides * Description of the report * Report Author * Publishing Date * If there are any linked copies and where they reside * What data sources are used (servers, databases, stored proc names, etc) * What parameters does it accept * What fields appear on the report . . . You get the idea. Some of this information is readily available from the tables within the ReportServer database while other info is encrypted and can't be reached. That is unless you were to use a copy of the RDL file as the source for the report. It's all in there. After a full day of screwing around with the new XML features in SQL Server 2005, I only find myself with what I think is a good idea and a high frustration level at not being able to catch on to the XML features to realize it. Even tried developing an SSIS package as the data source, but can't get the XML data source to fly with the RDL. Anyone out there have a handle on the XML features to be able to help me extract some of those items listed above? Or maybe someone is aware of a solution that's on the market that will do something like this.
Hello KS,
Thank you for your posting! From your description, my understanding of this issue is: You want to retrieve the following information of a report. * Path of where report resides * Description of the report * Report Author * Publishing Date * If there are any linked copies and where they reside * What data sources are used (servers, databases, stored proc names, etc) * What parameters does it accept * What fields appear on the report If I misunderstood your concern, please feel free to let me know. Based on my scope, some information is restored in the RDL file, such as the dataset the report using and the the parameter, fields etc. You need to use some customize code to read the RDL file and generate the informatio into a SQL Table or a published XML file. You could not use the RDL file as the data source directly. To use the RDL file as the data source, you need to copy it to a web site and then modify the extension to xml, then you could use it as the data source. For example, your rdl file is Report1.rdl. You could copy it to the c:\inetpub\wwwroot folder and modify it to Report1.xml. Then you could use the http://localhost/Report1.xml as the XML data source connecting string. In your case, you could do this using some scheduled program. Hope this will be helpful! Thank you for your patience. Sincerely, Wei Lu Microsoft Online Community Support ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ================================================== (This posting is provided "AS IS", with no warranties, and confers no rights.)
Hello KS,
How is everythin going? If you have any questions, please feel free to let me know. Sincerely, Wei Lu Microsoft Online Community Support
Thank you for the reply and my apologies in the delayed response as I was out
last week. Yes, I understand that the RDL is an XML file that should be able to be used as a report data source, but as I posted in my original message the "how to" is what continues to elude me. For the sake of simplicity, that me pose a single question in hopes that you might be able to provide me with some direction. If you were going to build a report that accepted as a parameter a report name and then returned the different data sources that were used in the selected report, how would you accomplish this assuming you had the source RDL as your data source? Would you create an SSIS package to extract the data from a renamed RDL (to XML) file? Would you use the OPENXML T-SQL statement to query the RDL source data (example would be appreciated)? Thank you in advance for any assistance you might be able to provide. Sincerely, KS [quoted text, click to view] "Wei Lu [MSFT]" wrote:
> Hello KS, > > How is everythin going? If you have any questions, please feel free to let > me know. > > Sincerely, > > Wei Lu > > Microsoft Online Community Support >
This morning I stumbled upon the tutorial that uses the ReportingService2005
web service as a report data source. Perhaps this is the way to go to accomplish what I'm after, but I'm unable to find a method that gives me the actual names of the datasets or queries/stored procs that are used in the report. GetItemDataSources lists the actual data sources referenced, but not the datasets or dataset source info. Is there a method I can call via SOAP using this web service that will provide me with the dataset information? [quoted text, click to view] "KS" wrote:
> Thank you for the reply and my apologies in the delayed response as I was out > last week. > > Yes, I understand that the RDL is an XML file that should be able to be used > as a report data source, but as I posted in my original message the "how to" > is what continues to elude me. For the sake of simplicity, that me pose a > single question in hopes that you might be able to provide me with some > direction. > > If you were going to build a report that accepted as a parameter a report > name and then returned the different data sources that were used in the > selected report, how would you accomplish this assuming you had the source > RDL as your data source? Would you create an SSIS package to extract the > data from a renamed RDL (to XML) file? Would you use the OPENXML T-SQL > statement to query the RDL source data (example would be appreciated)? > > Thank you in advance for any assistance you might be able to provide. > > Sincerely, > KS > "Wei Lu [MSFT]" wrote: > > > Hello KS, > > > > How is everythin going? If you have any questions, please feel free to let > > me know. > > > > Sincerely, > > > > Wei Lu > > > > Microsoft Online Community Support > >
Hi KS,
There is not such method you could call via SOAP to provide the dataset information from the report definition file. My suggestion is using the GetReportDefinition() method of the ReportServices2005 class to get the definition of the report and save it to a XmlDocument object. Then you could try to query the data set information from the XmlDocument object. For example: LocalReportService2005.ReportingService2005 rs = new LocalReportService2005.ReportingService2005(); rs.Credentials = System.Net.CredentialCache.DefaultCredentials; rs.Url = "http://localhost/reportserver/reportservice2005.asmx"; Property pName = new Property(); pName.Name = "Name"; Property pDesc = new Property(); pDesc.Name = "Description"; Property[] properties = new Property[2]; properties[0] = pName; properties[1] = pDesc; string reportName = "/AdventureWorks Sample Reports/Company Sales"; try { byte[] b = rs.GetReportDefinition(reportName); MemoryStream strm = new MemoryStream(b); XmlDocument doc = new XmlDocument(); doc.Load(strm); MemoryStream strmOut = new MemoryStream(); XmlNodeList nodes = doc.GetElementsByTagName("DataSet"); foreach (XmlNode node in nodes) { Console.WriteLine(node.InnerXml); } Hope this will be helpful! Sincerely, Wei Lu Microsoft Online Community Support
Hi KS,
Does my suggestion make sense to you? Please let me know if you have any question. Sincerely, Wei Lu Microsoft Online Community Support
Thank you for the information, Wei. Admittedly it's a little over my head at
the moment, but your example gives me an avenue for learning something new. Thanks for the direction. KS [quoted text, click to view] "Wei Lu [MSFT]" wrote:
> Hi KS, > > Does my suggestion make sense to you? Please let me know if you have any > question. > > Sincerely, > > Wei Lu > > Microsoft Online Community Support >
Hi Wei,
What would be the disadvantages of using XmlNodeList nodes = doc.GetElementsByTagName("Query") OR XmlNodeList nodes = doc.GetElementsByTagName("CommandText") Versus XmlNodeList nodes = doc.GetElementsByTagName("DataSet") It looks like if I just want the name of the StoredProc OR the select statement the report is based off <CommandText> would do the trick. Correct? Also, where are the custom Properties being used in your sample? And do I necessarily need a directive (namespace) to use the custom Properties? Or maybe I'm just overlooking something. Thanks, Eric [quoted text, click to view] "Wei Lu [MSFT]" wrote:
> Hi KS, > > There is not such method you could call via SOAP to provide the dataset > information from the report definition file. > > My suggestion is using the GetReportDefinition() method of the > ReportServices2005 class to get the definition of the report and save it to > a XmlDocument object. > > Then you could try to query the data set information from the XmlDocument > object. > > For example: > > > LocalReportService2005.ReportingService2005 rs = new > LocalReportService2005.ReportingService2005(); > rs.Credentials = System.Net.CredentialCache.DefaultCredentials; > rs.Url = "http://localhost/reportserver/reportservice2005.asmx"; > Property pName = new Property(); > pName.Name = "Name"; > Property pDesc = new Property(); > pDesc.Name = "Description"; > Property[] properties = new Property[2]; > properties[0] = pName; > properties[1] = pDesc; > > string reportName = "/AdventureWorks Sample Reports/Company > Sales"; > > try > { > byte[] b = rs.GetReportDefinition(reportName); > MemoryStream strm = new MemoryStream(b); > XmlDocument doc = new XmlDocument(); > doc.Load(strm); > > MemoryStream strmOut = new MemoryStream(); > > > XmlNodeList nodes = doc.GetElementsByTagName("DataSet"); > > foreach (XmlNode node in nodes) > { > Console.WriteLine(node.InnerXml); > > } > > Hope this will be helpful! > > Sincerely, > > Wei Lu > > Microsoft Online Community Support >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |