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 ;)