report parameter with mdx

Hi,
I am running analysis services with SQL Server 2000. There I created a
cube which I am handling with reporting services. So far, no problem.
But now I also added some report parameter, which I would like to use
to define the dataset. But every time when I try to include a parameter
into the mdx-query I get an error-message, that named parameter are not
supported by OLE DB. I try to call a parameter by @.param_name
Is it possible to include reportparameter in mdx-queries?
If yes, how?
Thanksyes it is, show me your mdx query and i'll tell you how to do it.
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134637862.017069.135030@.o13g2000cwo.googlegroups.com...
> Hi,
> I am running analysis services with SQL Server 2000. There I created a
> cube which I am handling with reporting services. So far, no problem.
> But now I also added some report parameter, which I would like to use
> to define the dataset. But every time when I try to include a parameter
> into the mdx-query I get an error-message, that named parameter are not
> supported by OLE DB. I try to call a parameter by @.param_name
> Is it possible to include reportparameter in mdx-queries?
> If yes, how?
> Thanks
>|||Hi Greg,
thanks for your response.
Thats my current query:
WITH
MEMBER [Measures].[percentage] AS '[Measures].[Analysis_Count] /
([Measures].[Analysis_Count], [analysis].[Alle Analysis])',
FORMAT_STRING = 'Percent'
SELECT
{[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0),
{client.members} ON AXIS(1),
{area.members} ON AXIS(2),
{date_month.members} ON AXIS(3),
{analysis.members} ON AXIS(4)
FROM ISDBIv2
something like that I would like to add:
... WHERE ([client].[@.paramname])
Thanks|||Hi Greg,
thanks for your response.
Thats my current query:
WITH
MEMBER [Measures].[percentage] AS '[Measures].[Analysis_Count] /
([Measures].[Analysis_Count], [analysis].[Alle Analysis])',
FORMAT_STRING = 'Percent'
SELECT
{[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0),
{client.members} ON AXIS(1),
{area.members} ON AXIS(2),
{date_month.members} ON AXIS(3),
{analysis.members} ON AXIS(4)
FROM ISDBIv2
something like that I would like to add:
... WHERE ([client].[@.paramname])
Thanks|||ok, no problem. i assume client is a dimension so,
you do this
1. run the query without the parameter in so you get the fields
2. create a second dataset which just gets out the clients
select {[Measures].[Analysis_count]} on AXIS(0),
{[client].Members} on AXIS(1)
from FROM ISDBIv2
3. set up the parameter as you would normally, i.e. click Report -> Report
Parameters etc etc, give it a name 'paramname' and get it to get the clients
from that dataset
4. change the main query to
= "WITH " &
"MEMBER [Measures].[percentage]" & "AS " &
"'[Measures].[Analysis_Count] / ([Measures].[Analysis_Count],
[analysis].[Alle Analysis])', " &
"FORMAT_STRING = 'Percent' " &
"SELECT " &
"{[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0), "&
" {client.members} ON AXIS(1), " &
"{area.members} ON AXIS(2), " &
"{date_month.members} ON AXIS(3), " &
"{analysis.members} ON AXIS(4) " &
"FROM ISDBIv2 " &
"where ([client].[ " & Parameters!paramname.Value & "])"
that should do it, very long winded explanation, but thought i'd cover all
bases just incase. You will notice that you cannot run the query anymore
this is because of the = sign, but do a preview and it should work. Any
problems let me know, cos i've come accross quite a few random ones.
good luck Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134651740.902628.68600@.o13g2000cwo.googlegroups.com...
> Hi Greg,
> thanks for your response.
> Thats my current query:
> WITH
> MEMBER [Measures].[percentage] AS '[Measures].[Analysis_Count] /
> ([Measures].[Analysis_Count], [analysis].[Alle Analysis])',
> FORMAT_STRING = 'Percent'
> SELECT
> {[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0),
> {client.members} ON AXIS(1),
> {area.members} ON AXIS(2),
> {date_month.members} ON AXIS(3),
> {analysis.members} ON AXIS(4)
> FROM ISDBIv2
> something like that I would like to add:
> ... WHERE ([client].[@.paramname])
> Thanks
>|||Hi Greg,
thanks. that is the solution I were looking for. The first three steps
I had allready done. But the way to build the query by concatenating
the substrings was very useful.
But I got one big problem. Every time I try to do the preview I got an
error message:
"... double dimensions about (independent) axis - during calculating an
axis" (translated to english)
When I delete the " {client.members} ON AXIS(1), " &" - part the
preview is running. Unfortunatly I need this dimension in my matrix.
Do you have some ideas?
thanks|||I guess, I found the orginal english error message. It is:
"... duplicate dimensions accross independent axis..."|||sorry my fault, should've actually read the script.
Take the where clause out completely and put this in
"{[client].[ " & Parameters!paramname.Value & "]} ON AXIS(1), " &
instead of
" {client.members} ON AXIS(1), " &
The problem is that the original script was referring to the same dimension
twice. Sorry bout that,should work fine now. Let me know how it goes.
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134725728.305647.143860@.z14g2000cwz.googlegroups.com...
> Hi Greg,
> thanks. that is the solution I were looking for. The first three steps
> I had allready done. But the way to build the query by concatenating
> the substrings was very useful.
> But I got one big problem. Every time I try to do the preview I got an
> error message:
> "... double dimensions about (independent) axis - during calculating an
> axis" (translated to english)
> When I delete the " {client.members} ON AXIS(1), " &" - part the
> preview is running. Unfortunatly I need this dimension in my matrix.
> Do you have some ideas?
> thanks
>|||Yes, indeed it is working pretty fine now :-) But one last question.
What do I have do to filter data where "date_month.members" are older
than "startdate" and younger than "enddate". I know that is normal mdx.
but i havent done this before.
thanks|||From what you've said i assume you have parameters 'startdate' and
'enddate'. So you can do this by applying a filter to the main dataset.
click the '...' button next to the dataset title and click the filters tab.
In the expressions put date field in the expressions box should look
something like Fields!datename.value then put >= in operator box and put the
startdate parameter in the value box, you'll find it by clicking on the
expression option on the drop down from value. Then repeat for the end date
but put <= in the operator box.
That should do it. I can't off the top of my head think how to do it mdx
without referring to the date_month.members twice and we know that will
cause problems.
cheers
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134728307.957458.193420@.g44g2000cwa.googlegroups.com...
> Yes, indeed it is working pretty fine now :-) But one last question.
> What do I have do to filter data where "date_month.members" are older
> than "startdate" and younger than "enddate". I know that is normal mdx.
> but i havent done this before.
> thanks
>|||Hi Greg,
maybe you are interested in a solution with mdx. It is possible to
declare a MEMBER-Field defining a startdate and enddate.
SET [filter] AS '[date].[2005].[12].[1] : [date].[2005].[12].[19] '
Unfortunatly this method creates some other problems. First the named
dates "2005/12/01" and "2005/12/19" have to exist in your database.
Besides, if your time dimension contains weeks instead of month this
way is not possible, unless you are programming a function which is
calculating a week number of a date. So this way is not the best one.
I tried to solve my problem by using filters. But that creates some
problems too. First of all, my dimension is a time-dimension. So I dont
know how to call the entire date, because the dimension is divided in
day, month, year. In addition I set the type of my parameter (startdate
and enddate) to DateTime. But everytime I when I am calling the
preview, I get the errormessage, that the type of the parameter is not
correct. Switching the type to string, the filter does not work
correctly.
Do you have some ideas?
Thanks|||sorry could you just clarify how your dates are stored in your database? Is
it three different columns, one for day, month and year?
If that is the case you could concatenate them into a string of one date in
reporting services, or you could add a new column to your database with the
full date in...
Sorry if i've misinterpretted the issue here.
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134977793.241846.273900@.g44g2000cwa.googlegroups.com...
> Hi Greg,
> maybe you are interested in a solution with mdx. It is possible to
> declare a MEMBER-Field defining a startdate and enddate.
> SET [filter] AS '[date].[2005].[12].[1] : [date].[2005].[12].[19] '
> Unfortunatly this method creates some other problems. First the named
> dates "2005/12/01" and "2005/12/19" have to exist in your database.
> Besides, if your time dimension contains weeks instead of month this
> way is not possible, unless you are programming a function which is
> calculating a week number of a date. So this way is not the best one.
> I tried to solve my problem by using filters. But that creates some
> problems too. First of all, my dimension is a time-dimension. So I dont
> know how to call the entire date, because the dimension is divided in
> day, month, year. In addition I set the type of my parameter (startdate
> and enddate) to DateTime. But everytime I when I am calling the
> preview, I get the errormessage, that the type of the parameter is not
> correct. Switching the type to string, the filter does not work
> correctly.
> Do you have some ideas?
> Thanks
>