Stored Procedures usage in AdHoc 6.0

Since version 6.0, Izenda Reports can expose existing in MSSQL database Stored Procedures (SP) as DataSources for designed reports. This tutorial explains how you can create the appropriate SP in MSSQL 2005, and include it as a DataSourcein a report. Northwind sample database will be used in the examples. The main considerations are:

• Creating or using an existing stored procedure in MSSQL 2005
• Making the SP visible in Izenda Reports
• IMPORTANT- Filtering the Equals select drop-down
• Building the report

1. SP in MSSQL 2005.

• Name of SP will be used as DataSource name in the list of DataSources in ReportDesigner.
• SP parameters generally can be input, output, input output, and some other types. AdHoc supports only input parameters, while parameters of other types are ignored. Input parameters play role of columns which are used in WHERE clause of SELECT statement.
• To be suitable for AdHoc, SP must return usual SELECT statement as result - this will be treated as result of usual SELECT statement from SQL table. Columns presenting in final SELECT statement in SP body, will be available as fields of DataSource (SP) in report.

CREATE PROCEDURE GetContact @companyName varchar (250) AS
	BEGIN 
		SELECT ContactName, Phone FROM Customers 
		WHERE CompanyName LIKE '%'   @companyName   '%' 
	END        

Here we have SP named "GetContact", which accepts one input parameter - name of a company, and returns two columns - a person and a phone number for companies that have similar names. If we will execute this SP in MSSQL 2005 using Customers table from Northwind, we will get the result shown at the figure 1. Figure

Figure 1. Results of sample SP execution in MSSQL 2005.

2. Adding code to the Izenda Reports for SPs.

void Session_Start(object sender, EventArgs e)
{
	AdHocSettings.AdHocConfig = new CustomAdHocConfig();
}
[Serializable]
public class CustomAdHocConfig : DatabaseAdHocConfig
{
	public override void ConfigureSettings()
	{
		AdHocSettings.VisibleTables = new string[] {"GetContact", "DummyTable"};
	}
}

Visible tables must contain exact names of all SP that should be available as DataSources. Attention - when VisibleTables is empty, all tables are visible by default, while all SP are hidden. If VisibleTables will contain any names, then all tables not included in VisibleTables will become hidden, and must be also included in VisibleTables to be used together with SP. In this code example you DummyTable is added to VisibleTables, because we want it to be still visible.

4. Adding code for filtering the equals select drop-down.

Stored procedures generate sql for you and override the default queries created by Izenda Reports. Hence, the equals select drop-down will not be properly filtered because the stored procedure will not reflect the filter. In order to do this, you must populate the drop-down yourself using the ProcessEqualsSelect method.

This will enable you to fully control what dropdown options are shown to the user.

// YOUR method to get the values which are to be used in the equals select dropdown   
public string[] getColumnsValues(string columnName, string dataSource) 
{ 
	string sql = string.Format(@"SELECT TOP 500 {0} FROM {1} ", columnName, dataSource); 

	System.Data.IDbCommand command = Izenda.AdHoc.AdHocContext.Driver.CreateCommand(sql); 
	string[] results = null; 
	try 
	{ 
		command.ExecuteReader().GetValues(results); 
	} 
	finally 
	{ 
		if (command.Connection.State == System.Data.ConnectionState.Open) 
			command.Connection.Close(); 
	} 
	return results; 
} 

// Izenda Reports method to populate the equals select dropdown with your values  
public override string[] ProcessEqualsSelectList(Izenda.AdHoc.Database.Column column) 
{ 
	bool YouWantToUseCategories = true; 
	if(YouWantToUseCategories) 
		return getColumnsValues(column.Name, "Categories"); 
	return base.ProcessEqualsSelectList(column); 
}

4. Building report using SP as DataSource.

Figure 2. SP in DataSources list.

Notice that all DataSources represented by SP, have "(SP)" in the end of name. After SP selected as DataSource, move to other reports pages, for example - to Fields. Notice that some of fields have "(Param)" in the end of name (figure 3).

Figure 3. List of fields for "GetContact (SP)" DataSource.

These fields represent input parameters of SP, and can not be used as output fields. In our example we selected two fields - ContactName and Phone, which are columns in the final SELECT statement of our SP (see 1.) The last that we need before executing report is to set value for input parameter "companyName". This can be done at the Filters tab (figure 4).

Figure 4. Assigning value to SP parameter at Filters tab.

To assign values to parameters of SP, you need to select field with the name of parameter (it will also contain "(Param)" in the end of name), then select operator Equals, and type in the value for the parameter. In this example value is "Gourmet", exactly as for the test usage of SP in the MSSQL 2005 (figure 1). Now report can be saved and executed. As the result, you should see in the ReportViewer table, presented at figure 5.

Figure 5. Results of executing report, using "GetContact" SP as DataSource.