Reports with "Multi Parameters" Accessing Analysis Services Cube (MDX calls)?

I have an OLAP Cube that is being accessed by Reporting services reports
with MDX syntax. I am successful so far in having one parameter set. I need
to know if it is possible to have more than one to set and get the desired
results accordingly. Here is what I have so far:
Data Source:
="Select NON EMPTY{ {[Geo1].[Div].members}*{[Dept1].[Department].members}*
{ [Employee].[All Employee].children}} On ROWS,
NON EMPTY {[Measures].[Percent Complete]} ON COLUMNS
FROM [My Cube1]
WHERE ([" + Parameters!pCurr.Value + "])"
pCurr data set :
SELECT { [Measures].[Percent Complete]} ON Columns,NON EMPTY [Curriculum].
[Curriculum].Members ON ROWS FROM [My Cube1]
So, thus Far I can control the result set by Choosing the Curriculum only.
I need to be able to have another parameter set, besides Curriculum, for
instance Department, where I would choose department, then curriculum,
then the result set would be the outcome needed? Is it feasible and how?
Thanks so much in advance.
--
Message posted via http://www.sqlmonster.com="SELECT NON EMPTY { {[Geo1].[Div].members } * {[Dept1].[Department].[" +
Parameters!pDept.Value + "] } * { [Employee].[All Employee].children}} On
ROWS,
NON EMPTY {[Measures].[Percent Complete]} ON COLUMNS
FROM [My Cube1]
WHERE ([" + Parameters!pCurr.Value + "])"
btw, i usually don't use the where clause, i just put parameter into the
select dimensions.
"Hobie via SQLMonster.com" wrote:
> I have an OLAP Cube that is being accessed by Reporting services reports
> with MDX syntax. I am successful so far in having one parameter set. I need
> to know if it is possible to have more than one to set and get the desired
> results accordingly. Here is what I have so far:
> Data Source:
> ="Select NON EMPTY{ {[Geo1].[Div].members}*{[Dept1].[Department].members}*
> { [Employee].[All Employee].children}} On ROWS,
> NON EMPTY {[Measures].[Percent Complete]} ON COLUMNS
> FROM [My Cube1]
> WHERE ([" + Parameters!pCurr.Value + "])"
> pCurr data set :
> SELECT { [Measures].[Percent Complete]} ON Columns,NON EMPTY [Curriculum].
> [Curriculum].Members ON ROWS FROM [My Cube1]
> So, thus Far I can control the result set by Choosing the Curriculum only.
> I need to be able to have another parameter set, besides Curriculum, for
> instance Department, where I would choose department, then curriculum,
> then the result set would be the outcome needed? Is it feasible and how?
> Thanks so much in advance.
> --
> Message posted via http://www.sqlmonster.com
>|||Great. it worked with few glitches that I am working on correcting.
However, I do not want to include my parameters in the select. see I would
like the below format:
Select Select Select
Division: Services Department:Acounting Curriculum: Excel (as params)
_________________________________________________________________
Employee Percent Complete
John 40%
Amy 20%
Kerry 90%
That is why I was adding the where clause. If I can say in the WHERE:
WHERE ([" + Parameters!pDiv.Value + "] AND [" + Parameters!pDept.Value + "]
" AND [" + Parameters!pCurr.Value + "] ),
But I know there is no AND in this fasion in MDX. Any suggestions!
Again thanks in advance for the help!!!
--
Message posted via http://www.sqlmonster.com|||WEll - I solved my own problem, but thanks to mike for the tip; No more
WHERE statement in MDX for params specifications here is what mike
suggested and how I implemented it. :) .
--
DataSource1:
="SELECT NON EMPTY { {[Geo1].[Div].[" + Parameters!pDiv.Value + "]} * {
[Dept1].[Department].[" + Parameters!pDept.Value + "] }*{[Curriculum].
[Curriculum].[" + Parameters!pCurr.Value + "] }*{ [Employee].[All Employee]
.children} } On ROWS, NON EMPTY {[Measures].[Curr Percent Complete]} ON
COLUMNS FROM [CertificationsDB]"
--
DataSet pCurr:
SELECT { [Measures].[Curr percent Complete]} ON Columns,NON EMPTY
[Curriculum].[Curriculum].Members ON ROWS FROM [CertificationsDB]
DataSet pDept:
SELECT { [Measures].[Curr percent Complete]} ON Columns,NON EMPTY [Dept1].
[Department].Members ON ROWS FROM [CertificationsDB]
DataSet pDiv:
SELECT { [Measures].[Curr percent Complete]} ON Columns,NON EMPTY [Geo1].
[Div].Members ON ROWS FROM [CertificationsDB]
Enjoy y'all
--
Message posted via http://www.sqlmonster.comsql