I'm trying to use the report module, and when I run the Test Query option it tells me Test Successful, n records returned.  However as soon as I hit Update the page tells me There is an error in your SQL Query on line X.  When I go back in to look at it the bottom part has been cut off.  The following query is what I'm trying to run:

 

---

 

declare @cname nvarchar(50)
set @cname = 'TestTest
declare @cpid int
set @cpid = (select PropertyDefinitionID from dnn_ProfilePropertyDefinition where PropertyName='Company')
declare @cid int
set @cid = (select CompanyID from sn_Companies where CompanyName = @cname)

DECLARE @enddate datetime, @startdate datetime
SET @enddate = CAST(FLOOR(CAST(GETDATE() - 1 AS float)) AS datetime)
IF DATEPART(day, @enddate) > 15
    SET @startdate = DATEADD(day, -DATEPART(day, @enddate)+16, @enddate)
ELSE
    SET @startdate = DATEADD(day, -DATEPART(day, @enddate)+1, @enddate)

SELECT
--    DISTINCT
    PROJECTS.ProjectName,
    ISSUES.IssueId,
    ISSUES.IssueTitle,
    USERS.DisplayName,
    ISSUES.DateCreated,
    CATEGORIES.CategoryName,
    PRIORITIES.PriorityName,
    STATUS.StatusName,
--    COMMENTS.Hours,
    CSR.Multiplier * PSR.Multiplier Multiplier,
    SUM(COMMENTS.Hours) Hours,
    COMPANIES.Rate Rate,
    CSR.Multiplier * PSR.Multiplier * SUM(Comments.Hours) * COMPANIES.Rate TicketCharge
FROM
    dnn_ONYAK_SIGMAPRO_Projects PROJECTS,
    dnn_ONYAK_SIGMAPRO_Issues ISSUES,
    dnn_ONYAK_SIGMAPRO_IssueComments COMMENTS,
    dnn_ONYAK_SIGMAPRO_ProjectCategories CATEGORIES
        JOIN sn_CategoryServiceRates CSR ON    CATEGORIES.CategoryID = CSR.CategoryID,
    dnn_ONYAK_SIGMAPRO_ProjectPriorities PRIORITIES
        JOIN sn_PriorityServiceRates PSR ON    PRIORITIES.PriorityID = PSR.PriorityID,
    dnn_ONYAK_SIGMAPRO_ProjectStatus STATUS,
    dnn_Users USERS,
--    sn_CategoryServiceRates CSR,
--    sn_PriorityServiceRates PSR,
    sn_Companies COMPANIES,
    (SELECT UserID FROM dnn_UserProfile    WHERE PropertyDefinitionID = @cpid AND PropertyValue = @cname) COMPANYUSERIDS
WHERE
    ISSUES.ProjectID = PROJECTS.ProjectId AND
    ISSUES.RequesterID = COMPANYUSERIDS.UserID AND
    COMMENTS.DateCreated <= @enddate AND
    COMMENTS.DateCreated >= @startdate AND
    ISSUES.IssueId = COMMENTS.IssueId AND
    ISSUES.IssueCategoryId = CATEGORIES.CategoryID AND
    ISSUES.IssuePriorityId = PRIORITIES.PriorityId AND
    ISSUES.IssueStatusId = STATUS.StatusID AND
    USERS.UserID = ISSUES.RequesterID AND
--    ISSUES.IssuePriorityId = PSR.PriorityID    AND
--    ISSUES.IssueCategoryId = CSR.CategoryID    AND
    COMPANIES.CompanyID = @cid
GROUP BY
    PROJECTS.ProjectName,
    ISSUES.IssueId,
    ISSUES.IssueTitle,
    USERS.DisplayName,
    ISSUES.DateCreated,
    CATEGORIES.CategoryName,
    PRIORITIES.PriorityName,
    STATUS.StatusName,
--    COMMENTS.Hours,
--    COMMENTS.CommentId,
    CSR.Multiplier,
    PSR.Multiplier,
    COMPANIES.Rate

---

 

And this is what remains after hitting Update:

---

 

declare @cname nvarchar(50)
set @cname = 'TestTest'
declare @cpid int
set @cpid = (select PropertyDefinitionID from dnn_ProfilePropertyDefinition where PropertyName='Company')
declare @cid int
set @cid = (select CompanyID from sn_Companies where CompanyName = @cname)

DECLARE @enddate datetime, @startdate datetime
SET @enddate = CAST(FLOOR(CAST(GETDATE() - 1 AS float)) AS datetime)
IF DATEPART(day, @enddate) > 15
    SET @startdate = DATEADD(day, -DATEPART(day, @enddate)+16, @enddate)
ELSE
    SET @startdate = DATEADD(day, -DATEPART(day, @enddate)+1, @enddate)

SELECT
--    DISTINCT
    PROJECTS.ProjectName,
    ISSUES.IssueId,
    ISSUES.IssueTitle,
    USERS.DisplayName,
    ISSUES.DateCreated,
    CATEGORIES.CategoryName,
    PRIORITIES.PriorityName,
    STATUS.StatusName,
--    COMMENTS.Hours,
    CSR.Multiplier * PSR.Multiplier Multiplier,
    SUM(COMMENTS.Hours) Hours,
    COMPANIES.Rate Rate,
    CSR.Multiplier * PSR.Multiplier * SUM(Comments.Hours) * COMPANIES.Rate TicketCharge
FROM
    dnn_ONYAK_SIGMAPRO_Projects PROJECTS,
    dnn_ONYAK_SIGMAPRO_Issues ISSUES,
    dnn_ONYAK_SIGMAPRO_IssueComments COMMENTS,
    dnn_ONYAK_SIGMAPRO_ProjectCategories CATEGORIES
        JOIN sn_CategoryServiceRates CSR ON    CATEGORIES.CategoryID = CSR.CategoryID,
    dnn_ONYAK_SIGMAPRO_ProjectPriorities PRIORITIES
        JOIN sn_PriorityServiceRates PSR ON    PRIORITIES.PriorityID = PSR.PriorityID,
    dnn_ONYAK_SIGMAPRO_ProjectStatus STATUS,
    dnn_Users USERS,
--    sn_CategoryServiceRates CSR,
--    sn_PriorityServiceRates PSR,
    sn_Companies COMPANIES,
    (SELECT UserID FROM dnn_UserProfile    WHERE PropertyDefinitionID = @cpid AND PropertyValue = @cname) COMPANYUSERIDS
WHERE
    ISSUES.ProjectID = PROJECTS.ProjectId AND
    ISSUES.RequesterID = COMPANYUSERIDS.UserID AND
    COMMENTS.DateCreated <= @enddate AND
    COMMENTS.DateCreated >= @startdate AND
    ISSUES.IssueId = COMMENTS.IssueId AND
    ISSUES.IssueCategoryId = CATEGORIES.CategoryID AND
    ISSUES.IssuePriorityId = PRIORITIES.PriorityId AND
    ISSUES.IssueStatusId = STATUS.

 

---

What's going on?  Is there a character limit on the query?  If so, what is it?  And ideally, can it be removed?

The Reports module saves all queries in the "modulesettings" table.

You can find the query by item id, and the setting name of "dnn_reports_query"

This table has a 2000 character cap for the SettingValue

I am not sure if you could simply do an alter table statement to up the size limit or if it is sized elsewhere in the core as well.

---

-Mitchel Sellers
Microsoft MVP, ASPInsider, DNN MVP
CEO/Director of Development - IowaComputerGurus Inc.


Visit mitchelsellers.com for my mostly DNN Blog and support forum.

Visit IowaComputerGurus.com for free DNN Modules, DNN Performance Tips, DNN Consulting Quotes, and DNN Technical Support Services

Not shameless Paul,

It's a great flexible module for displaying/editing/creating data. Certainly not as powerful as something like ListX, but ListX has a huge learning curve.

---