I have a tabular report with several groupings.
I want to customize one of the groups.
For example I have a field called country. I want to group it by
region, western and eastern. How would I do this?where does the regional information come from? you need to provide us with
more info about your fields and their data.
"OogleGoogle" <Yvonhong@.gmail.com> wrote in message
news:1183576344.729726.5950@.i38g2000prf.googlegroups.com...
>I have a tabular report with several groupings.
> I want to customize one of the groups.
> For example I have a field called country. I want to group it by
> region, western and eastern. How would I do this?
>
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Customer specific price matrix design (Long)
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.
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.
Monday, March 19, 2012
Custom Rollup Dimension for AVG
I am trying to build a dimension and a cube that contains a measure of Avg Family Size and a dimension that group different types of families into different categories.
The dimension is a parent-child type but the parent should be the AVG of all children instead of SUM.
I tried to use Custom Rollup feature in dimension (there are 2 places, one during the dimension setup in pop-up window, there is check box then to define with MDX or use a column with unitary operator.Another place is in dimension Properties after dimension is setup).I cannot get it to work no matter what I do in dimension with MDX.The cube display #ERR for each cell.
Also, on cube setup, measure setup has a property of Aggregation.It has SUM, MAX, MIN, COUNT, DISTINCT COUNT but just does not have AVG.
So far, the only way is to define a calc measure with such MDX:
IIF(IsLeaf([Test1].CurrentMemeber), [Measure].size, AVG([Test1].CurrentMember.Children, [Measuer].size) )
This is sort of awkward because it renders the original Measure useless.
Any ideas to do this directly in Dimension or in Mesaure? This is for AS 2000.
One of the standard way of doing an average in AS is to set up 2 measures, a SUM and a COUNT and then create a calculated measure for the average and set it up as the SUM / COUNT. If you don't need the original SUM and COUNT measures you can set their visible properties to false. This would create an average for a single measure.
If you had a lot of measures and they all have to be averaged you could try something like one the following which I think should work across all measures (but I have not tested it)
AVG([Test1].CurrentMember,1))
OR
SUM(descendants([Test1].CurrentMember,1))/COUNT(descendants([Test1].CurrentMember,1))
The descendants(<member>,1) will go down 1 level if there is one, otherwise it will return the member itself, which helps avoid the IIF(IsLeaf(...)) test. And not explicitly stating a measure *should* mean that the query will use the current measure context.
Sunday, March 11, 2012
Custom PDF export...
Group...
I have a requirement to add a custom button to an ASPX 'View PDF'. When the
user clicks this button a specific SSRS report will be returned (Open/Save)
as a pdf. I need to be able to do this without using the MS Sample Report
Viewer control (which has the 'Select a format, Export' option just like in
Report Manager).
From what I have read there may be a call that can be made to the RS Web
Service, is this true and if so, is this the only way to accomplish a PDF
export.
Thanks in advance everyone.I have same case as yours, and I am using web service call to accomplish
this. I dont know if there is another way but using RS web service is quite
easy. If you need further information or sample code drop a message.
Regards
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:eHXMZx92EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Group...
> I have a requirement to add a custom button to an ASPX 'View PDF'. When
the
> user clicks this button a specific SSRS report will be returned
(Open/Save)
> as a pdf. I need to be able to do this without using the MS Sample Report
> Viewer control (which has the 'Select a format, Export' option just like
in
> Report Manager).
> From what I have read there may be a call that can be made to the RS Web
> Service, is this true and if so, is this the only way to accomplish a PDF
> export.
> Thanks in advance everyone.
>|||Do you have a sample link handy?
Thanks so much.
"saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
news:ul5ho$92EHA.2540@.TK2MSFTNGP09.phx.gbl...
>I have same case as yours, and I am using web service call to accomplish
> this. I dont know if there is another way but using RS web service is
> quite
> easy. If you need further information or sample code drop a message.
> Regards
>
> "Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
> news:eHXMZx92EHA.1452@.TK2MSFTNGP11.phx.gbl...
>> Group...
>> I have a requirement to add a custom button to an ASPX 'View PDF'. When
> the
>> user clicks this button a specific SSRS report will be returned
> (Open/Save)
>> as a pdf. I need to be able to do this without using the MS Sample Report
>> Viewer control (which has the 'Select a format, Export' option just like
> in
>> Report Manager).
>> From what I have read there may be a call that can be made to the RS Web
>> Service, is this true and if so, is this the only way to accomplish a PDF
>> export.
>> Thanks in advance everyone.
>>
>|||My custemer can chose which format to download. I have a sub that accepts
mime type, so I can pass render format to sub, and use one sub for all
formats.
First you must add web referance to ReportService.
Here is the code;
Private Sub renderdoc(ByVal mime As String)
Dim mime_type As String
Dim filename As String
Select Case mime
Case "pdf"
mime_type = "application/pdf"
filename = "fiyatlistesi.pdf"
Case "Excel"
mime_type = "application/x-msexcel"
filename = "fiyatlistesi.xls"
Case "xml"
mime_type = "application/xml"
filename = "fiyatlistesi.xml"
Case Else
mime_type = "application/pdf"
filename = "fiyatlistesi.pdf"
End Select
Dim report As Byte() = Nothing
Dim rs As localhost.ReportingService = New
localhost.ReportingService
'login details comes here
rs.Credentials = New
System.Net.NetworkCredential("raport_user_name_here", "password_here")
rs.PreAuthenticate = True
'report path here, I have a report named fiyat.rdl
Dim reportPath As String = "/rapor/fiyat"
Dim format As String = mime
Dim devInfo As String = _
"<DeviceInfo>" + _
"<Toolbar>False</Toolbar>" + _
"<Parameters>False</Parameters>" + _
"<DocMap>True</DocMap>" + _
"<Zoom>100</Zoom>" + _
"</DeviceInfo>"
Dim historyID As String = Nothing
'I have 3 report parameters, I define them here
Dim parameters(2) As localhost.ParameterValue
Dim paramValue As localhost.ParameterValue = New
localhost.ParameterValue
'First param name is cari_isim (ok its in Turkish)
paramValue.Name = "cari_isim"
paramValue.Value = textbox1.text
parameters(0) = paramValue
'second here
paramValue = New localhost.ParameterValue
paramValue.Name = "fiyatgrup"
paramValue.Value = textbox2.text
parameters(1) = paramValue
'third here
paramValue = New localhost.ParameterValue
paramValue.Name = "urunler"
paramValue.Value = dropdownlist1.selecteditem.value
parameters(2) = paramValue
Dim credentials() As localhost.DataSourceCredentials = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings() As localhost.Warning = Nothing
Dim reportHistoryParameters() As localhost.ParameterValue = Nothing
Dim streamIDs() As String = Nothing
Dim sh As localhost.SessionHeader = New localhost.SessionHeader
rs.SessionHeaderValue = sh
Try
report = rs.Render(reportPath, format, historyID, _
devInfo, parameters, credentials, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
streamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
Response.Clear()
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = mime_type
HttpContext.Current.Response.AddHeader("Content-disposition",
"attachment; filename=" + filename + "")
HttpContext.Current.Response.BinaryWrite(report)
HttpContext.Current.Response.Flush()
HttpContext.Current.Response.End()
Catch ex As Exception
If ex.Message <> "Thread was being aborted." Then
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
HttpContext.Current.Response.Write( _
"&
Error" & _
ex.Message & "
")
HttpContext.Current.Response.End()
End If
End Try
End Sub
Hope works for you.
Regards
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:#yYGGv#2EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Do you have a sample link handy?
> Thanks so much.
> "saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
> news:ul5ho$92EHA.2540@.TK2MSFTNGP09.phx.gbl...
> >I have same case as yours, and I am using web service call to accomplish
> > this. I dont know if there is another way but using RS web service is
> > quite
> > easy. If you need further information or sample code drop a message.
> >
> > Regards
> >
> >
> >
> > "Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
> > news:eHXMZx92EHA.1452@.TK2MSFTNGP11.phx.gbl...
> >> Group...
> >> I have a requirement to add a custom button to an ASPX 'View PDF'. When
> > the
> >> user clicks this button a specific SSRS report will be returned
> > (Open/Save)
> >> as a pdf. I need to be able to do this without using the MS Sample
Report
> >> Viewer control (which has the 'Select a format, Export' option just
like
> > in
> >> Report Manager).
> >>
> >> From what I have read there may be a call that can be made to the RS
Web
> >> Service, is this true and if so, is this the only way to accomplish a
PDF
> >> export.
> >>
> >> Thanks in advance everyone.
> >>
> >>
> >
> >
>|||In the URL for the report you can specify the Render property for whatever
output you wish. The following (part) URLs demonstrate this. I have a number
of buttons on my asps, that allow the user to output directly to XL, PDF or
the standard RS report page, by using the following::
NOTE: I use javascript to create the link and then open a new results window
at this point::
function GET_RS(aFormat)
{
var sReportName="myReport1";
if(aFormat=="EXCEL"){sReportName="ReportRS1XL";GET_HELP('XL')}
if(aFormat=="PDF"){sReportName="ReportRS1";GET_HELP('PD')}
HLINK = HLINK + "http://myServer/ReportServer"
HLINK = HLINK + "?%2fBaanReports%2f"+sReportName HLINK = HLINK +
"&rs%3aClearSession=true"
HLINK = HLINK + "&rs%3aCommand=Render"
HLINK = HLINK + "&rs%3aFormat=" + aFormat
.....
....
}
This blows through the standard RS page, where the user then has to select
the output format and click the Export link.
Hope this helps
Tony
"Terry Mulvany" wrote:
> Group...
> I have a requirement to add a custom button to an ASPX 'View PDF'. When the
> user clicks this button a specific SSRS report will be returned (Open/Save)
> as a pdf. I need to be able to do this without using the MS Sample Report
> Viewer control (which has the 'Select a format, Export' option just like in
> Report Manager).
> From what I have read there may be a call that can be made to the RS Web
> Service, is this true and if so, is this the only way to accomplish a PDF
> export.
> Thanks in advance everyone.
>
>
I have a requirement to add a custom button to an ASPX 'View PDF'. When the
user clicks this button a specific SSRS report will be returned (Open/Save)
as a pdf. I need to be able to do this without using the MS Sample Report
Viewer control (which has the 'Select a format, Export' option just like in
Report Manager).
From what I have read there may be a call that can be made to the RS Web
Service, is this true and if so, is this the only way to accomplish a PDF
export.
Thanks in advance everyone.I have same case as yours, and I am using web service call to accomplish
this. I dont know if there is another way but using RS web service is quite
easy. If you need further information or sample code drop a message.
Regards
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:eHXMZx92EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Group...
> I have a requirement to add a custom button to an ASPX 'View PDF'. When
the
> user clicks this button a specific SSRS report will be returned
(Open/Save)
> as a pdf. I need to be able to do this without using the MS Sample Report
> Viewer control (which has the 'Select a format, Export' option just like
in
> Report Manager).
> From what I have read there may be a call that can be made to the RS Web
> Service, is this true and if so, is this the only way to accomplish a PDF
> export.
> Thanks in advance everyone.
>|||Do you have a sample link handy?
Thanks so much.
"saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
news:ul5ho$92EHA.2540@.TK2MSFTNGP09.phx.gbl...
>I have same case as yours, and I am using web service call to accomplish
> this. I dont know if there is another way but using RS web service is
> quite
> easy. If you need further information or sample code drop a message.
> Regards
>
> "Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
> news:eHXMZx92EHA.1452@.TK2MSFTNGP11.phx.gbl...
>> Group...
>> I have a requirement to add a custom button to an ASPX 'View PDF'. When
> the
>> user clicks this button a specific SSRS report will be returned
> (Open/Save)
>> as a pdf. I need to be able to do this without using the MS Sample Report
>> Viewer control (which has the 'Select a format, Export' option just like
> in
>> Report Manager).
>> From what I have read there may be a call that can be made to the RS Web
>> Service, is this true and if so, is this the only way to accomplish a PDF
>> export.
>> Thanks in advance everyone.
>>
>|||My custemer can chose which format to download. I have a sub that accepts
mime type, so I can pass render format to sub, and use one sub for all
formats.
First you must add web referance to ReportService.
Here is the code;
Private Sub renderdoc(ByVal mime As String)
Dim mime_type As String
Dim filename As String
Select Case mime
Case "pdf"
mime_type = "application/pdf"
filename = "fiyatlistesi.pdf"
Case "Excel"
mime_type = "application/x-msexcel"
filename = "fiyatlistesi.xls"
Case "xml"
mime_type = "application/xml"
filename = "fiyatlistesi.xml"
Case Else
mime_type = "application/pdf"
filename = "fiyatlistesi.pdf"
End Select
Dim report As Byte() = Nothing
Dim rs As localhost.ReportingService = New
localhost.ReportingService
'login details comes here
rs.Credentials = New
System.Net.NetworkCredential("raport_user_name_here", "password_here")
rs.PreAuthenticate = True
'report path here, I have a report named fiyat.rdl
Dim reportPath As String = "/rapor/fiyat"
Dim format As String = mime
Dim devInfo As String = _
"<DeviceInfo>" + _
"<Toolbar>False</Toolbar>" + _
"<Parameters>False</Parameters>" + _
"<DocMap>True</DocMap>" + _
"<Zoom>100</Zoom>" + _
"</DeviceInfo>"
Dim historyID As String = Nothing
'I have 3 report parameters, I define them here
Dim parameters(2) As localhost.ParameterValue
Dim paramValue As localhost.ParameterValue = New
localhost.ParameterValue
'First param name is cari_isim (ok its in Turkish)
paramValue.Name = "cari_isim"
paramValue.Value = textbox1.text
parameters(0) = paramValue
'second here
paramValue = New localhost.ParameterValue
paramValue.Name = "fiyatgrup"
paramValue.Value = textbox2.text
parameters(1) = paramValue
'third here
paramValue = New localhost.ParameterValue
paramValue.Name = "urunler"
paramValue.Value = dropdownlist1.selecteditem.value
parameters(2) = paramValue
Dim credentials() As localhost.DataSourceCredentials = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings() As localhost.Warning = Nothing
Dim reportHistoryParameters() As localhost.ParameterValue = Nothing
Dim streamIDs() As String = Nothing
Dim sh As localhost.SessionHeader = New localhost.SessionHeader
rs.SessionHeaderValue = sh
Try
report = rs.Render(reportPath, format, historyID, _
devInfo, parameters, credentials, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
streamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
Response.Clear()
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = mime_type
HttpContext.Current.Response.AddHeader("Content-disposition",
"attachment; filename=" + filename + "")
HttpContext.Current.Response.BinaryWrite(report)
HttpContext.Current.Response.Flush()
HttpContext.Current.Response.End()
Catch ex As Exception
If ex.Message <> "Thread was being aborted." Then
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
HttpContext.Current.Response.Write( _
"&
Error" & _
ex.Message & "
")
HttpContext.Current.Response.End()
End If
End Try
End Sub
Hope works for you.
Regards
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:#yYGGv#2EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Do you have a sample link handy?
> Thanks so much.
> "saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
> news:ul5ho$92EHA.2540@.TK2MSFTNGP09.phx.gbl...
> >I have same case as yours, and I am using web service call to accomplish
> > this. I dont know if there is another way but using RS web service is
> > quite
> > easy. If you need further information or sample code drop a message.
> >
> > Regards
> >
> >
> >
> > "Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
> > news:eHXMZx92EHA.1452@.TK2MSFTNGP11.phx.gbl...
> >> Group...
> >> I have a requirement to add a custom button to an ASPX 'View PDF'. When
> > the
> >> user clicks this button a specific SSRS report will be returned
> > (Open/Save)
> >> as a pdf. I need to be able to do this without using the MS Sample
Report
> >> Viewer control (which has the 'Select a format, Export' option just
like
> > in
> >> Report Manager).
> >>
> >> From what I have read there may be a call that can be made to the RS
Web
> >> Service, is this true and if so, is this the only way to accomplish a
> >> export.
> >>
> >> Thanks in advance everyone.
> >>
> >>
> >
> >
>|||In the URL for the report you can specify the Render property for whatever
output you wish. The following (part) URLs demonstrate this. I have a number
of buttons on my asps, that allow the user to output directly to XL, PDF or
the standard RS report page, by using the following::
NOTE: I use javascript to create the link and then open a new results window
at this point::
function GET_RS(aFormat)
{
var sReportName="myReport1";
if(aFormat=="EXCEL"){sReportName="ReportRS1XL";GET_HELP('XL')}
if(aFormat=="PDF"){sReportName="ReportRS1";GET_HELP('PD')}
HLINK = HLINK + "http://myServer/ReportServer"
HLINK = HLINK + "?%2fBaanReports%2f"+sReportName HLINK = HLINK +
"&rs%3aClearSession=true"
HLINK = HLINK + "&rs%3aCommand=Render"
HLINK = HLINK + "&rs%3aFormat=" + aFormat
.....
....
}
This blows through the standard RS page, where the user then has to select
the output format and click the Export link.
Hope this helps
Tony
"Terry Mulvany" wrote:
> Group...
> I have a requirement to add a custom button to an ASPX 'View PDF'. When the
> user clicks this button a specific SSRS report will be returned (Open/Save)
> as a pdf. I need to be able to do this without using the MS Sample Report
> Viewer control (which has the 'Select a format, Export' option just like in
> Report Manager).
> From what I have read there may be a call that can be made to the RS Web
> Service, is this true and if so, is this the only way to accomplish a PDF
> export.
> Thanks in advance everyone.
>
>
custom path for data files
Hi,
I am about to install sql 2005 in a clustered scenario. I am reading the
installation notes and it says :
On the Cluster Group Selection page, select the group that contains the
drive you want to use for SQL Server from the Available Cluster Groups. The
selected cluster group is where the SQL Server virtual SQL Server resources
are placed. If you select the group containing the cluster quorum resource, a
warning is displayed recommending you do not install to the cluster quorum
resource. You can also specify a custom path for data files in the Data Files
field. To proceed, click Next.
My question is that I have two nodes both sharing D, E & QDrive if I select
the custom path option at this point in the installaion will I be able to
select D for databases and E for logs?
http://msdn2.microsoft.com/en-us/library/ms179530.aspx
Yes. You can add E: to the cluster group, make SQL dependent on it, and use
it for data or logs.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:99A9574F-1DDF-4741-8676-FC6EB36DFAFB@.microsoft.com...
> Hi,
> I am about to install sql 2005 in a clustered scenario. I am reading the
> installation notes and it says :
> On the Cluster Group Selection page, select the group that contains the
> drive you want to use for SQL Server from the Available Cluster Groups.
> The
> selected cluster group is where the SQL Server virtual SQL Server
> resources
> are placed. If you select the group containing the cluster quorum
> resource, a
> warning is displayed recommending you do not install to the cluster quorum
> resource. You can also specify a custom path for data files in the Data
> Files
> field. To proceed, click Next.
> My question is that I have two nodes both sharing D, E & QDrive if I
> select
> the custom path option at this point in the installaion will I be able to
> select D for databases and E for logs?
>
> http://msdn2.microsoft.com/en-us/library/ms179530.aspx
|||Hi Geoff,
Thanks for this but I'm new at this and a little confused.
I have:
cluster group containing Q
group 0 containing D Drive (I would like the data files here)
group 1 containing E Drive (I would like the log files here)
When I went through the setup I was expecting to have the choice of
splitting the files but could only chose one cluster group so I chose cluster
group 0.
Now when I opened management studio to change the logs location I can only
see D, adding the E Drive to group 0 also does not allow me to see E drive
from management studio.
What am I missing?
thanks in advance
I have now installed sql and at the point when asked
"Geoff N. Hiten" wrote:
> Yes. You can add E: to the cluster group, make SQL dependent on it, and use
> it for data or logs.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:99A9574F-1DDF-4741-8676-FC6EB36DFAFB@.microsoft.com...
>
|||You are missing dependency.
Using the Cluster admin tool, take the SQL Service offline but leave the
rest of the group online. Right-click the SQL Service... Properties.
Advanced tab. Add the new disk as an item the SQL Service is dependent on.
Bring SQL back online.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1AB63836-C0CF-4228-A1DC-9D8F28AB63FA@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for this but I'm new at this and a little confused.
> I have:
> cluster group containing Q
> group 0 containing D Drive (I would like the data files here)
> group 1 containing E Drive (I would like the log files here)
> When I went through the setup I was expecting to have the choice of
> splitting the files but could only chose one cluster group so I chose
> cluster
> group 0.
> Now when I opened management studio to change the logs location I can only
> see D, adding the E Drive to group 0 also does not allow me to see E drive
> from management studio.
> What am I missing?
> thanks in advance
>
> I have now installed sql and at the point when asked
> "Geoff N. Hiten" wrote:
I am about to install sql 2005 in a clustered scenario. I am reading the
installation notes and it says :
On the Cluster Group Selection page, select the group that contains the
drive you want to use for SQL Server from the Available Cluster Groups. The
selected cluster group is where the SQL Server virtual SQL Server resources
are placed. If you select the group containing the cluster quorum resource, a
warning is displayed recommending you do not install to the cluster quorum
resource. You can also specify a custom path for data files in the Data Files
field. To proceed, click Next.
My question is that I have two nodes both sharing D, E & QDrive if I select
the custom path option at this point in the installaion will I be able to
select D for databases and E for logs?
http://msdn2.microsoft.com/en-us/library/ms179530.aspx
Yes. You can add E: to the cluster group, make SQL dependent on it, and use
it for data or logs.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:99A9574F-1DDF-4741-8676-FC6EB36DFAFB@.microsoft.com...
> Hi,
> I am about to install sql 2005 in a clustered scenario. I am reading the
> installation notes and it says :
> On the Cluster Group Selection page, select the group that contains the
> drive you want to use for SQL Server from the Available Cluster Groups.
> The
> selected cluster group is where the SQL Server virtual SQL Server
> resources
> are placed. If you select the group containing the cluster quorum
> resource, a
> warning is displayed recommending you do not install to the cluster quorum
> resource. You can also specify a custom path for data files in the Data
> Files
> field. To proceed, click Next.
> My question is that I have two nodes both sharing D, E & QDrive if I
> select
> the custom path option at this point in the installaion will I be able to
> select D for databases and E for logs?
>
> http://msdn2.microsoft.com/en-us/library/ms179530.aspx
|||Hi Geoff,
Thanks for this but I'm new at this and a little confused.
I have:
cluster group containing Q
group 0 containing D Drive (I would like the data files here)
group 1 containing E Drive (I would like the log files here)
When I went through the setup I was expecting to have the choice of
splitting the files but could only chose one cluster group so I chose cluster
group 0.
Now when I opened management studio to change the logs location I can only
see D, adding the E Drive to group 0 also does not allow me to see E drive
from management studio.
What am I missing?
thanks in advance
I have now installed sql and at the point when asked
"Geoff N. Hiten" wrote:
> Yes. You can add E: to the cluster group, make SQL dependent on it, and use
> it for data or logs.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:99A9574F-1DDF-4741-8676-FC6EB36DFAFB@.microsoft.com...
>
|||You are missing dependency.
Using the Cluster admin tool, take the SQL Service offline but leave the
rest of the group online. Right-click the SQL Service... Properties.
Advanced tab. Add the new disk as an item the SQL Service is dependent on.
Bring SQL back online.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1AB63836-C0CF-4228-A1DC-9D8F28AB63FA@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for this but I'm new at this and a little confused.
> I have:
> cluster group containing Q
> group 0 containing D Drive (I would like the data files here)
> group 1 containing E Drive (I would like the log files here)
> When I went through the setup I was expecting to have the choice of
> splitting the files but could only chose one cluster group so I chose
> cluster
> group 0.
> Now when I opened management studio to change the logs location I can only
> see D, adding the E Drive to group 0 also does not allow me to see E drive
> from management studio.
> What am I missing?
> thanks in advance
>
> I have now installed sql and at the point when asked
> "Geoff N. Hiten" wrote:
Custom Ordering question
Hi group, i'm having a problem with custom ordering in a SP. My code is
the following:
The data type from a CASE expression is always the same and is determine
from the strict data-type precedence that SQL Server employs. (See in Books
Online under Datatypes for details). In this case, varchar has lower
precedence than int, so it's converted to int, which then fails.
the following:
CREATE PROCEDURE [dbo].[PersonasSelectAll_P]
@.fromRow int,
@.toRow int,
@.expresionOrdenamiento int = null
AS
SET NOCOUNT ON
DECLARE @.TotalFilas int
Select @.TotalFilas = count(*) from [Personas]
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @.expresionOrdenamiento =
1 THEN [Id]
--WHEN @.expresionOrdenamiento = 2 THEN [Nombre_RazonSocial]
WHEN @.expresionOrdenamiento = 3 THEN [CUIT_CUIL]
ELSE null
END
) AS Row
, * , @.TotalFilas as TotalRows
FROM [Personas]
AS NumberedPersons
WHERE
(Row >= isnull(@.fromRow, 0)) AND (Row <= isnull(@.toRow, row))
The line WHEN @.expresionOrdenamiento = 2 THEN [Nombre_RazonSocial] is
erroring out (the sp compiles, it's a runtime error), i'm getting an
'Cannot convert varchar to int'. Maybe it's because Nombre_RazonSocial
is varchar? If i comment that line then everything works fine. Any
Help??
Thanks in advanceGonza (gonzalow@.gmail.com) writes:
Quote:
Originally Posted by
SELECT ROW_NUMBER() OVER (ORDER BY CASE
WHEN @.expresionOrdenamiento = 1 THEN [Id]
WHEN @.expresionOrdenamiento = 2 THEN [Nombre_RazonSocial]
WHEN @.expresionOrdenamiento = 3 THEN [CUIT_CUIL]
ELSE null
END
>...
The line WHEN @.expresionOrdenamiento = 2 THEN [Nombre_RazonSocial] is
erroring out (the sp compiles, it's a runtime error), i'm getting an
'Cannot convert varchar to int'. Maybe it's because Nombre_RazonSocial
is varchar? If i comment that line then everything works fine. Any
Help??
The data type from a CASE expression is always the same and is determine
from the strict data-type precedence that SQL Server employs. (See in Books
Online under Datatypes for details). In this case, varchar has lower
precedence than int, so it's converted to int, which then fails.
The remedy is to write:
ORDER BY CASE @.x WHEN 1 THEN id WHEN 2 THEN CUIT_CUIL END,
CASE @.x WHEN 2 THEN Nombre_RazonSocial END
--
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
Thursday, March 8, 2012
Custom Grouping Problem
I am having some trouble with grouping. I have a custom function that returns the field to group on depending on the parameters provided by the user. This is not working, however if I select the same value from the grouping drop down list that is returned by the custom function the grouping works. My custom funtion is as follows:
Function OrganisationGroup() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!EnterpriseOption.Value
Case "1"
Group = Group1Value()
Case "2"
Group = "DepartmentDesc"
Case "3"
Group = "OrganizationDesc"
Case "4"
Group = "OrganizationDesc"
End Select
If Group = "" Then
Return "0"
Else
Return "Fields!" & Group & ".Value"
End If
End Function
Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1"
Group = "StockFamilyDesc"
Case "2"
Group = "GroupDesc"
Case "3"
Group = "Saleable"
Case "4"
Group = "Saleable"
Case "5"
Group = "SaleableGLCodeDesc"
Case "6"
Group = "Saleable"
End Select
Return Group
End Function
Does anyone have any ideas on why the grouping would not be working with the custom function?
Thanks in advance.
I have solved the problem myself. The following changes to the custom code fixed the problem.
Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = "StockFamilyDesc"
Case "2" Group = "GroupDesc"
Case "3" Group = "Saleable"
Case "4" Group = "Saleable"
Case "5" Group = "SaleableGLCodeDesc"
Case "6" Group = "Saleable"
End Select
Return Group
End Function
Now is ...
Function Group1Value(fields As Fields) As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = fields!StockFamilyDesc.Value
Case "2" Group = fields!GroupDesc.Value
Case "3" Group = fields!Saleable.Value
Case "4" Group = fields!Saleable.Value
Case "5" Group = fields!SaleableGLCodeDesc.Value
Case "6" Group = fields!Saleable.Value
End Select
Return Group
End Function
Similar changes in Function OrganisationGroup()
Custom Grouping Problem
I am having some trouble with grouping. I have a custom function that returns the field to group on depending on the parameters provided by the user. This is not working, however if I select the same value from the grouping drop down list that is returned by the custom function the grouping works. My custom funtion is as follows:
Function OrganisationGroup() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!EnterpriseOption.Value
Case "1"
Group = Group1Value()
Case "2"
Group = "DepartmentDesc"
Case "3"
Group = "OrganizationDesc"
Case "4"
Group = "OrganizationDesc"
End Select
If Group = "" Then
Return "0"
Else
Return "Fields!" & Group & ".Value"
End If
End Function
Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1"
Group = "StockFamilyDesc"
Case "2"
Group = "GroupDesc"
Case "3"
Group = "Saleable"
Case "4"
Group = "Saleable"
Case "5"
Group = "SaleableGLCodeDesc"
Case "6"
Group = "Saleable"
End Select
Return Group
End Function
Does anyone have any ideas on why the grouping would not be working with the custom function?
Thanks in advance.
I have solved the problem myself. The following changes to the custom code fixed the problem.
Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = "StockFamilyDesc"
Case "2" Group = "GroupDesc"
Case "3" Group = "Saleable"
Case "4" Group = "Saleable"
Case "5" Group = "SaleableGLCodeDesc"
Case "6" Group = "Saleable"
End Select
Return Group
End Function
Now is ...
Function Group1Value(fields As Fields) As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = fields!StockFamilyDesc.Value
Case "2" Group = fields!GroupDesc.Value
Case "3" Group = fields!Saleable.Value
Case "4" Group = fields!Saleable.Value
Case "5" Group = fields!SaleableGLCodeDesc.Value
Case "6" Group = fields!Saleable.Value
End Select
Return Group
End Function
Similar changes in Function OrganisationGroup()
Custom Grouping
My reqmt is to group records based on items purchased. I hv 2 groups cat1 ,cat2 with different set of items in them. My third group cat3 will contain customers who have purchased any items from cat1 AND cat2.
My formula is something like
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'
Am not able to get the cat3(3rd Group) on my report. Any thots on wat I cud b missing?
Am using Crystal Reports XII'd guess that your condition checks are in the wrong order.
You'll never get to the final if statement 'cos one of the previous two will always be true if the 3rd is true.|||Sorry if i have confused u. My reqmt is to get a count of all customers who buy items. Cat1 will have a set of items. Cat2 will have another set of items. My third group will contain items frm both cat1 and cat2. I sud basically get a count of customers in each cat.
I have grouped my report on items using the formula
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'
My report sud luk something like this :
Count(customer)
Cat1 xxxx
Cat2 xxxxx
Cat3 xxx(sud be the count of customers who hv bought frm cat1 and cat2)|||Assuming that your cat1 items and cat2 item lists are mutually exclusive then the 3rd if statement can never be true.
If there is an overlap (which I suspect there isn't, otherwise an item would be both a cat1 and cat2 item) then my previous comment is still true.
i.e. you will never get a cat3 with that formula.
Your main problem, however, is that you want to catagorise a customer, and your formula categorises an item.
My formula is something like
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'
Am not able to get the cat3(3rd Group) on my report. Any thots on wat I cud b missing?
Am using Crystal Reports XII'd guess that your condition checks are in the wrong order.
You'll never get to the final if statement 'cos one of the previous two will always be true if the 3rd is true.|||Sorry if i have confused u. My reqmt is to get a count of all customers who buy items. Cat1 will have a set of items. Cat2 will have another set of items. My third group will contain items frm both cat1 and cat2. I sud basically get a count of customers in each cat.
I have grouped my report on items using the formula
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'
My report sud luk something like this :
Count(customer)
Cat1 xxxx
Cat2 xxxxx
Cat3 xxx(sud be the count of customers who hv bought frm cat1 and cat2)|||Assuming that your cat1 items and cat2 item lists are mutually exclusive then the 3rd if statement can never be true.
If there is an overlap (which I suspect there isn't, otherwise an item would be both a cat1 and cat2 item) then my previous comment is still true.
i.e. you will never get a cat3 with that formula.
Your main problem, however, is that you want to catagorise a customer, and your formula categorises an item.
custom group count and suppressed data leaving blank space
I wil set you up with my scenario.
I am making a CR for a medical facility. I need to pull data from our databases that includes patients over 65 years of age that are chronically ill and how many chronic conditions they have. I grouped the report by age, with the name of the patients and their chronic conditions listed under the drilled down age. So, for example, it shows 65 YEARS and once clicked a seperate report will open with the patient info in it.
My boss wants to know how many patients have chronic conditions and how old they are.
I need help with this little problem. I know it is somthing small. I have some suppressed data that wont fully disappear. It leaves a blank space in between the group sections. I was wondering how can I get rid of that blank space. Also I noticed that when you click on the blank space you can still open up the suppressed data section even if there is no shown data.
How can I make the data suppressed and not have that blank space? How can I get the count to show only the patients that have chronic conditions that are 65 years or older? If you can help please do so, THANKS!If you want to remove the blank space when suppressing, try suppressing the section instead of the field.
I am making a CR for a medical facility. I need to pull data from our databases that includes patients over 65 years of age that are chronically ill and how many chronic conditions they have. I grouped the report by age, with the name of the patients and their chronic conditions listed under the drilled down age. So, for example, it shows 65 YEARS and once clicked a seperate report will open with the patient info in it.
My boss wants to know how many patients have chronic conditions and how old they are.
I need help with this little problem. I know it is somthing small. I have some suppressed data that wont fully disappear. It leaves a blank space in between the group sections. I was wondering how can I get rid of that blank space. Also I noticed that when you click on the blank space you can still open up the suppressed data section even if there is no shown data.
How can I make the data suppressed and not have that blank space? How can I get the count to show only the patients that have chronic conditions that are 65 years or older? If you can help please do so, THANKS!If you want to remove the blank space when suppressing, try suppressing the section instead of the field.
Saturday, February 25, 2012
custom count within group crystal XI
Hi,
I need to count number of consequtive weeks within a group:
Group_name consequtive: 2
person1 7/5/07
person2 7/19/07
person3 7/26/07
Consequtive is equal to 2 within this group, since person 2 and 3 are 1 week apart.
anybody know how to code this in CR XI?
thx in advanceUse the DateDiff function. If the DateDiff (w) of the previous week to this week = 1, then increment a running total by 1.
I need to count number of consequtive weeks within a group:
Group_name consequtive: 2
person1 7/5/07
person2 7/19/07
person3 7/26/07
Consequtive is equal to 2 within this group, since person 2 and 3 are 1 week apart.
anybody know how to code this in CR XI?
thx in advanceUse the DateDiff function. If the DateDiff (w) of the previous week to this week = 1, then increment a running total by 1.
Friday, February 24, 2012
Custom Code...For the Sake of Argument
Is it possible to write custom code that achieves the same thing as an aggregate function?
ex: Calculating the sum of a group? (with out using the sum( field!one.value) function)
if I had a group that grouped on name.
Name Number
header Fields!FirstName.Value ************
details Fields!Number.Value
footer
Can i write a custom code function (or functions) that will get the sume of the numbers in that group.
Thanks in advance.
Hi all
I am also interested in writing code that can access the datasets created for the reports. I wanted to write a search function that searched a dataset from a parameter input to custom code. I am trying to count records of a group, and also trying to count filltered records within that group to calculate a percentage and display them on a group banner. The easy formatting examples everybody provides for custom code is no help... I was trying to use ADO code with the already created datasets, but got nowhere... That infernal "error in custom code" I think is hard-coded into the SSRS...
Dick
I just stumbled on to "Microsoft Reporting Services inAction: Extending Microsoft SQL Server 2000 Reporting Services with Custom Code". Some light weekend reading.
|||Yes you can perform certain types of custom aggregations via custom code. One example is shown in the following blog posting: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
-- Robert
|||Currently SSRS doesn't support custom aggregates although SQL Server 2005 provides this capability in the form of CLR custom aggregate functions. One approach is to prepare your dataset in such a way that your aggregates can be produced by the standard SSRS aggregate functions, e.g. SUM. For example, if some report lines need to add up while others to subtract from the group aggregate, you can have an additional sign column which will have values of 1 or -1 accordingly.Custom Code problem!
Hi,
I want to use Custom Code to control the Visibility of a Group.
I write a test code, it always Return True:
Function CanSeen() as Boolean
Return True
End Function
Then I set the initial visibility of a group to Expression =Code.CanSeen()
But the the group can't seen!!
What is the problem?Are you calling your custom code from the row properties? If so, which
property? The "hidden" one? If so, notice that RS is asking you "do you want
this row to be hidden?" and you're saying "True". Perhaps that's the point.
I hope this helps
--
Please mark the correct/helpful answers!
"ad" wrote:
> Hi,
> I want to use Custom Code to control the Visibility of a Group.
> I write a test code, it always Return True:
> Function CanSeen() as Boolean
> Return True
> End Function
> Then I set the initial visibility of a group to Expression =Code.CanSeen()
> But the the group can't seen!!
> What is the problem?
>
>
>
I want to use Custom Code to control the Visibility of a Group.
I write a test code, it always Return True:
Function CanSeen() as Boolean
Return True
End Function
Then I set the initial visibility of a group to Expression =Code.CanSeen()
But the the group can't seen!!
What is the problem?Are you calling your custom code from the row properties? If so, which
property? The "hidden" one? If so, notice that RS is asking you "do you want
this row to be hidden?" and you're saying "True". Perhaps that's the point.
I hope this helps
--
Please mark the correct/helpful answers!
"ad" wrote:
> Hi,
> I want to use Custom Code to control the Visibility of a Group.
> I write a test code, it always Return True:
> Function CanSeen() as Boolean
> Return True
> End Function
> Then I set the initial visibility of a group to Expression =Code.CanSeen()
> But the the group can't seen!!
> What is the problem?
>
>
>
Sunday, February 19, 2012
custom assmebly permissions (I really need help)
I try to deploy a custom assembly which access a oracle database.
I' read all post in this news group about it and also the policies refered.
I'm very confused now, becouse it is a huge amount of information and I
wonder how unspecific it is.
I need exactly to know which file I must change how and where exactly in the
file. I tried many combinations, but I have a deadline on the project an I'm
really in troubles.
In the KB Articles are CodeGroups described and also PermissionSets 3 files
to modify described too, but which file must be how modified is not described.
Please help me somebody with detailled information. It really drives me to
despair.The assembly by it self works if I remove the code for access the oracle
database.
In a article is mentoined that the code inside the assembly must be modified
too. But I don't know how to transfer this to my VB Assmebly. But this is
possibly not the only wrong thing in my project.
The purpose of the assembly is localization. And in the database still
exists a table with translations used by the application used to maintain the
data. Therefore I must access the database with the assemly.
And btw. I can not understand why the samples are for file access
permissions. How many people will store f.e. currency information in a xml
file on the server? How does the data uses by RS come usual into the
database? Posibly with existing other applications? Or does RS support data
maintainance?
"Dev Main" wrote:
> I try to deploy a custom assembly which access a oracle database.
> I' read all post in this news group about it and also the policies refered.
> I'm very confused now, becouse it is a huge amount of information and I
> wonder how unspecific it is.
> I need exactly to know which file I must change how and where exactly in the
> file. I tried many combinations, but I have a deadline on the project an I'm
> really in troubles.
> In the KB Articles are CodeGroups described and also PermissionSets 3 files
> to modify described too, but which file must be how modified is not described.
> Please help me somebody with detailled information. It really drives me to
> despair.
I' read all post in this news group about it and also the policies refered.
I'm very confused now, becouse it is a huge amount of information and I
wonder how unspecific it is.
I need exactly to know which file I must change how and where exactly in the
file. I tried many combinations, but I have a deadline on the project an I'm
really in troubles.
In the KB Articles are CodeGroups described and also PermissionSets 3 files
to modify described too, but which file must be how modified is not described.
Please help me somebody with detailled information. It really drives me to
despair.The assembly by it self works if I remove the code for access the oracle
database.
In a article is mentoined that the code inside the assembly must be modified
too. But I don't know how to transfer this to my VB Assmebly. But this is
possibly not the only wrong thing in my project.
The purpose of the assembly is localization. And in the database still
exists a table with translations used by the application used to maintain the
data. Therefore I must access the database with the assemly.
And btw. I can not understand why the samples are for file access
permissions. How many people will store f.e. currency information in a xml
file on the server? How does the data uses by RS come usual into the
database? Posibly with existing other applications? Or does RS support data
maintainance?
"Dev Main" wrote:
> I try to deploy a custom assembly which access a oracle database.
> I' read all post in this news group about it and also the policies refered.
> I'm very confused now, becouse it is a huge amount of information and I
> wonder how unspecific it is.
> I need exactly to know which file I must change how and where exactly in the
> file. I tried many combinations, but I have a deadline on the project an I'm
> really in troubles.
> In the KB Articles are CodeGroups described and also PermissionSets 3 files
> to modify described too, but which file must be how modified is not described.
> Please help me somebody with detailled information. It really drives me to
> despair.
Custom assembly security
Hi
I have a function that checks if a user is in a Windows domain group. (John
is in Administrator group?). What kind of security permissions is required
to execute my function?
<'')> _
Public Shared Function UserIsInGroup(ByVal Group As String) As String
Dim currentIdentity As WindowsIdentity = WindowsIdentity.GetCurrent()
......
End Function
Thanks
AndresAs outlined on MSDN, the GetCurrent() call requires security permissions
with the ControlPrincipal flag. See also:
*
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemSecurityPrincipalWindowsIdentityClassGetCurrentTopic.asp
*
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemSecurityPermissionsSecurityPermissionFlagClassTopic.asp
Embedded code in a report has ExecutionOnly permission (open
rssrvpolicy.config and search for the CodeGroup with the
name="Report_Expressions_Default_Permissions").
You might consider one of these:
* move the code into a custom assembly, give the custom assembly the
required permissions and assert(!) the permission in the custom assembly
methods prior to invoking .GetCurrent().
* or give the CodeGroup for report expressions more default permissions
(which is not recommended).
However, before modifying any configuration files and security permissions I
recommend to backup your current config files and read the MSDN article
about code security in RS:
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"afontan" <andres.fontan@.alcuadrado.com> wrote in message
news:eiAsm6PeEHA.2812@.tk2msftngp13.phx.gbl...
> Hi
> I have a function that checks if a user is in a Windows domain group.
(John
> is in Administrator group?). What kind of security permissions is required
> to execute my function?
> <'')> _
> Public Shared Function UserIsInGroup(ByVal Group As String) As String
> Dim currentIdentity As WindowsIdentity = WindowsIdentity.GetCurrent()
> ......
> End Function
> Thanks
> Andres
>
I have a function that checks if a user is in a Windows domain group. (John
is in Administrator group?). What kind of security permissions is required
to execute my function?
<'')> _
Public Shared Function UserIsInGroup(ByVal Group As String) As String
Dim currentIdentity As WindowsIdentity = WindowsIdentity.GetCurrent()
......
End Function
Thanks
AndresAs outlined on MSDN, the GetCurrent() call requires security permissions
with the ControlPrincipal flag. See also:
*
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemSecurityPrincipalWindowsIdentityClassGetCurrentTopic.asp
*
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemSecurityPermissionsSecurityPermissionFlagClassTopic.asp
Embedded code in a report has ExecutionOnly permission (open
rssrvpolicy.config and search for the CodeGroup with the
name="Report_Expressions_Default_Permissions").
You might consider one of these:
* move the code into a custom assembly, give the custom assembly the
required permissions and assert(!) the permission in the custom assembly
methods prior to invoking .GetCurrent().
* or give the CodeGroup for report expressions more default permissions
(which is not recommended).
However, before modifying any configuration files and security permissions I
recommend to backup your current config files and read the MSDN article
about code security in RS:
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"afontan" <andres.fontan@.alcuadrado.com> wrote in message
news:eiAsm6PeEHA.2812@.tk2msftngp13.phx.gbl...
> Hi
> I have a function that checks if a user is in a Windows domain group.
(John
> is in Administrator group?). What kind of security permissions is required
> to execute my function?
> <'')> _
> Public Shared Function UserIsInGroup(ByVal Group As String) As String
> Dim currentIdentity As WindowsIdentity = WindowsIdentity.GetCurrent()
> ......
> End Function
> Thanks
> Andres
>
Tuesday, February 14, 2012
Custom Aggrigate Functions
I was trying to calculate the Meadian of the group of values (grouped based on some columns). I could not find any standard aggrigate funtion for doing so.
It would be great if I can be able to create my own custom aggrigate functions and use the same in the RunningValues funtion.
It would be even interesting if the user can share code written across reports and report projects, with out the need to copy and paste the same custom function in all the reports.
Rich and very interesting feature would be enabling the developer to use the traditional VS2005 UI to develop his code instead of the mundane text box.
Take a look at the following blog article:
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
Another example (a moving average aggregate implementation for a chart - can be applied similarly for a table or matrix) is shown in one of the samples of the following whitepaper - search for the section about "Moving Average Calculations": http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp
-- Robert
|||
Robert, Thanks for the reply. I have done some thing similar to the one in the first link.
I am facing a problem with this kind of logic. I get the #Error in the first group footer, in second group footer I get the value, but it is the median value of the first group. It continues like this and last footer has the median value of the previous group footer (missing its own value).
To confrim how the values string is built I did reset the visibility of the hidden column and saw the values building correctly. It accumalates each value row by row and last row in the group (before the footer) has all the values to calculate median. And this holds good for the First Group also.
Am I doing some thing wrong?
The code is below:
Public AucBaseAmtString As String
'Append all the amounts to a string seperated by comma. ex: ,12,322,23,232
'Call this funtion in the hidden column of the table for each row
Public Function AccumulateAucBase(Amt As String) As String
AucBaseAmtString = AucBaseAmtString & "," & Amt
Return AucBaseAmtString
End Function
'Call the Median function in the Group Footer.
Public Function Median() As String
Dim Count As Integer
Dim AucBaseAmts() As String
'Truncate the first comma
If AucBaseAmtString <> "" Then
AucBaseAmtString = Mid(AucBaseAmtString, 2)
End If
'Get the amounts to array
AucBaseAmts = AucBaseAmtString.Split(",")
'Reset the string for the next group
AucBaseAmtString = ""
'Calculate median and return
Count = AucBaseAmts.GetLength(0)
If Count = 0 Then Return "0"
Array.Sort(AucBaseAmts)
If Count Mod 2 = 0 Then
Return CStr((CInt(AucBaseAmts((Count / 2) - 1)) + CInt(AucBaseAmts(Count / 2))) / 2)
Else
Return CStr(AucBaseAmts(Count / 2))
End If
End Function
Subscribe to:
Posts (Atom)