Calculated measure

I have multiple fact tables very much like the internet-sales store-sales scenario described in the analysis services tutorial:

http://msdn2.microsoft.com/en-us/library/ms166568(SQL.90).aspx

I've created a calculated measure for total revenue (= [Ad Revenue] + [Preprint Revenue]) This works fine when the fact tables share a dimension such as customer. There are, however, dimensions that are specific to each of the fact tables

For example, if I display revenue by customer in the browser, I correctly get the sum of ad revenue and preprint revenue for total revenue. If I display revenue by Ad Product (dimension specifc to ads and not preprints) the total revenue figure includes the ad revenue that is specifc to the Ad product and all preprint revenue.

That's potetnially confusing for end users. Is there a way to hide the calculation when certain dimesnsions are selected or to convert the preprint revenue to 0?

thanks

Peter

Hi Peter,

For the Measure Group which includes [Preprint Revenue], will setting the IgnoreUnrelatedDimensions property to False achieve the desired result?

http://msdn2.microsoft.com/en-us/library/ms365411.aspx

>>

Configuring Measure Group Properties

IgnoreUnrelatedDimensions

Determines whether unrelated dimensions are forced to their top level when members of dimensions that are unrelated to the measure group are included in a query. Default setting is True.

>>

|||

Thanks Deepak. That was the answer.

Peter

cache

Sometimes we expect a report to run from cache and it doesnt. Scenario is we
subscribe to the report to run at 3 am and set execution to cache report and
expire at midnight. So this way when a user runs during the day, the report
is already cached
However I noticed in the executionlog table that when the report runs from a
subscription it runs as MHTML and when the user runs from browser it runs
from HTML 4.0 format and I think these differences in formats throw off the
cache execution status.
What is the difference in formats.
I also notice that sometimes a subscription executes once but the user gets
two emails. They try to export to excel and get a message that they do not
have sufficient permissions. My thought was that Installation of Microsoft
Publisher put Office in Read Only mode. We have had that issue with Office
Web Component in the past.
Assistance with these issues is appreciated
ThanksPlease answer my post
Thanks
"Will Byron" <WillB@.CEPSystems.com> wrote in message
news:%23G%23q9rBcEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Sometimes we expect a report to run from cache and it doesnt. Scenario is
we
> subscribe to the report to run at 3 am and set execution to cache report
and
> expire at midnight. So this way when a user runs during the day, the
report
> is already cached
> However I noticed in the executionlog table that when the report runs from
a
> subscription it runs as MHTML and when the user runs from browser it runs
> from HTML 4.0 format and I think these differences in formats throw off
the
> cache execution status.
> What is the difference in formats.
> I also notice that sometimes a subscription executes once but the user
gets
> two emails. They try to export to excel and get a message that they do not
> have sufficient permissions. My thought was that Installation of Microsoft
> Publisher put Office in Read Only mode. We have had that issue with Office
> Web Component in the past.
> Assistance with these issues is appreciated
> Thanks
>|||The format should not be a factor in the cachine decision, but different
parameters can be. The cache key is composed of the report and its
parameters. Are you running the report with the same parameters in the two
cases?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Will Byron" <will.byron@.maxqtech.com> wrote in message
news:OaIZOJOcEHA.1408@.TK2MSFTNGP12.phx.gbl...
> Please answer my post
> Thanks
> "Will Byron" <WillB@.CEPSystems.com> wrote in message
> news:%23G%23q9rBcEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Sometimes we expect a report to run from cache and it doesnt. Scenario
is
> we
> > subscribe to the report to run at 3 am and set execution to cache report
> and
> > expire at midnight. So this way when a user runs during the day, the
> report
> > is already cached
> >
> > However I noticed in the executionlog table that when the report runs
from
> a
> > subscription it runs as MHTML and when the user runs from browser it
runs
> > from HTML 4.0 format and I think these differences in formats throw off
> the
> > cache execution status.
> >
> > What is the difference in formats.
> >
> > I also notice that sometimes a subscription executes once but the user
> gets
> > two emails. They try to export to excel and get a message that they do
not
> > have sufficient permissions. My thought was that Installation of
Microsoft
> > Publisher put Office in Read Only mode. We have had that issue with
Office
> > Web Component in the past.
> > Assistance with these issues is appreciated
> > Thanks
> >
> >
>