Help with cube measures

I'm relatively new to AS but have managed to get a data mart, dimensions and
cubes up and running in AS 2005 (SP1).

I'm having difficulty trying to do something seemingly easy with respect to
measures.

One of my dimensions is Accounts. I have about 7 additional dimensions
including Time.
I have 5 measures. One of these measures is heavily used most all of the
time.
I need to filter this measure by the Account dimension to include only
measures >= <defined_number>.
The other measures should reflect this change also, but I also need to
analyze other dimensions with or without the Accounts dimension in the
crosstab.
I need this to be put in the aggregations, not a temporary dynamic
calculation.

Aside from doing this at the database level, is their a way to do this in AS
either with a calculated member, named calculation or other?
I've been trying for a few days but just can't seem to get it.

Please, any help would be great.

-Troy

Let me see if I can go at this a different way.

If I try to create a calculated member as follows in the VS 2005 designer:

Name: Filtered Volumes

Parent Hierarchy: MEASURES

Expression: [Measures].[Export TEUS] >= 500.00

Format String: "#,#.0"

Visible: True

Non-Empty Behavior: Export TEUS

After processing the cube, the values for any and all cells is -1.0

Maybe if someone could explain the why to me, it could start the wheels turning more and perhaps help anyone viewing theis post.

Thanks,

-Troy

|||

What's happening with our calculated member is that the Expression "[Measures].[Export TEUS] >= 500.00" is evaulated and returns True which is being displayed as a numberic value and True is -1.0 while False is 0 in numeric terms. (Actually any non-zero value is consider True, but True generally converts to 1.0 or -1.0 depending on the system.)

I don't quite understand what you're trying to achieve, but what you might want to try doing is using something like if([Measures].[Export TEUS] >= 500.00, [Measures].[Export TEUS], Null). (If you are using Analysis Services 2000 both the second and third parameters to the iff functions will need to be of the same type so you'll need to change the null to something like 0.)

Another possibility, depending on what you are trying to do, is to use a the Filter() mdx function in an MDX query.

|||

Matt,

Your suggestion is exactly what I am trying to do. Thank you.

The expression is evaluating as I would have hoped. However, the measure is displaying NULL cells, which is one thing I don't want.

Could you suggest a way to prevent this? I have looked at NONEMPTY, but because of my lack of experience writing MDX I've had no luck.

Again, thanks for the help!!

|||

Here's an example of the use of non empty in an MDX Query in order to filter rows containing only nulls:

First the query that returns rows with null:

with member measures.a as iif([Measures].[Unit Sales]>500, [Measures].[Unit Sales], Null)
select {measures.a} on 0,
[Customer].[City].members on 1

Then the query with non empty added to remove the null rows:

with member measures.a as iif([Measures].[Unit Sales]>500, [Measures].[Unit Sales], Null)
select {measures.a} on 0,
non empty [Customer].[City].members on 1

HELP recovering from .Net 1.1 SP1 install

Report server has been failing on my machine ever since I installed the .Net
framework 1.1 SP1 to correct another problem. The error reported was that the
encryption keys were not working. Based on previous posts to solve this
problem I performed the following staps:
1. I ran rskeymgmt -d to delete the encrypted data.
2. restarted IIS
3. ran rsconfig with the following options
rsconfig -c -s <myserver> -d ReportServer -a Windows
rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
<password>
The above commands completed successfully
4. I tried to run rsactivate -r -c RSReportServer.config, but it responded
that the web service is already in a valid state or its state cannot be
determined.
When I try to access the report manager, I receive the following error:
The version of the report server database is either in a format that is
not valid, or it cannot be read. The found version is 'T.0.6.51'. The
expected version is 'C.0.6.51'.
I do not know what to try at this point other than trying to reinstall
report server, but from other posts it appears that a reinstall doesn't
always fix the problem.run rsconfig only once using the ReportServer database name. The temp
database name is always derived from the reportserver db name and you do not
need to use RSConfig to tell RS about the tempdb name.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:4C7D2D20-F6B5-4616-8476-265FCC42E045@.microsoft.com...
> Report server has been failing on my machine ever since I installed the
.Net
> framework 1.1 SP1 to correct another problem. The error reported was that
the
> encryption keys were not working. Based on previous posts to solve this
> problem I performed the following staps:
> 1. I ran rskeymgmt -d to delete the encrypted data.
> 2. restarted IIS
> 3. ran rsconfig with the following options
> rsconfig -c -s <myserver> -d ReportServer -a Windows
> rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
> rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
> <password>
> The above commands completed successfully
> 4. I tried to run rsactivate -r -c RSReportServer.config, but it responded
> that the web service is already in a valid state or its state cannot be
> determined.
> When I try to access the report manager, I receive the following error:
> The version of the report server database is either in a format that
is
> not valid, or it cannot be read. The found version is 'T.0.6.51'. The
> expected version is 'C.0.6.51'.
> I do not know what to try at this point other than trying to reinstall
> report server, but from other posts it appears that a reinstall doesn't
> always fix the problem.
>|||What do I do at this point?
"Daniel Reib [MSFT]" wrote:
> run rsconfig only once using the ReportServer database name. The temp
> database name is always derived from the reportserver db name and you do not
> need to use RSConfig to tell RS about the tempdb name.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:4C7D2D20-F6B5-4616-8476-265FCC42E045@.microsoft.com...
> > Report server has been failing on my machine ever since I installed the
> ..Net
> > framework 1.1 SP1 to correct another problem. The error reported was that
> the
> > encryption keys were not working. Based on previous posts to solve this
> > problem I performed the following staps:
> >
> > 1. I ran rskeymgmt -d to delete the encrypted data.
> > 2. restarted IIS
> > 3. ran rsconfig with the following options
> >
> > rsconfig -c -s <myserver> -d ReportServer -a Windows
> > rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
> > rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
> > <password>
> > The above commands completed successfully
> > 4. I tried to run rsactivate -r -c RSReportServer.config, but it responded
> > that the web service is already in a valid state or its state cannot be
> > determined.
> >
> > When I try to access the report manager, I receive the following error:
> >
> > The version of the report server database is either in a format that
> is
> > not valid, or it cannot be read. The found version is 'T.0.6.51'. The
> > expected version is 'C.0.6.51'.
> >
> > I do not know what to try at this point other than trying to reinstall
> > report server, but from other posts it appears that a reinstall doesn't
> > always fix the problem.
> >
>
>|||First decide if you are going to use windows auth or SQL auth. Then run
rsconfig passing in ReportServer for the -d option your choice for -a -u
and -p.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:71D5A499-F838-4507-A64F-D6F6884B3118@.microsoft.com...
> What do I do at this point?
> "Daniel Reib [MSFT]" wrote:
> > run rsconfig only once using the ReportServer database name. The temp
> > database name is always derived from the reportserver db name and you do
not
> > need to use RSConfig to tell RS about the tempdb name.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> > news:4C7D2D20-F6B5-4616-8476-265FCC42E045@.microsoft.com...
> > > Report server has been failing on my machine ever since I installed
the
> > ..Net
> > > framework 1.1 SP1 to correct another problem. The error reported was
that
> > the
> > > encryption keys were not working. Based on previous posts to solve
this
> > > problem I performed the following staps:
> > >
> > > 1. I ran rskeymgmt -d to delete the encrypted data.
> > > 2. restarted IIS
> > > 3. ran rsconfig with the following options
> > >
> > > rsconfig -c -s <myserver> -d ReportServer -a Windows
> > > rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
> > > rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
> > > <password>
> > > The above commands completed successfully
> > > 4. I tried to run rsactivate -r -c RSReportServer.config, but it
responded
> > > that the web service is already in a valid state or its state cannot
be
> > > determined.
> > >
> > > When I try to access the report manager, I receive the following
error:
> > >
> > > The version of the report server database is either in a format
that
> > is
> > > not valid, or it cannot be read. The found version is 'T.0.6.51'. The
> > > expected version is 'C.0.6.51'.
> > >
> > > I do not know what to try at this point other than trying to reinstall
> > > report server, but from other posts it appears that a reinstall
doesn't
> > > always fix the problem.
> > >
> >
> >
> >|||I gave up trying rebuild the encrypted data. I couldn't get around the
problem of the database version being incorrect. I reinstalled report server
and that cleared up the problem.
"Daniel Reib [MSFT]" wrote:
> First decide if you are going to use windows auth or SQL auth. Then run
> rsconfig passing in ReportServer for the -d option your choice for -a -u
> and -p.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:71D5A499-F838-4507-A64F-D6F6884B3118@.microsoft.com...
> > What do I do at this point?
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > run rsconfig only once using the ReportServer database name. The temp
> > > database name is always derived from the reportserver db name and you do
> not
> > > need to use RSConfig to tell RS about the tempdb name.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> > > news:4C7D2D20-F6B5-4616-8476-265FCC42E045@.microsoft.com...
> > > > Report server has been failing on my machine ever since I installed
> the
> > > ..Net
> > > > framework 1.1 SP1 to correct another problem. The error reported was
> that
> > > the
> > > > encryption keys were not working. Based on previous posts to solve
> this
> > > > problem I performed the following staps:
> > > >
> > > > 1. I ran rskeymgmt -d to delete the encrypted data.
> > > > 2. restarted IIS
> > > > 3. ran rsconfig with the following options
> > > >
> > > > rsconfig -c -s <myserver> -d ReportServer -a Windows
> > > > rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
> > > > rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
> > > > <password>
> > > > The above commands completed successfully
> > > > 4. I tried to run rsactivate -r -c RSReportServer.config, but it
> responded
> > > > that the web service is already in a valid state or its state cannot
> be
> > > > determined.
> > > >
> > > > When I try to access the report manager, I receive the following
> error:
> > > >
> > > > The version of the report server database is either in a format
> that
> > > is
> > > > not valid, or it cannot be read. The found version is 'T.0.6.51'. The
> > > > expected version is 'C.0.6.51'.
> > > >
> > > > I do not know what to try at this point other than trying to reinstall
> > > > report server, but from other posts it appears that a reinstall
> doesn't
> > > > always fix the problem.
> > > >
> > >
> > >
> > >
>
>