Design Question

I am just getting into SQL reporting services and I am looking into upgrading some Access reports with SQL reporting services. In the Access reports there was an interface that allowed the user to select some different options and then generate the report based on some of those options. To generate the report there was a large amount of vba code that created some collector tables that were then used by the report. My question is what would be the best way to recreate this functionality in reporting services. Would it be best to just have an external assembly that I called and passed in the parameters, this assembly would then create and a populate any tables on the sql server that could then be used by the report to display the data? If this is the case how would I go about connecting to the database? Or would it be better to create a custom data processing extension to do this? I have not looked into the data processing extensions much yet so I am not sure if it is even possible to do this.
Thanks for your suggestions
--
Message posted via http://www.sqlmonster.comI am assuming here that the data you are reporting against is in SQL Server.
The way you have solved it today in Access essentially allows you to have
your own temporary tables. Your recordsets in your VBA code acted as
temporary tables. I think you would be better off to create a stored
procedure than to go either of the paths you mention below. A custom data
processing extension is not easy. Doable but non-trivial. One point, when
creating a stored procedure don't try to do a port of your vba code, rewrite
it. Your VBA code is most likely cursor based (walking through recordsets).
When doing SP you should try to do set based logic. Fill in temp tables,
doing joins with the temp table etc. I once took 64 pages of C code and
turned it into 2 pages of a stored procedure. You can use cursors in SP but
that should be a last resort.
With the next beta of Widbey their will be winform and webform controls that
might make this easier.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason DeWeeese via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:6626b9ec55f6440c865863d7d4c984dd@.SQLMonster.com...
> I am just getting into SQL reporting services and I am looking into
upgrading some Access reports with SQL reporting services. In the Access
reports there was an interface that allowed the user to select some
different options and then generate the report based on some of those
options. To generate the report there was a large amount of vba code that
created some collector tables that were then used by the report. My question
is what would be the best way to recreate this functionality in reporting
services. Would it be best to just have an external assembly that I called
and passed in the parameters, this assembly would then create and a populate
any tables on the sql server that could then be used by the report to
display the data? If this is the case how would I go about connecting to the
database? Or would it be better to create a custom data processing extension
to do this? I have not looked into the data processing extensions much yet
so I am not sure if it is even possible to do this.
> Thanks for your suggestions
> --
> Message posted via http://www.sqlmonster.com|||Thanks for your suggestion, that is one other alternative that I had briefly considered, but had not really looked into. Thanks
--
Message posted via http://www.sqlmonster.com