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 Models using Islookup and expandinline

I am building and deploying models.

I have many small description tables, they include two attributes. On attribute is the key (which is a code or type) and the other is a varchar description. They tables are each optionalone related to the primary table. I am "denormalizing" the description tables using Islookup. I change the Role in the primary table to ISLOOKUP, I modify the defaultattributes to remove the code or type key attribute, only the description attribute is now a detailattribute. I hide the key attribute just in case.

In most cases when I do this the description tables are denormalized and the user now sees only the description in the primary table in report builder. But in a few cases the key attribute (which is normally a code - 1, 2, 4, 5, etc) is displayed, it displays even though it is Hidden, ISlookup is defined and I remove it from the default attributes.

I don't know why some work and some don't, the relationships seem to be identical in my view. Is there something in the model that could designate a table can be used as a ISLookup?

To attempt to work around this I have tried using expandinline.

Any ideas?

Report Builder "honors" the Entity.IsLookup property in the UI if and only if the cardinality of the role used to reach the entity is One/OptionalOne, and the lookup entity has exactly one attribute in its IdentifyingAttributes collection.

|||the entity lookup property is one/optional one and there is only one identifying attribute, this is exactly the issue I have, it does not seem to be consistent. And I cannot find any other differences in the tables to help me understand why this is happening.|||There is one other minor constraint - the one identifying attribute must actually belong to the lookup entity (it cannot come from a related entity). Is this the problem?|||No, it does not come from a related table. I used expandinline and it looks good, if I only have the one identifying attribute I seem to get the correct result, anything I should be aware of?|||

If you use ExpandInline, the user will not be able to navigate to the lookup entity using the Advanced Explorer mode in Report Builder. This is useful when creating reports that compare an item to other items with the same lookup value.

It also sounds like you may have confused the DefaultDetailAttributes and IdentifyingAttributes collections on the lookup entity. For an entity to be treated as a lookup, the IdentifyingAttributes collection must have exactly one attribute in it. The contents of DefaultDetailAttributes is irrelevant.

Report Models using Islookup and expandinline

I am building and deploying models.

I have many small description tables, they include two attributes. On attribute is the key (which is a code or type) and the other is a varchar description. They tables are each optionalone related to the primary table. I am "denormalizing" the description tables using Islookup. I change the Role in the primary table to ISLOOKUP, I modify the defaultattributes to remove the code or type key attribute, only the description attribute is now a detailattribute. I hide the key attribute just in case.

In most cases when I do this the description tables are denormalized and the user now sees only the description in the primary table in report builder. But in a few cases the key attribute (which is normally a code - 1, 2, 4, 5, etc) is displayed, it displays even though it is Hidden, ISlookup is defined and I remove it from the default attributes.

I don't know why some work and some don't, the relationships seem to be identical in my view. Is there something in the model that could designate a table can be used as a ISLookup?

To attempt to work around this I have tried using expandinline.

Any ideas?

Report Builder "honors" the Entity.IsLookup property in the UI if and only if the cardinality of the role used to reach the entity is One/OptionalOne, and the lookup entity has exactly one attribute in its IdentifyingAttributes collection.

|||the entity lookup property is one/optional one and there is only one identifying attribute, this is exactly the issue I have, it does not seem to be consistent. And I cannot find any other differences in the tables to help me understand why this is happening.|||There is one other minor constraint - the one identifying attribute must actually belong to the lookup entity (it cannot come from a related entity). Is this the problem?|||No, it does not come from a related table. I used expandinline and it looks good, if I only have the one identifying attribute I seem to get the correct result, anything I should be aware of?|||

If you use ExpandInline, the user will not be able to navigate to the lookup entity using the Advanced Explorer mode in Report Builder. This is useful when creating reports that compare an item to other items with the same lookup value.

It also sounds like you may have confused the DefaultDetailAttributes and IdentifyingAttributes collections on the lookup entity. For an entity to be treated as a lookup, the IdentifyingAttributes collection must have exactly one attribute in it. The contents of DefaultDetailAttributes is irrelevant.

Report Model on tables without primary key

I am trying to create a report model on some tables that do not have a
primary key. The wizard keeps failing with the message "Table does not have a
primary key". Is a primary key mandatory?You could define logic primary key in your dsv.
"Dan Varozza" wrote:
> I am trying to create a report model on some tables that do not have a
> primary key. The wizard keeps failing with the message "Table does not have a
> primary key". Is a primary key mandatory?|||WHen I run the DSV wizard it tells me that "no foreign keys were found. You
can create logical relationships on matching columns."
Then it gives me the choice to "Create logical relationships by matching
columns" and 3 Foreign Key Matches to choose from:
"Same as Primary key"
or
"Same name as destination table name"
or
"Destination table name + primary key name"
I have tried all 3 of the choices above and still the wizard fails with the
same error. "Table does not have a primary key".
Any ideas?
"yongli" wrote:
> You could define logic primary key in your dsv.
> "Dan Varozza" wrote:
> > I am trying to create a report model on some tables that do not have a
> > primary key. The wizard keeps failing with the message "Table does not have a
> > primary key". Is a primary key mandatory?sql