Hello All,

I am newbie when it comes to programming. So please bare with me. I have an existing stored procedure that I have been using to construct my dynamic sql which returns a table as a result. I would like to use that stored procedure in LLBLGen tool. When I try to add the stored procedure it comes up as an action stored procedure. I want to return a data table. It should be a retrieval stored procedure?

1) Below is the code for the stored procedure. Please let me know if there is a better way and whether it is possible to do the following in a typed list with group by. I would prefer to use my existing stored procedure.

2) Also can you pass null values using LLBLGen stored procedure functions?

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'SelectClientDefinedMarketPackData')
    BEGIN
        PRINT 'Dropping SelectClientDefinedMarketPackData'
        DROP  Procedure  SelectClientDefinedMarketPackData
    END

GO

PRINT 'Creating Procedure SelectClientDefinedMarketPackData'
GO
CREATE Procedure SelectClientDefinedMarketPackData
    /* Param List */
    @MarketID                   smallint,
    @GeoLevelID                 smallint,
    @GeoPointID                 int,
    @ProductGrpID                   smallint = null,
    @DatePart                   varchar(4) = null,
    @DatePartFilterValue                smalldatetime = null,
    @ShowOneSublevelDetail              bit = null,     
    @IsOurProdGrp                   bit = null,
    @IsFocusProd                    bit = null,
    @BeginDate                  smalldatetime = null,
    @EndDate                    smalldatetime = null

AS

/******************************************************************************
**      File: 
**      Name: SelectClientDefinedMarketPackData
**      Desc: 
**
**      This template can be customized:
**          
**      Return values:
** 
**      Called by:   
**          
**      Parameters:
**      Input                           Output
**   ----------                         -----------
**
**      Auth: 
**      Date: 
*******************************************************************************
**      Change History
*******************************************************************************
**      Date:       Author:             Description:
**      --------        --------                -------------------------------------------
**  
*******************************************************************************/
SET NOCOUNT ON

DECLARE @SQL1 nvarchar(4000), @SQLGroupByColumns nvarchar(2000), @SQLOrderBy nvarchar(2000)
DEcLARE @SQLGeoPointFilter nvarchar(150), @SQLSelectStmt nvarchar(1000), @SQLDateGroupByAndOrderBy nvarchar(1000)

declare @iReturn int

SET @SQL1 = ' '
SET @SQLGroupByColumns = ' '
SET @SQLOrderBy = ' '
SET @SQLGeoPointFilter = ' '
SET @SQLSelectStmt = ' '
SET @SQLDateGroupByAndOrderBy = ' '


DECLARE @levelOrderCursor CURSOR
SET @levelOrderCursor = CURSOR FAST_FORWARD FOR
SELECT LevelOrder FROM GeographicLevelColumnDetail WHERE GeoLevelID = @GeoLevelID

OPEN @levelOrderCursor
DECLARE @levelOrder int

FETCH FROM @levelOrderCursor INTO @levelOrder

CREATE TABLE #tempGeoColumnDetail
(
    GeoLevelID smallint, 
    GeoColName varchar(50),
    LevelOrder tinyint, 
    ColumnName varchar(40), 
    ColumnAlias varchar(40), 
    IsIdentityField bit,
    IsGeoPointName bit
)

CLOSE @levelOrderCursor
DEALLOCATE @levelOrderCursor

IF @ShowOneSublevelDetail = 1
    BEGIN
        SELECT @levelOrder = @levelOrder + 1
    END

INSERT INTO #tempGeoColumnDetail (GeoLevelID, GeoColName, LevelOrder, ColumnName, 
                                    ColumnAlias, IsIdentityField, IsGeoPointName)
            SELECT GeoLevelID, GeoColName, LevelOrder, ColumnName, 
                ColumnAlias, IsIdentityField, IsGeoPointName 
            FROM GeographicLevelColumnDetail 
            WHERE LevelOrder <= @levelOrder

