OBIEE XMLViewSerive.executeXMLQuery does not accept filters in ReportParams.filterExpressions

OBIEE version:

11.1.1.5.0

Symptom:

I need to evoke XMLViewService.executeXMLQuery() with different saved filters. I tried to set the filter in ReportParams.filterExpressions. The server returned the query result without any complaints. But the results were always the same as the result without any filters.

Work-around:

Set filters directly in ReportRef.

Bind Telerik MVC TreeView to XElement

In order to bind Telerik MVC TreeView to an XElement, you need to define a recursive function, not a helper. For example,

@using Telerik.Web.Mvc.UI.Fluent;
@using System.Xml.Linq;
@model XElement

@functions {
 void BindXElement(TreeViewItemFactory item, XElement elem){
  var node = item.Add().Text(elem.Name.LocalName);
  foreach (var e in elem.Elements()) {
   node.Items(subItem => BindXElement(subItem, e));
  }
 }
}

@(Html.Telerik().TreeView()
  .Name("TreeView")
  .Items(item => BindXElement(item, Model))
)

A Killer Example for Functional Programming

Published at dotnetslakers.

Get SQL Expression out of WhereClause

Introduction

Several years ago I posted a method to get SQL expression out of a WhereClause. I did not dig deep enough, so it was practically unusable because of Null Object Exception. This post is a long overdue correction.

Implementation

Put the following code in a .cs file under App_Code (website) or Shared (web application) folder.

using BaseClasses;
using BaseClasses.Data;
using BaseClasses.Data.SqlProvider;

namespace DingJing {
	public static class ExtensionWhereClause {
		public static string GetSQL(this WhereClause wc, BaseTable tbl) {
			var f = new CompoundFilterExt(wc.GetFilter() as CompoundFilter);
			return f.GetSQL(tbl.DataAdapter);
		}
	}

	class CompoundFilterExt : CompoundFilter {
		public CompoundFilterExt(CompoundFilter cf)
			: base(cf.CompoundingOperator, cf.GetFilters()) { }

		public string GetSQL(IRelationalDataAdapter adapter) {
			var arg = new SqlGenerationArgs() {
				Adapter = adapter,
				Encoder = new SqlFragmentEncoder()
			};
			
			var tjl = new TableJoinList();
			var s = ToSql(arg, ref tjl);
			return s.Expression;
		}
	} 
}

The above code adds an extension method GetSQL() to WhereClause. To get the SQL expression, you only need 1 line of code. For example,

public override WhereClause CreateWhereClause() {
	var wc = base.CreateWhereClause();
	if (wc != null)
		SQLClause.Text = wc.GetSQL(OrdersTable.Instance);

	return wc;
}

DataStage Shared Container Parameters

Shared containers can have parameters. They are passed literally. Single quotes must be escaped with "\" character.


(v8.1)

Smart Search Filter

Introduction

In Iron Speed Designer, it is very easy to add a search filter above a table control. It is also very easy to config the filter to work in Equals, StartsWith, EndsWith or Contains mode. At design time, I always set search filters in Contains mode to get the most search power. At run-time, however, Contains mode sometimes returns too many hits. These are the times I wish I could double-quote the search text, and the filter would automatically switch to Equals mode.

This turns out to be a pretty straightforward customization.

Code customization

Use the following convention to define a search filter's operator:

Text	Operator
"search term"	Equals
"search term	StartsWith
search term"	EndsWith
search term	Contains

Override the table control's CreateWhereClause() method. Find the code block that defines the search behavior. Insert the following code right before the WhereClause is declared.

var searchOp = BaseFilter.ComparisonOperator.Contains;
if (formatedSearchText.StartsWith("\"") && formatedSearchText.EndsWith("\"")) {
  searchOp = BaseFilter.ComparisonOperator.EqualsTo;
  formatedSearchText = formatedSearchText.Substring(1, formatedSearchText.Length - 2);
} else if (formatedSearchText.StartsWith("\"")) {
  searchOp = BaseFilter.ComparisonOperator.Starts_With;
  formatedSearchText = formatedSearchText.Substring(1);
} else if (formatedSearchText.EndsWith("\"")) {
  searchOp = BaseFilter.ComparisonOperator.Ends_With;
  formatedSearchText = formatedSearchText.Substring(0, formatedSearchText.Length - 1);
}

Then replace the search operator in the WhereClause:

WhereClause search = new WhereClause();
search.iOR(CustomersTable.CustomerID, searchOp, formatedSearchText, true, false);
search.iOR(CustomersTable.CompanyName, searchOp, formatedSearchText, true, false);
search.iOR(CustomersTable.EmailAddress, searchOp, formatedSearchText, true, false);
search.iOR(CustomersTable.City, searchOp, formatedSearchText, true, false);
search.iOR(CustomersTable.Region, searchOp, formatedSearchText, true, false);
search.iOR(CustomersTable.PostalCode, searchOp, formatedSearchText, true, false);

Conclusion

Now you have a smart search filter.

DataStage Custom Routine to Get a File Size

Below is a DataStage custom transform routine to get the size of a file. The full path of the file is passed in as a parameter called "Filename".

CMD = "ls -la " : Filename : " | awk '{print $5}'"
CALL DSExecute("UNIX",CMD,Output,SystemReturnCode)
size = Group(Output, @FM, 1)
Ans = If Num(size) Then size Else -1

If the file doesn't exist, -1 is returned.