Field Not Recognized In Dataset

I have a dataset that I was using in a report that I determined I needed to
add a field to. After I changed the query to include this field, the field
is not displaying as part of the dataset when I look in the field browser or
try to use the field in the report. The field I have added is "Quota AS
SalesTarget". Quota is a field on the table, but for some reason in the
dataset it is not being recognized. If I run the query in Enterprise
Manager, it runs fine displaying the column. Any suggestions would be
appreciated. Here is the query:
="SELECT CorpEntityCode AS CFPBusinessUnitCode,
CorpEntity AS CFPBusinessUnitCodename,
9 AS SalesStageCode,
'Remaining Quota' AS SalesStageCodename,
Quota AS SalesTarget,
Quota - (SELECT sum(EstimatedValue) FROM Opportunity o WHERE ((StateCode = 1) AND (EstimatedValue >= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy,
DATEDIFF(yy, 0, GETDATE()), 0)) AND (DeletionStateCode = 0)" &
IIf(Parameters!BUSelect.Value = 0, "", " AND (CFPBusinessUnitCode = " &
Parameters!BUSelect.Value & ")") & ")) AS SalesStageValue
FROM CustomSalesQuota
WHERE (CorpEntityCode = " & Parameters!BUSelect.Value & ")
UNION
SELECT CFPBusinessUnitCode,
CASE CFPBusinessUnitCode WHEN 1 THEN 'Commercial Solutions' WHEN 2 THEN
'Europe' WHEN 3 THEN 'Government Services' WHEN 4 THEN 'Healthcare' WHEN 5
THEN 'Technology Solutions' END AS CFPBusinessUnitCodename, SalesStageCode,
CASE SalesStageCode WHEN 2 THEN 'Open' WHEN 3 THEN 'Develop' WHEN 4 THEN
'Scope/Propose' WHEN 5 THEN 'Negotiate/Close' WHEN 6 THEN 'Won' END AS
SalesStageCodename,
0.00 AS SalesTarget,
Sum(EstimatedValue) AS SalesStageValue
FROM dbo.Opportunity
WHERE ((StateCode = 0) AND (SalesStageCode IN (3, 4, 5)) AND (EstimatedValue
>= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy, DATEDIFF(yy, 0,
GETDATE()), 0)) AND (DeletionStateCode = 0) OR (StateCode = 1) AND
(EstimatedValue >= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy,
DATEDIFF(yy, 0, GETDATE()), 0)) AND (DeletionStateCode = 0))" &
IIf(Parameters!BUSelect.Value = 0, "", " AND (CFPBusinessUnitCode = " &
Parameters!BUSelect.Value & ")") & " GROUP BY CFPBusinessUnitCode,
SalesStageCode"
ThanksGo to the Data Tab, and click the refresh Fields button... or re-execute
the query from the data tab...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CaskeyRW" <CaskeyRW@.discussions.microsoft.com> wrote in message
news:5ABCD385-BBCB-4A55-823F-F33507B1EEBB@.microsoft.com...
>I have a dataset that I was using in a report that I determined I needed to
> add a field to. After I changed the query to include this field, the
> field
> is not displaying as part of the dataset when I look in the field browser
> or
> try to use the field in the report. The field I have added is "Quota AS
> SalesTarget". Quota is a field on the table, but for some reason in the
> dataset it is not being recognized. If I run the query in Enterprise
> Manager, it runs fine displaying the column. Any suggestions would be
> appreciated. Here is the query:
> ="SELECT CorpEntityCode AS CFPBusinessUnitCode,
> CorpEntity AS CFPBusinessUnitCodename,
> 9 AS SalesStageCode,
> 'Remaining Quota' AS SalesStageCodename,
> Quota AS SalesTarget,
> Quota - (SELECT sum(EstimatedValue) FROM Opportunity o WHERE ((StateCode => 1) AND (EstimatedValue >= 2000000.00) AND (EstimatedCloseDate >=> DATEADD(yy,
> DATEDIFF(yy, 0, GETDATE()), 0)) AND (DeletionStateCode = 0)" &
> IIf(Parameters!BUSelect.Value = 0, "", " AND (CFPBusinessUnitCode = " &
> Parameters!BUSelect.Value & ")") & ")) AS SalesStageValue
> FROM CustomSalesQuota
> WHERE (CorpEntityCode = " & Parameters!BUSelect.Value & ")
> UNION
> SELECT CFPBusinessUnitCode,
> CASE CFPBusinessUnitCode WHEN 1 THEN 'Commercial Solutions' WHEN 2 THEN
> 'Europe' WHEN 3 THEN 'Government Services' WHEN 4 THEN 'Healthcare' WHEN 5
> THEN 'Technology Solutions' END AS CFPBusinessUnitCodename,
> SalesStageCode,
> CASE SalesStageCode WHEN 2 THEN 'Open' WHEN 3 THEN 'Develop' WHEN 4 THEN
> 'Scope/Propose' WHEN 5 THEN 'Negotiate/Close' WHEN 6 THEN 'Won' END AS
> SalesStageCodename,
> 0.00 AS SalesTarget,
> Sum(EstimatedValue) AS SalesStageValue
> FROM dbo.Opportunity
> WHERE ((StateCode = 0) AND (SalesStageCode IN (3, 4, 5)) AND
> (EstimatedValue
>>= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy, DATEDIFF(yy, 0,
> GETDATE()), 0)) AND (DeletionStateCode = 0) OR (StateCode = 1) AND
> (EstimatedValue >= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy,
> DATEDIFF(yy, 0, GETDATE()), 0)) AND (DeletionStateCode = 0))" &
> IIf(Parameters!BUSelect.Value = 0, "", " AND (CFPBusinessUnitCode = " &
> Parameters!BUSelect.Value & ")") & " GROUP BY CFPBusinessUnitCode,
> SalesStageCode"
> Thankssql