Do I need a data extension?

I'm working on a project for a national restaurant chain. I've built a
complex class that allows them to do various forms of sales estimations for
stores who fail to report their weekly sales. I say "complex" because it
has a detailed class hierarchy under it and performs lot of SQL Server work
inside its "black box." Using this class within a web page is working
great...
Now they want this functionality to also appear in a report, and I'm
wondering what I have to do -- never done a RS data extension and first am
wondering: Is this what I need to add this function to a report?
If the answer is "yes," is there a resource that describes in detail how to
do it, with a non-trivial example?
I'm really under the gun on this one, so I'll be grateful for any help --
thanks!
JoeI'd start with the sample reports that ship with the product. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_overview_v1_631v.asp?frame=true
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe Helmick" <joe_helmick@.4dv.net> wrote in message
news:O2r7tE5jEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I'm working on a project for a national restaurant chain. I've built a
> complex class that allows them to do various forms of sales estimations
for
> stores who fail to report their weekly sales. I say "complex" because it
> has a detailed class hierarchy under it and performs lot of SQL Server
work
> inside its "black box." Using this class within a web page is working
> great...
> Now they want this functionality to also appear in a report, and I'm
> wondering what I have to do -- never done a RS data extension and first am
> wondering: Is this what I need to add this function to a report?
> If the answer is "yes," is there a resource that describes in detail how
to
> do it, with a non-trivial example?
> I'm really under the gun on this one, so I'll be grateful for any help --
> thanks!
> Joe
>|||Joe,
One option in your scenario may be to dump your object data (state) in a
database before the report is run. Unfortunately, version 1.0 of Reporting
Services doesn't support events, so to implement preprocessing you need to
get somewhat innovative. One option is to attach an expression to a property
of the report body band (it will fire before the regions get loaded). This
expression could gather the report parameters, pass it to your object which
in turn can serialize itself to the database and let the regions pick data
from there. Of course, this is a rather simplified version of what you may
need to do.
Yes, another and more elegant option is to wrap you object in a custom data
extension. This is actually very straightforward. You need to expose your
object data as a forward-only tabular stream which conforms to the
IDataReader interface. You may find my ADO.NET custom data extension
(http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B846
8707-56EF-4864-AC51-D83FC3273FE5) useful to get you started. It comes with
step-by-step help instructions.
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Joe Helmick" <joe_helmick@.4dv.net> wrote in message
news:0l1aj0h4452vd72jles53foioiqfhfdphn@.4ax.com...
> Ravi,
> Thanks, but I have all the samples and unless I missed something due
> to my newbie-ness, I don't see what I need. I don't need drilldown or
> drillthrough, and simple functions in a code-behind don't seem like
> they'd nearly fill the bill. I'm already doing cascading parameters
> from multiple datasets and stuff like that, no problem, but attaching
> functionality from a 700-line VB class with deep inheritance? I don't
> see anything like that in the samples...
> Did I miss something, or do I need another resource besides the
> samples?
> Joe
> "Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> bellowed
> forth with this wisdom for all to hear:
> > I'd start with the sample reports that ship with the product. Check
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_overview_v1_631v.asp?frame=true
> > for details.
>sql

division in a view

In trying to simplify an overly complex query [at one time, 1000+ views and
14 queries run in query analyzer!], I am trying to incorporate many of the
different views/queries together but am having troubles with one thing;
trying to divide one of the columns in a view.
SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
Doesn't workPerhaps you can incorporate a CASE statement
select
case MLD1ST when 0 then 0
else
100 * (RCD1ST / MLD1ST)
end
from <your table>
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||Are you encountering a divide by zero error? Make sure that you code the
T-SQL in such a way as to not have any problems with a divide by zero error:
create table #foo (RCD1ST int, MLD1ST decimal(5,2))
insert into #foo values (5, 2)
insert into #foo values (10, 2)
insert into #foo values (15, 0)
insert into #foo values (20, null)
PRINT ''
PRINT '********************* this fails *********************'
SELECT *, 100 * (RCD1ST / MLD1ST) AS POR FROM #foo
go
PRINT ''
PRINT '********************* this works *********************'
SELECT *, CASE WHEN MLD1ST = 0 THEN NULL ELSE 100 * (RCD1ST / MLD1ST) END AS
POR FROM #foo
Keith
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||You can not reference a column alias in the same column list. Try:
SELECT
FIPS,
[MTG TYPE],
COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST,
AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
from ...
AMB
"notme" wrote:

> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Not really, depend if sql server decides to escalate the lock .
Example:
-- connection 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
where orderid = 10250
-- connection 2
use northwind
go
select * from orders
where orderid < 10250 or orderid > 10250
-- connection 1
rollback transaction
AMB
"notme" wrote:

> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Not really, depend if sql server decides to escalate the lock .
> Example:
> -- connection 1
> use northwind
> go
> begin transaction
> update orders
> set orderdate = orderdate
> where orderid = 10250
> -- connection 2
> use northwind
> go
> select * from orders
> where orderid < 10250 or orderid > 10250
> -- connection 1
> rollback transaction
>
> AMB
>
> "notme" wrote:
>|||On 3/23/2005 1:49:09 PM, "examnotes" wrote:
>You can not reference a column alias in the same column list. Try:
>SELECT
> FIPS,
> [MTG TYPE],
> COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST,
> AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
> AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
> 100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
>from ...
>
>AMB
>
>
That was it - thanks
jeff