Calculated measures in report builder

Has anyone been able to get time calculations to show up in Report Builder? I'm using the BI Wizard standard calculations for YTD & Period over period growth, but I can't figure out how to use them within Report Builder. They are available in Report Designer, however. I did find the info about assigning the calculations to a measure group. I tried that, but it didn't have any affect.

Any help is appreciated.

You can't. The Report Builder doesn't fetch dimension calculated members. For this reason, we abandoned dimension calculated members and defined them as calculated measures in the cube. I wasn't successful also in trying to define the calculated dimension members as regular members by implementing an utility dimension because cells that do not exists in the cube cannot be written to.|||

Thanks for your response, Teo.

Are you saying that you couldn't do it at all, or that you were able to convert the time calcs to calculated measures and you were then able to use them in Report Builder? At the moment I'm not sure how I would go about converting the time calcs to calculated measures.

BTW, I read your book. Nice work!

|||

Thanks. The calculated mesures approached worked but you will end up with as many calculated measures as the number of the time-based calculations (QTD, YTD, etc.). Another issue you will discover if you decide to take this road is that a date filter in the RB report won't overwrite the default member fo the Date dimension. This will force you to always bring the Date dimension in the report if the report uses the time calculated measures.

What I wasn't able to do is implement the Time intelligence dimension calculated members as regular members of an utility dimension.

It will be great if you could log this issue to connect.microsoft.com. The more it is asked for the more likely will be to get implemented.

|||

Hi,

I want to generate Ad-Hoc reports using report builder with cube created. But am not able to see any calculated measures in the report. Can anyone tell me how to solve this.

|||You need to assign the calculated measures to display folders using the Properties window on the Calculations tab.sql

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.