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