Showing posts with label mdx. Show all posts
Showing posts with label mdx. Show all posts

Monday, March 19, 2012

How to use parameters in the MDX?

Hi all,

I'm working on a project that builds a report in RS 2005 agains the data cube created in AS 2005.

I created a report in RS 2005 against database table, and the parameters were easy to be added in the T-SQl query.

There are about 15 parameters needs to add in the query, and I can use "IN, =, Betweent.. and ," in the "where" clause, e.g.,

select *
FROM Mytable1
WHERE EmployeeID IN (@.EmployeeID)
and EmployeeName] IN (@.EmployeeName)
...
and Salary between @.SalaryFrom and @.SalaryTo

... and Date between @.DateStart and @.DateEnd

most of the restriction fields are column fileds in the report.

Right now, I need to switch the datasource to the data cube, and then I have to use MDX to do the query. But I really cannot get enough information about MDX in the MSDN library --> SQL Server Language Reference -->MDX Reference to write a MDX query, especially with parameters.

Could somebody tell me how to do it?

please give me a complete example for the following T-SQL query:

select t1.EmployeeName, t2.Salary, t3.Date, t4.SaleAmount


FROM Mytable1 t1, mytable2 t2, mytable3 t3, mytable4 t4


WHERE t1.EmployeeName IN (@.EmployeeName)
...
and t2.Salary between @.SalaryFrom and @.SalaryTo

... and t3.Date between @.DateStart and @.DateEnd

and t4.SaleAmount betweent @.SaleAmountFrom and @.SaleAmountTo

in the cube, those data are stored in DimEmployee (field EmployeeName), DimSalary(filed Salary), DimDate, FactSales, and the SaleAmount will be the messure field.

Or if sombody can provide me the link to MDX tutorial other than what I can get from the "MDX reference" I mented above, I'll be very appreciated !!

Thanks!!

Jone

Once you set up your data source to use the Analysis Services provider, you will be thrown in the MDX Query Designer. From there, you should be able to create your reports by just dragging and droppping attributes and measures. You may find this link useful.|||

Thank you, Teo, that's really a help.

But I still have some problems when I set up the parameters.

1. After I set up couple of parameters, say 5, p1, p2, p3, p4,p5; then if I change the order of the parameters, like p1,p2,p5,p3,p4, in the parameter pane (I still change the order in the report-->report parameters), the query fails since the "forward dependency " isn't allowed.

My question here is : how to change the parameter's order?

2. I have some parameters in style " from .. to", such as "date". when defining the parameter, I choose "Range(Inclusive)" as the "Operator", and choose "all : all" . There are tones of date values in the table, so I think it's not a good idea to let the user choose one date from the drop-down list.

My question is: how to let the user input a date by themselves? how to show a calendar when user click on this parameter field?

Thank you very much

Jone

|||

1. Are these parameters dependent on each other? Please provide repro steps or sample report definition targeting the Adventure Works cube.

2. You can use a DateTime parameter as long as your main query can resolve it (StrToMember() function) to a valid member in in your Date dimension. The 4_ProductSalesQueryMode.rdl report in this download shows how. If I recall I had to set the Value property of the dimension key of the Date dimension to a DateTime column in the underlying database table to get it to work.

|||

Thanks Teo.

In my report,

1. some of those parameters are dependent on each other, while some are not(but all connected to Facttable). i tried to "move up" or "move down" the undependent parameters in the filter pane, I got errors on "forward dependency".

Upon each setting up of a parameter(say p2, and p1 has been set up before p2), a 'select (strtoset() ...) .. from)' was created automatically in such style ( in the report dataset and the parameter dataset):

FROM ( SELECT ( STRTOSET(@.DimP2, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimP1, CONSTRAINED) ) ON COLUMNS FROM [My Report]))

after changing the order, I got errors.

2. I can get the calendar against the dataset against the database table, but failed against the datacube. I modified the parameter @.Dimdate from string to DateTime, then I got "constrain voilated".

If you have any idea on what I missed, please save me.

Thank you very much!

Jone

|||

Thanks Teo.

In my report,

1. some of those parameters are dependent on each other, while some are not(but all connected to Facttable). i tried to "move up" or "move down" the undependent parameters in the filter pane, I got errors on "forward dependency".

Upon each setting up of a parameter(say p2, and p1 has been set up before p2), a 'select (strtoset() ...) .. from)' was created automatically in such style ( in the report dataset and the parameter dataset):

FROM ( SELECT ( STRTOSET(@.DimP2, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimP1, CONSTRAINED) ) ON COLUMNS FROM [My Report]))

after changing the order, I got errors.

2. I can get the calendar against the dataset against the database table, but failed against the datacube. I modified the parameter @.Dimdate from string to DateTime, then I got "constrain voilated".

If you have any idea on what I missed, please save me.

Thank you very much!

Jone

how to use parameters in mdx query

