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

Create a Third Selection Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 5:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 9, 2014 1:21 AM
Points: 58, Visits: 191
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]
)
-------------------------------------
Post #1510462
Posted Friday, November 1, 2013 3:38 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:11 PM
Points: 3,204, Visits: 1,399
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
Post #1510537
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse