Report Object Model?

Hi
I am looking for an object model to learn how to access report items an
conections (if available?) in code. Because the code window on report
properties is THAT programmer-friendly I have no idea where to start
accessing the items.
Thanks.
MarkusThe code window you are refering is used for writing small functions to be
used inside the report something like converting Dollar to pounds etc.
If for using connection and report items or anyother things, I suggest use
the RS web services, there are good samples available in SS online help to
start with.
Amarnath, MCTS
"MarkusPoehler" wrote:
> Hi
> I am looking for an object model to learn how to access report items an
> conections (if available?) in code. Because the code window on report
> properties is THAT programmer-friendly I have no idea where to start
> accessing the items.
> Thanks.
> Markus

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 - Custom Code or Assembly Reference

Hi, I'm trying to create a new Report Model Project, but I have a problem. The problem is that I have a one custom column in the database table, lets say column's name is 'Test', with encoded data, so I need to add a new field to the generated model, lets say 'Test Decoded', and use a some method from my custom assembly to decode the data in the expression designer. I don't see how to use a method from my added custom assembly in the expression designer, also I was browsing Google many hours without an result. Also Data - > Add New Datasources -> Object throws an exception in the VS2005 (Report Model Project). Maybe someone can help me. Thanks

HI,Maryan:

Not possible as far as i know. The Report Model used for the Report Builder to generate ad-hoc report.

I don't think you could use custom assembly in the Report Model. I suggest the you to do the decode job in the SQL Server side.

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi Rex Lin, thank you for a reply. Exactly - I need to create a Models to enable my customers to create own specific reports by the Report Builder (nice feature of the SSRS 2005 from the first view). That is why we have selected this solution from Microsoft.

Actually this column is implementation of the Custom Fields of the some model, so in this column are decoded X custom columns, as result from the real column in the table - ColumnA, we need to create dynamic columns ColumnA1, ColumnA2, ColumnA3... based on the decoded content of the column ColumnA. So Model on the server side (SSRS) should contain all these dynamic columns (if some doesn't exist in the decoded column it will be Null). Unfortunaly we received this DB from the other team and we can't change the architecture of database, because there are real projects that already use it. It is possible to implement Custom Fields in the more accurate way, but it can't be a subject of discussion...

Ok, you wrote that it is not possible. But, do you know that if we will create Report Server Project, create some datasource based on a some query, then add Reference to the custom assembly, then we can add custom fields to the datasource and use methods from the custom assembly in the Expression and it works! It also requires to deploy (copy) custom assembly to the both folders in 'C:\Program Files\...', one for the Visual Studio and the next one for the SSRS 2005. So, the SSRS support it - it can execute a some set of the predefined functions in the Expression or to call a custom assembly without problems. So, why it doesn't work in the Report Model - I don't know. It is more - Report Model project contains context menu "New Assembly Reference..." in the solution explorer, also there is menu at the top Data->Add Datasource->Object Datasource (this is exactly what I need), but it always crashes after the first click on the Next button. I'm sure - this is a bug. An any report enginemust supports custom code - it is clear. Maybe development team already working on the Sql Server 2008, 2011... and do not have a time to complete existing services...

Please let me know if you have a some ideas, you have more detailed description of my problem now. Also maybe you are related to the development team and you can ask them directly ;) Please note, the database server uses Sql Server 2000, so we can not use a some features like Table-Valued Functions or Managed Code on the server side. Also a custom Data Processing Extension isn't good solution for me (theoretically it is possible to parse command query, and if it is related to the table with decoded column..., but I do not think it is good). Thank you again

|||

Also I have a new question. We moved our database to the SQL Server 2005. We can not use Table-Valued Function (TVF), because our origin table contains more than 200000 records, and TVF haven't access to the Select/Query Parameters (it can be much more powerful in this case), so to create a dynamic table with 200k records and then select 10-20 records for report will be really very bad idea. I created CLR based stored procedure and deployed it to the server, it works, but I don't know how to use it in the Report Model Project, because Data Source View asks only for tables and views... You suggested me to decode columns on the Server Side, my quesion is - how? I already have lost one week and I'm trying to to break an impasse.

|||

Ok, I found solution by myself. As usually it is not possible to receive an answer for non standard/simple question on these forums, even from Microsoft guys. If someone have the same problem - my solution is based on the one CLR based Table-Valued Function (TVF) that accept one parameter (ID of the row) and do all required custom logic, one additional Transact-SQL TVF that use first TVF and feature of the SQL server '... CROSS APPLY ...', actually it selects ID from the real DB table and pass it as the input parameter to the first TVF through CROSS APPLY, and one DB View that selects data from the second Transac-SQL TVF. In the model designer I used this view - it works ;)