Is this possible? How

hello guys,

I want to create a report using a bar chart data region from a cube. The cube contains Date and Time dimentions. Here is my question: The catagory field (x-axis) value of the bar chart is dependent on the paramaters value(i.e.StartDate and EndDate). For instance if the difference between StartDate and EndDate is greater than 1 year, the x-axis value for the bar chart should come from the Date dimention and display the year value. Similarly, if the difference between StartDate and EndDate is less than a day, the x-axis value for the bar chart will come from the Time dimention and display the hours value.

Does any body came across this kind of scenario.

Appreciate your assistance.

Amde

Hi,

Try the following:

1. One mdx query that returns on rows the hours of the time dimension from the startDate to endDate

2. Another mdx query equals to the above, but returns the years of the date dimension

3. Create two charts overlapped with each other (one for query 1 and the other to the query 2)

4. In the hidden property of the charts set the respectively expressions so that only one chart is visible, accordingly to your date conditions.

It's not a pretty solution but I hope that solves your problem.

Regards,

Telmo Moreira

|||

Hi Telmo,

Even I was thinking to implement the same way you said. I am just wondering if there is a possibility to implement using only one chart.

Anyways thanks!

Sincerely,

Amde

|||

hey Telmo,

Let me ask you one more thing, I want to add mdx filter for the time dimention based on the parameters value (StartDate and EndDate). If you click the data tab of the report designer and go to the filter pane, then in the operator section you can select MDX from the drop down list which allows you to write MDX filter expression. So, I want to filter the data between the StartDate and EndDate parameters value. Can you please tell me the syntax to write the mdx expression.

Sincerely,

Amde

|||Hi,
You can use the "Range (Inclusive )" Operator, fill the "filter expression" with default values and check the two "check boxes" so you can use the parameters. After that you go to the parameters dialog box and you'll see the two parameters just automatically created, and assign these parameters to your startDate and endDate datasets.

Regards,

Telmo Moreira
|||

Hi Telmo,

You are right. But this will work if you want to create a parameter associated with the dimension value.

The thing is I want to create arbitrary mdx parameters that are not associated with the dimention.

Do you have any idea how to do this?

Thanks,

Amde

|||Hi,

You can select another dataset for the startDate and endDate parameters that were created automatically in the design view (just go to the "parameters dialog box" and select it). That dataset can be an arbitrary mdx that are not associated with the dimension, i.e., a mdx query that you have created.

I don′t know if this is the problem, but I hope this is helpful.

Regards,
Telmo Moreira|||

hi Telmo,

Let me make my question clear. Here is the thing. I have a date dimention which contains dates information. On the other hand I created an arbitrary parameters using the parameters dialog box. So in the filter pane of mdx designer, I want to select the date dimension and filter it based on the arbitrary parameters that I created in the parameter dialog box. However, in the filter expression column of the mdx designer, it always allows me to select values from the dimension. So, is there any way to create an arbitrary parameters and filter a dimension based on the arbitrary parameters? In my case, I want to create a datetime picker parameters using the parameters dialog box, and filter the date dimension based on the parameter I created.

I think I made myself clear.

Please let me know if you need more clarification

Sincerely,

Amde

|||Hi,

If you created datetime picker parameters, you must converting it to an equivalent member of the date dimension. You must modify the mdx query by switching to the "edit mode". You can use the VB.NET date/time functions for extracting the day/month/year of that parameter and makes an equvalent member of the date dimension . Note that your mdx query should be an expression (="....") so you can use the VB.NET date/time functions.

Regards,
Telmo Moreira

|||

Hi Telmo,

I totally understand what you are saying like converting the datetime picker parameters into the equivalent member of the date dimension. However, before doing this, I have to associate the date dimension with the datetime pickers parameter(this is the most important concept I would like to know) and then I will think about converting the datetime picker to the equivalent member of the date dimension. In other words, in the filter pane of the mdx query, I have to select the date dimension and filter it based on the arbitrary datetime pickers parameters and then I will make the conversion.

I would appreciate if you give me a practical example to illustrate this concept.

Sincerely,

Amde

|||Hi,

I told you that you must edit your mdx query in the "edit view" , not in the design view (forget the step “associate the date dimension with the datetime pickers parameter”; the selection of default values in the design view is merely for the design view running the query).

When you switch to the “edit mode”, find the parameters @.endDate and @.startDate; then convert them into an equivalent member of the date dimension. Suppose that your date dimension has an hierarchy with the levels day, month and year, then you must replace the parameter @.endDate to something like this “[Your Date Dimension].[Your Hierarchy].[“ + Year(@.parameter) + “].[“+ Month(@.parameter) + “].[” + Day(Month) + “]”

Don′t forget to use an expression for the dataset:

=”select

{…} On columns

{…} on rows

// supposing that the parameters endDate and startDAte are in the “where statement”

Where “ + “[Your Date Dimension].[Your Hierarchy].[“ + Year(@.parameter) + “].[“+ Month(@.parameter) + “].[” + Day(@.parameter) + “]”

I never used the date pickers parameters in my mdx queries. So my previous solution is just what I would do in such a similar case. Sorry, if my help was not useful.

Regards,

Telmo Moreira|||

Hi Telmo,

Thank you for your clarification. I want to ask you one more thing; you said "find the parameters @.endDate and @.startDate when I switch to the edit mode". But the problem is, I created these parameters using the Report -> Report parameters...dialog box, and you will never find these parameters when you go back to the mdx query coz the query doesn't recognize the parameter created in the parameter dialog box.

What do you say about this issue?

appreaciate your help

Sincerely,

Amde

