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?
Basic Steps in the Setup for using your BRE Rules from SSRS:
- Copy the Microsoft.RuleEngine library to the ReportServer\bin folder
- 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)
- 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)
- 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)
- 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).
- Restart your report editing environment to pickup the added library
- 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:
Make sure to right-click an empty area in the designer and select Report Properties and paste this code into the Code tab of Report Properties. To make the code work, you'll need to replace the BRE connection information with your local BRE DB and DB Server, in addition to correctly representing the Ruleset name, the XML Schema and XML Content.
This VB Code snippet for the report level custom code will perform the following:
- Connect to the rules engine, and load the indicated ruleset
- Create the XML document to pass to the rules engine as a typed XML document
- 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
Create a text box within the report using the expression as follows to call the rules engine above (here we have report parameters we are passing into the function):
=Code.Main(Parameters!SchemaItem1.Value,Parameters!SchemaItem2.Value)
Sample Report Results: