Report Model missing some fields

I hope someone can clarify what I observe below.

When I add a certain Table into my report model, one of the fields is not automatically converted into an attribute, but I'm not sure what the exact pattern is.

This table has 3 fields as its key, two of them get included and one does not. The one that does not, is also added as a Role as it is used in a relationship within the DSV (Data Source View).

Does anyone know what rules BIS (Business Intelligence Studio) uses in deciding which fields to automatically convert using the wizard and which to skip?

Perhaps I'm doing something wrong, or there is a workaround?

If anyone can shed any light in the issue, I'd greatly appreciate their comment.

Thanks in advance and kindest regards

Craig

What is the datatype of the column that is not added? Text fields are not supported. Also, are you saying that the field IS added as a role? This is not clear.

-Carolyn [MSFT]

|||

Carolyn,

Thanks for your reply.

I hope will make what I'm trying to say slightly clearer.

CREATE TABLE [dbo].[MBB010](
[PRE_B01] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PARTNO] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACCOUNT15] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMMCODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HAZARD1] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
<...SNIP... (total of 77 fields) >
[ITSTACODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__MBB010__ITSTACOD__03681F15] DEFAULT (' '),
CONSTRAINT [MBB010_1] UNIQUE CLUSTERED
(
[PRE_B01] ASC,
[PARTNO] ASC,
[ACCOUNT15] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Indexes as follows

index_name index_description index_keys
-- --
MBB010_1 clustered, unique, unique key located on PRIMARY PRE_B01, PARTNO, ACCOUNT15
MBB010_2 nonclustered located on PRIMARY PRODGROUP, PRE_B01, PARTNO, ACCOUNT15
MBB010_3 nonclustered located on PRIMARY ACCOUNT15, HSYSCODE_ITEM

So PARTNO column points off to other tables within the DSV.

PRE_B01 and ACCOUNT15 get added by the wizard when I add this table to the report model, but PARTNO gets skipped. Only thing I can see different about this one field is that its included as a role.

I'm keen to understand and avoid having to manually edit the model to fix this as I have 500+ tables :-(

Thanks in advance

Craig

|||

Sorry to reply to me own question but I think I made a mistake. ACCOUNT15 does NOT get added either. So can someone clarify the rules that the SQL wizard (when adding a new table to a report model) follows?

Do all fields that become roles not get included (to end users) when they select this table in Report Builder?

|||

I too am struggling with the same problem. There are about 277 tables in my model and each table one or more such fields that a part of primary key go missing and appear as roles. So when I am trying to build a report I cannot find it under the parent table I have to go the related child table and pick it from there. This is not necessarily obvious to the end users of the model who are building reports.

It will be great to hear if anyone know how to work around this problem. It is not feasible to add all of the manually again.

|||

Sorry for not updating people.

I'm since got this working as you would expect (for new test fields I added into the model), by which I mean the "field" remains as an Attribute but is also created as a Role. I am giving SSRS the benefit of the doubt that I had corrupted the report model as I created the entire thing programmatically by reverse engineering the XML from other examples. Mind you, many times during this excersise Visual Studio would report the model as being unloadable or corrupt in some way (so I'm saying its validation is usually very good), but my current model definately loads without complaint.

The only related thing that I find annoying is that it renames the fields. So as I have many tables that link on PARTNO, the roles gets renamed PARTNO2, 3, 4, 5, etc. I guess this is because the underlying format is XML which is case-sensitive and so SSRS can't allow to "items" to have the same name.