May 30, 2012 at 12:20 pm
Problem 1 : I need to develop a report that allows the data to return the top ten performing [indent][/indent]projects by actual GP per region. The problem is that I seem to only get the [indent][/indent]top ten jobs. The regional dimension is totall ignored.
[indent][/indent]The results should shos ten projects per region based on the Actual GP for those jobs.
Problem 2 : The customer would like to use the TOPCOUNT as a parameter rather than a [indent][/indent]static type data set.
June 1, 2012 at 3:47 am
battery_acid_h (5/30/2012)
Problem 1 : I need to develop a report that allows the data to return the top ten performing [indent][/indent]projects by actual GP per region. The problem is that I seem to only get the [indent][/indent]top ten jobs. The regional dimension is totall ignored.[indent][/indent]The results should shos ten projects per region based on the Actual GP for those jobs.
Problem 2 : The customer would like to use the TOPCOUNT as a parameter rather than a [indent][/indent]static type data set.
You would need to enter some MDX instead of using the default click and drag MDX editor in SSRS.
The following example does what you want (example based on Adventureworks demo [codeplex.com])
Points:-
1) The code you want the cities to be returned per region is the GENERATE function.
2) The TopCount function will return the top N cities in this case sorted by Reseller Sales Amount desc.
3) The StrToValue will covert the parameter TopXValue into a string
3) You will need to create a parameter using the @ icon within the MDX editor called TopXValues in my case (do not select a hierarchy etc) and add a default such as 4.
SELECT
{ [Measures].[Reseller Sales Amount] } ON COLUMNS,
non empty Generate(
[Geography].[State-Province].[All Geographies].children,
topcount(
[Geography].[State-Province].currentmember *
[Geography].[City].[All Geographies].children,
STRTOVALUE(@TopXValue, CONSTRAINED),
[Measures].[Reseller Sales Amount]
)
)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,
FONT_NAME, FONT_SIZE, FONT_FLAGS
Fitz
June 1, 2012 at 7:51 am
The script works great! However, I still have issues using this as a parameter in a SSRS report.
I used the script as MDX expression in the parameters.
When opening the data set for the main data (where I return the results) I added another parameter and selected MDX. Then in the expression build I added this expression (I cannot add a full script)
Generate(
[Project].[Company Region].[All].children,
topcount(
[Project].[Company Region].currentmember *
[Project].[Main Project Name].children,
STRTOVALUE(10, CONSTRAINED),
[Measures].[Act GP To Date]
)
)
But I cannot use the STRTOVALUE(@TopCountXX, CONSTRAINED), as i get an error that its used in an inner subexpression.....
June 1, 2012 at 7:53 am
I see you say, dont use MDX drag... can you elaborate?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply