Create a Third Selection

Question

Create a Third Selection

akirajt

Right there with Babe

Points: 762
More actions
October 31, 2013 at 5:25 pm

#281883

Goal:
Enable to select everything in the semester list with one click only.
Problem:
I do not know how to create a third select with message "select all" or similiar in the parameter list Semester.
Information:
*Using SSAS as a data source
*One selection only in the semester list. THe user shall select one value only.
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/27b4dce4-ca89-4fa3-9000-541b64b4e1d9/create-a-third-selection?forum=sqlreportingservices
Code:
Data set 1-------------------------------------
SELECT NON EMPTY
{
[Measures].[Internet Freight Cost],
[Measures].[Internet Standard Product Cost],
[Measures].[Internet Sales Amount]
} ON COLUMNS,
NON EMPTY
{
(
[Date].[Calendar Year].[Calendar Year].ALLMEMBERS * [Date].[Calendar Quarter of Year].[Calendar Quarter of Year].ALLMEMBERS * [Date].[Calendar Semester of Year].ALLMEMBERS
)
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
(
SELECT ( { STRTOSET(@Semester, CONSTRAINED) } ) ON COLUMNS
FROM
(
SELECT ( { STRTOSET(@Year, CONSTRAINED) } ) ON COLUMNS
FROM [Adventure Works]
)
)
-------------------------------------
Dataset date -------------------------------------
WITH MEMBER
[Measures].[ParameterCaption] AS [Date].[Calendar Year].CURRENTMEMBER.MEMBER_CAPTION MEMBER
[Measures].[ParameterValue] AS [Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME MEMBER
[Measures].[ParameterLevel] AS [Date].[Calendar Year].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
}
ON COLUMNS,
[Date].[Calendar Year].[Calendar Year].allmembers ON ROWS
FROM [Adventure Works]
-------------------------------------
DatasetSemester -------------------------------------
WITH MEMBER
[Measures].[ParameterCaption] AS [Date].[Calendar Semester of Year].CURRENTMEMBER.MEMBER_CAPTION MEMBER
[Measures].[ParameterValue] AS [Date].[Calendar Semester of Year].CURRENTMEMBER.UNIQUENAME MEMBER
[Measures].[ParameterLevel] AS [Date].[Calendar Semester of Year].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
}
ON COLUMNS,
[Date].[Calendar Semester of Year].[Calendar Semester of Year].ALLMEMBERS ON ROWS
FROM
(
SELECT ( { STRTOSET(@Year, CONSTRAINED) } ) ON COLUMNS
FROM [Adventure Works]
)
-------------------------------------

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 1

I have reproduced you requirement using a different cube. The issue is the hierarchy you have chosen to show ([Date].[Calendar Semester of Year].[Calendar Semester of Year]) is part of a user defined hierarchy and you are using a level within that. The ALL member is not available here without adding it back in using UNION. Changing the hierarchy to the attribute hierarchy would be simpler.

WITH MEMBER

[Measures].[ParameterCaption] AS [Date].[Calendar Semester of Year].CURRENTMEMBER.MEMBER_CAPTION MEMBER

[Measures].[ParameterValue] AS [Date].[Calendar Semester of Year].CURRENTMEMBER.UNIQUENAME MEMBER

[Measures].[ParameterLevel] AS [Date].[Calendar Semester of Year].CURRENTMEMBER.LEVEL.ORDINAL

SELECT

{

[Measures].[ParameterCaption],

[Measures].[ParameterValue],

[Measures].[ParameterLevel]

}

ON COLUMNS,

[Date].[Calendar Semester of Year].ALLMEMBERS ON ROWS

FROM

(

SELECT ( { STRTOSET(@Year, CONSTRAINED) } ) ON COLUMNS

FROM [Adventure Works]

)

You may need to change your report dataset query to also handle the ALL item.

Fitz