RAC is a set of stored procedures that run on MS Server 7+.RAC stands for R(eplacement) for the A(ccess) C(rosstab) query.RAC can be used to generate various crosstab like reports with its many options.In addition it can be used to solve pivoting problems generally encountered in data manipulation.Hopefully this tour will give you an understanding of what you can do with RAC.

RAC Basics and Report Options l

These examples illustrate some basic things you can do.The Northwind sample database is used throughout.For additional options see 'RAC Report Options ll' and 'Additional Examples'.Please refer to the doc notes for further explanations and operating instructions.

RAC creates a crosstab type table.The basic parameters needed for this is a row field,a column field, the table that has these fields and the type of aggregate function that summarizes the intersection of each row and column,ie the cell value.

Lets start with a simple tabulation of counts for customers and employees using the orders table.

Here's the last 15 rows along with the overall/grand totals.

The @where parameter is used to specify criteria in @from.Let's limit the crosstab to customers starting with 'T'.Note that single quotes around the T are represented with '~' in the @where parameter.

Most of the parameters have defaults.To change the behaviour of the crosstab specify the parameter with the desired action.For example,to change the sort order of the columns from the default of ascending to descending use the @pvtsortype.

RAC uses an alias for the @transform if present.Here 'cnt' is an alias and the rows are sorted descending.

You can represent an empty cell with any string you want using the @emptycell parameter.Here '0' is inserted for an empty cell.(RAC distinguishes a empty cell from a null value,see the doc notes for more on this subject).

Instead of '0' use 'n/a' (not availiable).

If you want to move the overall (grand) totals to the beginning and the row totals to end of the columns you can use the @grandtotalsposition and/or @rowtotalsposition parameter(s).

The row totals are obtained by default.The @row_totals parameter can be used to suppress the row totals and still obtain the overall totals.

Just the overall totals can be obtained using the @totals_only parameter.

RAC can produce reports based on any number of transform/aggregates.Here are counts and the freight sums for 2 customers. By default RAC sequences the aggregates,based on the order their entered,for each row (customerid). Also by default RAC breaks on the row column.

The breaks on each row can be turned off by the @rowbreak parameter.

Instead of using the default mode of sequencing one aggregate after the other for each row you can choose to stack the aggregates,all rows for the first aggregate followed by all rows or the second aggregate and so on.You can choose which behavior you want with the @blocktype parameter.Changing the default ('seq') to 'stack' produces the following:

Multiple aggregates can be put on the same row by changing the @display parameter default setting ('m' for multiple rows) to 's' for single row.The @space parameter controls how many blanks are between the aggregates.(See the doc notes for how to combine single and multiple rows in a report).

Row and/or column percents can be obtained by using @rpercents and @cpercents parameters. The row percents(of the grant totals) for each cell are in brackets ( [ ] ) while the column percents (of the row totals) are shown in parenthesis ( ).The first cell value is the aggregate value.

The percentages of just the row totals of the grand total can be obtained using @rpercentstotals.

The @from parameter can contain any valid sql 'from' statements which includes joins and derived tables with their own 'where' clauses.Of course there's nothing stopping you from pre-processing your data into a temp table and using it as input to RAC.

The @grpcol and @pvtcol parameters can also be expressions which include functions.If an expression is used which includes a datename it is sorted appropriately.The @functionlen parameter is used to determine how many characters are extracted from the expression to make up the field.

All date fields are converted to mm/dd/yy format and sorted numerically.

By default RAC creates the crosstab report as a local tempory table (this incidently enables multiple users of RAC to avoid contention between their reports).By using the @xtab parameter a global or permanent table can be created.In addition the select of the report can be disabled by using the @return parameter.The next example creates the report as a global temporary table.The table contains an additional field, rd. which is a record counter based on the sort order of the row field (@grpcol-->customerid).

========================================================================

RAC Report Options ll

These options can be used to further enhance a report.

@force

The @force parameter allows columns, whether their present in the data or not, to be placed in any desired order and also filters on them.This parameter is similiar to MS Access PIVOT IN (...). But unlike Access,rows that do not meet the criteria are not automatically included in the report. (See the @forcerows parameter for including all rows with @force).

The @forcerows parameter is used to insert empty rows into the report for rows that were excluded by any criteria (@force,@where, or logic within a derived table).See the doc notes on @force for more informations/restrictions.

@grpart @pvtpart

These parameters can be used to create intervals or groupings of the row and column fields/expressions.This is similiar to the Access Partition function.Intervals can be created for integer and date data.There are serveral more options availiable for dates.See the doc notes.

This example partitions freight into intervals./ranges of 20 starting at 1 and ending at 220.

Dates can be partitioned by days,months or years.Here intervals are formed for 60 days and employees grouped into sets of 3.

@rowruns

This parameter is used to generate running sums of columns down the rows.Any running sum can be obtained by associating any column with any @transform/aggregate.This example shows the running sum of freight (frt) for employees 1 and 2 and the running sum of counts (cnt) for the row totals (totals) of count(*).The order that the runs appear are the order they are input (for a particular @transform).Runs can be be based on any @transform expression.

@colruns

This parameter generates column runnings sums, i.e. runs over all columns for each row. In this example the number in the cell is the count followed by the running sum of the the counts.Runs can be be based on any @transform expression

This example uses @force instead of @where to filter employees (<7) and pre-orders

them.The column runs are accumulated based on the ordering in @force.

@rowfunctions

Rowfunctions allow you to summarize the cell values in each row in a particular way.They allow you to apply aggregates to cells values which are themselves aggregates.You can apply count min,max,sum and average.In this examples the count and max aggregates are applied to the column values.The first function translates to count(count(*)).This returns the count of availiable data for each row.It is also the distinct count in each row.To answer the question what is the max count in each row and what column values does it occur in, the max aggregate can be applied.This is max(count(*)).Note that it is more convient to apply the rowfunctions if @transform is expressed with an alias.You can pose and answer many interesting question be using various @transform aggregates and then applying rowfunctions to them.

@displayrowfunctions

Rowfunctions for each @transform are by default displayed on the same row.Use the @displayrowfunction parameter set to 'm' (multiple lines) to display each rowfunction on a separate row.There will then be a break on the funct field.

@cutpvt

This parameter can be used to suppress the creation and display of all columns.But while the columns are suppressed they are still availiable for internal/computational use by other parameters. So if you don't want to create columns or if the number of columns in your data exceed the limitations of RAC to create a table based on them you can still use RAC to create reports that take all columns and their corresponding cell data into account.

This example is like the preceeding one.While the columns are suppressed all data is availiable to the rowfunctions and running sums.

@colretain

When 'y' a special column [all_cols] is created that will contain a concatenated comma separated list of all the @transform (cell) values.Continuing with the previous example here is the concatenated list of counts for the customers.The columns are suppressed with @cutpvt and the rowfunction count tells how many values go into the string.The order of the string can be changed by using @pvtsortype='d'.Note that [all_cols] is blank for 'totals' since this can produce a very large string and the intent of the table is to show concatenated strings for all rows independent of 'total'. Also note that with @colretain and @cutpvt='y' the row totals can be suppressed.

You can obtain many different types of concatenated lists.Here are listed the unique

category names,which have a categoryid<5, that group products for the first 6 customers.

@colretainpvt

RAC can produce multicell reports where the cell values are concatenated strings Using @colretainpvt with @colretain creates a special type of [all_cols] where a unique column heading is appended to the concatenated string.This string is then used as input to a 2nd run of RAC to create the report.Continuing with the last example suppose we want a report of all the unique categorynames of products for customers and employees where employees are listed by their lastname.The cell value will therefore contain the concatenated string of categorynames for that customer/employee combination.

The first step is to create a tempory table of customers and employees and all their categorynames.This is done by:

exec rac @transform='max(emp_cat) as test', @grpcol='cust',@pvtcol='emp_cat',@printagg='n',@cutpvt='y', @xtab='##steve',@colretain='test',@colretainpvt='y', @from='(select customerid as cust, e.lastname+~/~+categoryname as emp_cat,c.categoryid,a.employeeid from orders as a inner join [order details] as b on a.orderid=b.orderid inner join products as c on c.productid=b.productid inner join categories as d on c.categoryid=d.categoryid inner join employees as e on a.employeeid=e.employeeid ) as a'

A record from the ##steve table looks like:

rd cust all_cols

32 GROSR Callahan/Dairy Products!Meat/Poultry,Davolio/Beverages!Seafood

Each string is separated into parts by commas.Each part starts with a lastname followed by '/.' and categorynames themselves separated by an exclamation mark (!).Obtaining the report requires executing RAC a 2nd time via sp multicell.The multicell procedure parses the all_col string to enable RAC to create the report (see the doc notes for details).

Here's what some of the report looks like.

Please read the doc notes for more details.

===============================================================================

Additional Examples

These examples illustrate how RAC can be further extended.

This example shows the use of virtual column values.A case statement in a derived table is used to create a new field (emp) that holds three categories of employees (1-3,4-6,7-9).The derived field is used to form the columns of the report and two of the field values (cat-1,cat-2) are used in the @rowsum parameter to obtain running sums of freight for customers.

Note that a similiar report could be produced by partitioning the employeeid field.

exec rac @grpcol='customerid',@pvtcol='orders.employeeid',
@transform='sum(freight) as frt',@from='orders',@pvtpart='1,9,3',
@rowruns='1:3(frt),4:6(frt)'

A virtual column can be also be used to show the running sum of 'all' customer freight costs. In this case a row is the concatenation of customerid and orderid since all individual values are to be taken into account.A virtual column value is created that is a constant that will be the single column in the report.The max function is used as the aggregate as it will also return the individual value for a row and column here.

This ends the RAC for SQLServer 7 quick tour, thank you for comming.