Dynamically Adding DataFields to Chart

I am in need of some advice on my first project.
I have a chart which displays as I would expect when a static query is
executed. I need to somehow dynamically add the datafields to the chart at
runtime which is where I am coming unstuck. The number and name of
datafields could change each time the report is run so they absolutely need
to be dynamic.
Any advice on the best way to approach thsi problem would be much
appreciated.
Thanks, SimonDoes anyone have experience in creating Reports with Dynamic fields like
this. The only solution I can think of is to programatically build the RDL
each time, save it to the server then call the report to render it for the
client. It seems very inefficient which is why I wanted to ask others with
more experience in this field '?
Simon
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote
> I am in need of some advice on my first project.
> I have a chart which displays as I would expect when a static query is
> executed. I need to somehow dynamically add the datafields to the chart at
> runtime which is where I am coming unstuck. The number and name of
> datafields could change each time the report is run so they absolutely
need
> to be dynamic.
> Any advice on the best way to approach thsi problem would be much
> appreciated.
> Thanks, Simon
>

Dynamic Where clause

I need to build a dynamic where clause. Somehow I can't get it to work.
Here's the stored procedure. I believe I'm not concat. the
@.WhereOrderByClause parameter correct? Does anybody have any idea's?
Joshua
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
-- Insert statements for procedure here
SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode
& @.WhereOrderByClause
END> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
Eep.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html|||You need to use EXEC or sp_executesql to run dynamic SQL. You'll have to
store the exec string in a variable and then execute it:
DECLARE @.str varchar (8000)
set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode'
& @.WhereOrderByClause
EXEC (@.str)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
I need to build a dynamic where clause. Somehow I can't get it to work.
Here's the stored procedure. I believe I'm not concat. the
@.WhereOrderByClause parameter correct? Does anybody have any idea's?
Joshua
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
-- Insert statements for procedure here
SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode
& @.WhereOrderByClause
END|||Tom,
I get this error message'?
Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
Invalid operator for data type. Operator equals boolean AND, type equals
varchar.
Joshua
"Tom Moreau" wrote:

> You need to use EXEC or sp_executesql to run dynamic SQL. You'll have to
> store the exec string in a variable and then execute it:
> DECLARE @.str varchar (8000)
> set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode'
> & @.WhereOrderByClause
> EXEC (@.str)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
> news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
> I need to build a dynamic where clause. Somehow I can't get it to work.
> Here's the stored procedure. I believe I'm not concat. the
> @.WhereOrderByClause parameter correct? Does anybody have any idea's?
> Joshua
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> -- ========================================
=====
> -- Author: JBlubaugh
> -- Create date: 03/13/2006
> -- Description: Gets information for Program
> -- Summary report
> -- ========================================
=====
> ALTER PROCEDURE [cle].[getProgramSummaryRpt]
> -- Add the parameters for the stored procedure here
> @.ProgName varchar(300),
> @.ProgNo int,
> @.StartDate datetime,
> @.EndDate datetime,
> @.ProgCatCode int,
> @.ProgTypeName varchar(30),
> @.OfficeCode varchar(3),
> @.DateCreated datetime,
> @.SortOrder varchar(10),
> @.WhereOrderByClause varchar(500)
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
> if @.StartDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
> @.StartDate
> if @.EndDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
> @.EndDate
> if @.ProgCatCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
> + @.ProgCatCode + ')'
> if @.ProgTypeName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
> (' + @.ProgTypeName + ')'
> if @.OfficeCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
> @.OfficeCode + ')'
> if @.DateCreated IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
> @.DateCreated
> if @.SortOrder = 'p.ProgNo'
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
> else
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC
'
> -- Insert statements for procedure here
> SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode
> & @.WhereOrderByClause
> END
>
>|||Try something like this in case because the first one may not exist
set @.WhereOrderByClause = ' WHERE 1=1'
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgName IN ('
+ @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'|||I've already fixed the other part... this is what i'm trying:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
Declare @.str varchar (8000)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @.WhereOrderByClause = ' WHERE '
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + 'p.ProgName IN (' +
@.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode'
& @.WhereOrderByClause
-- Insert statements for procedure here
EXEC (@.str)
END
"JeffB" wrote:

> Try something like this in case because the first one may not exist
> set @.WhereOrderByClause = ' WHERE 1=1'
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgName IN ('
> + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
>|||& is not for concatenation (someone's been playing with VB/VBScript). You
should use + instead of &
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:363E0952-AC3F-44FF-959E-B822DFAD7EEB@.microsoft.com...
> Tom,
> I get this error message'?
> Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar.|||What if @.ProgName is NULL and @.ProgNo is 3? Then the sql generated
will be 'WHERE AND p.ProgNo IN (3)' which won't work. The initial
setting should be 'WHERE 1 = 1
set @.WhereOrderByClause = ' WHERE '
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause +
'p.ProgName IN (' +
@.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND
p.ProgNo IN (' +
@.ProgNo + ')'|||Doh! I do that a lot - jumping back and forth between VB and T-SQL. :-S
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eReETs6RGHA.1948@.TK2MSFTNGP09.phx.gbl...
& is not for concatenation (someone's been playing with VB/VBScript). You
should use + instead of &
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:363E0952-AC3F-44FF-959E-B822DFAD7EEB@.microsoft.com...
> Tom,
> I get this error message'?
> Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar.|||Use = instead of in, unless you are actually dealing with a list of values
contained within a string. It works either way, but is easier to understand
with the =.
When concatenating the string together, you need to place quotes around your
values, or pass them explicitly as parameters with sp_executesql. Two
single quotes are used to represent single quotes within quotes.
i.e.
set @.ProgName = 'Test'
set @.WhereOrderByClause = ' WHERE p.ProgName = ''' + @.ProgName + ''''
the resulting strign is
WHERE p.ProgName = 'Test'
or, pass the parameters to the sp_executesql:
set @.ProgName = 'Test'
set @.WhereOrderByClause = ' WHERE p.ProgName = @.ProgName'
set @.SelectString = 'Select * from SomeTable ' + @.WhereOrderByClause
SET @.ParmDefinition = '@.ProgName varchar(300)'
/* Execute the string with the first parameter value. */
EXECUTE sp_executesql @.SelectString, @.ParmDefinition, @.ProgName =
@.ProgName
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
> I need to build a dynamic where clause. Somehow I can't get it to work.
> Here's the stored procedure. I believe I'm not concat. the
> @.WhereOrderByClause parameter correct? Does anybody have any idea's?
> Joshua
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> -- ========================================
=====
> -- Author: JBlubaugh
> -- Create date: 03/13/2006
> -- Description: Gets information for Program
> -- Summary report
> -- ========================================
=====
> ALTER PROCEDURE [cle].[getProgramSummaryRpt]
> -- Add the parameters for the stored procedure here
> @.ProgName varchar(300),
> @.ProgNo int,
> @.StartDate datetime,
> @.EndDate datetime,
> @.ProgCatCode int,
> @.ProgTypeName varchar(30),
> @.OfficeCode varchar(3),
> @.DateCreated datetime,
> @.SortOrder varchar(10),
> @.WhereOrderByClause varchar(500)
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
> if @.StartDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
> @.StartDate
> if @.EndDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
> @.EndDate
> if @.ProgCatCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
> + @.ProgCatCode + ')'
> if @.ProgTypeName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
> (' + @.ProgTypeName + ')'
> if @.OfficeCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
> @.OfficeCode + ')'
> if @.DateCreated IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
> @.DateCreated
> if @.SortOrder = 'p.ProgNo'
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
> else
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn
ASC'
> -- Insert statements for procedure here
> SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode
> & @.WhereOrderByClause
> END
>