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
>

Report parameter using Member_Key

I am trying to create a parameter driven SSRS report agains my cube but I want the parameter to contain only the Key value of the member I am trying to select. When I use the query designer, I get something like this:

SELECT ( STRTOSET(@.PlantStructureDepartment, CONSTRAINED)

Where @.PlantStructureDepartment is my paramter and contains the Unique Name of the member like this "[Plant Structure].[Department].&[39952]" where "39952" is the Key value.

What I want is to be able to pass the paramter in as "39952" and build the rest of the string. I tried this:

SELECT ( STRTOSET('[Plant Structure].[Department].&[' + @.PlantStructureDepartment + ']', CONSTRAINED)

but that didn't work.

The reason I'm trying to do this is that we are using SharePoint integrated filtering and we have a number of basic relational reports using the same filters as the reports against the cubes. We would like to keep them all using the integer values.

For sake of completeness, here is the full query as it sits right now:

SELECT NON EMPTY { [Measures].[FTT Percent] } ON COLUMNS, NON EMPTY { ([Plant Structure].[Prod Line].[Prod Line].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.PlantStructureDepartment, CONSTRAINED) ) ON COLUMNS FROM [FTT]) WHERE ( IIF( STRTOSET(@.PlantStructureDepartment, CONSTRAINED).Count = 1, STRTOSET(@.PlantStructureDepartment, CONSTRAINED), [Plant Structure].[Department].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I am fairly new to MDX and would appreciate any help or suggestions.

Here is a copy of the query modified to use Adventure Works but illustrating what you requested. Where I've used '1', you would use your report parameter value.

Hope that helps,
Bryan

Code Snippet

SELECT

NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS,

NON EMPTY { ([Product].[SubCategory].[SubCategory].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM (

SELECT ( STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED) ) ON COLUMNS

FROM [Adventure Works])

WHERE (

IIF( STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED).Count = 1,

STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED),

[Product].[Category].currentmember

)

)

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

Bryan,

Thanks for the reply. I think you are stating the same thing that I tried already. When I hardcode the value as you suggest, the query works. However, when I set up the parameter, I get this message:

Parser: The query contains the Department parameter, which is not declared. (msmgdsrv)

This is because the paramter isn't declared in the data tab of the report. When I declare the parameter, I get this error:

Query (6, 23) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated. (Microsoft SQL Server 2005 Analysis Services)

The parameters page of the Data tab only allows you to tie a parameter to a dimension of the cube. I think I need a way around that.

Thanks again for the idea. If you have another, I'd appreciate it.

Chris

|||

The CONSTRAINED keyword is there to prevent an injection of a function call into the statement. While I think this is a good idea, I get the same error as you do when no injection is occuring. While I would recommend digging into the MDX statement a little deeper to understand the issue, I have in the past simply removed the CONSTRAINED keyword.

Not sure this is the best of ideas, but .....

Bryan

|||

That's what it was.

Thanks!

|||

You have CONSTRAINED in several areas, do you need to remove them all. Can you post your modified code?

Thanks,

Shari

|||I don't have access to my query right at the moment (I'm at a different location) but suffice it to say, I removed all of the CONSTRAINED flags and it worked. To the point made earlier, I'm not sure if it was a good idea, but it got me around the issue I was having.|||

I tried that, removing all the CONSTRAINED but got another error about STRTOSET function expects a tuple set, string was supplied .... . I am anxious to see your query if you could share (when you have access).

Thanks,

Shari

Report Model Security Integration with SSAS 2005

Hi,

I have a question here for report model generated on top of SSAS 2005. If security has been defined in SSAS 2005 cube where RoleA only have access to certain dimension, is this security setting integrated with the report model and propogate down to the report builder when used where RoleA users have no access to dimension allowed?

Thanks,

J Lim

Unfortunately you're going to have to duplicate these security settings in the report model layer, too. You'll need to connect to Reporting Services inside Management Studio and edit the model item security on the report model in question from there. Sorry nobody answered your question months ago.sql

Report Model Security Integration with SSAS 2005

Hi,

I have a question here for report model generated on top of SSAS 2005. If security has been defined in SSAS 2005 cube where RoleA only have access to certain dimension, is this security setting integrated with the report model and propogate down to the report builder when used where RoleA users have no access to dimension allowed?

Thanks,

J Lim

Unfortunately you're going to have to duplicate these security settings in the report model layer, too. You'll need to connect to Reporting Services inside Management Studio and edit the model item security on the report model in question from there. Sorry nobody answered your question months ago.

Report Model On Top Of Analysis Services Cube

Hello, Creating a report model on top of a analysis services cube is very straight forward, but is it possible to modify that model such as adding folder structure?

Thanks

I am also interested in any information on modifying a Report Model created from SSAS datasource/cube.

Report Model On Top Of Analysis Services Cube

Hello, Creating a report model on top of a analysis services cube is very straight forward, but is it possible to modify that model such as adding folder structure?

Thanks

I am also interested in any information on modifying a Report Model created from SSAS datasource/cube.

Report Model Filtering

When creating a filter with Report Model against my cube (or perhaps any
source) - it seems to force me to free form type how the filter will work.
For example -
Attribute: Does_Client_Smoke
Values: Yes, No, N/A, Won't Answer, Cannot Answer
When I create the filter it makes me type in one of the above values instead
of letting me select it from a pull-down such as how BIDS works - what am I
doing wrong here?Can someone help me on this question below. When using the report viewer,
there is a BACK button but when using browser, it won't page back correctly.
I'm using latest IE beta - maybe that's my issue?
"Joe" <hortoristic@.gmail dot com> wrote in message
news:%23ZxRAzSzGHA.772@.TK2MSFTNGP05.phx.gbl...
> When creating a filter with Report Model against my cube (or perhaps any
> source) - it seems to force me to free form type how the filter will work.
> For example -
> Attribute: Does_Client_Smoke
> Values: Yes, No, N/A, Won't Answer, Cannot Answer
> When I create the filter it makes me type in one of the above values
> instead of letting me select it from a pull-down such as how BIDS works -
> what am I doing wrong here?
>

Report Model Against MSAS cubes

Hi!

Could anyone give me a detailed description of how to create a report model against an analysis services cube?

Thanks in advance!

http://msdn2.microsoft.com/en-us/library/ms345292.aspx

Report Model against Cube data source

We have successfully generated a Report Model against a Cube data source. My question is I can't seem to figure out how to modify the model. The wizard organized and named entities in an odd way - and even finding the measures is in some obscure place. I know how to create models in BIDS, but it doesn't seem to give me that same flexibility against a cube source.
unfortunately you can't edit it using VS.
maybe you can manually edit the XML file behind the model... but its a huge job...
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:7FE0118E-8DD0-4D77-B39A-024254A0C10E@.microsoft.com...
We have successfully generated a Report Model against a Cube data source. My question is I can't seem to figure out how to modify the model. The wizard organized and named entities in an odd way - and even finding the measures is in some obscure place. I know how to create models in BIDS, but it doesn't seem to give me that same flexibility against a cube source.

Report Model against Cube data source

We have successfully generated a Report Model against a Cube data source. M
y question is I can't seem to figure out how to modify the model. The wizar
d organized and named entities in an odd way - and even finding the measures
is in some obscure place. I know how to create models in BIDS, but it does
n't seem to give me that same flexibility against a cube source.unfortunately you can't edit it using VS.
maybe you can manually edit the XML file behind the model... but its a huge
job...
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:7FE0118E-8DD0-4D77-B
39A-024254A0C10E@.microsoft.com...
We have successfully generated a Report Model against a Cube data source. M
y question is I can't seem to figure out how to modify the model. The wizar
d organized and named entities in an odd way - and even finding the measures
is in some obscure place. I know how to create models in BIDS, but it does
n't seem to give me that same flexibility against a cube source.

Report in SSRS using cube in SQL 2000

I want to create reports in SSRS that use cube in SQL 2000, I don't know which type of connection that i have to use,

I tested it with Microsoft SQL Analysis Services, means I connect to my cube with this type , everything is ok in developpement (create MDX query , make reports...), but in production It gives me this error message (cannot connect to the datasource),

so I don't know how to solve this problem,

Thanks in advance for your help...,

Regards

Posting the full error message would help.

Posting it in the Reporting Services forum would help even more.

-Jamie

Report from SSAS with two different date column

Hi all,

I created a SSAS cube with the dimension "date" (including dates from 2006 to 2007) and "service numbers" (including service number where customer can call in) and a measure "number of calls".

The I created a report with the MDX builder showing a date as column (i.e. 02/01/2007), service numbers as rows and number of calls in the middle. Easy :-)

Now I will add a column with a date one year before (i.e. 02/01/2006) and a column showing the difference between the two dates as a percentage value.

How can I create this in the graphical builder?

Or do I have to create the MDX query manualy?

Thanks in advance!

some ASCII art:

2/1/07 2/1/06 diff.

-

service number 1 | 345 | 690 | 50%

service number 1 | 100 | 50 | 200%

...

Hi,

Do you have the data for the previous year's date in the query? For this scenario, it is must to create a calculated measure either in the query builder or in the cube which returns the value for the previous year's same date. You can use the LAG function to get this.

After getting both measures, you can directly give the percentage expression in the column "diff". No need for creating an MDX query for the percentage.