Phidiax Tech Blog

Making SQL Server Reporting Play by Your Rules

The BizTalk Rules Engine provides an efficient way of implementing custom flexible business rules and the related vocabulary used by those rules. So if you are already leveraging this solution in your business to house your rules, it seems a bit inefficient to code any needed subset of your BRE rules in SQL Server procedures, functions, integration packages, or even reports. If these rules are to be used by an integration package or a report, it is fairly easy to implement calls directly to your BRE to make use of your existing rules to keep a consistent implementation across your organization. In fact, anywhere you can call C#/VB .NET code, you can call your rules engine!

Server Setup:

For this sample, I am going to assume an existing published policy and vocabulary using XML Schemas as the source condition items and targets for rule use. It is possible to use GAC'd assemblies or database tables as rule source and targets as well. So what is the basic setup here? 

1. Copy the Microsoft.RuleEngine library to the ReportServer\bin folder
2. Copy the Microsoft.RuleEngine library to the Report Builder or Visual Studio folder (if using Report Builder, that is likely C:\Program Files (x86)\Microsoft SQL Server\Report Builder)
3. Update the RSPreviewPolicy.config to change Report_Expression_Default_Permissions from Execution to FullTrust if developing or running in Report Builder (when disconnected from report server)
   
4. Update the rssrvpolicy.config to change Report_Expressions_Default_Permissions from Execution to FullTrust  (Note: if you are familiar with setting up for other libraries that are non Microsoft, you would have to explicitly also add this library to both this configuration files as fully trusted and to the report development preview configuration as well. Because the Rule Engine library has the same public key token as other trusted MS libraries, no need to do that here, only to trust the expressions that call it)
5. Add the service user for the SSRS Service to the BizTalk Application User group (this gives the user rights to load the rules via the re_getruleset stored procedure).
6. Restart your report editing environment to pickup the added library
7. Create an SSRS report on a server with the above steps completed, and setup Report level custom Code and reference it in a placeholder

VB Code Snippet:

1. Connect to the rules engine, and load the indicated ruleset
2. Create the XML document to pass to the rules engine as a typed XML document
3. Execute the rules and return the inner XML of the result 

Function Main(s1 As String, s2 As String) As String
 Dim _ruleConnection  As Microsoft.RuleEngine.RuleSetDeploymentDriver 
 Dim _ruleStore As Microsoft.RuleEngine.RuleStore
 Dim _rsic As Microsoft.RuleEngine.RuleSetInfoCollection
 Dim _rs As Microsoft.RuleEngine.RuleSet
 Dim engine As Microsoft.RuleEngine.RuleEngine

 Try
  _ruleConnection = New Microsoft.RuleEngine.RuleSetDeploymentDriver(".", "BizTalkRuleEngineDb")
  _ruleStore = _ruleConnection.GetRuleStore()
  _rsic = _ruleStore.GetRuleSets("PhidiaxTest", Microsoft.RuleEngine.RuleStore.Filter.LatestPublished)
  _rs = _ruleStore.GetRuleSet(_rsic(0))
  engine = New Microsoft.RuleEngine.RuleEngine(_rs)
  Dim doc As New System.Xml.XmlDocument()
  Dim sXmlContent As String = "<ns0:TestRoot xmlns:ns0='http://phidiax.com/TestSchema'><TestField1>"+s1+"</TestField1><TestField2>"+s2+"</TestField2><TestInt>0</TestInt></ns0:TestRoot>"
  doc.LoadXml(sXmlContent)
  Dim txd As New Microsoft.RuleEngine.TypedXmlDocument("BizTalk_Server_Project1.SchemaTest", doc)

  engine.Assert(txd)
  engine.Execute()
  return txd.Document.InnerXml
 Catch e As Exception
  return e.ToString()
 End Try
End Function

=Code.Main(Parameters!SchemaItem1.Value,Parameters!SchemaItem2.Value)

Sample Report Results: