Calculating a percentage across a matrix group

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Calculating a percentage across a matrix...

Calculating a percentage across a matrix group

Rate Topic

Display Mode

Topic Options

Author

Message

iain.cowden

Posted Monday, October 22, 2012 3:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 2:57 AM
Points: 6, Visits: 22

Dear All,

I am developing a report in Report Builder 3.0

Thus far I have a matrix which uses Service_Locality, Instance and ActiveStatus as row fields and User_Status as a column field, with UserKey as my count.

Now when the report is run, User_Status splits out as expected into 4 columns - Deleted, Active_HCP, Active_User, Dummy.

What I now want to do is add an indicator column which is based on the percentage of Deleted users for the Service_Locality Scope.

I have tried a few variations, but cannot figure out how to reference the columns / data generated when the report runs. Can anyone help me here? My MDX level is basic to say the least!

Iain

Post #1375311

Mackers

Posted Monday, October 22, 2012 4:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:19 AM
Points: 117, Visits: 460

Hi Iain,

Try this sort of thing - In my example I have created a memeber that would be your calulation (in this case % European clients) and then unioned that to the list of clients

with
member [Client].[Client Region].[% split] as
([Client].[Client Region].[Europe]/[Client].[Client Region].[All])

SELECT UNION({ [Client].[Client Region].[All].CHILDREN } ,{[Client].[Client Region].[% split]}) ON COLUMNS ,
NON EMPTY { [Product].[Product Level 1].[All].CHILDREN }
* { [Product].[Product Level 2].[All].CHILDREN }
ON ROWS
FROM [OLAP]
WHERE ( [Measures].[Revenue] )

It may not be 100% what you are after - but I can help if it isn't

Mack

Post #1375326

iain.cowden

Posted Monday, October 22, 2012 5:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 2:57 AM
Points: 6, Visits: 22

Hi Mack,

I am unclear as to where you are working with this. i have been trying to work with the value expression for my indicator.

I am guessing that your solution is to create a calculated member within the query?

Iain

Post #1375346

Mackers

Posted Tuesday, October 23, 2012 1:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:19 AM
Points: 117, Visits: 460

Hi Iain,

"I am guessing that your solution is to create a calculated member within the query?" Yes that what I was doing?

What are you trying to get - is this a static report that you can write the MDX for or do you want to create a calculated measure in the cube so that the users can select that?

Mack

Post #1375872

iain.cowden

Posted Tuesday, October 23, 2012 9:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 2:57 AM
Points: 6, Visits: 22

Hi Mack,

This is a static report.

I had been working on trying to create a value expression within the indicator itself, hence my initial confusion. It did occur to me that i could also try creating this as a KPI within the cube.

Iain

Post #1376149

« Prev Topic | Next Topic »

Permissions