DECLARE @geographicLevelCursor CURSOR 
SET @geographicLevelCursor = CURSOR FAST_FORWARD FOR
    SELECT GeoLevelID, ColumnAlias, IsIdentityField, IsGeoPointName FROM #tempGeoColumnDetail

OPEN @geographicLevelCursor

DECLARE @ColumnAlias varchar(40), @IsIdentityField bit, @IsGeoPointName bit, @GeoLevelIDCur int, @LevelOrder2 int

DECLARE @PrevIsGeoPointName bit

FETCH FROM @geographicLevelCursor INTO @GeoLevelIDCur, @ColumnAlias, @IsIdentityField, @IsGeoPointName
WHILE @@fetch_status = 0
    BEGIN   
        -- grab all geolevel columns
        SET @SQLGroupByColumns = @SQLGroupByColumns + @ColumnAlias + ', '
        IF @IsGeoPointName = 1
            BEGIN
                SET  @SQLOrderBy = @SQLOrderBy + @ColumnAlias + ', '
            END
        SET @PrevIsGeoPointName = @IsGeoPointName

        IF @GeoLevelIDCur = @GeoLevelID AND @IsIdentityField = 1
            BEGIN
                SET @SQLGeoPointFilter = @ColumnAlias + ' = ' + CAST(@GeoPointID AS varchar(100))
            END

        FETCH NEXT FROM @geographicLevelCursor INTO @GeoLevelIDCur, @ColumnAlias, @IsIdentityField, @IsGeoPointName
    END

CLOSE @geographicLevelCursor
DEALLOCATE @geographicLevelCursor

DROP TABLE #tempGeoColumnDetail

    
SET  @SQLGroupByColumns = @SQLGroupByColumns + ' MarketID, MarketName, ProductGroupID, ProductGroupName, IsFocusProdGrp, IsOurProdGrp'
SET  @SQLOrderBy = @SQLOrderBy + ' ProductGroupName '

IF @DatePart IS NOT NULL
    BEGIN
        IF @DatePart = 'qq' 
            BEGIN
                SET  @SQLSelectStmt = @SQLSelectStmt + ', DATEPART(yy, DataDate) AS Year'
                SET  @SQLSelectStmt = @SQLSelectStmt + ', DATEPART(qq, DataDate) AS Quarter'
                SET  @SQLDateGroupByAndOrderBy = @SQLDateGroupByAndOrderBy + ', DATEPART(yy, DataDate) '
                SET  @SQLDateGroupByAndOrderBy = @SQLDateGroupByAndOrderBy + ', DATEPART(qq, DataDate) '
            END
        ELSE 
            BEGIN
                IF @DatePart = 'yy'
                    BEGIN
                        SET  @SQLSelectStmt = @SQLSelectStmt + ', DATEPART(yy, DataDate) AS Year'
                        SET  @SQLDateGroupByAndOrderBy = @SQLDateGroupByAndOrderBy + ', DATEPART(yy, DataDate) '
                    END
                ELSE
                    BEGIN
                        SET  @SQLSelectStmt = @SQLSelectStmt + ', DATEPART(yy, DataDate) AS Year'
                        SET  @SQLSelectStmt = @SQLSelectStmt + ', DATEPART(qq, DataDate) AS Quarter'
                        SET  @SQLSelectStmt = @SQLSelectStmt + ', DATEPART(mm, DataDate) AS Month'
                        SET  @SQLDateGroupByAndOrderBy = @SQLDateGroupByAndOrderBy + ', DATEPART(yy, DataDate) '
                        SET  @SQLDateGroupByAndOrderBy = @SQLDateGroupByAndOrderBy + ', DATEPART(qq, DataDate) '
                        SET  @SQLDateGroupByAndOrderBy = @SQLDateGroupByAndOrderBy + ', DATEPART(mm, DataDate) '
                END
            END

            
    END
ELSE
    BEGIN
        SET @SQLGroupByColumns = @SQLGroupByColumns + ', DataDate '
    END

-- select the columns for output
SET  @SQL1 = 'SELECT ' + @SQLGroupByColumns 
IF @DatePart IS NOT NULL
    BEGIN
        SET @SQL1 = @SQL1 + @SQLSelectStmt
    End

SET  @SQl1 = @SQL1 + ', SUM(PackData) AS PackData '

-- SELECT TABLE
SET  @SQL1 = + @SQL1 + ' FROM ClientDefinedTargetedMarketPackData '

-- WHERE
SET  @SQl1 = @SQL1 + ' WHERE '
SET  @SQL1 = @SQL1 + ' MarketID = ' +  CAST(@MarketID AS varchar(100))
SET  @SQL1 = @SQL1 + ' AND ' + @SQLGeoPointFilter + ' '

IF @ProductGrpID IS NOT NULL
    BEGIN
        SET  @SQL1 = + @SQL1 + ' AND ProductGroupID = ' + CAST(@ProductGrpID AS varchar(1000)) 
    END

IF @DatePartFilterValue IS NOT NULL AND @DatePart IS NOT NULL
    BEGIN
        SET  @SQL1 = + @SQL1 + ' AND DATEPART(' + @DatePart + ', DataDate) = DATEPART('+ @DatePart + ',''' + CAST(@DatePartFilterValue AS varchar(20)) + ''') '
    
    END

IF @IsOurProdGrp IS NOT NULL
    BEGIN
        SET  @SQL1 = + @SQL1 + ' AND IsOurProdGrp = ' + CAST(@IsOurProdGrp AS char(1))
    END
    
IF @IsFocusProd IS NOT NULL
    BEGIN
        SET  @SQL1 = + @SQL1 + ' AND IsFocusProdGrp = ' + CAST(@IsFocusProd AS char(1))
    END
    
IF @BeginDate IS NOT NULL
    BEGIN
        SET  @SQL1 = @SQL1 + ' AND DataDate > ' + QUOTENAME(CAST(@BeginDate AS varchar(20)),'''') + ' '
    END

