SELECT NON EMPTY { [Measures].[Patient Stay Count], [Measures].[Length
of Stay], [Measures].[Patient Count] } ON COLUMNS,

NON EMPTY { ([Patient Stay].[Gender].[Gender].ALLMEMBERS ),

([Patient Stay].[Gender].[M].Parent as Total ) } -----> this line
should build me the sum

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
[Clinical Performance] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I'd like to add a total in a table. Unfortunately I can't sum the
values up and so I have to use MDX to do it. I thought of calculating
the sum via this statement:

([Patient Stay].[Gender].[M].Parent as Total ) - I'd like to use the
root element as a total line. This gives me the right figures. But in
my first column and last row gender is Null (because it can not be
calculated) and instead I'd like to write Total in this field. How can
I achieve this?

Second best would be to life with the Null value but unfortunately I
can't display the result data with reporting services (I guess because
of the NULL).

Thanks

One standard technique with Reporting Services, which might work for
you, is to add a calaculated measure for the name:

iif([Patient Stay].[Gender].CurrentMember is
[Patient Stay].[Gender].[All], "Total",
[Patient Stay].[Gender].CurrentMember.Name)
SELECT NON EMPTY {[Measures].[GenderName],
[Measures].[Patient Stay Count],
[Measures].[Length of Stay],
[Measures].[Patient Count] } ON COLUMNS,
NON EMPTY {[Patient Stay].[Gender].[Gender].ALLMEMBERS,
[Patient Stay].[Gender].[All}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
[Clinical Performance] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for your thoughts. Unfortunately it doesn't work. In the Data
View my table looks like this:

Gender GenderName Patient Stays ............
M M 12
F F 23
(NULL) Total 35

When I want to display these results in the layout view and preview it
the row with the total is not shown. I guess it is because Gender is no
colum but works as a row and the last row with NULL in it is therefor
not shown.

Any ideas to solve my problem?

Thanks Florian

Deepak Puri schrieb:

In the report data set, are you using the OLE DB for OLAP Provider
(which should work)? I think that there may be problems with returning
the parent row, when using the Analysis Services Provider. Also, in the
report design, only the GenderName field should be used, not Gender.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Where can I see where which OLAP Provider I'm using?

I only used the GenderName.

My problem is that my report is a little more complex - my matrix looks
like this:


Q1 Q2 total
TownA Patients
Patient Stay

Patient Stays is simply summed up but patients is a "distinct" sum and
the total can't be calculated as a sum because I also have to keep in
mind that I need a distinct sum.

By the way when I use :

With Member [Measures].[GenderName] as
iif([Patient Stay].[Gender].CurrentMember is
[Patient Stay].[Gender].[All], "Total",
[Patient Stay].[Gender].CurrentMember.Name)
SELECT NON EMPTY {[Measures].[GenderName],
[Measures].[Patient Stay Count],
[Measures].[Length of Stay],
[Measures].[Patient Count] } ON COLUMNS,

NON EMPTY {[Patient Stay].[Gender].[Gender].ALLMEMBERS,
[Patient Stay].[Gender].[All}




---> and delete [Patient Stay].[Gender].[Gender].ALLMEMBERS - gender is
not displayed any more and only one row with total in it is displayed.
Why?




DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
[Clinical Performance] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS




Deepak Puri schrieb:

I use Microsoft SQL Analysis Services as a data source and as Datatype:

..Net Framework Dataprovider for Microsoft Analysis Services

for the reports


florian schrieb:

Can you try Data Source type of "OLE DB", with OLE DB Provider selected
as "Microsoft OLE DB Provider for Analysis Services 9", and see if the
"Total" row is visible?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for your great help. The "OLE DB" driver did the trick.

Cheers from Vienna Florian

Deepak Puri schrieb: