Calculated member?

I'm doing some reports and i need to create individual results as attributes
or Calculated members. I have a Hierachy called Injury with five options,
A-jury, B-injury, C-injury, and O-injury. The Query builder only lets me put
in the hierachy. So I need to get the "person count" (measure) for each
individual one, instead of all together. And i dont want to filter in
different datasets. I want them all in the same dataset as the total. How can
this be done? I've been scratching my head for a couple days now.Correction, i want them each seperate in the dataset with their values, as
well as the total.
"Tenchy" wrote:
> I'm doing some reports and i need to create individual results as attributes
> or Calculated members. I have a Hierachy called Injury with five options,
> A-jury, B-injury, C-injury, and O-injury. The Query builder only lets me put
> in the hierachy. So I need to get the "person count" (measure) for each
> individual one, instead of all together. And i dont want to filter in
> different datasets. I want them all in the same dataset as the total. How can
> this be done? I've been scratching my head for a couple days now.|||Have you tried working with MDX statements in the Anaysis Services managment
studio?
You could write it out how it's supposed to in the AS studio, then use it as
a classic mdx query in your report. You need to use a OLE DB connection for
your data source, though.
1) Instead of using the Analysis Services data source, create your data
source as OLE DB, and connect to your AS 2005 server through that.
2) Now you have the old school query interface. No help, but also no mess.
3) Create your non dynamic query in the SQL Server Management studio, by
connecting to your AS and work out the MDX
4) Copy paste your non dynamic query to create all your fields etc.
5) Make it dynamic by adding the =" and " and all the parameter names
Kaisa M. Lindahl Lervik
"Tenchy" <Tenchy@.discussions.microsoft.com> wrote in message
news:ACAFDA00-56BE-4B67-A028-73F852F67E34@.microsoft.com...
> Correction, i want them each seperate in the dataset with their values, as
> well as the total.
> "Tenchy" wrote:
>> I'm doing some reports and i need to create individual results as
>> attributes
>> or Calculated members. I have a Hierachy called Injury with five
>> options,
>> A-jury, B-injury, C-injury, and O-injury. The Query builder only lets me
>> put
>> in the hierachy. So I need to get the "person count" (measure) for each
>> individual one, instead of all together. And i dont want to filter in
>> different datasets. I want them all in the same dataset as the total. How
>> can
>> this be done? I've been scratching my head for a couple days now.|||Thats my problem, i dont know what the mdx code would be to do that. Because
i can do mdx in reporting services in the dataset as a calculated member. But
the code i put in there gives me all kinds of syntax errors. This is the code
i have right now.
SELECT { [Measures].[Vehicle Count], [Measures].[Injury Crashes],
[Measures].[Non-Fatal Injuries], [Measures].[Fatal Injuries],
[Measures].[CrashCount], [Measures].[Fatal Crashes] } ON COLUMNS, {
([Crash].[Weather].[Weather].ALLMEMBERS * [CrashPerson].[Person Injury
Class].[Person Injury Class].ALLMEMBERS ) } DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SDMTest] CELL PROPERTIES
VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS
I just want to have the person injury classes, in seperate columns, instead
of the total of all of them together, that way when i put them in my table,
the user can see the (A-Injuries) fatal crashes (b-Injury) Fatal crashes...
and so on. And i dont have to have 15 different datasets with the same
fields and just one different Filter. It has to be possible.|||When it comes to MDX queries, you might get more help in the newsgroup
called microsoft.public.sqlserver.olap.
What are the names /hierarchies of the dimensions you want to return?
What measures do you want to see?
Do you want to see number of Fatal Injuries per weather?
From your example, it looks to me like you just want something like
SELECT { [Measures].[Fatal Crashes] } ON COLUMNS,
{
[CrashPerson].[Person Injury Class].[Person Injury Class].ALLMEMBERS
} DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SDMTest]
Then you use a matrix, and create a column group for the CrashPerson field.
That should give you
..A-injury...|..B-injury..|..C-injury..|
..20.........|..20........|..10.........|
If you want to see injuries by weather and category, you can add the weather
dimension and create a row group for that.
Kaisa M. Lindahl Lervik
"Tenchy" <Tenchy@.discussions.microsoft.com> wrote in message
news:A8449F38-B807-4430-AF4C-10207DC3E5ED@.microsoft.com...
> Thats my problem, i dont know what the mdx code would be to do that.
> Because
> i can do mdx in reporting services in the dataset as a calculated member.
> But
> the code i put in there gives me all kinds of syntax errors. This is the
> code
> i have right now.
> SELECT { [Measures].[Vehicle Count], [Measures].[Injury Crashes],
> [Measures].[Non-Fatal Injuries], [Measures].[Fatal Injuries],
> [Measures].[CrashCount], [Measures].[Fatal Crashes] } ON COLUMNS, {
> ([Crash].[Weather].[Weather].ALLMEMBERS * [CrashPerson].[Person Injury
> Class].[Person Injury Class].ALLMEMBERS ) } DIMENSION PROPERTIES
> MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SDMTest] CELL PROPERTIES
> VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
> FONT_SIZE, FONT_FLAGS
> I just want to have the person injury classes, in seperate columns,
> instead
> of the total of all of them together, that way when i put them in my
> table,
> the user can see the (A-Injuries) fatal crashes (b-Injury) Fatal
> crashes...
> and so on. And i dont have to have 15 different datasets with the same
> fields and just one different Filter. It has to be possible.