IF @EndDate IS NOT NULL
    BEGIN
        SET  @SQL1 = @SQL1 + ' AND DataDate < ' + QUOTENAME(CAST(@EndDate AS varchar(20)), '''') + ' '
    END

-- GROUP BY
IF @DatePart IS NOT NULL
    BEGIN
        SET @SQLGroupByColumns = @SQLGroupByColumns + @SQLDateGroupByAndOrderBy
    END
SET  @SQL1 = @SQL1 + ' GROUP BY ' + @SQLGroupByColumns
        
        
-- ORDER BY
IF @DatePart IS NOT NULL
    BEGIN
        SET @SQLOrderBy = @SQLOrderBy + @SQLDateGroupByAndOrderBy
    END
SET  @SQL1 = @SQL1 + ' ORDER BY ' + @SQLOrderBy

-- EXEC(@SQL1)
EXEC @iReturn = sp_executesql @SQL1



GO

GRANT EXEC ON SelectClientDefinedMarketPackData TO PUBLIC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Thanks,

Vijay

To make it a retrieval procedure: go to the catalog explorer, browse to the procedure in the catalog, click open the node and select #0, then right click and select the menu entry to change the value to 1 or 2 (1 will result in a datatable, 2 in a dataset).

Your procedure is very complex, so I can't say in one statement how to port that to llblgen pro predicates. Overall it seems it is doable (it looks like a custom search routine). Could you explain a bit what each section does, or perhaps more efficient: what it really does (i.e.: searches on customers based on fields in order and customer)

Thanks for the info on the stored procedures.

I am working with pharmaceutical sales data. I want to create a web based reporting application. I would like to create a general mechanism by which I can 'slice and dice' the data. (i.e. get me all volume information for Prod B in the first quarter of 2004, get me the year to date volume totals for Product B's targeted market. Get me regional volume totals for the North East. Summarize volume data at the country, area, region, and territory level. Get me the highest volume total for territory A. Get me product XYZ group volume totals for market A for the last two years) I am not sure if that's at all possible?

I have a table (PACK) which has the outlet (Hospital) level volume data for products in each month. I have two other tables for my Product information and Outlet level information (Name, Address, State, Zip). I then have different tables which hold my companies geographic level information (i.e. Country, Area, Region, Territory). I have another table called market which I can define a market and associate product groups (Product Group Table) to that market. A product group has several products (from Product Table). I have another table called target which associates an outlet to a market. Finally I have a table called geographic level which holds the ordering of each geo level and the look up tables for Country, Area, Region, and Territory. I would like to create a mechanism by which I can summarize the data on different geographic levels, different time periods (qtr, yearly, monthly, 6m avg), markets and product groups. These geographic levels have to be dynamic so that I can work with different pharmaceutical companies. Some pharma companies may have more geographic divisions or levels than others. I am not sure if my approach is right and I would appreciate any input or links to resources on designing such an application.

I tried using a typed list in which I selected all the different tables. (country, area, region, territory, outlet, product, pack, market, product group and target) I would like to use this typed list to group the data by geographic levels (country, area, region) and/or different time periods, markets, product groups etc. But the problem that I encountered is that when I group by country all the columns that I don't want to be grouped are selected and I get an error saying that the column is not in the group by statement or an aggergate function is not applied. How do I remove a selected field from a list?

I have the tables:

[code]

CREATE TABLE [Pack] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [ProdID] [smallint] NOT NULL , [OutletID] [int] NOT NULL , [DataValue] [float] NOT NULL , [DataDate] [smalldatetime] NOT NULL , CONSTRAINT [PK_Pack] PRIMARY KEY NONCLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [FK_Pack_Outlet_Geo] FOREIGN KEY ( [OutletID] ) REFERENCES [Outlet_Geo] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_Pack_Product] FOREIGN KEY ( [ProdID] ) REFERENCES [Product] ( [ProdId] ) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY]

CREATE TABLE [Outlet_Geo] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Address] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ZipCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ZipPlus4Ext] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Outlet] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [FK_Outlet_ZipCode] FOREIGN KEY ( [ZipCode] ) REFERENCES [ZipCode_Geo] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY]

CREATE TABLE [Product] ( [ProdId] [smallint] IDENTITY (1, 1) NOT NULL , [ProductName] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [MfcrID] [int] NULL , [PkgQty] [int] NULL , [FormDescr] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ( [ProdId] ) ON [PRIMARY] , CONSTRAINT [FK_Product_Manufacturer] FOREIGN KEY ( [MfcrID] ) REFERENCES [Manufacturer] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY]

CREATE TABLE [Territory_Geo] ( [ID] [smallint] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [UpperGeoLevelID] [tinyint] NOT NULL , CONSTRAINT [PK_Territory_Geo] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [FK_Territory_Geo_Region_Geo] FOREIGN KEY ( [UpperGeoLevelID] ) REFERENCES [Region_Geo] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY]

CREATE TABLE [Region_Geo] ( [ID] [tinyint] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [UpperGeoLevelID] [tinyint] NOT NULL , CONSTRAINT [PK_Region_Geo] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [FK_Region_Geo_Area_Geo] FOREIGN KEY ( [UpperGeoLevelID] ) REFERENCES [Area_Geo] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY]

CREATE TABLE [Area_Geo] ( [ID] [tinyint] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [UpperGeoLevelID] [tinyint] NOT NULL , CONSTRAINT [PK_Area_Geo] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [FK_Area_Geo_Country_Geo] FOREIGN KEY ( [UpperGeoLevelID] ) REFERENCES [Country_Geo] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY]

CREATE TABLE [Target] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [ClientDefMarketID] [smallint] NOT NULL , [OutletID] [int] NOT NULL , [TerritoryID] [smallint] NOT NULL , [DateAdded] [datetime] NOT NULL , [DateModified] [datetime] NULL , CONSTRAINT [PK_Target] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [FK_Target_ClientDefinedMarket] FOREIGN KEY ( [ClientDefMarketID] ) REFERENCES [ClientDefinedMarket] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_Target_Outlet] FOREIGN KEY ( [OutletID] ) REFERENCES [Outlet_Geo] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_Target_Territory_Geo] FOREIGN KEY ( [TerritoryID] ) REFERENCES [Territory_Geo] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY]

CREATE TABLE [GeographicLevel] ( [GeoLevelID] [smallint] IDENTITY (1, 1) NOT NULL , [GeoColName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LevelOrder] [tinyint] NOT NULL , [ContainsValues] [bit] NOT NULL , [TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , CONSTRAINT [PK_GeographicLevel] PRIMARY KEY CLUSTERED ( [GeoLevelID] ) ON [PRIMARY] ) ON [PRIMARY]

I am working with pharmaceutical sales data. I want to create a web based reporting application. I would like to create a general mechanism by which I can 'slice and dice' the data. (i.e. get me all volume information for Prod B in the first quarter of 2004, get me the year to date volume totals for Product B's targeted market. Get me regional volume totals for the North East. Summarize volume data at the country, area, region, and territory level. Get me the highest volume total for territory A. Get me product XYZ group volume totals for market A for the last two years) I am not sure if that's at all possible?

Uh....ever hear of OLAP?

VijayB wrote:

Thanks for the info on the stored procedures.

I am working with pharmaceutical sales data. I want to create a web based reporting application. I would like to create a general mechanism by which I can 'slice and dice' the data. (i.e. get me all volume information for Prod B in the first quarter of 2004, get me the year to date volume totals for Product B's targeted market. Get me regional volume totals for the North East. Summarize volume data at the country, area, region, and territory level. Get me the highest volume total for territory A. Get me product XYZ group volume totals for market A for the last two years) I am not sure if that's at all possible?

Sure

The feature you should be using is 'dynamic lists', which allows you to create a dynamic list of fields in tabular format, which you can apply aggregate functions on, group by, having clauses etc. Dynamic lists are discussed in the Typedview/typed list section of the documentation and also in the aggregate/expression section of the documentation (in the using the generated code section).

I have a table (PACK) which has the outlet (Hospital) level volume data for products in each month. I have two other tables for my Product information and Outlet level information (Name, Address, State, Zip). I then have different tables which hold my companies geographic level information (i.e. Country, Area, Region, Territory). I have another table called market which I can define a market and associate product groups (Product Group Table) to that market. A product group has several products (from Product Table). I have another table called target which associates an outlet to a market. Finally I have a table called geographic level which holds the ordering of each geo level and the look up tables for Country, Area, Region, and Territory. I would like to create a mechanism by which I can summarize the data on different geographic levels, different time periods (qtr, yearly, monthly, 6m avg), markets and product groups. These geographic levels have to be dynamic so that I can work with different pharmaceutical companies. Some pharma companies may have more geographic divisions or levels than others. I am not sure if my approach is right and I would appreciate any input or links to resources on designing such an application.

As Jim also said, OLAP could help you here, or better: the analysis services of sqlserver. OLAP allowes you to define n-dimensional resultsets and pivotal tables (i.e.: based on 2 parameters you get a new resultset, which also can lead per cell to new resultsets). OLAP is used for datamining, the thing you actually are doing.

As OLAP uses resultset based SQL and is not entity related, it is not directly supported by LLBLGen Pro. You can however first try to grab the data in tabular form using the dynamic lists. If that doesn't work, you can try to write OLAP stored procedures and call them from LLBLGen Pro code, though I'd first go for the dynamic lists.

I tried using a typed list in which I selected all the different tables. (country, area, region, territory, outlet, product, pack, market, product group and target) I would like to use this typed list to group the data by geographic levels (country, area, region) and/or different time periods, markets, product groups etc. But the problem that I encountered is that when I group by country all the columns that I don't want to be grouped are selected and I get an error saying that the column is not in the group by statement or an aggergate function is not applied. How do I remove a selected field from a list?

The current version, 1.0.2004.1, has a bit limited typed list editor. In 1.0.2004.2 this is changed, though that's still beta. However with a dynamic list, you are more flexible and can control which fields are in the resultset in your code.

If you have a specific example you want to be converted to a dynamic list, let me know and I'll help you get started.