|||Switch to the design view and in the "filter pane" select the two "check boxes" so you can use the values as parameters. Then switch back to the "edit view", find the parameters that the design created and replace with your enddate and startdate parameters (don't forget to convert to an equivalent date dimension member)

Regards,

Telmo Moreira
|||

Hi Telmo,

I did what you told me, but I am getting some syntax error. On the top that I didn't get the parameters in the where condition. Anyhow here is the dataset automatically created: The thing is I am new for mdx and I hope you can help me in editing the query.

SELECT NON EMPTY { [Measures].[Sessions Count] } ON COLUMNS, NON EMPTY { ([Sessions].[Completion State].[Completion State].ALLMEMBERS * [Dates].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@.FromDatesDate, CONSTRAINED) : STRTOMEMBER(@.ToDatesDate, CONSTRAINED) ) ON COLUMNS FROM [Sessions]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Note:

1.The @.FromDatesDate and the @.ToDateDate are automatically created when I click the check box in the design view.

2. The name of the arbitrary parameters that I created in the parameter dialog box is @.StartDate and @.EndDate

--

hey Telmo, one more thing; when I edit the mdx query and switch back to the design view, I got a message which says "If you modify the mdx statement associated with the query and and then return to the design mode, your changes will be lost" . Do you have any idea?

Thanks in advance.

Sincerely,

Amde

|||Hi,

When you edit the query and you switch to design view, the changes will be lost, unfortunately

I'll try to help you editing the mdx. So , you must give me an example of a date dimension member, so we can convert the date picker to an equivalent date dimension member.

Regards,
Telmo Moreira

Is this possible? How

hello guys,

I want to create a report using a bar chart data region from a cube. The cube contains Date and Time dimentions. Here is my question: The catagory field (x-axis) value of the bar chart is dependent on the paramaters value(i.e.StartDate and EndDate). For instance if the difference between StartDate and EndDate is greater than 1 year, the x-axis value for the bar chart should come from the Date dimention and display the year value. Similarly, if the difference between StartDate and EndDate is less than a day, the x-axis value for the bar chart will come from the Time dimention and display the hours value.

Does any body came across this kind of scenario.

Appreciate your assistance.

Amde

Hi,

Try the following:

1. One mdx query that returns on rows the hours of the time dimension from the startDate to endDate

2. Another mdx query equals to the above, but returns the years of the date dimension

3. Create two charts overlapped with each other (one for query 1 and the other to the query 2)

4. In the hidden property of the charts set the respectively expressions so that only one chart is visible, accordingly to your date conditions.

It's not a pretty solution but I hope that solves your problem.

Regards,

Telmo Moreira

|||

Hi Telmo,

Even I was thinking to implement the same way you said. I am just wondering if there is a possibility to implement using only one chart.

Anyways thanks!

Sincerely,

Amde

|||

hey Telmo,

Let me ask you one more thing, I want to add mdx filter for the time dimention based on the parameters value (StartDate and EndDate). If you click the data tab of the report designer and go to the filter pane, then in the operator section you can select MDX from the drop down list which allows you to write MDX filter expression. So, I want to filter the data between the StartDate and EndDate parameters value. Can you please tell me the syntax to write the mdx expression.

Sincerely,

Amde

|||Hi,
You can use the "Range (Inclusive )" Operator, fill the "filter expression" with default values and check the two "check boxes" so you can use the parameters. After that you go to the parameters dialog box and you'll see the two parameters just automatically created, and assign these parameters to your startDate and endDate datasets.

Regards,

Telmo Moreira|||

Hi Telmo,

You are right. But this will work if you want to create a parameter associated with the dimension value.

The thing is I want to create arbitrary mdx parameters that are not associated with the dimention.

Do you have any idea how to do this?

Thanks,

Amde

|||Hi,

You can select another dataset for the startDate and endDate parameters that were created automatically in the design view (just go to the "parameters dialog box" and select it). That dataset can be an arbitrary mdx that are not associated with the dimension, i.e., a mdx query that you have created.

I don′t know if this is the problem, but I hope this is helpful.

Regards,
Telmo Moreira|||

hi Telmo,

Let me make my question clear. Here is the thing. I have a date dimention which contains dates information. On the other hand I created an arbitrary parameters using the parameters dialog box. So in the filter pane of mdx designer, I want to select the date dimension and filter it based on the arbitrary parameters that I created in the parameter dialog box. However, in the filter expression column of the mdx designer, it always allows me to select values from the dimension. So, is there any way to create an arbitrary parameters and filter a dimension based on the arbitrary parameters? In my case, I want to create a datetime picker parameters using the parameters dialog box, and filter the date dimension based on the parameter I created.

I think I made myself clear.

Please let me know if you need more clarification

Sincerely,

Amde

|||Hi,

If you created datetime picker parameters, you must converting it to an equivalent member of the date dimension. You must modify the mdx query by switching to the "edit mode". You can use the VB.NET date/time functions for extracting the day/month/year of that parameter and makes an equvalent member of the date dimension . Note that your mdx query should be an expression (="....") so you can use the VB.NET date/time functions.

Regards,
Telmo Moreira|||

Hi Telmo,

I totally understand what you are saying like converting the datetime picker parameters into the equivalent member of the date dimension. However, before doing this, I have to associate the date dimension with the datetime pickers parameter(this is the most important concept I would like to know) and then I will think about converting the datetime picker to the equivalent member of the date dimension. In other words, in the filter pane of the mdx query, I have to select the date dimension and filter it based on the arbitrary datetime pickers parameters and then I will make the conversion.

I would appreciate if you give me a practical example to illustrate this concept.

Sincerely,

Amde

|||Hi,

I told you that you must edit your mdx query in the "edit view" , not in the design view (forget the step “associate the date dimension with the datetime pickers parameter”; the selection of default values in the design view is merely for the design view running the query).

When you switch to the “edit mode”, find the parameters @.endDate and @.startDate; then convert them into an equivalent member of the date dimension. Suppose that your date dimension has an hierarchy with the levels day, month and year, then you must replace the parameter @.endDate to something like this “[Your Date Dimension].[Your Hierarchy].[“ + Year(@.parameter) + “].[“+ Month(@.parameter) + “].[” + Day(Month) + “]”

Don′t forget to use an expression for the dataset:

=”select

{…} On columns

{…} on rows

// supposing that the parameters endDate and startDAte are in the “where statement”

Where “ + “[Your Date Dimension].[Your Hierarchy].[“ + Year(@.parameter) + “].[“+ Month(@.parameter) + “].[” + Day(@.parameter) + “]”

I never used the date pickers parameters in my mdx queries. So my previous solution is just what I would do in such a similar case. Sorry, if my help was not useful.

Regards,

Telmo Moreira|||

Hi Telmo,

Thank you for your clarification. I want to ask you one more thing; you said "find the parameters @.endDate and @.startDate when I switch to the edit mode". But the problem is, I created these parameters using the Report -> Report parameters...dialog box, and you will never find these parameters when you go back to the mdx query coz the query doesn't recognize the parameter created in the parameter dialog box.

What do you say about this issue?

appreaciate your help

Sincerely,

Amde

|||Switch to the design view and in the "filter pane" select the two "check boxes" so you can use the values as parameters. Then switch back to the "edit view", find the parameters that the design created and replace with your enddate and startdate parameters (don't forget to convert to an equivalent date dimension member)

Regards,

Telmo Moreira
|||

Hi Telmo,

I did what you told me, but I am getting some syntax error. On the top that I didn't get the parameters in the where condition. Anyhow here is the dataset automatically created: The thing is I am new for mdx and I hope you can help me in editing the query.

SELECT NON EMPTY { [Measures].[Sessions Count] } ON COLUMNS, NON EMPTY { ([Sessions].[Completion State].[Completion State].ALLMEMBERS * [Dates].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@.FromDatesDate, CONSTRAINED) : STRTOMEMBER(@.ToDatesDate, CONSTRAINED) ) ON COLUMNS FROM [Sessions]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Note:

1.The @.FromDatesDate and the @.ToDateDate are automatically created when I click the check box in the design view.

2. The name of the arbitrary parameters that I created in the parameter dialog box is @.StartDate and @.EndDate

--

hey Telmo, one more thing; when I edit the mdx query and switch back to the design view, I got a message which says "If you modify the mdx statement associated with the query and and then return to the design mode, your changes will be lost" . Do you have any idea?

Thanks in advance.

Sincerely,

Amde

|||Hi,

When you edit the query and you switch to design view, the changes will be lost, unfortunately

I'll try to help you editing the mdx. So , you must give me an example of a date dimension member, so we can convert the date picker to an equivalent date dimension member.

Regards,
Telmo Moreira

Is this possible? How

hello guys,

I want to create a report using a bar chart data region from a cube. The cube contains Date and Time dimentions. Here is my question: The catagory field (x-axis) value of the bar chart is dependent on the paramaters value(i.e.StartDate and EndDate). For instance if the difference between StartDate and EndDate is greater than 1 year, the x-axis value for the bar chart should come from the Date dimention and display the year value. Similarly, if the difference between StartDate and EndDate is less than a day, the x-axis value for the bar chart will come from the Time dimention and display the hours value.

Does any body came across this kind of scenario.

Appreciate your assistance.

Amde

Hi,

Try the following:

1. One mdx query that returns on rows the hours of the time dimension from the startDate to endDate

2. Another mdx query equals to the above, but returns the years of the date dimension

3. Create two charts overlapped with each other (one for query 1 and the other to the query 2)

4. In the hidden property of the charts set the respectively expressions so that only one chart is visible, accordingly to your date conditions.

It's not a pretty solution but I hope that solves your problem.

Regards,

Telmo Moreira

|||

Hi Telmo,

Even I was thinking to implement the same way you said. I am just wondering if there is a possibility to implement using only one chart.

Anyways thanks!

Sincerely,

Amde

|||

hey Telmo,

Let me ask you one more thing, I want to add mdx filter for the time dimention based on the parameters value (StartDate and EndDate). If you click the data tab of the report designer and go to the filter pane, then in the operator section you can select MDX from the drop down list which allows you to write MDX filter expression. So, I want to filter the data between the StartDate and EndDate parameters value. Can you please tell me the syntax to write the mdx expression.

Sincerely,

Amde

|||Hi,
You can use the "Range (Inclusive )" Operator, fill the "filter expression" with default values and check the two "check boxes" so you can use the parameters. After that you go to the parameters dialog box and you'll see the two parameters just automatically created, and assign these parameters to your startDate and endDate datasets.

Regards,

Telmo Moreira
|||

Hi Telmo,

You are right. But this will work if you want to create a parameter associated with the dimension value.

The thing is I want to create arbitrary mdx parameters that are not associated with the dimention.

Do you have any idea how to do this?

Thanks,

Amde

|||Hi,

You can select another dataset for the startDate and endDate parameters that were created automatically in the design view (just go to the "parameters dialog box" and select it). That dataset can be an arbitrary mdx that are not associated with the dimension, i.e., a mdx query that you have created.

I don′t know if this is the problem, but I hope this is helpful.

Regards,
Telmo Moreira|||

hi Telmo,

Let me make my question clear. Here is the thing. I have a date dimention which contains dates information. On the other hand I created an arbitrary parameters using the parameters dialog box. So in the filter pane of mdx designer, I want to select the date dimension and filter it based on the arbitrary parameters that I created in the parameter dialog box. However, in the filter expression column of the mdx designer, it always allows me to select values from the dimension. So, is there any way to create an arbitrary parameters and filter a dimension based on the arbitrary parameters? In my case, I want to create a datetime picker parameters using the parameters dialog box, and filter the date dimension based on the parameter I created.

I think I made myself clear.

Please let me know if you need more clarification

Sincerely,

Amde

|||Hi,

If you created datetime picker parameters, you must converting it to an equivalent member of the date dimension. You must modify the mdx query by switching to the "edit mode". You can use the VB.NET date/time functions for extracting the day/month/year of that parameter and makes an equvalent member of the date dimension . Note that your mdx query should be an expression (="....") so you can use the VB.NET date/time functions.

Regards,
Telmo Moreira

|||

Hi Telmo,

I totally understand what you are saying like converting the datetime picker parameters into the equivalent member of the date dimension. However, before doing this, I have to associate the date dimension with the datetime pickers parameter(this is the most important concept I would like to know) and then I will think about converting the datetime picker to the equivalent member of the date dimension. In other words, in the filter pane of the mdx query, I have to select the date dimension and filter it based on the arbitrary datetime pickers parameters and then I will make the conversion.

I would appreciate if you give me a practical example to illustrate this concept.

Sincerely,

Amde

|||Hi,

I told you that you must edit your mdx query in the "edit view" , not in the design view (forget the step “associate the date dimension with the datetime pickers parameter”; the selection of default values in the design view is merely for the design view running the query).

When you switch to the “edit mode”, find the parameters @.endDate and @.startDate; then convert them into an equivalent member of the date dimension. Suppose that your date dimension has an hierarchy with the levels day, month and year, then you must replace the parameter @.endDate to something like this “[Your Date Dimension].[Your Hierarchy].[“ + Year(@.parameter) + “].[“+ Month(@.parameter) + “].[” + Day(Month) + “]”

Don′t forget to use an expression for the dataset:

=”select

{…} On columns

{…} on rows

// supposing that the parameters endDate and startDAte are in the “where statement”

Where “ + “[Your Date Dimension].[Your Hierarchy].[“ + Year(@.parameter) + “].[“+ Month(@.parameter) + “].[” + Day(@.parameter) + “]”

I never used the date pickers parameters in my mdx queries. So my previous solution is just what I would do in such a similar case. Sorry, if my help was not useful.

Regards,

Telmo Moreira|||

Hi Telmo,

Thank you for your clarification. I want to ask you one more thing; you said "find the parameters @.endDate and @.startDate when I switch to the edit mode". But the problem is, I created these parameters using the Report -> Report parameters...dialog box, and you will never find these parameters when you go back to the mdx query coz the query doesn't recognize the parameter created in the parameter dialog box.

What do you say about this issue?

appreaciate your help

Sincerely,

Amde

|||Switch to the design view and in the "filter pane" select the two "check boxes" so you can use the values as parameters. Then switch back to the "edit view", find the parameters that the design created and replace with your enddate and startdate parameters (don't forget to convert to an equivalent date dimension member)

Regards,

Telmo Moreira
|||

Hi Telmo,

I did what you told me, but I am getting some syntax error. On the top that I didn't get the parameters in the where condition. Anyhow here is the dataset automatically created: The thing is I am new for mdx and I hope you can help me in editing the query.

SELECT NON EMPTY { [Measures].[Sessions Count] } ON COLUMNS, NON EMPTY { ([Sessions].[Completion State].[Completion State].ALLMEMBERS * [Dates].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@.FromDatesDate, CONSTRAINED) : STRTOMEMBER(@.ToDatesDate, CONSTRAINED) ) ON COLUMNS FROM [Sessions]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Note:

1.The @.FromDatesDate and the @.ToDateDate are automatically created when I click the check box in the design view.

2. The name of the arbitrary parameters that I created in the parameter dialog box is @.StartDate and @.EndDate

--

hey Telmo, one more thing; when I edit the mdx query and switch back to the design view, I got a message which says "If you modify the mdx statement associated with the query and and then return to the design mode, your changes will be lost" . Do you have any idea?

Thanks in advance.

Sincerely,

Amde

|||Hi,

When you edit the query and you switch to design view, the changes will be lost, unfortunately

I'll try to help you editing the mdx. So , you must give me an example of a date dimension member, so we can convert the date picker to an equivalent date dimension member.

Regards,
Telmo Moreira

Is this possible? How

hello guys,

I want to create a report using a bar chart data region from a cube. The cube contains Date and Time dimentions. Here is my question: The catagory field (x-axis) value of the bar chart is dependent on the paramaters value(i.e.StartDate and EndDate). For instance if the difference between StartDate and EndDate is greater than 1 year, the x-axis value for the bar chart should come from the Date dimention and display the year value. Similarly, if the difference between StartDate and EndDate is less than a day, the x-axis value for the bar chart will come from the Time dimention and display the hours value.

Does any body came across this kind of scenario.

Appreciate your assistance.

Amde

Hi,

Try the following:

1. One mdx query that returns on rows the hours of the time dimension from the startDate to endDate

2. Another mdx query equals to the above, but returns the years of the date dimension

3. Create two charts overlapped with each other (one for query 1 and the other to the query 2)

4. In the hidden property of the charts set the respectively expressions so that only one chart is visible, accordingly to your date conditions.

It's not a pretty solution but I hope that solves your problem.

Regards,

Telmo Moreira

|||

Hi Telmo,

Even I was thinking to implement the same way you said. I am just wondering if there is a possibility to implement using only one chart.

Anyways thanks!

Sincerely,

Amde

|||

hey Telmo,

Let me ask you one more thing, I want to add mdx filter for the time dimention based on the parameters value (StartDate and EndDate). If you click the data tab of the report designer and go to the filter pane, then in the operator section you can select MDX from the drop down list which allows you to write MDX filter expression. So, I want to filter the data between the StartDate and EndDate parameters value. Can you please tell me the syntax to write the mdx expression.

Sincerely,

Amde

|||Hi,
You can use the "Range (Inclusive )" Operator, fill the "filter expression" with default values and check the two "check boxes" so you can use the parameters. After that you go to the parameters dialog box and you'll see the two parameters just automatically created, and assign these parameters to your startDate and endDate datasets.

Regards,

Telmo Moreira
|||

Hi Telmo,

You are right. But this will work if you want to create a parameter associated with the dimension value.

The thing is I want to create arbitrary mdx parameters that are not associated with the dimention.

Do you have any idea how to do this?

Thanks,

Amde

|||Hi,

You can select another dataset for the startDate and endDate parameters that were created automatically in the design view (just go to the "parameters dialog box" and select it). That dataset can be an arbitrary mdx that are not associated with the dimension, i.e., a mdx query that you have created.

I don′t know if this is the problem, but I hope this is helpful.

Regards,
Telmo Moreira|||

hi Telmo,

Let me make my question clear. Here is the thing. I have a date dimention which contains dates information. On the other hand I created an arbitrary parameters using the parameters dialog box. So in the filter pane of mdx designer, I want to select the date dimension and filter it based on the arbitrary parameters that I created in the parameter dialog box. However, in the filter expression column of the mdx designer, it always allows me to select values from the dimension. So, is there any way to create an arbitrary parameters and filter a dimension based on the arbitrary parameters? In my case, I want to create a datetime picker parameters using the parameters dialog box, and filter the date dimension based on the parameter I created.

I think I made myself clear.

Please let me know if you need more clarification

Sincerely,

Amde

|||Hi,

If you created datetime picker parameters, you must converting it to an equivalent member of the date dimension. You must modify the mdx query by switching to the "edit mode". You can use the VB.NET date/time functions for extracting the day/month/year of that parameter and makes an equvalent member of the date dimension . Note that your mdx query should be an expression (="....") so you can use the VB.NET date/time functions.

Regards,
Telmo Moreira

|||

Hi Telmo,

I totally understand what you are saying like converting the datetime picker parameters into the equivalent member of the date dimension. However, before doing this, I have to associate the date dimension with the datetime pickers parameter(this is the most important concept I would like to know) and then I will think about converting the datetime picker to the equivalent member of the date dimension. In other words, in the filter pane of the mdx query, I have to select the date dimension and filter it based on the arbitrary datetime pickers parameters and then I will make the conversion.

I would appreciate if you give me a practical example to illustrate this concept.

Sincerely,

Amde

|||Hi,

I told you that you must edit your mdx query in the "edit view" , not in the design view (forget the step “associate the date dimension with the datetime pickers parameter”; the selection of default values in the design view is merely for the design view running the query).

When you switch to the “edit mode”, find the parameters @.endDate and @.startDate; then convert them into an equivalent member of the date dimension. Suppose that your date dimension has an hierarchy with the levels day, month and year, then you must replace the parameter @.endDate to something like this “[Your Date Dimension].[Your Hierarchy].[“ + Year(@.parameter) + “].[“+ Month(@.parameter) + “].[” + Day(Month) + “]”

Don′t forget to use an expression for the dataset:

=”select

{…} On columns

{…} on rows

// supposing that the parameters endDate and startDAte are in the “where statement”

Where “ + “[Your Date Dimension].[Your Hierarchy].[“ + Year(@.parameter) + “].[“+ Month(@.parameter) + “].[” + Day(@.parameter) + “]”

I never used the date pickers parameters in my mdx queries. So my previous solution is just what I would do in such a similar case. Sorry, if my help was not useful.

Regards,

Telmo Moreira|||

Hi Telmo,

Thank you for your clarification. I want to ask you one more thing; you said "find the parameters @.endDate and @.startDate when I switch to the edit mode". But the problem is, I created these parameters using the Report -> Report parameters...dialog box, and you will never find these parameters when you go back to the mdx query coz the query doesn't recognize the parameter created in the parameter dialog box.

What do you say about this issue?

appreaciate your help

Sincerely,

Amde

|||Switch to the design view and in the "filter pane" select the two "check boxes" so you can use the values as parameters. Then switch back to the "edit view", find the parameters that the design created and replace with your enddate and startdate parameters (don't forget to convert to an equivalent date dimension member)

Regards,

Telmo Moreira
|||

Hi Telmo,

I did what you told me, but I am getting some syntax error. On the top that I didn't get the parameters in the where condition. Anyhow here is the dataset automatically created: The thing is I am new for mdx and I hope you can help me in editing the query.

SELECT NON EMPTY { [Measures].[Sessions Count] } ON COLUMNS, NON EMPTY { ([Sessions].[Completion State].[Completion State].ALLMEMBERS * [Dates].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@.FromDatesDate, CONSTRAINED) : STRTOMEMBER(@.ToDatesDate, CONSTRAINED) ) ON COLUMNS FROM [Sessions]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Note:

1.The @.FromDatesDate and the @.ToDateDate are automatically created when I click the check box in the design view.

2. The name of the arbitrary parameters that I created in the parameter dialog box is @.StartDate and @.EndDate

--

hey Telmo, one more thing; when I edit the mdx query and switch back to the design view, I got a message which says "If you modify the mdx statement associated with the query and and then return to the design mode, your changes will be lost" . Do you have any idea?

Thanks in advance.

Sincerely,

Amde

|||Hi,

When you edit the query and you switch to design view, the changes will be lost, unfortunately

I'll try to help you editing the mdx. So , you must give me an example of a date dimension member, so we can convert the date picker to an equivalent date dimension member.

Regards,
Telmo Moreira
sql

Is this possible in MDX queries

Hi

The cube i have conatins the ProductName , Time as dim and Sales as measure. I need to query from the cube the product name and sales for multiple date ranges. As an example, The I/P shall be in the format

ProductName : A,B

DateRange : 1/1/2004:2/1/2004,1/1/2005:2/1/2005,and so on.

The number of date ranges is not fixed and also is the productname but the date ranges are common across both the Products. I have to query for the above I/P and use SSRS to get the O/P.

Thanking you in advance

regards

saishyam

I am not sure exactly what you are looking for, but here is an example using Adventure Works that list products with sales by total for different date ranges:

WITH

SET [Selected Products] AS

{[Product].[Product Categories].[Subcategory].[Mountain Bikes].Children}

SET [Date Range 2002] AS

{[Date].[Date].[January 1, 2002]:[Date].[Date].[February 1, 2002]}

MEMBER [Date].[Date].[Date Range 2002 Total] AS

Aggregate([Date Range 2002])

SET [Date Range 2003] AS

{[Date].[Date].[January 1, 2003]:[Date].[Date].[February 1, 2003]}

MEMBER [Date].[Date].[Date Range 2003 Total] AS

Aggregate([Date Range 2003])

SET [All Date Range Totals] AS

{[Date].[Date].[Date Range 2002 Total],

[Date].[Date].[Date Range 2003 Total]}

SELECT

{[All Date Range Totals]} ON COLUMNS,

NonEmpty({[Selected Products]},{[All Date Range Totals]}) ON ROWS

FROM

[Adventure Works]

WHERE

([Measures].[Internet Sales Amount])

You can substitute another measure for "Internet Sales Amount" by changing the WHERE clause and get the totals for a different measure.

HTH,

Steve

|||

Hi Steve

Thank you for the response.

The query that you have mentioned conatins the date ranges fixed, in my case the number of date ranges is not fixed. So how will i know , how many Sets i need to create? Is there any way to do that. I hope now my quesition is more clear.

regards

Saishyam

|||

You don't have to define the sets explicitly, you should be able to just do this:

{ { Date1member : Date2member}, { Date3member : Date4member } }

Alternatively, MDX has a function called Union.

sql

is this layout in analysis services possible?

Hi,

I have a cube which should show top 10 products and a percentage for that versus the overall performance of the product.

Can this be possible?

Amount Percentage

Product 1 1000 5%

Product 2 2000 10%

Product 3 1500 7.5%

.

.

.

Product 10 4000 20%

SubTotal for top 10 10000 50%

Total 20000

This is definitely possible, showing the sub total and grand total might be tricky in a pivot table, but it is not too hard with an MDX query.

Is there Simple way to get all dimension & Hierarchy levels from a database?

I need to extract a list of all dimensions and their levels from all dimensions and hierarchies in a cube.

Im assuming that an XMLA query would do the trick...

The reason is that in converting from SAS 2000 to SAS2005, most of our virtual dimensions became member hierarchies in existing dimension, and now we have a large number of saved MDX queries that need to be updated to have the new dimension names. It will be a simple process if I can start from the known dimension and level names

For example the virtual dimension [Product Class] is now referenced as [Item].[Product Class], so now it and all levels of it's hierarchies need to be updated in all saved queries that used it. I have the search/replace but need to build a from/to list quickly

Any suggestions? sample scripts?

Thanks in advance,

Hi Clayton,

You can try this Discover XML/A query, which returns all levels in the AW cube:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>MDSCHEMA_LEVELS</RequestType>

<Restrictions>

<RestrictionList>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

</PropertyList>

</Properties>

</Discover>

is there any way AMO app can get information from data source without reprompting user for user

I'm writing some code that will use AMO to access an AnalysisServices cube. I've found that some information that I need doesn't appear to be available from the AMO interfaces directly, so I need to get the information from one of the data sources. I tried using ADO.NET to connect to the data source, but I found that the Microsoft.AnalysisServices.DataSource.ConnectionString has the authentication information (i.e. username and password) removed. So... is there any other alternative that will allow me to get information from the data source (i.e. issue SQL command to the data source and retrieve the result), that does not require me to prompt the user for the authentication information? Can I somehow go through the AMO and have it issue the command and get the result? I'm new to this and hoping that someone can provide a suggestion.

Thanks in advance!

Arden

I don't think there is anyway of doing this.

Have you had a look at some of the schema rowsets that are available? If you are after lists of members or something like that there may be another way of achieving what you are after. Someone might be able to help if you could explain what information you are after.

is there any way AMO app can get information from data source without reprompting user for u

I'm writing some code that will use AMO to access an AnalysisServices cube. I've found that some information that I need doesn't appear to be available from the AMO interfaces directly, so I need to get the information from one of the data sources. I tried using ADO.NET to connect to the data source, but I found that the Microsoft.AnalysisServices.DataSource.ConnectionString has the authentication information (i.e. username and password) removed. So... is there any other alternative that will allow me to get information from the data source (i.e. issue SQL command to the data source and retrieve the result), that does not require me to prompt the user for the authentication information? Can I somehow go through the AMO and have it issue the command and get the result? I'm new to this and hoping that someone can provide a suggestion.

Thanks in advance!

Arden

I don't think there is anyway of doing this.

Have you had a look at some of the schema rowsets that are available? If you are after lists of members or something like that there may be another way of achieving what you are after. Someone might be able to help if you could explain what information you are after.