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 Manager

I've recently installed SQL Server 2005 Expression with Advanced Services. I'm exploring Report Server, deploying and executing reports, but I'm having some problems. If I hit a report in IE 7 using the URL naming scheme, everything is fine. If (on an ASP.NET page) I embed a local (RDLC) report in a ReportViewer control, all is well. If I link to a remote (RDL) report with ReportViewer, even though IIS, SQL Server Express, Report Server, and the user are on the same machine, I get an error:

The permissions granted to user 'SERVER1\ASPNET' are insufficient for performing this operation. (rsAccessDenied).

This is whether I'm using integrated security or anonymous for my web application. The ReportServer$SQLEXPRESS vdir is set at integrated security by default. The message states ASPNET either way, even when a LoginName control on the page confirms that it's using my actual login info. I've seen some places that show elaborate code-based ways to impersonate to get this working better, but it seems like it should just work without resorting to custom code.

I also keep reading about setting roles properly on the server, but I see no way whatsoever to assign users/roles to reports. I've seen several links about the Report Manager (http://msdn2.microsoft.com/en-us/library/ms365166.aspx and http://msdn2.microsoft.com/en-us/library/ms161561.aspx). Supposedly I can go to http://localhost/Reports$SQLEXPRESS (the links both state this), but I get nothing, and IIS doesn't show a vdir with the Reports$SQLEXPRESS name. Is this something that I need to install separately? I've installed on two different XP Pro boxes with the same results.

What am I doing wrong? Help would very much appreciated!

-Arian

Any thoughts anyone?

Please?

|||

Report Manager should be an optional installation option with your SQL Express with Advanced Services edition.

I can't remember the exact UI layout for this but you should be able to either:

1) go into Add/Remove programs and use the change option to open the Installation program for SQL Express with Advanced Services. You should see a Feature selection screen (or need to click an 'advanced' button) to open it. There you should be able to check the report manager.

Or if you're OK doing a reinstall, (I check this on my comptuer) -

Run the express w/ adv svcs installation again.

Uncheck the "Hide advanced configuration options" check box on the Resgistration Information dialog.

On the Feature Selection dialog, in the tree view, click "Reporting Services" and select "Entire feature will be installed on local hard drive". You should see the X disapear from the Report Manager feature.

Proceed with the installation and you should get report manager.

-Lukasz

|||

Right you are! I went back through setup and saw it nested under Report Server. Thanks for that!

Is that all I need to get rid of the ASPNET issue? Once I have Report Manager I just assign permissions to any user, including ASPNET, right?

|||

I don't know off the top of my head. I need to check with some other folks. In the meantime, assiging permissions to ASP.Net user will get you running. If you don't hear back from me in a day or so, just ping this thread and I'll see it in my inbox again :-).

-Lukasz