i have a problem with mdx query. here is a simple example of what i=B4m
trying to do
select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name])} ON ROWS
from [TestCube]
this query works fine... my problem is that the [Customer] dimension
has 3 members and i want to select just one of them, i tried to use
filter function, but it wasn=B4t working, i probably used it the wrong
way. can anyone help me with this? i=B4m working with sql server 2005...You should write it like this:
select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name].[BillyTheKid])} ON ROWS
from [TestCube]
The query with parameters should look like this:
="select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name].[" & Parameters!ClientName.Value & ")} ON ROWS
from [TestCube]"
Everything needs to be on a single line (it will do a line break, but if you
copy it to Notepad, it should all be on one line.)
Even if you're working with RS 2005, you might pick up some tips from an
article called
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
by William Pearson
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_1
He's using RS 2000, but the way to do it is still similar. The good stuff
starts on page 8
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_8
The previous pages are on how to copy and edit one of the sample reports.
Pearson has written lots of good articles on RS and Analysis Services. You
find the full list at http://www.databasejournal.com/article.php/1459531
Kaisa M. Lindahl Lervik
<cellardoor0716@.gmail.com> wrote in message
news:1155818962.580093.222920@.m79g2000cwm.googlegroups.com...
i have a problem with mdx query. here is a simple example of what i´m
trying to do
select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name])} ON ROWS
from [TestCube]
this query works fine... my problem is that the [Customer] dimension
has 3 members and i want to select just one of them, i tried to use
filter function, but it wasn´t working, i probably used it the wrong
way. can anyone help me with this? i´m working with sql server 2005...

Monday, March 12, 2012

How to use MDX script in RS dimension filter (dataview)

Hi, I need to create filter which retrieves data for current moment. [time].[date].lastchild is not an option because the calender in database is generated till following year (2008).

I guess script goes like..

[time].[date].currentmember

but it does not work.

I'm green with these MDX scripts.. I'd be very pleased if some one of you gurus could help me.

Best Regards,
Eeva

You can use StrToMember() for this, e.g.:

StrToMember ("[Date].[Calendar Date].[2003].[H" +

CStr(IIf(Month(Now()) <= 6, 1, 2)) + " CY 2003].[Q" +

CStr(DatePart("q", Now())) + " CY 2003].[" + Format(Now(), "MMMM") + "].[" +

Format(Now(), "MMMM") + " " + CStr(Day(Now())) + "]")

assuming Year-Half Year - Quarter - Month - Day user-defined hierarchy. If you use a special format for the Date dimension key, e.g. YYYYMMDD and you should, then you can simplify the above syntax and use the Day attribute hierarchy directly, e.g. [Date].[Day].[Day].&[20070323]

Sunday, February 19, 2012

How to use a parameter in Calculated Member

I have an MDX query that has two calculated members that use dates.

Currently these dates are "hardcoded" in the calculated member definition.

I want to change this to use input parameters, thereby allowing the user to set the dates. I will then deploy the .rdl file to the Report Server. I have found no examples of using parameters in calculated members, so I am wondering if this is a bad idea.

I have explored the "filter wizard" in Visual Studio, and I am only offered the opportunity to filter on the fields that appear in the report.

I have made several attempts to alter the statement within Visual Studio 2005 while having the MDX statement in "Design Mode", but as soon as I attempt to execute the statement (or change to another view, such as "Preview", Visual Studio tells me that the "query cannot be retrieved from the query builder" and then continues with the (older) most valid query, which does not have reference to the parameter. (This is a Reporting Services project).


Here is a statement with hard coded dates that works properly.


WITH

MEMBER [Measures].[Completed Work On Period Start] AS

(

[Date].[Year Month Date].[Date].&[2006-04-30T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work On Period End] AS

(

[Date].[Year Month Date].[Date].&[2006-05-06T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed_Work] AS

(

[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]

)

SELECT

NON EMPTY

(

FILTER

(

NONEMPTYCROSSJOIN

(


[Assigned To].[Person].[Person],

[Work Item].[System_Id].[System_Id],

[Work Item].[System_Title].[System_Title],

[Work Item].[System_WorkItemType].[System_WorkItemType],

[Measures].[Current Work Item Count],

4

),

[Measures].[Completed_Work] > 0

)

)

ON ROWS,

NON EMPTY

{

[Measures].[Work Item Url],

[Measures].[Completed_Work]

}

ON COLUMNS

FROM

[Team System]

You need to be in the MDX view to hand author the query to do this.

- In the Query Designer, switch to MDX view

- Change the query to be a parameterised query, e.g. replacing the date with StrToSet(@.Date, CONSTRAINED)

- Select the "Query Parameters" option from the toolbar, to define the parameter. You must provide a default value, that will be used when previewing/preparing the query

|||

Following Paul's guidance, I was able to edit the query without VS flagging the query as invalid.

BTW: I used StrToMember instead of StrToSet, since my parameter was in the Members section. The results of the query are valid.

WITH

MEMBER [Measures].[Completed Work On Period Start] AS

(

(STRTOMember(@.prmStartDate)),

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work On Period End] AS

(

(STRTOMember(@.prmEndDate)),

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed_Work] AS

(

[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]

)

SELECT

NON EMPTY

(

FILTER

(

NONEMPTYCROSSJOIN

(


[Assigned To].[Person].[Person],

[Work Item].[System_Id].[System_Id],

[Work Item].[System_Title].[System_Title],

[Work Item].[System_WorkItemType].[System_WorkItemType],

[Measures].[Current Work Item Count],

4

),

[Measures].[Completed_Work] > 0

)

)

ON ROWS,

NON EMPTY

{

[Measures].[Work Item Url],

[Measures].[Completed_Work]

}

ON COLUMNS

FROM

[Team System]