Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Thursday, 16 February 2012

Parsing an XML Node in a SQL Server Reporting services report.

Nothing more fun than having to parse XML in a report. 

It is basically the following steps:

- modify the query in the dataset to include the XML column

- In the Report Properties (from the report menu pad)

   * Add a Reference to System.XML

   * Paste the code from the attached txt file in Code section (this code is specific to the XML that I was parsing, but the concept could be modified or enhanced as needed based on your requirements)

- Add a column to the report for what you are getting from the XML (notes in my case) and set the textbox Expression to =Code.ParseXML(Fields!{column here no brackets}.Value)

Below is the code to paste into the code section

Function ParseXML (ByVal xmlText as String) AS String
Dim i as Integer
Dim ret as String
Dim xmlDoc As New System.Xml.XmlDocument
Dim nodeList As System.Xml.XmlNodeList
Dim node As System.Xml.XmlNode

Try
xmlDoc.LoadXml(xmlText)
nodeList = xmlDoc.GetElementsByTagName("fd")

' Loop through the nodelist returned by the "fd" query (should be only 1)
For Each node In nodeList
' Loop through all the child nodes of "fd" and format the key-value
  For i = 0 to node.ChildNodes.Count - 1
      ret = ret & node.ChildNodes.Item(i).InnerText & vbCrLf
  Next
Next

'If the DB is Null this sets the value to "N/A" instead of #Error
Catch ex As Exception
  ret = "N/A"
  Return ret
End Try

'Format as date and time if it's an ETA
Try
Dim dt as DateTime
  dt = DateTime.parse(ret)
  ret = "ETA: " & Format(dt, "General Date")
Catch ex As Exception
End Try

Return ret

End Function


While working on this I found a new XML Editor, I have no idea if it works, but I like it, "the interface is plain and convenient"!

 

Thursday, 16 February 2012 14:27:15 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server |  Reporting Services#
Monday, 09 March 2009

SSRS - Reporting Services Subscriptions not sending

Recently we ran into an issue with a Reporting Service Instance that was not sending any email subscriptions.  We could find no errors or obvious issues.  Of course a quick reboot solved the problem, but only temporarily, the next day, the issue was right back.  Time to call out for help, Microsoft Support.

Microsoft Support found the problem and provided us some useful scripts.  Basically the issue was that someone had created a snapshot, that ran every night.  The snapshot ran for 12+ hours, causing performance issues and other problems with all other subscriptions.  This is a "shared" reporting service instance, set up for several application groups.  The scripts from Microsoft were quite useful, i've listed them here, all of them are in my Script Vault.

I found the scripts very useful, as it addresses one of my continual issues, which is addressing capacity in shared environments.  For our Shared Reporting Service Environment, we create a Directory / Folder for each group and tweak permissions to keep each application separated, than we turn over full permissions to each sub-folder to the application group. 

Obviously the danger here is that someone can create a poorly performing report that effects the entire environment.  The scripts included below can help identify which reports run the longest.  We then modified this query a bit more to roll-up the report times, by sub-directory, so we can determine which application is using the most capacity in the environment.  We can then engage this group to optimize their reports or began capacity planning to build them a dedicated environment.

Here is a script listing of what I added to the Script Vault

1.  Find What User runs What Report - (Rpt Svc - Who Runs What)
2.  Find what subscriptions have been run and when - (Rpt Svc - Subscr Run When)
3.  Find what SQL Agent Schedule goes with what Report (Rpt Svc - SQL Agent to Report)
4.  Average Report RunTime - (Rpt Svc - Avg Rpt Runtime)
5.  Average Report RunTime Per Parent Folder - (Rpt Svc - Avg RunTime Pnt Fld)

We then used the last two queries to develop a Reporting Services reports that is available to the administrators of each directory, so they can manage their own performance and capacity, thanks to Tom Reeves,  for developing the last two.

Monday, 09 March 2009 10:57:18 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server |  Reporting Services#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
Calculate Stock Break Even Price
Useable Space
Recent Posts
Archive
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll