Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to Rename row values of result set coming from MDX query

Posted on 2012-09-21
4
Medium Priority
?
1,883 Views
Last Modified: 2012-09-26
Hello,

I have the following challange.
I'd like to rename dim attribute members in mdx.

as-is

row    measure
cf       10
fm     20

how i would love to rename cf and fm to cf1 and cf1 for instance...

row    measure
cf1      10
fm1    20

how do i do that?
0
Comment
Question by:T-Virus
  • 2
4 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 38422786
dataitem!value + "1"
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38424439
I think what TempDBA means is:

=Fields!Row.Value & "1"

This assumes that Row is the name of your dataset field and that all values in that column would need to get the suffix.  If it only applies to certain fields, you may want to look into creating a calculated field in your dataset.  Then use the IIF() or Switch() to take out the special cases to have the suffix added.  Let us know if you'd require more info on that.
0
 

Author Comment

by:T-Virus
ID: 38427689
Hey thanks for your answers...
But sry i am not getting it...

I have ssrs report which is using this MDX statement.

 SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS,
 NON EMPTY { ([DIM].[Somemember].ALLMEMBERS ) }
 DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Cube]
 CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

now i would like to rename a member of the somemember attribute
from XXX to YYY. How is =Fields!Row.Value & "1" helping me there?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 38428297
In your example your replaced cf with cf1 and fm with fm1.  So in both cases you've appended the "1" to the original string.  If that's not what you're looking for, please post more details.

If you're okay with hardcoding the strings in the report, perhaps this is an option.  Create a calculated field on your dataset, using an expression like:

=Switch(
  Fields!Somemember.Value = "cf", "cf1",
  Fields!Somemember.Value = "fm", "fm1",
  True, Fields!Somemember.Value
 )

Open in new window

For more details on that Switch statement, especially the last line, have a look at this article: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2279-Adding-an-Else-to-your-Switch.html
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question