| Author |
Topic |
|
spyork
Starting Member
6 Posts |
 Posted - 2005-08-19 : 12:32:58
|
| I have a report that queries a table in the database (using a stored procedure) and displays various things about a customer. The report is basically displaying the same information that one of the screens in our web app displays. The problem is that one of the fields that I need to display on the report is not stored in the database table it is calculated. The calculation is performed by doing some database calls and various other stuff. A method exists for this calculation in our data layer that I would like to call from the report. My question is what is the best way to go about doing this call? Can/Should I do this from a custom code assembly?Thanks...Spyork |
|
|
gcowhsu
Starting Member
38 Posts |
Posted - 2005-08-19 : 13:08:40
|
| I don't know if you can do that in the custom code because I don't know if you can import the required assemblies to execute the commands you need.You say that the calculation is made my using some database calls, so I think you should be able to join the table with the tables needed to make the calculation.You can also create a function that will run when you make your select statement so you would haveSelect a, b, c, doCalc(d) as d ...then you will be able to pull data from other tables.You said you have this so I would just put it in the Select statement |
 |
|
|
spyork
Starting Member
6 Posts |
Posted - 2005-08-19 : 13:25:57
|
| The problem is that it's a very complex calculation with lots of logic and other stuff that would be very difficult to implement using plain sql. Since this calculation could change it would be nice to be able to make a call to our common data layer assembly. Thanks... |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-19 : 13:44:09
|
| Spyork, so when you say "data layer" you're not talking about the database and stored procedures, but rather some compiled (.Net?) code? What sort of "various other stuff" besides the database calls does it do? Can you replicate that in your stored procedure?You can build a custom code assembly if it has to be .Net based, but there is a lot of effort that goes along with that, so I'd encourage you to give it some serious thought about doing it just in your stored procedure first.---------------------------EmeraldCityDomains.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-19 : 13:48:57
|
| We have 4 reports that are calling custom .NET assemblies. We went this route due to the complexity of the calculations being made. The web site already needed the assemblies created and T-SQL was not suitable for this, so we just referenced the assembly in our reports. One recommendation when you do this is to not version the assembly with each modification. If you do, you'll have to modify the report each time the assembly is updated, otherwise it'll still see the old version, which I believe is duet to the GAC. So we only modify the version when there are drastic changes made to it.Tara |
|
|
|
spyork
Starting Member
6 Posts |
Posted - 2005-08-19 : 13:57:56
|
| Sorry, i guess i should've explained myself a little more. By "data layer" I meant our business and persistence layer written in C# code. I guess why I was really wanting to make calls to our business/persistence layer was so that I wouldn't have to re-implement code that already existed. It would also be nice to share common code so as if it changes the change will be reflected everywhere. I'm used to writing reports in ActiveReports.NET and I guess I'm a little spoiled since all ActiveReports have code-behind classes just like .aspx pages. Anyway I can probably manage to re-implement the calculation in a stored procedure or function, but I was just trying to share code whenever possible. Thanks for the reply... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-19 : 14:01:33
|
| spyork,Another problem with using .NET assemblies in reports is that they need their own copy in a specific directory on the Report Server or on your client machine when in development. So you won't have to re-implement the code, but you will have to account for this during installation time.Tara |
|
|
|
spyork
Starting Member
6 Posts |
Posted - 2005-08-19 : 14:07:02
|
| tduggan,Currently I have a web app that references this same .NET assembly. If I want my RS reports to access this same assembly I will have to copy this assebly to the report server to the specified directy. Okay I understand this, but where do you store your connection string? For web apps you can put the connection string in the web.config file, but since dll files cannot have their own config file what do you do? Again thanks for the helpful info... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-19 : 15:17:15
|
| They opted to store it in the registry.Tara |
|
|
|
|
Data Retrieval Question...