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
No comments:
Post a Comment