Calculated fields not displaying in the layout

I am continually seeing an issue where I write a query with some calculations
in it and when I put that same query in reporting services those calculated
fields do not display in the report. I get a warning saying those particular
fields is not part of the result set.
I can usually get around this by creating a calulated field in Reporting
Services but I'd much rather do the math in my query. Here's an example of
my latest issue:
SELECT *
FROM
( SELECT dense_rank() over (partition by openbusinessdate order by
(NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
GUEST_CHECK_HIST.openDatetime) desc) dr,
GUEST_CHECK_HIST.openbusinessdate,
GUEST_CHECK_HIST.checkNum as checkNum,
GUEST_CHECK_HIST.tableRef as Tablenum,
GUEST_CHECK_HIST.openDatetime as opentime,
GUEST_CHECK_HIST.closeDatetime as closetime,
(NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
GUEST_CHECK_HIST.openDatetime) * 1440 AS duration,
GUEST_CHECK_HIST.numGuests as numguests,
substr(LOCATION_HIERARCHY_ITEM.name,3,4) AS locName,
REVENUE_CENTER.nameMaster AS rvcName,
initcap(EMPLOYEE.firstName) as fname,
initcap(EMPLOYEE.lastName) as lname
FROM EMPLOYEE RIGHT OUTER JOIN REVENUE_CENTER RIGHT OUTER JOIN
GUEST_CHECK_HIST
LEFT OUTER JOIN LOCATION_HIERARCHY_ITEM ON GUEST_CHECK_HIST.locationID = LOCATION_HIERARCHY_ITEM.locationID
ON GUEST_CHECK_HIST.revenueCenterID = REVENUE_CENTER.revenueCenterID
ON GUEST_CHECK_HIST.employeeID = EMPLOYEE.employeeID
WHERE (GUEST_CHECK_HIST.organizationID = 2000) AND
(GUEST_CHECK_HIST.openBusinessDate = :begindate))
where dr <=100
as you can guess what I'm calling duration does do display in the report.
Any help would be greatly appreciated.Never mind. I resolved the issue.
Thanks
"Zach" wrote:
> I am continually seeing an issue where I write a query with some calculations
> in it and when I put that same query in reporting services those calculated
> fields do not display in the report. I get a warning saying those particular
> fields is not part of the result set.
> I can usually get around this by creating a calulated field in Reporting
> Services but I'd much rather do the math in my query. Here's an example of
> my latest issue:
> SELECT *
> FROM
> ( SELECT dense_rank() over (partition by openbusinessdate order by
> (NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
> GUEST_CHECK_HIST.openDatetime) desc) dr,
> GUEST_CHECK_HIST.openbusinessdate,
> GUEST_CHECK_HIST.checkNum as checkNum,
> GUEST_CHECK_HIST.tableRef as Tablenum,
> GUEST_CHECK_HIST.openDatetime as opentime,
> GUEST_CHECK_HIST.closeDatetime as closetime,
> (NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
> GUEST_CHECK_HIST.openDatetime) * 1440 AS duration,
> GUEST_CHECK_HIST.numGuests as numguests,
> substr(LOCATION_HIERARCHY_ITEM.name,3,4) AS locName,
> REVENUE_CENTER.nameMaster AS rvcName,
> initcap(EMPLOYEE.firstName) as fname,
> initcap(EMPLOYEE.lastName) as lname
> FROM EMPLOYEE RIGHT OUTER JOIN REVENUE_CENTER RIGHT OUTER JOIN
> GUEST_CHECK_HIST
> LEFT OUTER JOIN LOCATION_HIERARCHY_ITEM ON GUEST_CHECK_HIST.locationID => LOCATION_HIERARCHY_ITEM.locationID
> ON GUEST_CHECK_HIST.revenueCenterID = REVENUE_CENTER.revenueCenterID
> ON GUEST_CHECK_HIST.employeeID = EMPLOYEE.employeeID
> WHERE (GUEST_CHECK_HIST.organizationID = 2000) AND
> (GUEST_CHECK_HIST.openBusinessDate = :begindate))
> where dr <=100
> as you can guess what I'm calling duration does do display in the report.
> Any help would be greatly appreciated.