database collation: Changing from dropdowns to datepickers

Hi guys,

just a simple question here: i have some input parameters on my report that are datetimes (i.e. the user gets the date picker to select the date), if i want to use that parameter in a MDX statement what will it look like? IOW would today's date look like the string "13/02/07", or would i be expecting a string like this: "2007/02/13 00:00"?

I am looking to convert some dropdowns that contain dates extracted from a cube heirarchy with the datepickers so i need to know what i have to change in the MDX to accomodate this.

I also filter the dates that appear in the current dropdowns, is there a way to do this with the datepickers (maybe by pointing them to a dataset of dates extracted from the cube)?

Thanks!

sluggy

This report sample may help.

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>AdventureWorksAS</DataSourceReference>

<rd:DataSourceID>aad8c909-02c6-4fb6-841c-0557ea327ec4</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>String</DataType>

<Value>[Product].[Product Categories].[Category].&[1]</Value>

</Values>

</DefaultValue>

<Prompt>Product Categories</Prompt>

<DataSetName>ProductProductCategories</DataSetName>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaptionIndented</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<DataType>DateTime</DataType>

</Values>

</DefaultValue>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox9</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox12</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox16</rd:DefaultName>

<Style>

</Style>

<Value>Category Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox17</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox18</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>Category</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Category.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox11</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox13</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox14</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox15</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Category.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

<rd:DefaultName>textbox26</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox27</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox28</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Aggregate(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox21</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Subcategory</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Subcategory.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox23</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox24</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox25</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Subcategory.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

</TableGroups>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox22</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Product</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Product.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!InternetSalesAmount.FormattedValue</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!ResellerSalesAmount.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

</Style>

<Value>Category</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

</Style>

<Value>Product</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Internet Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Reseller Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>03e1fd95-077c-4176-a8c7-4d09893efe4e</rd:ReportID>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>

<Value>=Parameters!ProductProductCategories.Value</Value>

</QueryParameter>

<Value>="[Date].[Date].[" & CDate(Parameters!DateDate.Value).ToString("MMMM d, yyyy") & "]"</Value>

</QueryParameter>

</QueryParameters>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Category</LevelName><UniqueName>[Product].[Product Categories].[Category]</UniqueName></ID><ItemCaption>Category</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Subcategory</LevelName><UniqueName>[Product].[Product Categories].[Subcategory]</UniqueName></ID><ItemCaption>Subcategory</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Product</LevelName><UniqueName>[Product].[Product Categories].[Product]</UniqueName></ID><ItemCaption>Product</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Measure"><MeasureName>Internet Sales Amount</MeasureName><UniqueName>[Measures].[Internet Sales Amount]</UniqueName></ID><ItemCaption>Internet Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Reseller Sales Amount</MeasureName><UniqueName>[Measures].[Reseller Sales Amount]</UniqueName></ID><ItemCaption>Reseller Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Total Sales</MeasureName><UniqueName>[Measures].[Total Sales]</UniqueName></ID><ItemCaption>Total Sales</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions><ParameterDefinition><Name>ProductProductCategories</Name><DefaultValues><DefaultValue>[Product].[Product Categories].[Category].&[1]</DefaultValue></DefaultValues><Caption>Product Categories</Caption><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Product Categories].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition><ParameterDefinition><Name>DateDate</Name><DefaultValues><DefaultValue>[Date].[Date].[All Periods]</DefaultValue></DefaultValues><Caption>Date.Date</Caption><HierarchyUniqueName>[Date].[Date]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

</DataSet>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</CommandText>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:AutoGenerated>true</rd:AutoGenerated>

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

<rd:Hidden>true</rd:Hidden>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Value>=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value</Value>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

|||

Thanks Teo, I'll pull that apart and have a look at it.

Cheers

sluggy

|||

Note that whatever you do with the date parameter, you need to comply with the AS member format, e.g. [Date][Day].[Day].&[20070213]. For the sample report, I think I pulled a little trick where I changed the Value property of the dimension key in the AW Date dimension to load the DateTime value. Also you can try:

1. Using the MDX query designer for your main query, define a date parameter.

2. Edit the report parameter it created. Make it DateTime type. Don't query the database. Uncheck multi-value. Default it to null.

3. Delete the extra dataset it created which would have driven the date parameter.

4. Edit the query parameter and write an expression which converts the DateTime from the parameter into an MDX member name... such as:
="[Date].[Date].&[" & Year(Parameters!DateDate.Value) & Right("0" & Month(Parameters!DateDate.Value),2) & Right("0" & Day(Parameters!DateDate.Value),2) & "]"

Sunday, February 19, 2012

Changing from dropdowns to datepickers

No comments:

Post a Comment

database collation

Blog Archive

About Me