Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculating a percentage across a matrix group Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 3:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:15 AM
Points: 6, Visits: 59
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
Posted Monday, October 22, 2012 4:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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
Posted Monday, October 22, 2012 5:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:15 AM
Points: 6, Visits: 59
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
Posted Tuesday, October 23, 2012 1:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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
Posted Tuesday, October 23, 2012 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:15 AM
Points: 6, Visits: 59
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 »

Add to briefcase

Permissions Expand / Collapse