sql server reporting services: Calculated member?? -- sql server reporting services

RE: Calculated member??

Tenchy
10/25/2006 8:23:02 AM

Correction, i want them each seperate in the dataset with their values, as
well as the total.

[quoted text, click to view]

Re: Calculated member??

Kaisa M. Lindahl Lervik
10/25/2006 10:20:57 PM

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

[quoted text, click to view]

Re: Calculated member??

Tenchy
10/26/2006 9:25:02 AM

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

Re: Calculated member??

Kaisa M. Lindahl Lervik
10/29/2006 9:01:40 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.