customers in 2003, not 2004 (was "Help with (probably) simple query")

I have 2 tables showing customers from 2003 and 2004.
They show customer number, customer name, and date attended.
I need to show what customers came in 2003, but not in 2004 by name. Problem with the customer number is that some times the customers lose their cards and get new numbers.

Any help is appreciated.You will still have difference if also the name changed!
But you could try:
SELECT NAME FROM TAB2003
MINUS
SELECT NAME FROM TAB2004;
;)

customer wants to remove power user (was "Need some advise")

Hi everyone,

I have a customer that wants to remove a power user
from her company.

She has given him the SA password (against my advise)
and he has his own user account setup in the database.

I am not sure if he has created any backdoor(s) into the
SQL box or not - I left him kinda on his own and didn't
pay too much attention to him.

I assume people have faced this problem before,
and i am looking for a best practices or "here is what we did"
post mortem of how they handled the issue.

thanks
tonyChange the sa password (you have no apps accessing via sa right)?

Check all the high permissions roles (sysadmins, securityadmins etc) and account for all the logins associated with them. Be very suspicious of any that are not domain accounts.

That would do for starters.

Future ref - always give permissions like that to domain accounts not SQL ones.|||The Flump speaks wisdom.

Also check for any new SQL Agent jobs.|||Thanks for the info -

Agent jobs was checked and double checked.

Also did a grep on the stored procs for any create users references

I am going to make a backup of the database and restore it
into a virtual machine, change the SA and the users password
without deleting the user.

I will leave the server running for a day or so looking for issues before the production server is touched.

Thanks again for ALL the hep!

take care
tony|||The Flump speaks wisdom.
Shouldn't this already be in his sig by now? :p

Customer table and customer address table

I am trying to do a select query where I select the customer
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only onehi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'=
CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
bye

Customer table and customer address table

I am trying to do a select query where I select the customer
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only one
hi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'=
CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
bye

Customer table and customer address table

I am trying to do a select query where I select the customer
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no = cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only onehi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'= CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
byesql

customer table

I have a customer table with the column "invoice_customer" (this is the customer_acccount of account where the bill is invoiced..)

so to get the invoice_customer address for the customer account - 13301

SELECT B1.address as InvoiceAddress
from CUSTOMER AS B1 , CUSTOMER AS E1
WHERE B1.customer_account = E1.invoice_customer and E1.customer_account = '13310'

i want to add the above InvoiceAddress to the query below:

select customer_account, order_no, date_req, del_address (InvoiceAddress)
from CUSTOMERS
INNER JOIN Orders on CUSTOMERS.customer_account = Orders.account
where status 'D'

How would I put the 2 togtheer...

Thanks in advance!!!please always identify exactly which table each of your columns comes from

if you don't, you're making us guess, and do extra work trying to figure out what you're doingselect E1.customer_account
, O.order_no
, O.date_req
, B1.address as InvoiceAddress
from CUSTOMERS as B1
inner
join CUSTOMERS as E1
on E1.invoice_customer = B1.customer_account
and E1.customer_account = '13310'
INNER
JOIN Orders as O
on O.account = B1.customer_account
where B1.status = 'D'|||Im sorry I will try and be more careful next time..

Thanks for your help :)

Customer Statements

I am trying to develop a "customer statements" type report.
I have no issue creating "one off single customer at a time" versions.
I have an issue reporting groups of customers at a time.
The scenario is this:
Single Parameter = All Customers in Canada (for example)
3 different data sets of info to show on
each customer, 1 for general address info, 1 for
invoices and 1 for payments. It is necessary
for them to be broken into 3 seperate datasets.
How can I have 3 different table each with the
above datasets and have them group on one page,
and then the next page is the next customer...
The issue is my grouping, where do I set to
start a new group after all 3 tables/datasets based
on a customer grouping?
I am debating trying subreports to accomplish this
but was hoping I could get away with using 3 tables.
Any suggestions?You definitely want to use subreports. Have your general in the main report.
I suggest using a list and placing the data in individual text boxes (versus
using the table control). Then have two subreports using the table control.
This is exactly what subreports are designed for. Subreports are your
friend.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:6B1D0B5C-8DE3-4507-AF7D-E4113D10236E@.microsoft.com...
>I am trying to develop a "customer statements" type report.
> I have no issue creating "one off single customer at a time" versions.
> I have an issue reporting groups of customers at a time.
> The scenario is this:
> Single Parameter = All Customers in Canada (for example)
> 3 different data sets of info to show on
> each customer, 1 for general address info, 1 for
> invoices and 1 for payments. It is necessary
> for them to be broken into 3 seperate datasets.
> How can I have 3 different table each with the
> above datasets and have them group on one page,
> and then the next page is the next customer...
> The issue is my grouping, where do I set to
> start a new group after all 3 tables/datasets based
> on a customer grouping?
> I am debating trying subreports to accomplish this
> but was hoping I could get away with using 3 tables.
> Any suggestions?|||Thanks.
I am trying to implentment your suggestion of using a list (which I have
never used before) and dropping the fields on the body. I am not seeing the
"main area" data populate though, the fields show up blank.
"Bruce L-C [MVP]" wrote:
> You definitely want to use subreports. Have your general in the main report.
> I suggest using a list and placing the data in individual text boxes (versus
> using the table control). Then have two subreports using the table control.
> This is exactly what subreports are designed for. Subreports are your
> friend.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:6B1D0B5C-8DE3-4507-AF7D-E4113D10236E@.microsoft.com...
> >I am trying to develop a "customer statements" type report.
> > I have no issue creating "one off single customer at a time" versions.
> > I have an issue reporting groups of customers at a time.
> >
> > The scenario is this:
> >
> > Single Parameter = All Customers in Canada (for example)
> > 3 different data sets of info to show on
> > each customer, 1 for general address info, 1 for
> > invoices and 1 for payments. It is necessary
> > for them to be broken into 3 seperate datasets.
> >
> > How can I have 3 different table each with the
> > above datasets and have them group on one page,
> > and then the next page is the next customer...
> > The issue is my grouping, where do I set to
> > start a new group after all 3 tables/datasets based
> > on a customer grouping?
> >
> > I am debating trying subreports to accomplish this
> > but was hoping I could get away with using 3 tables.
> >
> > Any suggestions?
>
>

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.

Customer specific data backup (conditional backup)

We are planning to serve a web application on the Internet that customers will be signing up for, to use it.

The application is using SQL server database to store customer data. Same database and same tables will be used for all customers. Tables that store customer data (not system data such as country codes etc) will have a column, say, CustomerID that will indicate the record belongs to that customer.

Now we are looking for some sort of tool to allow us to backup a single customer's data and give it to them in case they ask for their data. For example, if Customer with CustomerID = 445 wants his data, we should be able to create a sql database that contains all records from all tables that have a customerID column, where the customerID = 445. If a table does not have customerID column we want to include all rows from such table.

I was wondering if such a tool (MS or third party) exists, or shall we create our own?

I appreciate your response.

You would create an SSIS package to do something like this.

Customer Sales based on YTM dimension

Hello,

We have a cube that has customer sales data for last 5 years. Time Dimension displays the YTM hierarchy.

=> Selecting "month" is a parameter for the user on a Reporting Service report. Once he selects a month from YTM hierarchy - how to get list of "only those customers" whose sales have been continuously below say 80,000 dollars, beginning the month he selects as a "start month" until "next 6 months".

Multiple selection - not allowed. Only one month can be selected by user at any time.

Any help highly appreciated.

Thanks,

RajShri

Here's an Adventure Works example, which lists all customers with < $1000 in sales for each of 6 months, starting with the selected month (here, Jan. 2004). Note that this includes customers with no sales as well:

>>

With

Member [Measures].[Max6MonthSales] as

Max(LastPeriods(-6,

OpeningPeriod([Date].[Calendar].[Month])),

[Measures].[Internet Sales Amount])

Set [LowSalesCustomers] as

Filter([Customer].[Customer Geography].[Full Name].Members,

[Measures].[Max6MonthSales] < 1000)

select {[Measures].[Internet Sales Amount],

[Measures].[Max6MonthSales]} on 0,

[LowSalesCustomers] on 1

from [Adventure Works]

where [Date].[Calendar].[Month].&[2004]&[1]

>>

sql

Customer Registration - Temporary data storage

All
I am cross-posting, as I'm not sure if this is an issue for the data layer
or the application layer. If this is unacceptable, can someone let me know
so that I don't do this in future.
I'm using ASP .NET, framework version 1 and SQL Server 2000.
I have an ASPX page with a form for customers to register. When they click
on the "Proceed" button, they are shown a preview page of the data that they
are submitting. They can then either click on an "Edit" button to return to
the form, or click on the "Submit" button to create their account.
My question is: what is the best way to handle the data between the time
that the customer clicks on the "Proceed" button in the original form, and
the customer clicks on the final "Submit" button?
The two issues that I see are concurrency (the email address must be unique
within the database table), and performance. I was told by a colleague that
using a seperate database table to store the data from the original form
submission and then selecting from this table into the Customer table when
the customer clicks on the final "Submit" button poses less of a risk of
locking the Customer table than inserting directly into the Customer table.
Is this correct? Should I be using the database to store temporary data, or
use the built-in ASP .NET state maintenance objects?
Many thanks in advance for any insight.
Regards
Chris Marsh
Chris,
See in-line comments.
-- Bill
"Chris" <cjm@.newsgroup.nospam> wrote in message
news:u8lk0LNMHHA.1240@.TK2MSFTNGP03.phx.gbl...
> All
> I am cross-posting, as I'm not sure if this is an issue for the data layer
> or the application layer. If this is unacceptable, can someone let me know
> so that I don't do this in future.
> I'm using ASP .NET, framework version 1 and SQL Server 2000.
> I have an ASPX page with a form for customers to register. When they click
> on the "Proceed" button, they are shown a preview page of the data that
> they are submitting. They can then either click on an "Edit" button to
> return to the form, or click on the "Submit" button to create their
> account.
> My question is: what is the best way to handle the data between the time
> that the customer clicks on the "Proceed" button in the original form, and
> the customer clicks on the final "Submit" button?
> The two issues that I see are concurrency (the email address must be
> unique within the database table), and performance. I was told by a
> colleague that using a seperate database table to store the data from the
> original form submission and then selecting from this table into the
> Customer table when the customer clicks on the final "Submit" button poses
> less of a risk of locking the Customer table than inserting directly into
> the Customer table.
I agree in principle. This all falls under the category of session state. In
addition to the customer entered data, you could also keep other session
information in that same table: IP address, affiliate codes, session
duration, click-stream.
If your website is successful, this information will become valuable in
analyzing the user experience and calculating abandon rates.

> Is this correct? Should I be using the database to store temporary data,
> or use the built-in ASP .NET state maintenance objects?
If you use .NET there are special considerations if you are going to farm
your web tier: sicky ip, etc.

> Many thanks in advance for any insight.
> --
> Regards
> Chris Marsh
>
|||Bill
Firstly, thanks a lot for the response.
[..]

> I agree in principle. This all falls under the category of session state.
> In addition to the customer entered data, you could also keep other
> session information in that same table: IP address, affiliate codes,
> session duration, click-stream.
> If your website is successful, this information will become valuable in
> analyzing the user experience and calculating abandon rates.
Session management in a broader sense is beyond the scope of this particular
task, so although I agree totally that this data is important, it's
irrelevant to my query.
Since submitting my initial query, I've realised that there is a gaping
(non-technical) flaw in storing this data at all. If the user closes the
browser before completing the process, they have not agreed to the terms and
conditions and privacy policy. Yet, we would be storing personally
identifiable information about this user. I believe that this contravenes
the Data Protection Act in the UK.

> If you use .NET there are special considerations if you are going to farm
> your web tier: sicky ip, etc.
Indeed. Currently the platform I'm working on is a single server, so session
state management is in-process.
Thanks again for getting back to me.
Regards
Chris Marsh

Customer Registration - Temporary data storage

All
I am cross-posting, as I'm not sure if this is an issue for the data layer
or the application layer. If this is unacceptable, can someone let me know
so that I don't do this in future.
I'm using ASP .NET, framework version 1 and SQL Server 2000.
I have an ASPX page with a form for customers to register. When they click
on the "Proceed" button, they are shown a preview page of the data that they
are submitting. They can then either click on an "Edit" button to return to
the form, or click on the "Submit" button to create their account.
My question is: what is the best way to handle the data between the time
that the customer clicks on the "Proceed" button in the original form, and
the customer clicks on the final "Submit" button?
The two issues that I see are concurrency (the email address must be unique
within the database table), and performance. I was told by a colleague that
using a seperate database table to store the data from the original form
submission and then selecting from this table into the Customer table when
the customer clicks on the final "Submit" button poses less of a risk of
locking the Customer table than inserting directly into the Customer table.
Is this correct? Should I be using the database to store temporary data, or
use the built-in ASP .NET state maintenance objects?
Many thanks in advance for any insight.
--
Regards
Chris MarshChris,
See in-line comments.
-- Bill
"Chris" <cjm@.newsgroup.nospam> wrote in message
news:u8lk0LNMHHA.1240@.TK2MSFTNGP03.phx.gbl...
> All
> I am cross-posting, as I'm not sure if this is an issue for the data layer
> or the application layer. If this is unacceptable, can someone let me know
> so that I don't do this in future.
> I'm using ASP .NET, framework version 1 and SQL Server 2000.
> I have an ASPX page with a form for customers to register. When they click
> on the "Proceed" button, they are shown a preview page of the data that
> they are submitting. They can then either click on an "Edit" button to
> return to the form, or click on the "Submit" button to create their
> account.
> My question is: what is the best way to handle the data between the time
> that the customer clicks on the "Proceed" button in the original form, and
> the customer clicks on the final "Submit" button?
> The two issues that I see are concurrency (the email address must be
> unique within the database table), and performance. I was told by a
> colleague that using a seperate database table to store the data from the
> original form submission and then selecting from this table into the
> Customer table when the customer clicks on the final "Submit" button poses
> less of a risk of locking the Customer table than inserting directly into
> the Customer table.
I agree in principle. This all falls under the category of session state. In
addition to the customer entered data, you could also keep other session
information in that same table: IP address, affiliate codes, session
duration, click-stream.
If your website is successful, this information will become valuable in
analyzing the user experience and calculating abandon rates.
> Is this correct? Should I be using the database to store temporary data,
> or use the built-in ASP .NET state maintenance objects?
If you use .NET there are special considerations if you are going to farm
your web tier: sicky ip, etc.
> Many thanks in advance for any insight.
> --
> Regards
> Chris Marsh
>|||Bill
Firstly, thanks a lot for the response.
[..]
>> The two issues that I see are concurrency (the email address must be
>> unique within the database table), and performance. I was told by a
>> colleague that using a seperate database table to store the data from the
>> original form submission and then selecting from this table into the
>> Customer table when the customer clicks on the final "Submit" button
>> poses less of a risk of locking the Customer table than inserting
>> directly into the Customer table.
> I agree in principle. This all falls under the category of session state.
> In addition to the customer entered data, you could also keep other
> session information in that same table: IP address, affiliate codes,
> session duration, click-stream.
> If your website is successful, this information will become valuable in
> analyzing the user experience and calculating abandon rates.
Session management in a broader sense is beyond the scope of this particular
task, so although I agree totally that this data is important, it's
irrelevant to my query.
Since submitting my initial query, I've realised that there is a gaping
(non-technical) flaw in storing this data at all. If the user closes the
browser before completing the process, they have not agreed to the terms and
conditions and privacy policy. Yet, we would be storing personally
identifiable information about this user. I believe that this contravenes
the Data Protection Act in the UK.
>> Is this correct? Should I be using the database to store temporary data,
>> or use the built-in ASP .NET state maintenance objects?
> If you use .NET there are special considerations if you are going to farm
> your web tier: sicky ip, etc.
Indeed. Currently the platform I'm working on is a single server, so session
state management is in-process.
Thanks again for getting back to me.
--
Regards
Chris Marsh

Customer Registration - Temporary data storage

All
I am cross-posting, as I'm not sure if this is an issue for the data layer
or the application layer. If this is unacceptable, can someone let me know
so that I don't do this in future.
I'm using ASP .NET, framework version 1 and SQL Server 2000.
I have an ASPX page with a form for customers to register. When they click
on the "Proceed" button, they are shown a preview page of the data that they
are submitting. They can then either click on an "Edit" button to return to
the form, or click on the "Submit" button to create their account.
My question is: what is the best way to handle the data between the time
that the customer clicks on the "Proceed" button in the original form, and
the customer clicks on the final "Submit" button?
The two issues that I see are concurrency (the email address must be unique
within the database table), and performance. I was told by a colleague that
using a seperate database table to store the data from the original form
submission and then selecting from this table into the Customer table when
the customer clicks on the final "Submit" button poses less of a risk of
locking the Customer table than inserting directly into the Customer table.
Is this correct? Should I be using the database to store temporary data, or
use the built-in ASP .NET state maintenance objects?
Many thanks in advance for any insight.
Regards
Chris MarshChris,
See in-line comments.
-- Bill
"Chris" <cjm@.newsgroup.nospam> wrote in message
news:u8lk0LNMHHA.1240@.TK2MSFTNGP03.phx.gbl...
> All
> I am cross-posting, as I'm not sure if this is an issue for the data layer
> or the application layer. If this is unacceptable, can someone let me know
> so that I don't do this in future.
> I'm using ASP .NET, framework version 1 and SQL Server 2000.
> I have an ASPX page with a form for customers to register. When they click
> on the "Proceed" button, they are shown a preview page of the data that
> they are submitting. They can then either click on an "Edit" button to
> return to the form, or click on the "Submit" button to create their
> account.
> My question is: what is the best way to handle the data between the time
> that the customer clicks on the "Proceed" button in the original form, and
> the customer clicks on the final "Submit" button?
> The two issues that I see are concurrency (the email address must be
> unique within the database table), and performance. I was told by a
> colleague that using a seperate database table to store the data from the
> original form submission and then selecting from this table into the
> Customer table when the customer clicks on the final "Submit" button poses
> less of a risk of locking the Customer table than inserting directly into
> the Customer table.
I agree in principle. This all falls under the category of session state. In
addition to the customer entered data, you could also keep other session
information in that same table: IP address, affiliate codes, session
duration, click-stream.
If your website is successful, this information will become valuable in
analyzing the user experience and calculating abandon rates.

> Is this correct? Should I be using the database to store temporary data,
> or use the built-in ASP .NET state maintenance objects?
If you use .NET there are special considerations if you are going to farm
your web tier: sicky ip, etc.

> Many thanks in advance for any insight.
> --
> Regards
> Chris Marsh
>|||Bill
Firstly, thanks a lot for the response.
[..]

> I agree in principle. This all falls under the category of session state.
> In addition to the customer entered data, you could also keep other
> session information in that same table: IP address, affiliate codes,
> session duration, click-stream.
> If your website is successful, this information will become valuable in
> analyzing the user experience and calculating abandon rates.
Session management in a broader sense is beyond the scope of this particular
task, so although I agree totally that this data is important, it's
irrelevant to my query.
Since submitting my initial query, I've realised that there is a gaping
(non-technical) flaw in storing this data at all. If the user closes the
browser before completing the process, they have not agreed to the terms and
conditions and privacy policy. Yet, we would be storing personally
identifiable information about this user. I believe that this contravenes
the Data Protection Act in the UK.

> If you use .NET there are special considerations if you are going to farm
> your web tier: sicky ip, etc.
Indeed. Currently the platform I'm working on is a single server, so session
state management is in-process.
Thanks again for getting back to me.
Regards
Chris Marsh

Customer Profitability

Customer Profitability

Hi How can I use MS SQL server 2005 analysis services for measuring the Customer Profitability Index

Pl suggest if you have any Data model and any special algorithm that can be used to measure the Customer Profitability

Thanks In Advance

Rajesh Ladda

Analysis Services is a product that serves as a platform for your analytical applications. Using Analysis Services you are able to model complex business rules of your organization.

Your question is a bit vague. If you are looking for examples of how to build multidimensional model and then use it to analyze your data, I suggest you first start from going through tutorial that comes with Analysis Services installation. Then take a look at the sample project: AdventureWorks and see how it implements calculations.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Customer Passwords in SQL Server

I currently have a table with user passwords
Ideally I want to encrypt these passwords...
I have read in places to use pwdencrypt and pwdcompare as this is a SQL
function. But I have also read the microsoft may update this Hash function,
and may lead to me losing all of my passwords...
Can anybody assist in the correct way to store passwords in a SQL table...
Kind RegardsWhat is your application programmed in? The asp.net security
whitepaper has a section on how to encrypt, salt and store information
securely in a SQL Server database. It's available at
http://www.microsoft.com/downloads/...ReleaseID=44047
--Mary
On Wed, 1 Sep 2004 01:21:05 -0700, "Jay_Reborn"
<JayReborn@.discussions.microsoft.com> wrote:

>I currently have a table with user passwords
>Ideally I want to encrypt these passwords...
>I have read in places to use pwdencrypt and pwdcompare as this is a SQL
>function. But I have also read the microsoft may update this Hash function,
>and may lead to me losing all of my passwords...
>Can anybody assist in the correct way to store passwords in a SQL table...
>Kind Regards
>|||If you are using ASP.NET, this article might be helpful to you:
rl]
erik perez
[url]www.solien.com" target="_blank">http://www.dotnetjunkies.com/Tutori...]www.solien.com
"Jay_Reborn" <JayReborn@.discussions.microsoft.com> wrote in message
news:D673C5FE-DB92-4E8B-A598-F843AEE40176@.microsoft.com...
> I currently have a table with user passwords
> Ideally I want to encrypt these passwords...
> I have read in places to use pwdencrypt and pwdcompare as this is a SQL
> function. But I have also read the microsoft may update this Hash
function,
> and may lead to me losing all of my passwords...
> Can anybody assist in the correct way to store passwords in a SQL table...
> Kind Regards
>sql

Customer Interface for parameters

I found many hints that customer parameter interfaces are a possible solution
for several problems.
But I can't find one piece about how this can be done. Not in the
documentation, not in this news group and not in google. I'm really investet
many time, but don't find anything.
Can one give me a hint where I can find information about this?Here's the design doc I wrote for ours:
Reporting Phase II Design
A Dynamic User Interface for Report Navigation and Runtime Parameter Gathering
Introduction
Having a dynamic user interface to present reports to users and gather
runtime parameter values originates from a singular thought: report
customization. We desire that reports be developed by end user
administrators in addition to professional services and in-house developers.
By providing this support we must provide a means by which a report may be
deployed into the production environment and then appear in the application
without any user interface development activity on our part. The best way to
support this desire is to build our user interface such that it does not rely
on specific knowledge of any one report but rather is built with awareness
that reports are present and that they gather runtime parameters. In this
fashion, report developers are free to upload new reports or modify existing
reports independent of a release cycle. The following sections will
illustrate how we may expose this capability from within our application.
Report Navigation
The first challenge regarding our dynamic user interface is to present the
reports so a user may choose among them. Our current user interface design
calls for a hierarchical folder paradigm that contains and organizes all
reports. It begins with a root node labeled â'My Reportsâ' that appears in the
â'Trackingâ' page. This folder can contain any number of sub folders, which
serve to categorize all reports and will initially contain the folders â'Email
Reportsâ', â'Subscriber Reportsâ', â'Admin Reportsâ', and â'Custom Reportsâ'. It is
not clear whether the folder â'My Reportsâ' will ever contain reports
themselves and it is presumed these first level folders will contain reports
and perhaps additional folders.
The Report Services component, created to serve the user interface and other
consumers, provides a method GetReports() that takes no input parameters and
returns to the caller a data table containing all of the reports that the
calling user is authorized to view. This data table will also contain the
hierarchy of folders such that the consumer may illustrate reports in
accordance with the prescribed hierarchy.
The data table returned by GetReports() will contain the following items:
â?¢ ID â' a unique identifier (GUID) that uniquely identifies the report item
â?¢ Type â' an enumeration describing the nature of the report item. This is
supported by the public enum ReportItemType containing Folder and Report
(this may be expanded to include other types as demand warrants.
â?¢ Name â' the plain text name of the report, suitable for illustration in the
user interface. (This value originates from the â'nameâ' property of a report
on the report server, which gets its value from the â'nameâ' property of the
report itself, which is established at design time. It is therefore
necessary for developers to assign a meaningful name at design time,
presumably assigned by Product Management at the time of envisioning.)
â?¢ Description â' A plain text description that serves to illustrate the
content/functionality of the report in greater detail than â'Nameâ' (above).
It may be useful perhaps as tool-tip text given the folder paradigm. This
value originates from the â'descriptionâ' property of the report on the report
server, which gets its value from the â'descriptionâ' property of the report
itself, which is established at design time. It is therefore necessary for
developers to assign a meaningful value during the development process;
presumably this text will originate from Product Management.
â?¢ Path â' A text string that contains the fully qualified path to the report
on the report server. This item equally serves as a unique identifier for a
report, describes the location of the report within the folder hierarchy, and
is used as in input parameter by the RenderReport() method that identifies
the report to be rendered.
â?¢ Parent â' a unique identifier (GUID) that points to a report item that
contains this report item. If null, it indicates the root of the hierarchy.
It is presumed that our user interface will use this data table to construct
the tree view that is illustrated in the user interface document. Further,
all attempts will be made to provide all of the information desired by the
user interface team and in a structure that is most suitable to their
consumption.
Runtime Parameter Value Gathering
While it is necessary to provide a listing of reports suitable for display,
the greater challenge comes in gathering the runtime parameter values that
will be used by the report to scope its contents and perhaps drive the order
in which result rows appear. To support consumers in the gathering of
runtime parameter values, the Report Services component exposes a method
GetReportParameters(). This method takes one parameter (string reportPath)
that is acquired through the â'Pathâ' item returned by GetReports() as
described above and returns a collection containing the items described
below. (Note: the values these items contain are a function of developer
action at design time, primarily via the settings made in the â'Report
Parametersâ' dialog of the IDE.
â?¢ Bool AllowBlank â' Determines whether a parameter value can be an empty
string. This applies only to parameters of type string (identified by
ETParameterTypeEnum Type, below)
â?¢ String[] DefaultValues â' A string array that typically contains one
element whose value indicates the default for this parameter. The user
interface should populate the appropriate control with this value. (Note: in
the future we may choose to support multi-value parameters; perhaps for use
with the SQL IN operator. If and when this becomes true, the potential for
multiple default values becomes true, hence it is declared as an array.)
â?¢ Bool DefaultValuesQueryBased â' Indicates whether DefaultValues (above) is
populated by an expression (false) or by a database query (true).
â?¢ Bool DefaultValuesQueryBasedSpecified â' Indicates whether a determination
can be made at runtime to assign DefaultValuesQueryBased a value. If this
value is false the origin of the default value(s) for this parameter is
indeterminate.
â?¢ String[] Dependencies â' If this parameterâ's default or valid values are
based on a parameterized query and that queryâ's parameter(s) have
indeterminate values, then this array will contain the names of those
parameters. In other words, if this property is not null, you will need to
gather the values for the parameters named in this property, make a
subsequent call to GetReportParameters, and then gather the value for this
parameter. This is detailed in the following section.
â?¢ String Name â' The internal name of this parameter as used n the report
itself. It does not represent a value suitable for display in the user
interface. Instead, use the property â'Promptâ', which is described below.
â?¢ Bool Nullable â' Indicates that a valid value for this parameter can be
null. A combination of Nullable and AllowBlank (both are true) indicate that
this parameter is â'optionalâ' (a user need not specify a value).
â?¢ String NullableSpecified â' Indicates whether Nullable (above) is
indeterminate at runtime (if false). If it makes you feel any better, you
may include this property (along with Nullable and AllowBlank) when
determining whether a given parameter is optional or not. Our stored
procedure standards are such that every SPROC should be written as if all
parameters (with the exception of MemberID) are optional, so you shouldnâ't
encounter any runtime errors.
â?¢ String Prompt â' A string value that names the parameter accordingly.
Unlike â'Nameâ' (above) this value is suitable for display in the user
interface and should be used to label the control accordingly.
â?¢ Bool PromptUser â' This Boolean indicates whether a given parameter should
appear in the dialog. This value is determined at design time and will be
false if and only if there is an empty string defined for the â'Prompt:â' text
in the â'Report Parametersâ' dialog.
â?¢ Bool PromptUserSpecified â' Determines whether PromptUser (above) is
indeterminate at runtime.
â?¢ ETParameterStateEnum State â' This property reports the state of this
parameter. The enumeration is as follows: HasValidValue, MissingValidValue,
HasOutstandingDependencies, or DynamicValuesUnavailable. At first blush it
would appear as if this property can be used to determine whether nor not to
present a given parameter in the dialog (State = MissingValidValue). If a
parameter has a default specified for it, this property will have a State of
HasValidValue although the user has made no entry. However, using this
property to validate a parameter value, through a subsequent call to
GetReportParameters is perfectly allowable.
â?¢ Bool StateSpecified â' Indicates whether State (above) has a value based on
the most recent call to GetReportParameters().
â?¢ ETParameterTypeEnum Type â' Indicates the underlying type of the parameter
and can be one of: Boolean, DateTime, Float, Integer, or String. You will
use this property to determine the control type (and perhaps additional
formatting) of the control you will add to the page. Note: when working with
the Reporting Service component you will always use the string representation
of a parameter value. This string representation is translated under the
covers by SQL Reporting Services to its native type.
â?¢ TypeSpecified â' Indicates whether Type (above) is indeterminate at runtime.
â?¢ ETValidValues ValidValues â' This collection if not null is a list of valid
value for this parameter. The contents of this collection is determined by a
developer at design time. You may use the collection to only to validate a
value specified by a user but you should use the contents to populate a
dropdown list from which a user may select. The most powerful feature of
this property is its ability to support a lookup. The type ETValidValue is a
name/value pair, so for example instead of prompting the user to enter an
email ID, you can populate the dropdown list with email names and then assign
the associated email ID to the ETReportParameterValues collection for
submission to the componentâ's RenderReport method.
â?¢ Bool ValidValuesQueryBased â' Indicates whether ValidValues (above) are the
result of a static list (false) or a database query (true).
â?¢ Bool ValidValuesQueryBasedSpecified â' Indicates whether
ValidValuesQueryBased (above) is indeterminate at runtime (false).
The intent is for the user interface to use this collection to draw a page
containing user prompts and appropriate controls such that the user can
specify values as required. The order of the parameters as they appear in
the collection is the order in which they are displayed in the â'Report
Parametersâ' dialog in the IDE. In other words, the developer has the
opportunity at design time to specify the order in which they appear in the
user dialog (presuming a two column Prompt â' Value layout).
It is suggested that the user interface enumerate over this collection and
create controls according to the values of the collectionâ's properties. The
following steps serve to illustrate this approach:
1. Look only for parameters whose â'PromptUserâ' property is true. (Those
with a PromptUser property of false are used for other purposes and will
ALWAYS have a value resulting from a default value specification.)
2. Look for parameters whoâ's Dependencies are not null. If you find any,
this is indicative of a situation where the parameterâ's DefaultValues or
ValidValues are query based and that query requires an input parameter. You
will need to consult the array elements, take the name of each parameter
contained in an element, and use the following logic to establish a page of
controls to obtain values for the dependent parameters. Once you have these
values, you will make a subsequent call to GetReportParameters() providing
the values you have collected thus far. Then, you will construct a page of
[now] independent parameters seeking their values. Once you have these
values you may proceed with RenderReport(). Note: there is at most one level
of indirection here.
3. Use the â'Promptâ' propertyâ's value as the label text for your control.
(Depending on how you do this, you may have to wait until after the control
type is determined below.)
4. If ValidValues has zero elements, determine the type of control to use
from the Type property.
a. If Type is â'Stringâ' we can presume a Textbox control.
b. If Type is â'DateTimeâ' we can presume a Calendar control.
c. If Type is â'Booleanâ' we can presume a CheckBox control.
d. If Type is â'Integerâ' we can presume a Textbox control with integer
formatting.
e. If Type is â'Floatâ' we can presume a Textbox control with floating point
decimal formatting.
5. If ValidValues has one element, this is indicative of a rare but
potential condition where there is one and only one valid value. Since there
is only one you may consider not prompting the user at all and just use the
value supplied.
6. If ValidValues has two or more elements, the report developer is
indicating that he/she intends on the user picking a value from a list, you
should use a dropdown list control.
a. Determine if the Label property of the ETValidValue collection items is
blank.
i. If Label is NOT blank, then we have a lookup; we will display a
â'externalâ' value such as List Name, and use its corresponding â'internalâ'
value (List ID) as the parameter value. Populate the list itemâ's â'textâ'
property with the Label property of the ETValidValue and the list itemâ's
â'valueâ' property with the â'Valueâ' property of ETValidValue.
ii. If Label IS blank, then we have a simple lookup (such as a list of
dates). Populate the list boxâ's list itemâ's properties â'textâ' and â'valueâ'
with the â'Valueâ' property of ETValidValue.
7. If DefaultValues contains 1 element then use the assign the value of
DefaultValues[0] to the control in accordance with the controlâ's type, thus
establishing the default value for the control.
Future Consideration
While the design stated above is certainly dynamic, it not does not consider
all of the possibilities. We have not yet entertained for example that
report content is ordered in accordance with a runtime parameter. If it was,
we would simply gather the parameter like any other; however, it would be
used in an ORDER BY clause as opposed to a WHERE clause. Although similar in
nature, we would most likely want to visually separate those parameters that
manage scope versus those that manage ordering. SQL Reporting Services does
not directly support that segregation (through an internal property of a
report parameter) so if desires, we would have to implement our own
methodology.
Date ranges (or any range parameters for that matter) are quite common;
however we have no support for multiple ranges specified against a single
value. Should we choose to support this feature we would need to determine a
way to allow the user to enter multiple ranges, pass them to the report
renderer, which passes them along to the stored procedure that delivers the
result set. There is some hope for this support from within SQL Reporting
Services through the MultiValue property of its parameters collection but we
would still need to work out a method by which these values are passed on to
the SPROCS.
For now, user are limited to specifying a single value or (through using two
report parameters) a range of values. I think it will be necessary for us to
support multi value parameters in conjunction with the IN operator of T-SQL.
Quite frequently a user is going to want a report that contains more than
â'one itemâ'. How we will do this is not yet known as SQL Reporting Services
does not yet appear to support Multi-Value.
Another consideration, which is similar in nature to MultiValue is an
â'all-butâ' paradigm. Rather than constructing the â'positiveâ' case using
parameter values (range or multivalue) the user is constructing a WHERE NOT
clause.
In a report scenario that involves a heavy comparison by date, users
frequently desire what I refer to as date plus or minus where the user is
specifying a date and then a span of time either before or after the
specified date. This is nothing more than an alternate date range so we
really have no issue regarding our reports or stored procedures but rather it
becomes an issue for the user-interface to illustrate the parameters in this
fashion. This too is probably not too much of an endeavor however we have no
way at present for the report developer to indicate the type of control to
gather a parameter value by nor do we have support to communicate this fact
to the consumer through the GetParameters() method. Of course, one could
construct the MOADC (mother of all date controls) that allowed the user to
specify a range with a beginning and ending date or a date plus or minus a
day, week, month, quarter, half-year, year etc. but then we would have no way
to associate two date parameters into a range either.
"Dev Main" wrote:
> I found many hints that customer parameter interfaces are a possible solution
> for several problems.
> But I can't find one piece about how this can be done. Not in the
> documentation, not in this news group and not in google. I'm really investet
> many time, but don't find anything.
> Can one give me a hint where I can find information about this?|||@. Herman K: Thank you very much! This is real help and furthermore an
valuable documentation.
My misunderstanding was, that I thought I can replace the parameter section
in a report with something like a custom control. But this seems to be
impossible.
"Herman K" wrote:
> Here's the design doc I wrote for ours:
> Reporting Phase II Design
> A Dynamic User Interface for Report Navigation and Runtime Parameter Gathering
> Introduction
> Having a dynamic user interface to present reports to users and gather
> runtime parameter values originates from a singular thought: report
> customization. We desire that reports be developed by end user
> administrators in addition to professional services and in-house developers.
> By providing this support we must provide a means by which a report may be
> deployed into the production environment and then appear in the application
> without any user interface development activity on our part. The best way to
> support this desire is to build our user interface such that it does not rely
> on specific knowledge of any one report but rather is built with awareness
> that reports are present and that they gather runtime parameters. In this
> fashion, report developers are free to upload new reports or modify existing
> reports independent of a release cycle. The following sections will
> illustrate how we may expose this capability from within our application.
> Report Navigation
> The first challenge regarding our dynamic user interface is to present the
> reports so a user may choose among them. Our current user interface design
> calls for a hierarchical folder paradigm that contains and organizes all
> reports. It begins with a root node labeled â'My Reportsâ' that appears in the
> â'Trackingâ' page. This folder can contain any number of sub folders, which
> serve to categorize all reports and will initially contain the folders â'Email
> Reportsâ', â'Subscriber Reportsâ', â'Admin Reportsâ', and â'Custom Reportsâ'. It is
> not clear whether the folder â'My Reportsâ' will ever contain reports
> themselves and it is presumed these first level folders will contain reports
> and perhaps additional folders.
> The Report Services component, created to serve the user interface and other
> consumers, provides a method GetReports() that takes no input parameters and
> returns to the caller a data table containing all of the reports that the
> calling user is authorized to view. This data table will also contain the
> hierarchy of folders such that the consumer may illustrate reports in
> accordance with the prescribed hierarchy.
> The data table returned by GetReports() will contain the following items:
> â?¢ ID â' a unique identifier (GUID) that uniquely identifies the report item
> â?¢ Type â' an enumeration describing the nature of the report item. This is
> supported by the public enum ReportItemType containing Folder and Report
> (this may be expanded to include other types as demand warrants.
> â?¢ Name â' the plain text name of the report, suitable for illustration in the
> user interface. (This value originates from the â'nameâ' property of a report
> on the report server, which gets its value from the â'nameâ' property of the
> report itself, which is established at design time. It is therefore
> necessary for developers to assign a meaningful name at design time,
> presumably assigned by Product Management at the time of envisioning.)
> â?¢ Description â' A plain text description that serves to illustrate the
> content/functionality of the report in greater detail than â'Nameâ' (above).
> It may be useful perhaps as tool-tip text given the folder paradigm. This
> value originates from the â'descriptionâ' property of the report on the report
> server, which gets its value from the â'descriptionâ' property of the report
> itself, which is established at design time. It is therefore necessary for
> developers to assign a meaningful value during the development process;
> presumably this text will originate from Product Management.
> â?¢ Path â' A text string that contains the fully qualified path to the report
> on the report server. This item equally serves as a unique identifier for a
> report, describes the location of the report within the folder hierarchy, and
> is used as in input parameter by the RenderReport() method that identifies
> the report to be rendered.
> â?¢ Parent â' a unique identifier (GUID) that points to a report item that
> contains this report item. If null, it indicates the root of the hierarchy.
> It is presumed that our user interface will use this data table to construct
> the tree view that is illustrated in the user interface document. Further,
> all attempts will be made to provide all of the information desired by the
> user interface team and in a structure that is most suitable to their
> consumption.
> Runtime Parameter Value Gathering
> While it is necessary to provide a listing of reports suitable for display,
> the greater challenge comes in gathering the runtime parameter values that
> will be used by the report to scope its contents and perhaps drive the order
> in which result rows appear. To support consumers in the gathering of
> runtime parameter values, the Report Services component exposes a method
> GetReportParameters(). This method takes one parameter (string reportPath)
> that is acquired through the â'Pathâ' item returned by GetReports() as
> described above and returns a collection containing the items described
> below. (Note: the values these items contain are a function of developer
> action at design time, primarily via the settings made in the â'Report
> Parametersâ' dialog of the IDE.
> â?¢ Bool AllowBlank â' Determines whether a parameter value can be an empty
> string. This applies only to parameters of type string (identified by
> ETParameterTypeEnum Type, below)
> â?¢ String[] DefaultValues â' A string array that typically contains one
> element whose value indicates the default for this parameter. The user
> interface should populate the appropriate control with this value. (Note: in
> the future we may choose to support multi-value parameters; perhaps for use
> with the SQL IN operator. If and when this becomes true, the potential for
> multiple default values becomes true, hence it is declared as an array.)
> â?¢ Bool DefaultValuesQueryBased â' Indicates whether DefaultValues (above) is
> populated by an expression (false) or by a database query (true).
> â?¢ Bool DefaultValuesQueryBasedSpecified â' Indicates whether a determination
> can be made at runtime to assign DefaultValuesQueryBased a value. If this
> value is false the origin of the default value(s) for this parameter is
> indeterminate.
> â?¢ String[] Dependencies â' If this parameterâ's default or valid values are
> based on a parameterized query and that queryâ's parameter(s) have
> indeterminate values, then this array will contain the names of those
> parameters. In other words, if this property is not null, you will need to
> gather the values for the parameters named in this property, make a
> subsequent call to GetReportParameters, and then gather the value for this
> parameter. This is detailed in the following section.
> â?¢ String Name â' The internal name of this parameter as used n the report
> itself. It does not represent a value suitable for display in the user
> interface. Instead, use the property â'Promptâ', which is described below.
> â?¢ Bool Nullable â' Indicates that a valid value for this parameter can be
> null. A combination of Nullable and AllowBlank (both are true) indicate that
> this parameter is â'optionalâ' (a user need not specify a value).
> â?¢ String NullableSpecified â' Indicates whether Nullable (above) is
> indeterminate at runtime (if false). If it makes you feel any better, you
> may include this property (along with Nullable and AllowBlank) when
> determining whether a given parameter is optional or not. Our stored
> procedure standards are such that every SPROC should be written as if all
> parameters (with the exception of MemberID) are optional, so you shouldnâ't
> encounter any runtime errors.
> â?¢ String Prompt â' A string value that names the parameter accordingly.
> Unlike â'Nameâ' (above) this value is suitable for display in the user
> interface and should be used to label the control accordingly.
> â?¢ Bool PromptUser â' This Boolean indicates whether a given parameter should
> appear in the dialog. This value is determined at design time and will be
> false if and only if there is an empty string defined for the â'Prompt:â' text
> in the â'Report Parametersâ' dialog.
> â?¢ Bool PromptUserSpecified â' Determines whether PromptUser (above) is
> indeterminate at runtime.
> â?¢ ETParameterStateEnum State â' This property reports the state of this
> parameter. The enumeration is as follows: HasValidValue, MissingValidValue,
> HasOutstandingDependencies, or DynamicValuesUnavailable. At first blush it
> would appear as if this property can be used to determine whether nor not to
> present a given parameter in the dialog (State = MissingValidValue). If a
> parameter has a default specified for it, this property will have a State of
> HasValidValue although the user has made no entry. However, using this
> property to validate a parameter value, through a subsequent call to
> GetReportParameters is perfectly allowable.
> â?¢ Bool StateSpecified â' Indicates whether State (above) has a value based on
> the most recent call to GetReportParameters().
> â?¢ ETParameterTypeEnum Type â' Indicates the underlying type of the parameter
> and can be one of: Boolean, DateTime, Float, Integer, or String. You will
> use this property to determine the control type (and perhaps additional
> formatting) of the control you will add to the page. Note: when working with
> the Reporting Service component you will always use the string representation
> of a parameter value. This string representation is translated under the
> covers by SQL Reporting Services to its native type.
> â?¢ TypeSpecified â' Indicates whether Type (above) is indeterminate at runtime.
> â?¢ ETValidValues ValidValues â' This collection if not null is a list of valid
> value for this parameter. The contents of this collection is determined by a
> developer at design time. You may use the collection to only to validate a
> value specified by a user but you should use the contents to populate a
> dropdown list from which a user may select. The most powerful feature of
> this property is its ability to support a lookup. The type ETValidValue is a
> name/value pair, so for example instead of prompting the user to enter an
> email ID, you can populate the dropdown list with email names and then assign
> the associated email ID to the ETReportParameterValues collection for
> submission to the componentâ's RenderReport method.
> â?¢ Bool ValidValuesQueryBased â' Indicates whether ValidValues (above) are the
> result of a static list (false) or a database query (true).
> â?¢ Bool ValidValuesQueryBasedSpecified â' Indicates whether
> ValidValuesQueryBased (above) is indeterminate at runtime (false).
> The intent is for the user interface to use this collection to draw a page
> containing user prompts and appropriate controls such that the user can
> specify values as required. The order of the parameters as they appear in
> the collection is the order in which they are displayed in the â'Report
> Parametersâ' dialog in the IDE. In other words, the developer has the
> opportunity at design time to specify the order in which they appear in the
> user dialog (presuming a two column Prompt â' Value layout).
> It is suggested that the user interface enumerate over this collection and
> create controls according to the values of the collectionâ's properties. The
> following steps serve to illustrate this approach:
> 1. Look only for parameters whose â'PromptUserâ' property is true. (Those
> with a PromptUser property of false are used for other purposes and will
> ALWAYS have a value resulting from a default value specification.)
> 2. Look for parameters whoâ's Dependencies are not null. If you find any,
> this is indicative of a situation where the parameterâ's DefaultValues or
> ValidValues are query based and that query requires an input parameter. You
> will need to consult the array elements, take the name of each parameter
> contained in an element, and use the following logic to establish a page of
> controls to obtain values for the dependent parameters. Once you have these
> values, you will make a subsequent call to GetReportParameters() providing
> the values you have collected thus far. Then, you will construct a page of
> [now] independent parameters seeking their values. Once you have these
> values you may proceed with RenderReport(). Note: there is at most one level
> of indirection here.
> 3. Use the â'Promptâ' propertyâ's value as the label text for your control.
> (Depending on how you do this, you may have to wait until after the control
> type is determined below.)
> 4. If ValidValues has zero elements, determine the type of control to use
> from the Type property.
> a. If Type is â'Stringâ' we can presume a Textbox control.
> b. If Type is â'DateTimeâ' we can presume a Calendar control.
> c. If Type is â'Booleanâ' we can presume a CheckBox control.
> d. If Type is â'Integerâ' we can presume a Textbox control with integer
> formatting.
> e. If Type is â'Floatâ' we can presume a Textbox control with floating point
> decimal formatting.
> 5. If ValidValues has one element, this is indicative of a rare but
> potential condition where there is one and only one valid value. Since there
> is only one you may consider not prompting the user at all and just use the
> value supplied.
> 6. If ValidValues has two or more elements, the report developer is
> indicating that he/she intends on the user picking a value from a list, you
> should use a dropdown list control.
> a. Determine if the Label property of the ETValidValue collection items is
> blank.
> i. If Label is NOT blank, then we have a lookup; we will display a
> â'externalâ' value such as List Name, and use its corresponding â'internalâ'
> value (List ID) as the parameter value. Populate the list itemâ's â'textâ'
> property with the Label property of the ETValidValue and the list itemâ's
> â'valueâ' property with the â'Valueâ' property of ETValidValue.
> ii. If Label IS blank, then we have a simple lookup (such as a list of
> dates). Populate the list boxâ's list itemâ's properties â'textâ' and â'valueâ'
> with the â'Valueâ' property of ETValidValue.
> 7. If DefaultValues contains 1 element then use the assign the value of
> DefaultValues[0] to the control in accordance with the controlâ's type, thus
> establishing the default value for the control.
> Future Consideration
> While the design stated above is certainly dynamic, it not does not consider
> all of the possibilities. We have not yet entertained for example that
> report content is ordered in accordance with a runtime parameter. If it was,
> we would simply gather the parameter like any other; however, it would be
> used in an ORDER BY clause as opposed to a WHERE clause. Although similar in
> nature, we would most likely want to visually separate those parameters that
> manage scope versus those that manage ordering. SQL Reporting Services does
> not directly support that segregation (through an internal property of a
> report parameter) so if desires, we would have to implement our own
> methodology.
> Date ranges (or any range parameters for that matter) are quite common;
> however we have no support for multiple ranges specified against a single
> value. Should we choose to support this feature we would need to determine a
> way to allow the user to enter multiple ranges, pass them to the report
> renderer, which passes them along to the stored procedure that delivers the
> result set. There is some hope for this support from within SQL Reporting
> Services through the MultiValue property of its parameters collection but we
> would still need to work out a method by which these values are passed on to
> the SPROCS.
> For now, user are limited to specifying a single value or (through using two
> report parameters) a range of values. I think it will be necessary for us to
> support multi value parameters in conjunction with the IN operator of T-SQL.
> Quite frequently a user is going to want a report that contains more than
> â'one itemâ'. How we will do this is not yet known as SQL Reporting Services
> does not yet appear to support Multi-Value.
> Another consideration, which is similar in nature to MultiValue is an
> â'all-butâ' paradigm. Rather than constructing the â'positiveâ' case using
> parameter values (range or multivalue) the user is constructing a WHERE NOT
> clause.
> In a report scenario that involves a heavy comparison by date, users
> frequently desire what I refer to as date plus or minus where the user is
> specifying a date and then a span of time either before or after the
> specified date. This is nothing more than an alternate date range so we
> really have no issue regarding our reports or stored procedures but rather it
> becomes an issue for the user-interface to illustrate the parameters in this
> fashion. This too is probably not too much of an endeavor however we have no
> way at present for the report developer to indicate the type of control to
> gather a parameter value by nor do we have support to communicate this fact
> to the consumer through the GetParameters() method. Of course, one could
> construct the MOADC (mother of all date controls) that allowed the user to
> specify a range with a beginning and ending date or a date plus or minus a
> day, week, month, quarter, half-year, year etc. but then we would have no way
> to associate two date parameters into a range either.
>
> "Dev Main" wrote:
> > I found many hints that customer parameter interfaces are a possible solution
> > for several problems.
> >
> > But I can't find one piece about how this can be done. Not in the
> > documentation, not in this news group and not in google. I'm really investet
> > many time, but don't find anything.
> >
> > Can one give me a hint where I can find information about this?|||Read Chapter 11 of the "Hitchhiker's Guide to SQL Server 2000 Reporting
Services" book.
"Dev Main" wrote:
> @. Herman K: Thank you very much! This is real help and furthermore an
> valuable documentation.
> My misunderstanding was, that I thought I can replace the parameter section
> in a report with something like a custom control. But this seems to be
> impossible.
> "Herman K" wrote:
> > Here's the design doc I wrote for ours:
> >
> > Reporting Phase II Design
> > A Dynamic User Interface for Report Navigation and Runtime Parameter Gathering
> > Introduction
> > Having a dynamic user interface to present reports to users and gather
> > runtime parameter values originates from a singular thought: report
> > customization. We desire that reports be developed by end user
> > administrators in addition to professional services and in-house developers.
> > By providing this support we must provide a means by which a report may be
> > deployed into the production environment and then appear in the application
> > without any user interface development activity on our part. The best way to
> > support this desire is to build our user interface such that it does not rely
> > on specific knowledge of any one report but rather is built with awareness
> > that reports are present and that they gather runtime parameters. In this
> > fashion, report developers are free to upload new reports or modify existing
> > reports independent of a release cycle. The following sections will
> > illustrate how we may expose this capability from within our application.
> > Report Navigation
> > The first challenge regarding our dynamic user interface is to present the
> > reports so a user may choose among them. Our current user interface design
> > calls for a hierarchical folder paradigm that contains and organizes all
> > reports. It begins with a root node labeled â'My Reportsâ' that appears in the
> > â'Trackingâ' page. This folder can contain any number of sub folders, which
> > serve to categorize all reports and will initially contain the folders â'Email
> > Reportsâ', â'Subscriber Reportsâ', â'Admin Reportsâ', and â'Custom Reportsâ'. It is
> > not clear whether the folder â'My Reportsâ' will ever contain reports
> > themselves and it is presumed these first level folders will contain reports
> > and perhaps additional folders.
> >
> > The Report Services component, created to serve the user interface and other
> > consumers, provides a method GetReports() that takes no input parameters and
> > returns to the caller a data table containing all of the reports that the
> > calling user is authorized to view. This data table will also contain the
> > hierarchy of folders such that the consumer may illustrate reports in
> > accordance with the prescribed hierarchy.
> >
> > The data table returned by GetReports() will contain the following items:
> > â?¢ ID â' a unique identifier (GUID) that uniquely identifies the report item
> > â?¢ Type â' an enumeration describing the nature of the report item. This is
> > supported by the public enum ReportItemType containing Folder and Report
> > (this may be expanded to include other types as demand warrants.
> > â?¢ Name â' the plain text name of the report, suitable for illustration in the
> > user interface. (This value originates from the â'nameâ' property of a report
> > on the report server, which gets its value from the â'nameâ' property of the
> > report itself, which is established at design time. It is therefore
> > necessary for developers to assign a meaningful name at design time,
> > presumably assigned by Product Management at the time of envisioning.)
> > â?¢ Description â' A plain text description that serves to illustrate the
> > content/functionality of the report in greater detail than â'Nameâ' (above).
> > It may be useful perhaps as tool-tip text given the folder paradigm. This
> > value originates from the â'descriptionâ' property of the report on the report
> > server, which gets its value from the â'descriptionâ' property of the report
> > itself, which is established at design time. It is therefore necessary for
> > developers to assign a meaningful value during the development process;
> > presumably this text will originate from Product Management.
> > â?¢ Path â' A text string that contains the fully qualified path to the report
> > on the report server. This item equally serves as a unique identifier for a
> > report, describes the location of the report within the folder hierarchy, and
> > is used as in input parameter by the RenderReport() method that identifies
> > the report to be rendered.
> > â?¢ Parent â' a unique identifier (GUID) that points to a report item that
> > contains this report item. If null, it indicates the root of the hierarchy.
> >
> > It is presumed that our user interface will use this data table to construct
> > the tree view that is illustrated in the user interface document. Further,
> > all attempts will be made to provide all of the information desired by the
> > user interface team and in a structure that is most suitable to their
> > consumption.
> > Runtime Parameter Value Gathering
> > While it is necessary to provide a listing of reports suitable for display,
> > the greater challenge comes in gathering the runtime parameter values that
> > will be used by the report to scope its contents and perhaps drive the order
> > in which result rows appear. To support consumers in the gathering of
> > runtime parameter values, the Report Services component exposes a method
> > GetReportParameters(). This method takes one parameter (string reportPath)
> > that is acquired through the â'Pathâ' item returned by GetReports() as
> > described above and returns a collection containing the items described
> > below. (Note: the values these items contain are a function of developer
> > action at design time, primarily via the settings made in the â'Report
> > Parametersâ' dialog of the IDE.
> > â?¢ Bool AllowBlank â' Determines whether a parameter value can be an empty
> > string. This applies only to parameters of type string (identified by
> > ETParameterTypeEnum Type, below)
> > â?¢ String[] DefaultValues â' A string array that typically contains one
> > element whose value indicates the default for this parameter. The user
> > interface should populate the appropriate control with this value. (Note: in
> > the future we may choose to support multi-value parameters; perhaps for use
> > with the SQL IN operator. If and when this becomes true, the potential for
> > multiple default values becomes true, hence it is declared as an array.)
> > â?¢ Bool DefaultValuesQueryBased â' Indicates whether DefaultValues (above) is
> > populated by an expression (false) or by a database query (true).
> > â?¢ Bool DefaultValuesQueryBasedSpecified â' Indicates whether a determination
> > can be made at runtime to assign DefaultValuesQueryBased a value. If this
> > value is false the origin of the default value(s) for this parameter is
> > indeterminate.
> > â?¢ String[] Dependencies â' If this parameterâ's default or valid values are
> > based on a parameterized query and that queryâ's parameter(s) have
> > indeterminate values, then this array will contain the names of those
> > parameters. In other words, if this property is not null, you will need to
> > gather the values for the parameters named in this property, make a
> > subsequent call to GetReportParameters, and then gather the value for this
> > parameter. This is detailed in the following section.
> > â?¢ String Name â' The internal name of this parameter as used n the report
> > itself. It does not represent a value suitable for display in the user
> > interface. Instead, use the property â'Promptâ', which is described below.
> > â?¢ Bool Nullable â' Indicates that a valid value for this parameter can be
> > null. A combination of Nullable and AllowBlank (both are true) indicate that
> > this parameter is â'optionalâ' (a user need not specify a value).
> > â?¢ String NullableSpecified â' Indicates whether Nullable (above) is
> > indeterminate at runtime (if false). If it makes you feel any better, you
> > may include this property (along with Nullable and AllowBlank) when
> > determining whether a given parameter is optional or not. Our stored
> > procedure standards are such that every SPROC should be written as if all
> > parameters (with the exception of MemberID) are optional, so you shouldnâ't
> > encounter any runtime errors.
> > â?¢ String Prompt â' A string value that names the parameter accordingly.
> > Unlike â'Nameâ' (above) this value is suitable for display in the user
> > interface and should be used to label the control accordingly.
> > â?¢ Bool PromptUser â' This Boolean indicates whether a given parameter should
> > appear in the dialog. This value is determined at design time and will be
> > false if and only if there is an empty string defined for the â'Prompt:â' text
> > in the â'Report Parametersâ' dialog.
> > â?¢ Bool PromptUserSpecified â' Determines whether PromptUser (above) is
> > indeterminate at runtime.
> > â?¢ ETParameterStateEnum State â' This property reports the state of this
> > parameter. The enumeration is as follows: HasValidValue, MissingValidValue,
> > HasOutstandingDependencies, or DynamicValuesUnavailable. At first blush it
> > would appear as if this property can be used to determine whether nor not to
> > present a given parameter in the dialog (State = MissingValidValue). If a
> > parameter has a default specified for it, this property will have a State of
> > HasValidValue although the user has made no entry. However, using this
> > property to validate a parameter value, through a subsequent call to
> > GetReportParameters is perfectly allowable.
> > â?¢ Bool StateSpecified â' Indicates whether State (above) has a value based on
> > the most recent call to GetReportParameters().
> > â?¢ ETParameterTypeEnum Type â' Indicates the underlying type of the parameter
> > and can be one of: Boolean, DateTime, Float, Integer, or String. You will
> > use this property to determine the control type (and perhaps additional
> > formatting) of the control you will add to the page. Note: when working with
> > the Reporting Service component you will always use the string representation
> > of a parameter value. This string representation is translated under the
> > covers by SQL Reporting Services to its native type.
> > â?¢ TypeSpecified â' Indicates whether Type (above) is indeterminate at runtime.
> > â?¢ ETValidValues ValidValues â' This collection if not null is a list of valid
> > value for this parameter. The contents of this collection is determined by a
> > developer at design time. You may use the collection to only to validate a
> > value specified by a user but you should use the contents to populate a
> > dropdown list from which a user may select. The most powerful feature of
> > this property is its ability to support a lookup. The type ETValidValue is a
> > name/value pair, so for example instead of prompting the user to enter an
> > email ID, you can populate the dropdown list with email names and then assign
> > the associated email ID to the ETReportParameterValues collection for
> > submission to the componentâ's RenderReport method.
> > â?¢ Bool ValidValuesQueryBased â' Indicates whether ValidValues (above) are the
> > result of a static list (false) or a database query (true).
> > â?¢ Bool ValidValuesQueryBasedSpecified â' Indicates whether
> > ValidValuesQueryBased (above) is indeterminate at runtime (false).
> >
> > The intent is for the user interface to use this collection to draw a page
> > containing user prompts and appropriate controls such that the user can
> > specify values as required. The order of the parameters as they appear in
> > the collection is the order in which they are displayed in the â'Report
> > Parametersâ' dialog in the IDE. In other words, the developer has the
> > opportunity at design time to specify the order in which they appear in the
> > user dialog (presuming a two column Prompt â' Value layout).
> >
> > It is suggested that the user interface enumerate over this collection and
> > create controls according to the values of the collectionâ's properties. The
> > following steps serve to illustrate this approach:
> > 1. Look only for parameters whose â'PromptUserâ' property is true. (Those
> > with a PromptUser property of false are used for other purposes and will
> > ALWAYS have a value resulting from a default value specification.)
> > 2. Look for parameters whoâ's Dependencies are not null. If you find any,
> > this is indicative of a situation where the parameterâ's DefaultValues or
> > ValidValues are query based and that query requires an input parameter. You
> > will need to consult the array elements, take the name of each parameter
> > contained in an element, and use the following logic to establish a page of
> > controls to obtain values for the dependent parameters. Once you have these
> > values, you will make a subsequent call to GetReportParameters() providing
> > the values you have collected thus far. Then, you will construct a page of
> > [now] independent parameters seeking their values. Once you have these
> > values you may proceed with RenderReport(). Note: there is at most one level
> > of indirection here.
> > 3. Use the â'Promptâ' propertyâ's value as the label text for your control.
> > (Depending on how you do this, you may have to wait until after the control
> > type is determined below.)
> > 4. If ValidValues has zero elements, determine the type of control to use
> > from the Type property.
> > a. If Type is â'Stringâ' we can presume a Textbox control.
> > b. If Type is â'DateTimeâ' we can presume a Calendar control.
> > c. If Type is â'Booleanâ' we can presume a CheckBox control.
> > d. If Type is â'Integerâ' we can presume a Textbox control with integer
> > formatting.
> > e. If Type is â'Floatâ' we can presume a Textbox control with floating point
> > decimal formatting.
> > 5. If ValidValues has one element, this is indicative of a rare but
> > potential condition where there is one and only one valid value. Since there
> > is only one you may consider not prompting the user at all and just use the
> > value supplied.
> > 6. If ValidValues has two or more elements, the report developer is
> > indicating that he/she intends on the user picking a value from a list, you
> > should use a dropdown list control.
> > a. Determine if the Label property of the ETValidValue collection items is
> > blank.
> > i. If Label is NOT blank, then we have a lookup; we will display a
> > â'externalâ' value such as List Name, and use its corresponding â'internalâ'
> > value (List ID) as the parameter value. Populate the list itemâ's â'textâ'
> > property with the Label property of the ETValidValue and the list itemâ's
> > â'valueâ' property with the â'Valueâ' property of ETValidValue.
> > ii. If Label IS blank, then we have a simple lookup (such as a list of
> > dates). Populate the list boxâ's list itemâ's properties â'textâ' and â'valueâ'
> > with the â'Valueâ' property of ETValidValue.
> > 7. If DefaultValues contains 1 element then use the assign the value of
> > DefaultValues[0] to the control in accordance with the controlâ's type, thus
> > establishing the default value for the control.
> > Future Consideration
> > While the design stated above is certainly dynamic, it not does not consider
> > all of the possibilities. We have not yet entertained for example that
> > report content is ordered in accordance with a runtime parameter. If it was,
> > we would simply gather the parameter like any other; however, it would be
> > used in an ORDER BY clause as opposed to a WHERE clause. Although similar in
> > nature, we would most likely want to visually separate those parameters that
> > manage scope versus those that manage ordering. SQL Reporting Services does
> > not directly support that segregation (through an internal property of a
> > report parameter) so if desires, we would have to implement our own
> > methodology.
> >
> > Date ranges (or any range parameters for that matter) are quite common;
> > however we have no support for multiple ranges specified against a single
> > value. Should we choose to support this feature we would need to determine a
> > way to allow the user to enter multiple ranges, pass them to the report
> > renderer, which passes them along to the stored procedure that delivers the
> > result set. There is some hope for this support from within SQL Reporting
> > Services through the MultiValue property of its parameters collection but we
> > would still need to work out a method by which these values are passed on to
> > the SPROCS.
> >
> > For now, user are limited to specifying a single value or (through using two
> > report parameters) a range of values. I think it will be necessary for us to
> > support multi value parameters in conjunction with the IN operator of T-SQL.
> > Quite frequently a user is going to want a report that contains more than
> > â'one itemâ'. How we will do this is not yet known as SQL Reporting Services
> > does not yet appear to support Multi-Value.
> >
> > Another consideration, which is similar in nature to MultiValue is an
> > â'all-butâ' paradigm. Rather than constructing the â'positiveâ' case using
> > parameter values (range or multivalue) the user is constructing a WHERE NOT
> > clause.
> >
> > In a report scenario that involves a heavy comparison by date, users
> > frequently desire what I refer to as date plus or minus where the user is
> > specifying a date and then a span of time either before or after the
> > specified date. This is nothing more than an alternate date range so we
> > really have no issue regarding our reports or stored procedures but rather it
> > becomes an issue for the user-interface to illustrate the parameters in this
> > fashion. This too is probably not too much of an endeavor however we have no
> > way at present for the report developer to indicate the type of control to
> > gather a parameter value by nor do we have support to communicate this fact
> > to the consumer through the GetParameters() method. Of course, one could
> > construct the MOADC (mother of all date controls) that allowed the user to
> > specify a range with a beginning and ending date or a date plus or minus a
> > day, week, month, quarter, half-year, year etc. but then we would have no way
> > to associate two date parameters into a range either.
> >
> >
> >
> > "Dev Main" wrote:
> >
> > > I found many hints that customer parameter interfaces are a possible solution
> > > for several problems.
> > >
> > > But I can't find one piece about how this can be done. Not in the
> > > documentation, not in this news group and not in google. I'm really investet
> > > many time, but don't find anything.
> > >
> > > Can one give me a hint where I can find information about this?