Hi All,
Not sure if this is the right group so feel free to point me in a better
direction.
Most of our customers have a personal price for every product but Im having
a nightmare trying to produce a price matrix on the fly. I currently have a
solution in MS Access 2K which uses a series of append and update queries to
create a temp table, this takes a little over 2 minutes to generate and is
becoming problematic. The idea is to have the sales office be able to give a
customer a price over the phone - a 2-3 minute wait isnt going to work.
I have 3 sources of prices
global : This contains a default price for every stock item it is the base
of all prices
price set(s): these contain a group of stock items from global at a
discounted price
personal product prices : A customer specific price for a specific stock
item
All prices can and do change and frequently and there are are also quantity
discount breaks for the same stock Item.
We carry approximately 3500 products and have about 4000 customers.
Im currently in the process or moving things from MS Access 2K over to MS
SQL Server 2K
I realise that its difficult to visualise without the database but if
someone could offer some pointers,
Im put off buliding 1 big flat table due to the size and the frequent
updates - am I right to avoid doing this?
Is it correct to construct the temp table and then running update/inserts -
Im having problems with the lack of a unique ID as Stock code is duplicated
for different quantity.
I can quickly generate a temp table containing all the information from each
source adding a field value for the process number (1,2,3 ) what Id like is
the record with the highest process number, but if I use a group by and max
as soon as I add price in the view I get all the records again.
I cant help but think I have something fundamentally wrong as its causing
such a problem. Can anyone point me at some info or share some insight.
TIA."Annon" <Ieatlotsofspam@.yum.yum> wrote in message
news:rNomf.5843$iz3.8@.text.news.blueyonder.co.uk...
> Hi All,
> Not sure if this is the right group so feel free to point me in a better
> direction.
> Most of our customers have a personal price for every product but Im
> having
> a nightmare trying to produce a price matrix on the fly. I currently have
> a
> solution in MS Access 2K which uses a series of append and update queries
> to
> create a temp table, this takes a little over 2 minutes to generate and is
> becoming problematic. The idea is to have the sales office be able to give
> a
> customer a price over the phone - a 2-3 minute wait isnt going to work.
> I have 3 sources of prices
> global : This contains a default price for every stock item it is the base
> of all prices
> price set(s): these contain a group of stock items from global at a
> discounted price
> personal product prices : A customer specific price for a specific stock
> item
> All prices can and do change and frequently and there are are also
> quantity
> discount breaks for the same stock Item.
> We carry approximately 3500 products and have about 4000 customers.
> Im currently in the process or moving things from MS Access 2K over to MS
> SQL Server 2K
> I realise that its difficult to visualise without the database but if
> someone could offer some pointers,
> Im put off buliding 1 big flat table due to the size and the frequent
> updates - am I right to avoid doing this?
> Is it correct to construct the temp table and then running
> update/inserts -
> Im having problems with the lack of a unique ID as Stock code is
> duplicated
> for different quantity.
> I can quickly generate a temp table containing all the information from
> each
> source adding a field value for the process number (1,2,3 ) what Id like
> is
> the record with the highest process number, but if I use a group by and
> max
> as soon as I add price in the view I get all the records again.
> I cant help but think I have something fundamentally wrong as its causing
> such a problem. Can anyone point me at some info or share some insight.
>
You'll need to post your table DDL, sample data and desired output for any
real information.
David|||which takes precedence? (i assume customer -> price set -> global)
how does a price set relate to the customer? (if it does)
if it doesn't, how do you know which to use? (assuming a stockno can be
in more than one price set)
what do you mean by a "process number"?
it would probably be best to avoid a big flat table in this case, but if
you make the source for this matrix a data warehouse, then that's
probably what you'll have.
DDL will certainly help get better answers...
Annon wrote:
> Hi All,
> Not sure if this is the right group so feel free to point me in a better
> direction.
> Most of our customers have a personal price for every product but Im havin
g
> a nightmare trying to produce a price matrix on the fly. I currently have
a
> solution in MS Access 2K which uses a series of append and update queries
to
> create a temp table, this takes a little over 2 minutes to generate and is
> becoming problematic. The idea is to have the sales office be able to give
a
> customer a price over the phone - a 2-3 minute wait isnt going to work.
> I have 3 sources of prices
> global : This contains a default price for every stock item it is the base
> of all prices
> price set(s): these contain a group of stock items from global at a
> discounted price
> personal product prices : A customer specific price for a specific stock
> item
> All prices can and do change and frequently and there are are also quantit
y
> discount breaks for the same stock Item.
> We carry approximately 3500 products and have about 4000 customers.
> Im currently in the process or moving things from MS Access 2K over to MS
> SQL Server 2K
> I realise that its difficult to visualise without the database but if
> someone could offer some pointers,
> Im put off buliding 1 big flat table due to the size and the frequent
> updates - am I right to avoid doing this?
> Is it correct to construct the temp table and then running update/inserts
-
> Im having problems with the lack of a unique ID as Stock code is duplicate
d
> for different quantity.
> I can quickly generate a temp table containing all the information from ea
ch
> source adding a field value for the process number (1,2,3 ) what Id like
is
> the record with the highest process number, but if I use a group by and ma
x
> as soon as I add price in the view I get all the records again.
> I cant help but think I have something fundamentally wrong as its causing
> such a problem. Can anyone point me at some info or share some insight.
> TIA.
>
>|||If you can't post the DDL, explain the rules.
ML
http://milambda.blogspot.com/|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:ODmZj5R$FHA.436@.TK2MSFTNGP10.phx.gbl...
> which takes precedence? (i assume customer -> price set -> global)
> how does a price set relate to the customer? (if it does)
> if it doesn't, how do you know which to use? (assuming a stockno can be in
> more than one price set)
> what do you mean by a "process number"?
> it would probably be best to avoid a big flat table in this case, but if
> you make the source for this matrix a data warehouse, then that's probably
> what you'll have.
> DDL will certainly help get better answers...
Hi Tery,
Your precedence is right, customer - price set then global, 1 to 3.
What im looking for,
Item Qty Price
Item1 1 2.25
Item1 10 1.5
Item2 10 1.9
Item3 100 4.99
Item4 1 2.2
The best I've come up with so far is to copy each stage (global, price set
then customers price) into a temp table with triggers to check if the
stock_Code and Qty match then update rather than append.
Any help suggestions welcome.
(SQL server is all really rather new to me still)
DDL hopefully below.
CREATE TABLE [dbo].[tbl_Customers] (
[ID] [numeric](18, 0) NOT NULL ,
[Company] [char] (10) NULL ,
[ID_Price_set_Global] [numeric](18, 0) NULL ,
[ID_Price_set_Main] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Matrix_Base] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Price_Set_ID] [numeric](18, 0) NULL ,
[Company_ID] [numeric](18, 0) NULL ,
[Stock_Code] [varchar] (10) NOT NULL ,
[Quantity] [numeric](18, 0) NOT NULL ,
[Price] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Price_Sets] (
[ID] [numeric](18, 0) NOT NULL ,
[Price_set_ID] [numeric](18, 0) NOT NULL ,
[Description] [varchar] (50) NULL
) ON [PRIMARY]
GO
insert into tbl_customers
([ID],Company,ID_price_set_global,ID_Pri
ce_set_main) values (1,'Test1',1,2)
Go
insert into tbl_price_sets ([ID],Price_set_ID,[Description]) values
(1,1,'Price set 1 Global')
insert into tbl_price_sets ([ID],Price_set_ID,[Description]) values
(2,2,'Price set 2 Band A')
Go
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item1',1,2.25)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item1',10,2)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item2',10,3)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item3',100,4.99)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item4',1,2.2)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item2',10,1.9)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(null,1,'Item1',10,1.5)
Go|||Annon (Ieatlotsofspam@.yum.yum) writes:
> Your precedence is right, customer - price set then global, 1 to 3.
> What im looking for,
> Item Qty Price
> Item1 1 2.25
> Item1 10 1.5
> Item2 10 1.9
> Item3 100 4.99
> Item4 1 2.2
> The best I've come up with so far is to copy each stage (global, price set
> then customers price) into a temp table with triggers to check if the
> stock_Code and Qty match then update rather than append.
Is that table structure you posted carved in stone? Or can you change it?
I started to compose a query from your repro, but I found it darn
difficult as tbl_Matrix_base is so scattered. And there were also
some thing I did not understand. What does those two ID_price_set in
Customers mean? And your sample data had:
> (Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
> (1,null,'Item2',10,3)
> (Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
> (1,null,'Item2',10,1.9)
How do I know which is which?
First of all, you need to keep track of which is which. To start with
you need a table to define sellable items:
CREATE TABLE sellable_items (stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
stdprice float NOT NULL,
CONSTRAINT pk_selleable_items PRIMARY KEY (stockcode, qty))
This table could just as well hold the standard price.
A price-set table would then look like this
CREATE TABLE priceset (pricesetid integer NOT NULL,
pricesetname varchar(30) NOT NULL,
CONSTRAINT pk_priceset PRIMARY KEY (pricesetid))
The actual prices in a price set would be in:
CREATE TABLE pricesetprices (pricesetid integer NOT NULL,
stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
price float NOT NULL,
CONSTRAINT pk_pricesetprices PRIMARY KEY
(pricesetid, stockcode, qty),
CONSTRAINT fk_prp_priceset FOREIGN KEY(pricesetid)
REFERENCES pricesets (pricesetid),
CONSTRAINT fk_prp_sellable FOREIGN KEY (stockcode, qty),
REFERENCES sellable_items (stockcode, qty)
)
Then then customer-specific prices:
CREATE TABLE customerprices (customerid integer NOT NULL,
stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
price float NOT NULL,
CONSTRAINT pk_customerprices PRIMARY KEY
(customerid, stockcode, qty),
CONSTRAINT fk_cup_customer FOREIGN KEY(customerid)
REFERENCES tbl_Customers (ID),
CONSTRAINT fk_cup_sellable FOREIGN KEY (stockcode, qty),
REFERENCES sellable_items (stockcode, qty)
)
Now to get all prices for a customer you can do:
SELECT s.stock_code, s.qty, price = coalesce(cup.price, prp.price,
s.price)
FROM sellable_items s
CROSS JOIN (SELECT * FROM tbl_Customers WHERE ID = @.custid) c
LEFT JOIN pricesetprices prp ON c.pricesetid = prp.pricesetid
AND s.stock_code = prp.stock_code
AND s.qty = prp.qty
LEFT JOIN customerprices cup ON c.customerid = cup.customerid
AND s.stock_code = cup.stock_code
AND s.qty = cup.qty
A very important thing to note here is the use of foreign keys, and
multi-column primary keys. A common mistake is to add an ID column to
each table and think you have a primary key. For basic concepts like
customers, this can be a good thing, but for composed entities like the
price plan is not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns972985B77D074Yazorman@.127.0.0.1...
> Annon (Ieatlotsofspam@.yum.yum) writes:
<SNIP elegant solution>
> Now to get all prices for a customer you can do:
> SELECT s.stock_code, s.qty, price = coalesce(cup.price, prp.price,
> s.price)
> FROM sellable_items s
> CROSS JOIN (SELECT * FROM tbl_Customers WHERE ID = @.custid) c
> LEFT JOIN pricesetprices prp ON c.pricesetid = prp.pricesetid
> AND s.stock_code = prp.stock_code
> AND s.qty = prp.qty
> LEFT JOIN customerprices cup ON c.customerid = cup.customerid
> AND s.stock_code = cup.stock_code
> AND s.qty = cup.qty
> A very important thing to note here is the use of foreign keys, and
> multi-column primary keys. A common mistake is to add an ID column to
> each table and think you have a primary key. For basic concepts like
> customers, this can be a good thing, but for composed entities like the
> price plan is not.
>
Thanks for the solution Erland, unfortunatly as is always the way - Im stuck
with backward compatibility I cant rip out the tables and replace them with
the correct design. As you point out the problem is not know what is what
and from where it came. All the data is held in 1 table the tbl_Matrix_base,
customer, price set and global all lumped together. As time was pressing I
kept the temp table created a compound key field from Company & Stock_Code &
Qty , created a unqiue index on it but with ingnore_dup_key on. and ran the
3 insert queries. It adds new records but fails to add duplicates - as long
as I keep the insert order correct it gives me the results I need.
Its far from perfect - but its bought me more time, I'll take apart your
example and see if I can work it back into the live system.
Thanks again for the help.
Showing posts with label design. Show all posts
Showing posts with label design. Show all posts
Sunday, March 25, 2012
Customer Accounts Report
I am on security doubt on how to design a report that must show the accounts
of a customer.
I have an aspx page showing the customer info , then he must push a button a
see its accounts on a report generated by Reporting Services.
Obviously the report can not be accesed via a Customer parameters because
another users could see any customer 's accounts.
How can i solve it.
I am thinking in generating a temp table with a random id an pass it by
parameter, but it is only so as to reduce an intruder access.
ThanksHi Fernando,
If you are using ASP.NET you have more than one option:
1.- Storing in Sessions variables
2.- Using QueryString
3.- Using Server side code (static variables)
4.- Change the report viewer (passsing SOAP params)
The fact is the report params does not to be part of the URL you can easily
hide them using multiples techniques, depends how your app is designed you
may use some of them.
Thanks
Jerry
--
You can use this example to show you how to call the report params.
http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
"Fernando Hunth" wrote:
> I am on security doubt on how to design a report that must show the accounts
> of a customer.
> I have an aspx page showing the customer info , then he must push a button a
> see its accounts on a report generated by Reporting Services.
> Obviously the report can not be accesed via a Customer parameters because
> another users could see any customer 's accounts.
> How can i solve it.
> I am thinking in generating a temp table with a random id an pass it by
> parameter, but it is only so as to reduce an intruder access.
> Thanks|||Thats true we have multiples techniques to pass parameters but not to avoid
access to those public parameters.
When a report is deployed , anyone (authenticated) can access to the report
and can access a report via URL and with the posibility of changing any
parameter value via URL.
That 's what i want to avoid. I am trying to investigate doing it with a
DataProcessingExtension
Thanks.
"Jerry" wrote:
> Hi Fernando,
> If you are using ASP.NET you have more than one option:
> 1.- Storing in Sessions variables
> 2.- Using QueryString
> 3.- Using Server side code (static variables)
> 4.- Change the report viewer (passsing SOAP params)
> The fact is the report params does not to be part of the URL you can easily
> hide them using multiples techniques, depends how your app is designed you
> may use some of them.
> Thanks
> Jerry
> --
> You can use this example to show you how to call the report params.
> http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> "Fernando Hunth" wrote:
> > I am on security doubt on how to design a report that must show the accounts
> > of a customer.
> > I have an aspx page showing the customer info , then he must push a button a
> > see its accounts on a report generated by Reporting Services.
> > Obviously the report can not be accesed via a Customer parameters because
> > another users could see any customer 's accounts.
> > How can i solve it.
> >
> > I am thinking in generating a temp table with a random id an pass it by
> > parameter, but it is only so as to reduce an intruder access.
> >
> > Thanks|||Check out this link:
http://www.codeproject.com/useritems/SQLRSViewer.asp#TheCodeBehind
It's an ASP.NET app that lets you recreate the entire look of the report
viewer. It also give you flexibilty in what you feed the report.
I was able to use this program and feed in my own parameters. The report
will display and no parameters are visible in the URL.
Look for the variable 'reportParameterValues2' in the code. It's a array
that stores the parameters for your report.
Hope this helps, or at least gets you started.
"Fernando Hunth" wrote:
> Thats true we have multiples techniques to pass parameters but not to avoid
> access to those public parameters.
> When a report is deployed , anyone (authenticated) can access to the report
> and can access a report via URL and with the posibility of changing any
> parameter value via URL.
> That 's what i want to avoid. I am trying to investigate doing it with a
> DataProcessingExtension
> Thanks.
>
> "Jerry" wrote:
> > Hi Fernando,
> >
> > If you are using ASP.NET you have more than one option:
> >
> > 1.- Storing in Sessions variables
> > 2.- Using QueryString
> > 3.- Using Server side code (static variables)
> > 4.- Change the report viewer (passsing SOAP params)
> >
> > The fact is the report params does not to be part of the URL you can easily
> > hide them using multiples techniques, depends how your app is designed you
> > may use some of them.
> >
> > Thanks
> > Jerry
> > --
> > You can use this example to show you how to call the report params.
> > http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> >
> > "Fernando Hunth" wrote:
> >
> > > I am on security doubt on how to design a report that must show the accounts
> > > of a customer.
> > > I have an aspx page showing the customer info , then he must push a button a
> > > see its accounts on a report generated by Reporting Services.
> > > Obviously the report can not be accesed via a Customer parameters because
> > > another users could see any customer 's accounts.
> > > How can i solve it.
> > >
> > > I am thinking in generating a temp table with a random id an pass it by
> > > parameter, but it is only so as to reduce an intruder access.
> > >
> > > Thanks|||Yes, i previously took a look a this project, but that is another client
solution wher you can pass parameters to a public report.
"Hausbro" wrote:
> Check out this link:
> http://www.codeproject.com/useritems/SQLRSViewer.asp#TheCodeBehind
> It's an ASP.NET app that lets you recreate the entire look of the report
> viewer. It also give you flexibilty in what you feed the report.
> I was able to use this program and feed in my own parameters. The report
> will display and no parameters are visible in the URL.
> Look for the variable 'reportParameterValues2' in the code. It's a array
> that stores the parameters for your report.
> Hope this helps, or at least gets you started.
> "Fernando Hunth" wrote:
> > Thats true we have multiples techniques to pass parameters but not to avoid
> > access to those public parameters.
> > When a report is deployed , anyone (authenticated) can access to the report
> > and can access a report via URL and with the posibility of changing any
> > parameter value via URL.
> > That 's what i want to avoid. I am trying to investigate doing it with a
> > DataProcessingExtension
> >
> > Thanks.
> >
> >
> >
> > "Jerry" wrote:
> >
> > > Hi Fernando,
> > >
> > > If you are using ASP.NET you have more than one option:
> > >
> > > 1.- Storing in Sessions variables
> > > 2.- Using QueryString
> > > 3.- Using Server side code (static variables)
> > > 4.- Change the report viewer (passsing SOAP params)
> > >
> > > The fact is the report params does not to be part of the URL you can easily
> > > hide them using multiples techniques, depends how your app is designed you
> > > may use some of them.
> > >
> > > Thanks
> > > Jerry
> > > --
> > > You can use this example to show you how to call the report params.
> > > http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> > >
> > > "Fernando Hunth" wrote:
> > >
> > > > I am on security doubt on how to design a report that must show the accounts
> > > > of a customer.
> > > > I have an aspx page showing the customer info , then he must push a button a
> > > > see its accounts on a report generated by Reporting Services.
> > > > Obviously the report can not be accesed via a Customer parameters because
> > > > another users could see any customer 's accounts.
> > > > How can i solve it.
> > > >
> > > > I am thinking in generating a temp table with a random id an pass it by
> > > > parameter, but it is only so as to reduce an intruder access.
> > > >
> > > > Thanks|||Ok.
I decided to write some sample code for the solution i was thinking about
generating a temp table with a random id an pass it by
parameter, or something like this.
I 'll publish it in somewhere.
I 'll go on writing on my blog
http://msdevelopers.blogspot.com/2005/03/reporting-services-filtering.html
Fernando Hunth
Senior Developer
Huddle Group S.A.
fernando@.huddle.com.ar
Huddle Group S.A. | Enterprise Technology Services
Microsoft Certified Partner
Ciudad de Buenos Aires · Argentina
www.huddle.com.ar
"Fernando Hunth" wrote:
> I am on security doubt on how to design a report that must show the accounts
> of a customer.
> I have an aspx page showing the customer info , then he must push a button a
> see its accounts on a report generated by Reporting Services.
> Obviously the report can not be accesed via a Customer parameters because
> another users could see any customer 's accounts.
> How can i solve it.
> I am thinking in generating a temp table with a random id an pass it by
> parameter, but it is only so as to reduce an intruder access.
> Thanks
of a customer.
I have an aspx page showing the customer info , then he must push a button a
see its accounts on a report generated by Reporting Services.
Obviously the report can not be accesed via a Customer parameters because
another users could see any customer 's accounts.
How can i solve it.
I am thinking in generating a temp table with a random id an pass it by
parameter, but it is only so as to reduce an intruder access.
ThanksHi Fernando,
If you are using ASP.NET you have more than one option:
1.- Storing in Sessions variables
2.- Using QueryString
3.- Using Server side code (static variables)
4.- Change the report viewer (passsing SOAP params)
The fact is the report params does not to be part of the URL you can easily
hide them using multiples techniques, depends how your app is designed you
may use some of them.
Thanks
Jerry
--
You can use this example to show you how to call the report params.
http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
"Fernando Hunth" wrote:
> I am on security doubt on how to design a report that must show the accounts
> of a customer.
> I have an aspx page showing the customer info , then he must push a button a
> see its accounts on a report generated by Reporting Services.
> Obviously the report can not be accesed via a Customer parameters because
> another users could see any customer 's accounts.
> How can i solve it.
> I am thinking in generating a temp table with a random id an pass it by
> parameter, but it is only so as to reduce an intruder access.
> Thanks|||Thats true we have multiples techniques to pass parameters but not to avoid
access to those public parameters.
When a report is deployed , anyone (authenticated) can access to the report
and can access a report via URL and with the posibility of changing any
parameter value via URL.
That 's what i want to avoid. I am trying to investigate doing it with a
DataProcessingExtension
Thanks.
"Jerry" wrote:
> Hi Fernando,
> If you are using ASP.NET you have more than one option:
> 1.- Storing in Sessions variables
> 2.- Using QueryString
> 3.- Using Server side code (static variables)
> 4.- Change the report viewer (passsing SOAP params)
> The fact is the report params does not to be part of the URL you can easily
> hide them using multiples techniques, depends how your app is designed you
> may use some of them.
> Thanks
> Jerry
> --
> You can use this example to show you how to call the report params.
> http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> "Fernando Hunth" wrote:
> > I am on security doubt on how to design a report that must show the accounts
> > of a customer.
> > I have an aspx page showing the customer info , then he must push a button a
> > see its accounts on a report generated by Reporting Services.
> > Obviously the report can not be accesed via a Customer parameters because
> > another users could see any customer 's accounts.
> > How can i solve it.
> >
> > I am thinking in generating a temp table with a random id an pass it by
> > parameter, but it is only so as to reduce an intruder access.
> >
> > Thanks|||Check out this link:
http://www.codeproject.com/useritems/SQLRSViewer.asp#TheCodeBehind
It's an ASP.NET app that lets you recreate the entire look of the report
viewer. It also give you flexibilty in what you feed the report.
I was able to use this program and feed in my own parameters. The report
will display and no parameters are visible in the URL.
Look for the variable 'reportParameterValues2' in the code. It's a array
that stores the parameters for your report.
Hope this helps, or at least gets you started.
"Fernando Hunth" wrote:
> Thats true we have multiples techniques to pass parameters but not to avoid
> access to those public parameters.
> When a report is deployed , anyone (authenticated) can access to the report
> and can access a report via URL and with the posibility of changing any
> parameter value via URL.
> That 's what i want to avoid. I am trying to investigate doing it with a
> DataProcessingExtension
> Thanks.
>
> "Jerry" wrote:
> > Hi Fernando,
> >
> > If you are using ASP.NET you have more than one option:
> >
> > 1.- Storing in Sessions variables
> > 2.- Using QueryString
> > 3.- Using Server side code (static variables)
> > 4.- Change the report viewer (passsing SOAP params)
> >
> > The fact is the report params does not to be part of the URL you can easily
> > hide them using multiples techniques, depends how your app is designed you
> > may use some of them.
> >
> > Thanks
> > Jerry
> > --
> > You can use this example to show you how to call the report params.
> > http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> >
> > "Fernando Hunth" wrote:
> >
> > > I am on security doubt on how to design a report that must show the accounts
> > > of a customer.
> > > I have an aspx page showing the customer info , then he must push a button a
> > > see its accounts on a report generated by Reporting Services.
> > > Obviously the report can not be accesed via a Customer parameters because
> > > another users could see any customer 's accounts.
> > > How can i solve it.
> > >
> > > I am thinking in generating a temp table with a random id an pass it by
> > > parameter, but it is only so as to reduce an intruder access.
> > >
> > > Thanks|||Yes, i previously took a look a this project, but that is another client
solution wher you can pass parameters to a public report.
"Hausbro" wrote:
> Check out this link:
> http://www.codeproject.com/useritems/SQLRSViewer.asp#TheCodeBehind
> It's an ASP.NET app that lets you recreate the entire look of the report
> viewer. It also give you flexibilty in what you feed the report.
> I was able to use this program and feed in my own parameters. The report
> will display and no parameters are visible in the URL.
> Look for the variable 'reportParameterValues2' in the code. It's a array
> that stores the parameters for your report.
> Hope this helps, or at least gets you started.
> "Fernando Hunth" wrote:
> > Thats true we have multiples techniques to pass parameters but not to avoid
> > access to those public parameters.
> > When a report is deployed , anyone (authenticated) can access to the report
> > and can access a report via URL and with the posibility of changing any
> > parameter value via URL.
> > That 's what i want to avoid. I am trying to investigate doing it with a
> > DataProcessingExtension
> >
> > Thanks.
> >
> >
> >
> > "Jerry" wrote:
> >
> > > Hi Fernando,
> > >
> > > If you are using ASP.NET you have more than one option:
> > >
> > > 1.- Storing in Sessions variables
> > > 2.- Using QueryString
> > > 3.- Using Server side code (static variables)
> > > 4.- Change the report viewer (passsing SOAP params)
> > >
> > > The fact is the report params does not to be part of the URL you can easily
> > > hide them using multiples techniques, depends how your app is designed you
> > > may use some of them.
> > >
> > > Thanks
> > > Jerry
> > > --
> > > You can use this example to show you how to call the report params.
> > > http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> > >
> > > "Fernando Hunth" wrote:
> > >
> > > > I am on security doubt on how to design a report that must show the accounts
> > > > of a customer.
> > > > I have an aspx page showing the customer info , then he must push a button a
> > > > see its accounts on a report generated by Reporting Services.
> > > > Obviously the report can not be accesed via a Customer parameters because
> > > > another users could see any customer 's accounts.
> > > > How can i solve it.
> > > >
> > > > I am thinking in generating a temp table with a random id an pass it by
> > > > parameter, but it is only so as to reduce an intruder access.
> > > >
> > > > Thanks|||Ok.
I decided to write some sample code for the solution i was thinking about
generating a temp table with a random id an pass it by
parameter, or something like this.
I 'll publish it in somewhere.
I 'll go on writing on my blog
http://msdevelopers.blogspot.com/2005/03/reporting-services-filtering.html
Fernando Hunth
Senior Developer
Huddle Group S.A.
fernando@.huddle.com.ar
Huddle Group S.A. | Enterprise Technology Services
Microsoft Certified Partner
Ciudad de Buenos Aires · Argentina
www.huddle.com.ar
"Fernando Hunth" wrote:
> I am on security doubt on how to design a report that must show the accounts
> of a customer.
> I have an aspx page showing the customer info , then he must push a button a
> see its accounts on a report generated by Reporting Services.
> Obviously the report can not be accesed via a Customer parameters because
> another users could see any customer 's accounts.
> How can i solve it.
> I am thinking in generating a temp table with a random id an pass it by
> parameter, but it is only so as to reduce an intruder access.
> Thanks
Saturday, February 25, 2012
Custom ControlFlow
I am trying to design a Component which can be configured by a UI. After Configuration from the Design pane the Data should be passed to the back-end of the Component.
When I look at some code samples I only see, that it's possible to pass simple data.
What I want is to put a grid on the UI and connect it to a DataTable. This is used for displaying and changing a configuration as a table.
Well, the question is: Can I pass the DataTable object to the backend-DLL as is? If so then how?
THX
Fridtjof
Can you please clarify whether you are creating a custom Task or a custom Data Flow Component, or another type of run-time object like a connection manager? The APIs are similar but not identical.
Your code is from the Class which inherits from IDtsTaskUI.
The question is how can you access public members of my Backend-Class in the UI (Form).
Maybe I have to convert and cache then, because the ui is called at design-time and the Backend at runtime?
|||Your form should be invoked from the UI class (IDtsTaskUI). The sample I showed demonstrates grabbing TaskHost, and passing it through to the form. You use the TaskHost to access properies of the task itself. You cannot get direct access to the Task instance, but you should not actually need to as you have TaskHost instead.
If you have code that you wish to share between trasjk and UI classes, then make them public static. Instance members would not work anyway since you cannot get the instance. public static string SerializeObject(object graph) { BinaryFormatter formatter1 = new BinaryFormatter(); MemoryStream stream1 = new MemoryStream(); formatter1.Serialize(stream1, graph); string text1 = Convert.ToBase64String(stream1.GetBuffer()); stream1.Close(); return text1; } public static XmlElement AddXmlObjectProperties(XmlElement parentElement, string propertyName, object propertyObject) { XmlElement element1 = XMLPersistanceHelper.AddXmlElement(parentElement, propertyName); Type type1 = propertyObject.GetType(); foreach (PropertyInfo info1 in type1.GetProperties()) { Type type2 = info1.PropertyType; string text1 = type2.Name; object obj1 = info1.GetValue(propertyObject, null); if (type2.IsEnum) { obj1 = Convert.ToInt32(info1.GetValue(propertyObject, null)); } if (obj1 != null) { XmlElement element2 = XMLPersistanceHelper.AddXmlElement(element1, "Property", obj1.ToString()); XMLPersistanceHelper.AddXmlAttribute(element2, "Name", info1.Name); XMLPersistanceHelper.AddXmlAttribute(element2, "Type", text1); } } return element1; }
When I look at some code samples I only see, that it's possible to pass simple data.
What I want is to put a grid on the UI and connect it to a DataTable. This is used for displaying and changing a configuration as a table.
Well, the question is: Can I pass the DataTable object to the backend-DLL as is? If so then how?
THX
Fridtjof
Can you please clarify whether you are creating a custom Task or a custom Data Flow Component, or another type of run-time object like a connection manager? The APIs are similar but not identical.
-Doug
|||From your subect I assume you mean a Task. Why can you not just create a property of your task, typed as a DataTable?|||Well I don't have any instance of the Backend Class in the Definition of the UI-Class, have I?
If I had one there would be no problem setting the DataSource of the Grid to that DataTable.
|||
You keep referring to the backend-class, do you mean the task itself, the class that inherits from Task?
If so you should have a reference to it in the UI. You need to cache it and pass it through, but otherwise there is not much point to the UI.
Review this code form one of my tasks. This is the UI class, the one that inherits from IDtsTaskUI. My form is ZipFileTaskUIForm, and has a modified constructor to accept the parameters passed through. You can see the TaskHost is cached in Initialize, and then passed to the form. This is how you get a reference to your task into the form.
public System.Windows.Forms.ContainerControl GetView()
{
return new ZipFileTaskUIForm(_taskHost, _dtsConnectionService, _dtsVariableService);
}
public void Initialize(TaskHost taskHost, IServiceProvider serviceProvider)
{
_taskHost = taskHost;
IDtsVariableService dtsVariableService = serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;
IDtsConnectionService dtsConnectionService = serviceProvider.GetService(typeof(IDtsConnectionService)) as IDtsConnectionService;
_dtsConnectionService = dtsConnectionService;
_dtsVariableService = dtsVariableService;
}
Your code is from the Class which inherits from IDtsTaskUI.
The question is how can you access public members of my Backend-Class in the UI (Form).
Maybe I have to convert and cache then, because the ui is called at design-time and the Backend at runtime?
|||Your form should be invoked from the UI class (IDtsTaskUI). The sample I showed demonstrates grabbing TaskHost, and passing it through to the form. You use the TaskHost to access properies of the task itself. You cannot get direct access to the Task instance, but you should not actually need to as you have TaskHost instead.
If you have code that you wish to share between trasjk and UI classes, then make them public static. Instance members would not work anyway since you cannot get the instance.
|||In addition to the Initialize method in the UI class that comes from the IDtsXxxUI interface, I would be inclined to write an Initialize method of my own in my Windows Forms class and pass along a reference to my task or other object, so I can easily read its current property values to set initial control states and values, then update its properties from the controls when the user closes the form.
So Task passes reference to itself in calling Initialize in Task UI class, and Task UI class passes reference to task in calling custom Initialize method in Task UI Form class.
-Doug
|||
I am also facing same problem. When i explored i came to know that there was two method SaveToXML() and LoadXML() of IDTSPersist90 interface to handle this type of scenerio. But i could not able to raise these methods. you can transfer either string or interger...etc, but if you want to transfer some custom object i think you have to preserve the same in xml and retrieve it when ever needed
...Karun
|||As I understand the issue, bearing in mind this thread crossed several, none of which matched the title that well, but you are writing a task and have a complex type that you need to persist. All persistance is through XML, and for complex stuff you need to implement IDTSPersist90 yourself. The methods are never called by you, they are called by the designer. You fill in the code to save and load the values to and from the XML document provided, and you can choose how you store the information in the XML.
For objects, I found the easiest way to to serialize them, and actually binary serialization at that. This snippet gives me a string that I can easily store in an xml element for the property.
Another alternative is to use reflection to read an object's properties, and then build up an XML node that represents this. Here is an example of creating a new element with sub elements for each property-
Custom ControlFlow
I am trying to design a Component which can be configured by a UI. After Configuration from the Design pane the Data should be passed to the back-end of the Component.
When I look at some code samples I only see, that it's possible to pass simple data.
What I want is to put a grid on the UI and connect it to a DataTable. This is used for displaying and changing a configuration as a table.
Well, the question is: Can I pass the DataTable object to the backend-DLL as is? If so then how?
THX
Fridtjof
Can you please clarify whether you are creating a custom Task or a custom Data Flow Component, or another type of run-time object like a connection manager? The APIs are similar but not identical.
Your code is from the Class which inherits from IDtsTaskUI.
The question is how can you access public members of my Backend-Class in the UI (Form).
Maybe I have to convert and cache then, because the ui is called at design-time and the Backend at runtime?
|||Your form should be invoked from the UI class (IDtsTaskUI). The sample I showed demonstrates grabbing TaskHost, and passing it through to the form. You use the TaskHost to access properies of the task itself. You cannot get direct access to the Task instance, but you should not actually need to as you have TaskHost instead.
If you have code that you wish to share between trasjk and UI classes, then make them public static. Instance members would not work anyway since you cannot get the instance. public static string SerializeObject(object graph) { BinaryFormatter formatter1 = new BinaryFormatter(); MemoryStream stream1 = new MemoryStream(); formatter1.Serialize(stream1, graph); string text1 = Convert.ToBase64String(stream1.GetBuffer()); stream1.Close(); return text1; } public static XmlElement AddXmlObjectProperties(XmlElement parentElement, string propertyName, object propertyObject) { XmlElement element1 = XMLPersistanceHelper.AddXmlElement(parentElement, propertyName); Type type1 = propertyObject.GetType(); foreach (PropertyInfo info1 in type1.GetProperties()) { Type type2 = info1.PropertyType; string text1 = type2.Name; object obj1 = info1.GetValue(propertyObject, null); if (type2.IsEnum) { obj1 = Convert.ToInt32(info1.GetValue(propertyObject, null)); } if (obj1 != null) { XmlElement element2 = XMLPersistanceHelper.AddXmlElement(element1, "Property", obj1.ToString()); XMLPersistanceHelper.AddXmlAttribute(element2, "Name", info1.Name); XMLPersistanceHelper.AddXmlAttribute(element2, "Type", text1); } } return element1; }
When I look at some code samples I only see, that it's possible to pass simple data.
What I want is to put a grid on the UI and connect it to a DataTable. This is used for displaying and changing a configuration as a table.
Well, the question is: Can I pass the DataTable object to the backend-DLL as is? If so then how?
THX
Fridtjof
Can you please clarify whether you are creating a custom Task or a custom Data Flow Component, or another type of run-time object like a connection manager? The APIs are similar but not identical.
-Doug
|||From your subect I assume you mean a Task. Why can you not just create a property of your task, typed as a DataTable?|||Well I don't have any instance of the Backend Class in the Definition of the UI-Class, have I?
If I had one there would be no problem setting the DataSource of the Grid to that DataTable.
|||
You keep referring to the backend-class, do you mean the task itself, the class that inherits from Task?
If so you should have a reference to it in the UI. You need to cache it and pass it through, but otherwise there is not much point to the UI.
Review this code form one of my tasks. This is the UI class, the one that inherits from IDtsTaskUI. My form is ZipFileTaskUIForm, and has a modified constructor to accept the parameters passed through. You can see the TaskHost is cached in Initialize, and then passed to the form. This is how you get a reference to your task into the form.
public System.Windows.Forms.ContainerControl GetView()
{
return new ZipFileTaskUIForm(_taskHost, _dtsConnectionService, _dtsVariableService);
}
public void Initialize(TaskHost taskHost, IServiceProvider serviceProvider)
{
_taskHost = taskHost;
IDtsVariableService dtsVariableService = serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;
IDtsConnectionService dtsConnectionService = serviceProvider.GetService(typeof(IDtsConnectionService)) as IDtsConnectionService;
_dtsConnectionService = dtsConnectionService;
_dtsVariableService = dtsVariableService;
}
Your code is from the Class which inherits from IDtsTaskUI.
The question is how can you access public members of my Backend-Class in the UI (Form).
Maybe I have to convert and cache then, because the ui is called at design-time and the Backend at runtime?
|||Your form should be invoked from the UI class (IDtsTaskUI). The sample I showed demonstrates grabbing TaskHost, and passing it through to the form. You use the TaskHost to access properies of the task itself. You cannot get direct access to the Task instance, but you should not actually need to as you have TaskHost instead.
If you have code that you wish to share between trasjk and UI classes, then make them public static. Instance members would not work anyway since you cannot get the instance.
|||In addition to the Initialize method in the UI class that comes from the IDtsXxxUI interface, I would be inclined to write an Initialize method of my own in my Windows Forms class and pass along a reference to my task or other object, so I can easily read its current property values to set initial control states and values, then update its properties from the controls when the user closes the form.
So Task passes reference to itself in calling Initialize in Task UI class, and Task UI class passes reference to task in calling custom Initialize method in Task UI Form class.
-Doug
|||
I am also facing same problem. When i explored i came to know that there was two method SaveToXML() and LoadXML() of IDTSPersist90 interface to handle this type of scenerio. But i could not able to raise these methods. you can transfer either string or interger...etc, but if you want to transfer some custom object i think you have to preserve the same in xml and retrieve it when ever needed
...Karun
|||As I understand the issue, bearing in mind this thread crossed several, none of which matched the title that well, but you are writing a task and have a complex type that you need to persist. All persistance is through XML, and for complex stuff you need to implement IDTSPersist90 yourself. The methods are never called by you, they are called by the designer. You fill in the code to save and load the values to and from the XML document provided, and you can choose how you store the information in the XML.
For objects, I found the easiest way to to serialize them, and actually binary serialization at that. This snippet gives me a string that I can easily store in an xml element for the property.
Another alternative is to use reflection to read an object's properties, and then build up an XML node that represents this. Here is an example of creating a new element with sub elements for each property-
Friday, February 17, 2012
Custom Assembly deserialization issue
I see a lot of people are having the same issue when deploying to the report
server, even though things are running fine in design mode on their local
machines. I know that you need to copy all assemblies referenced to
/80/Tools/Report Designer/ and /MSSQL/Reporting Services/ReportServe/bin/ as
well as update rspreviewpolicy.config and rssrvpolicy.config files with
something resembling the following:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="Custom_Code_Group"
Description="Allows for custom code execution in Reporting Services">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:/Program Files/Microsoft SQL Server/80/Tools/Report Designer/*"/>
</CodeGroup>
However, when I try to run the report in the preview utility or on the
server, I am presented with:
<i>Request for the permission of type
System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=[PublicKeyToken]
failed.</i>
In the preview tab in the designer, the referenced assemblies are properly
deserializing my serialized object being returned by the query but since
there's this security issue when running the report elsewhere, I am having no
such luck. What am I doing wrong?It is also worth mentioning that the other error I get sporadically without
rhyme or reason is:
The value expression for the textbox â'textboxâ' contains an error: Cannot
find the assembly AssemblyName, Version=1.0.2120.25649, Culture=neutral,
PublicKeyToken=null.
This appears and disappears as I close and reopen Visual Studio as well as
rebuild my reporting project.|||Hi Keenan,
As you are doing the mermership of the assembly through the url and not the
strong name of the assembly maybe this is your case... in your server, the
assemblies are located exacly at: C:/Program Files/Microsoft SQL
Server/80/Tools/Report Designer/ ? or maybe it's in a different drive? if
they are not located in the same place you would have to change the url or
try with a membership with the strong name of the assembly
"Keenan Chadwick" <KeenanChadwick@.discussions.microsoft.com> wrote in
message news:D9C0CF29-9C02-45E9-AC79-5CBE3505173D@.microsoft.com...
> I see a lot of people are having the same issue when deploying to the
report
> server, even though things are running fine in design mode on their local
> machines. I know that you need to copy all assemblies referenced to
> /80/Tools/Report Designer/ and /MSSQL/Reporting Services/ReportServe/bin/
as
> well as update rspreviewpolicy.config and rssrvpolicy.config files with
> something resembling the following:
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="Custom_Code_Group"
> Description="Allows for custom code execution in Reporting Services">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="C:/Program Files/Microsoft SQL Server/80/Tools/Report Designer/*"/>
> </CodeGroup>
> However, when I try to run the report in the preview utility or on the
> server, I am presented with:
> <i>Request for the permission of type
> System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=[PublicKeyToken]
> failed.</i>
> In the preview tab in the designer, the referenced assemblies are properly
> deserializing my serialized object being returned by the query but since
> there's this security issue when running the report elsewhere, I am having
no
> such luck. What am I doing wrong?|||According to Microsoft, there is no need to have a strong-named assembly when
doing custom code. The URL references the exact spot on the server (from the
server's point of view, of course) where the assemblies reside. Nothing
works. I've tried strong-naming my assemblies and that caused a huge error
even in the design preview, so I have no idea at this point.
I am pretty sure there is some way to get my assemblies to run, since they
work in the design preview mode which runs everything with full permission.
Microsoft's support suggestion to me was really a question asking me to wait
for the 2005 suite of everything.
server, even though things are running fine in design mode on their local
machines. I know that you need to copy all assemblies referenced to
/80/Tools/Report Designer/ and /MSSQL/Reporting Services/ReportServe/bin/ as
well as update rspreviewpolicy.config and rssrvpolicy.config files with
something resembling the following:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="Custom_Code_Group"
Description="Allows for custom code execution in Reporting Services">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:/Program Files/Microsoft SQL Server/80/Tools/Report Designer/*"/>
</CodeGroup>
However, when I try to run the report in the preview utility or on the
server, I am presented with:
<i>Request for the permission of type
System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=[PublicKeyToken]
failed.</i>
In the preview tab in the designer, the referenced assemblies are properly
deserializing my serialized object being returned by the query but since
there's this security issue when running the report elsewhere, I am having no
such luck. What am I doing wrong?It is also worth mentioning that the other error I get sporadically without
rhyme or reason is:
The value expression for the textbox â'textboxâ' contains an error: Cannot
find the assembly AssemblyName, Version=1.0.2120.25649, Culture=neutral,
PublicKeyToken=null.
This appears and disappears as I close and reopen Visual Studio as well as
rebuild my reporting project.|||Hi Keenan,
As you are doing the mermership of the assembly through the url and not the
strong name of the assembly maybe this is your case... in your server, the
assemblies are located exacly at: C:/Program Files/Microsoft SQL
Server/80/Tools/Report Designer/ ? or maybe it's in a different drive? if
they are not located in the same place you would have to change the url or
try with a membership with the strong name of the assembly
"Keenan Chadwick" <KeenanChadwick@.discussions.microsoft.com> wrote in
message news:D9C0CF29-9C02-45E9-AC79-5CBE3505173D@.microsoft.com...
> I see a lot of people are having the same issue when deploying to the
report
> server, even though things are running fine in design mode on their local
> machines. I know that you need to copy all assemblies referenced to
> /80/Tools/Report Designer/ and /MSSQL/Reporting Services/ReportServe/bin/
as
> well as update rspreviewpolicy.config and rssrvpolicy.config files with
> something resembling the following:
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="Custom_Code_Group"
> Description="Allows for custom code execution in Reporting Services">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="C:/Program Files/Microsoft SQL Server/80/Tools/Report Designer/*"/>
> </CodeGroup>
> However, when I try to run the report in the preview utility or on the
> server, I am presented with:
> <i>Request for the permission of type
> System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=[PublicKeyToken]
> failed.</i>
> In the preview tab in the designer, the referenced assemblies are properly
> deserializing my serialized object being returned by the query but since
> there's this security issue when running the report elsewhere, I am having
no
> such luck. What am I doing wrong?|||According to Microsoft, there is no need to have a strong-named assembly when
doing custom code. The URL references the exact spot on the server (from the
server's point of view, of course) where the assemblies reside. Nothing
works. I've tried strong-naming my assemblies and that caused a huge error
even in the design preview, so I have no idea at this point.
I am pretty sure there is some way to get my assemblies to run, since they
work in the design preview mode which runs everything with full permission.
Microsoft's support suggestion to me was really a question asking me to wait
for the 2005 suite of everything.
Tuesday, February 14, 2012
Cursors inside SQL Functions - Very long execution time
I have a report that requires the use of cursors within a function because we
don't have control over the design of the database. So, the datasource for
the report is as follows:
1. Report calls a Stored Proc
2. Stored Proc runs this statement: Select... dbo.Function()...
3. Inside the Function(), I loop through a cursor.
Now when I run this Stored Proc directly in Query Analyzer, it takes 3
seconds.
But, when the report is run through SQL Reporting Services, it takes 108
seconds. I ran a SQL Profiler and noticed that Reporting Services was using
.Net Provider and the provider is way too slow for running cursors
- 2000ms for .Net Provider vs 0ms for Query Analyzer.
Is there anything I can do other than finding a way out of using cursors?Assuming that you call a stored procedure from RS it makes absolutely no
difference as far as executing the procedure. Stored procedures execute on
the server. It is not like the dotnet provider is opening a cursor, it is
not, it calls the stored procedure and gets back the results. What is most
likely happening is one of two things. Sometime there can be a delay due to
security authentication. More likely, however, is that you are returning a
lot of records and the rendering is what is taking the time. What are you
rendering to? Html, pdf, excel? PDF and Excel take much longer to render to
than HTML. Are you using a filter? Filters bring over all the data and then
filters it. So, the key questions are, what are you rendering to and how
many records is the stored procedure returning.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sanjay Gandhi" <SanjayGandhi@.discussions.microsoft.com> wrote in message
news:36286553-9F41-488F-BB24-4DC46C680168@.microsoft.com...
>I have a report that requires the use of cursors within a function because
>we
> don't have control over the design of the database. So, the datasource for
> the report is as follows:
> 1. Report calls a Stored Proc
> 2. Stored Proc runs this statement: Select... dbo.Function()...
> 3. Inside the Function(), I loop through a cursor.
> Now when I run this Stored Proc directly in Query Analyzer, it takes 3
> seconds.
> But, when the report is run through SQL Reporting Services, it takes 108
> seconds. I ran a SQL Profiler and noticed that Reporting Services was
> using
> .Net Provider and the provider is way too slow for running cursors
> - 2000ms for .Net Provider vs 0ms for Query Analyzer.
> Is there anything I can do other than finding a way out of using cursors?|||The number of records returned for the parameters I specified is only 69. And
I am rendering the report using the Reporting Server interface.
I may have solved this issue. I ran the Index Tuning Wizard on the query
used by the cursor and resulted in a 95% improvement. So that leads me to
think that when the cursor is run through Reporting Server, it did not use
the best execution plan. I'll do some more testing and see if that's it.
"Bruce L-C [MVP]" wrote:
> Assuming that you call a stored procedure from RS it makes absolutely no
> difference as far as executing the procedure. Stored procedures execute on
> the server. It is not like the dotnet provider is opening a cursor, it is
> not, it calls the stored procedure and gets back the results. What is most
> likely happening is one of two things. Sometime there can be a delay due to
> security authentication. More likely, however, is that you are returning a
> lot of records and the rendering is what is taking the time. What are you
> rendering to? Html, pdf, excel? PDF and Excel take much longer to render to
> than HTML. Are you using a filter? Filters bring over all the data and then
> filters it. So, the key questions are, what are you rendering to and how
> many records is the stored procedure returning.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sanjay Gandhi" <SanjayGandhi@.discussions.microsoft.com> wrote in message
> news:36286553-9F41-488F-BB24-4DC46C680168@.microsoft.com...
> >I have a report that requires the use of cursors within a function because
> >we
> > don't have control over the design of the database. So, the datasource for
> > the report is as follows:
> > 1. Report calls a Stored Proc
> > 2. Stored Proc runs this statement: Select... dbo.Function()...
> > 3. Inside the Function(), I loop through a cursor.
> >
> > Now when I run this Stored Proc directly in Query Analyzer, it takes 3
> > seconds.
> >
> > But, when the report is run through SQL Reporting Services, it takes 108
> > seconds. I ran a SQL Profiler and noticed that Reporting Services was
> > using
> > .Net Provider and the provider is way too slow for running cursors
> > - 2000ms for .Net Provider vs 0ms for Query Analyzer.
> >
> > Is there anything I can do other than finding a way out of using cursors?
>
>
don't have control over the design of the database. So, the datasource for
the report is as follows:
1. Report calls a Stored Proc
2. Stored Proc runs this statement: Select... dbo.Function()...
3. Inside the Function(), I loop through a cursor.
Now when I run this Stored Proc directly in Query Analyzer, it takes 3
seconds.
But, when the report is run through SQL Reporting Services, it takes 108
seconds. I ran a SQL Profiler and noticed that Reporting Services was using
.Net Provider and the provider is way too slow for running cursors
- 2000ms for .Net Provider vs 0ms for Query Analyzer.
Is there anything I can do other than finding a way out of using cursors?Assuming that you call a stored procedure from RS it makes absolutely no
difference as far as executing the procedure. Stored procedures execute on
the server. It is not like the dotnet provider is opening a cursor, it is
not, it calls the stored procedure and gets back the results. What is most
likely happening is one of two things. Sometime there can be a delay due to
security authentication. More likely, however, is that you are returning a
lot of records and the rendering is what is taking the time. What are you
rendering to? Html, pdf, excel? PDF and Excel take much longer to render to
than HTML. Are you using a filter? Filters bring over all the data and then
filters it. So, the key questions are, what are you rendering to and how
many records is the stored procedure returning.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sanjay Gandhi" <SanjayGandhi@.discussions.microsoft.com> wrote in message
news:36286553-9F41-488F-BB24-4DC46C680168@.microsoft.com...
>I have a report that requires the use of cursors within a function because
>we
> don't have control over the design of the database. So, the datasource for
> the report is as follows:
> 1. Report calls a Stored Proc
> 2. Stored Proc runs this statement: Select... dbo.Function()...
> 3. Inside the Function(), I loop through a cursor.
> Now when I run this Stored Proc directly in Query Analyzer, it takes 3
> seconds.
> But, when the report is run through SQL Reporting Services, it takes 108
> seconds. I ran a SQL Profiler and noticed that Reporting Services was
> using
> .Net Provider and the provider is way too slow for running cursors
> - 2000ms for .Net Provider vs 0ms for Query Analyzer.
> Is there anything I can do other than finding a way out of using cursors?|||The number of records returned for the parameters I specified is only 69. And
I am rendering the report using the Reporting Server interface.
I may have solved this issue. I ran the Index Tuning Wizard on the query
used by the cursor and resulted in a 95% improvement. So that leads me to
think that when the cursor is run through Reporting Server, it did not use
the best execution plan. I'll do some more testing and see if that's it.
"Bruce L-C [MVP]" wrote:
> Assuming that you call a stored procedure from RS it makes absolutely no
> difference as far as executing the procedure. Stored procedures execute on
> the server. It is not like the dotnet provider is opening a cursor, it is
> not, it calls the stored procedure and gets back the results. What is most
> likely happening is one of two things. Sometime there can be a delay due to
> security authentication. More likely, however, is that you are returning a
> lot of records and the rendering is what is taking the time. What are you
> rendering to? Html, pdf, excel? PDF and Excel take much longer to render to
> than HTML. Are you using a filter? Filters bring over all the data and then
> filters it. So, the key questions are, what are you rendering to and how
> many records is the stored procedure returning.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sanjay Gandhi" <SanjayGandhi@.discussions.microsoft.com> wrote in message
> news:36286553-9F41-488F-BB24-4DC46C680168@.microsoft.com...
> >I have a report that requires the use of cursors within a function because
> >we
> > don't have control over the design of the database. So, the datasource for
> > the report is as follows:
> > 1. Report calls a Stored Proc
> > 2. Stored Proc runs this statement: Select... dbo.Function()...
> > 3. Inside the Function(), I loop through a cursor.
> >
> > Now when I run this Stored Proc directly in Query Analyzer, it takes 3
> > seconds.
> >
> > But, when the report is run through SQL Reporting Services, it takes 108
> > seconds. I ran a SQL Profiler and noticed that Reporting Services was
> > using
> > .Net Provider and the provider is way too slow for running cursors
> > - 2000ms for .Net Provider vs 0ms for Query Analyzer.
> >
> > Is there anything I can do other than finding a way out of using cursors?
>
>
Subscribe to:
Posts (Atom)