Customize ReportViewer Animation

Hello All,
I am developing an application that queries a database for a large
amount of data. The SQL server is not local so the time to query the
data can take some time.
Once I have the data locally, I send it to the ReportViewer to display.
The ReportViewer then shows the "Processing" animation, and displays
the results quickly so the animation is shown for a breif period. The
problem is that it takes a long time to query for the data, but the
ReportViewer animation is only shown for the time it processes the
DataSet.
I was wondering if anyone had experience customizing the ReportViewer
control to prematurely start the ReportViewer "Processing..."
animation. I would like to start the animation before I query the
database for the data so the user is aware that something is happening
during the data query.
I would rather implement my own "Processing..." form, or progress bar,
but if there is no way to control the ReportViewer control animation
that is my backup solution.
Thank you,
bigcoopsDoes anyone have any suggestions?
Thanks.
bigcoops@.hotmail.com wrote:
> Hello All,
> I am developing an application that queries a database for a large
> amount of data. The SQL server is not local so the time to query the
> data can take some time.
> Once I have the data locally, I send it to the ReportViewer to display.
> The ReportViewer then shows the "Processing" animation, and displays
> the results quickly so the animation is shown for a breif period. The
> problem is that it takes a long time to query for the data, but the
> ReportViewer animation is only shown for the time it processes the
> DataSet.
> I was wondering if anyone had experience customizing the ReportViewer
> control to prematurely start the ReportViewer "Processing..."
> animation. I would like to start the animation before I query the
> database for the data so the user is aware that something is happening
> during the data query.
> I would rather implement my own "Processing..." form, or progress bar,
> but if there is no way to control the ReportViewer control animation
> that is my backup solution.
> Thank you,
> bigcoops

Customising toolbar in the browser of RS HTML viewer

Dear friends,

please help me by commenting on my query.

Query::

I want to know how to customise the tool bar in RS HTML viewer, we can hide or show the toolbar using rc:toolbar= false/true, But i want to customise the tool bar.

ie, There is a combo for export formats to export the displaying reports. I want to customise that combo. I can clear the items by removing the extension element in the config of RS, but i want to do it in run time .

ie, My different reports should show different export formats simulteniously

You can customize the individual items on the toolbar with the information below:

http://blogs.msdn.com/bimusings/archive/2005/07/08/436887.aspx

However, there is no way to dynamically customize the rendering extensions that can be used at runtime (or on a per-report basis). You essentially enable or disable each rendering extension in the config files, and based on this work, certain formats show or don't show in the combo-box.

If you *must* have this functionality, I'd suggest that you turn off the toolbar completely and create your own version of the HTML toolbar which handles paging, zooming, etc. via URL Access (There is a sample of this in SSRS 2000). If you go this route, YOU can choose what rendering extensions to show the user in your own combo-box...However this will take a fair amount of work.

|||

As u said i checked the path

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\styles

the Styles folder doesn't exist. my version is RS 2000 enterprise Edition.

i searched for .css files in my installation folder i only got one major css ie ,reportingservices.css in

Reportmanager\styles

|||

It's very helpful and good article.

but can we make the changes in main "htmlviewer.css" and we got the reflected changes in all the reportserver reports.so by doing this we don't have to make another copy of css file and give "rc:Stylesheet=cssfilename".

i have also second question is there any way by which we can pass "rc:Stylesheet=cssfilename" URL to parent report to child report(by drilling down-jump to report) method.

thanks in advance.

|||

i didnt see any provision for adding url parameters in child reports. but we shall try one thing .

In your parent u add a parametr in some name "stylesheet" , and pass the value . this we can do . and while making the chil report u can assign parametrs or that , so create a parameter in the name "rc:Stylesheet" and select the value . the value is the parent parame value

ie , "rc:Stylesheet = stylesheet "

hope u will inform me regarding this

|||

Hai it's good idea, thanks ..

i did one try and it's working.

i had add "rc:Stylesheet=cssfilename" in my parent report, now when i am drilling down to child report

"rc:Stylesheet=cssfilename" parameter will automatcally carried forward to next report(child report).so there is no problem

for passing "rc:Stylesheet=cssfilename" one report to another report.

but i try your solution in different case, because i have also problem while passing "rs:clearsession=true" to other report.

Thanks,
Sanket.

|||

Hi .

I had made the changes in respective CSS file for hiding print icon in report toolbar and deployed report in local reporting

service machine (LAN) and our onsite machine.

but the problem i am facing is when i am accessing my local reporting server machine at that time print icon is not

visible.so it's as per my requirement. but when i am accessing our onsite machine reporting server at that time i seen print

icon and it's only seen in my PC, my other team members doesn't seen that print icon both on local reporting service machine

and onsite reporting service machine.

can anyone help me out in this issue, because it's very urgent ..

Thanks,
Sanket.

Customising toolbar in the browser of RS HTML viewer

Dear friends,

please help me by commenting on my query.

Query::

I want to know how to customise the tool bar in RS HTML viewer, we can hide or show the toolbar using rc:toolbar= false/true, But i want to customise the tool bar.

ie, There is a combo for export formats to export the displaying reports. I want to customise that combo. I can clear the items by removing the extension element in the config of RS, but i want to do it in run time .

ie, My different reports should show different export formats simulteniously

You can customize the individual items on the toolbar with the information below:

http://blogs.msdn.com/bimusings/archive/2005/07/08/436887.aspx

However, there is no way to dynamically customize the rendering extensions that can be used at runtime (or on a per-report basis). You essentially enable or disable each rendering extension in the config files, and based on this work, certain formats show or don't show in the combo-box.

If you *must* have this functionality, I'd suggest that you turn off the toolbar completely and create your own version of the HTML toolbar which handles paging, zooming, etc. via URL Access (There is a sample of this in SSRS 2000). If you go this route, YOU can choose what rendering extensions to show the user in your own combo-box...However this will take a fair amount of work.

|||

As u said i checked the path

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\styles

the Styles folder doesn't exist. my version is RS 2000 enterprise Edition.

i searched for .css files in my installation folder i only got one major css ie ,reportingservices.css in

Reportmanager\styles

|||

It's very helpful and good article.

but can we make the changes in main "htmlviewer.css" and we got the reflected changes in all the reportserver reports.so by doing this we don't have to make another copy of css file and give "rc:Stylesheet=cssfilename".

i have also second question is there any way by which we can pass "rc:Stylesheet=cssfilename" URL to parent report to child report(by drilling down-jump to report) method.

thanks in advance.

|||

i didnt see any provision for adding url parameters in child reports. but we shall try one thing .

In your parent u add a parametr in some name "stylesheet" , and pass the value . this we can do . and while making the chil report u can assign parametrs or that , so create a parameter in the name "rc:Stylesheet" and select the value . the value is the parent parame value

ie , "rc:Stylesheet = stylesheet "

hope u will inform me regarding this

|||

Hai it's good idea, thanks ..

i did one try and it's working.

i had add "rc:Stylesheet=cssfilename" in my parent report, now when i am drilling down to child report

"rc:Stylesheet=cssfilename" parameter will automatcally carried forward to next report(child report).so there is no problem

for passing "rc:Stylesheet=cssfilename" one report to another report.

but i try your solution in different case, because i have also problem while passing "rs:clearsession=true" to other report.

Thanks,
Sanket.

|||

Hi .

I had made the changes in respective CSS file for hiding print icon in report toolbar and deployed report in local reporting

service machine (LAN) and our onsite machine.

but the problem i am facing is when i am accessing my local reporting server machine at that time print icon is not

visible.so it's as per my requirement. but when i am accessing our onsite machine reporting server at that time i seen print

icon and it's only seen in my PC, my other team members doesn't seen that print icon both on local reporting service machine

and onsite reporting service machine.

can anyone help me out in this issue, because it's very urgent ..

Thanks,
Sanket.

Customising Legends for chart

Hi to all,

A query about legends in a chart, is it possible to customise legends in chart? I have used formula fields as the value of a pie chart and apparently the legend shows "@." infront of the fieldname... I'd like to have my own legend name for reading friendliness.

Thanks.

-HenryAnd for your information I am using Crystal Report included with VS.net 2003.

Cheers.

-Henry|||i'm having the same problem..

anyone der to help?|||Hi Cynthia, it has been many years since I have post my question... But I think I don't have a solution. I believe a full version of Crystal Report allow renaming field name.|||ok. where can i get the full version of Crystal report? is it consider an additional component to integrate into visual studio?

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;
;)

Customers & Phones

Hello,
I have a customers table linked to a phones table.
Is there a way to write an update query which will take the first & second
phone number of every customer and put it into respective fields (strPhone1
&
strPhone2) on the customer table, and dump any other phones into a memo fiel
d?
Thanks,
LomasWhy? There is so much wrong with this is is not funny. First normal form
violations in each of your columns, for starters, but beyond that:
Data put into memo fields (text columns, I assume) is pretty much useless in
your database from then on. All of the things you can do with it is
basically lost. Best case, you use a query to make a view of the data like
you are asking for and abstract it out from there. I know it takes more
work to do it right at first but it is worth it in the long run. Not to
mention that you can ask here for any query help to make the abstractions
work.
A moderately reasonable solution would be to have two columns <purpose>Phone
and <otherPurpose>Phone in your customer table if they will always have the
same purpose and then the phone table to keep other phone types.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||Ok, let me explain a little bit better.
I am writing this sql code as part of a modification to a database. After
this update query comes the drop tblPhones statement. This sql statement, i
n
its final form, will handle the range of phone types.
It seems to me that allowing a user an unlimited number of phone numbers
will have little practical value, and will be more difficult to query.
Modifying at this stage, while there are still few users, shouldn't cause
too much of an impact.
"Louis Davidson" wrote:

> Why? There is so much wrong with this is is not funny. First normal form
> violations in each of your columns, for starters, but beyond that:
> Data put into memo fields (text columns, I assume) is pretty much useless
in
> your database from then on. All of the things you can do with it is
> basically lost. Best case, you use a query to make a view of the data lik
e
> you are asking for and abstract it out from there. I know it takes more
> work to do it right at first but it is worth it in the long run. Not to
> mention that you can ask here for any query help to make the abstractions
> work.
> A moderately reasonable solution would be to have two columns <purpose>Pho
ne
> and <otherPurpose>Phone in your customer table if they will always have th
e
> same purpose and then the phone table to keep other phone types.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "optidev" <optidev@.community.nospam> wrote in message
> news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
>
>|||On a conceptual level, SSN, LastName and Ethnicity are attributes of the
Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
are related to a specific Customer at a given moment in time, but could
belong to an entirely different Customer tomorrow. Consider, relating them
using a CustomerPhone table with StartDate and EndDate columns to keep an
audit whenever their profile changes.
"optidev" <optidev@.community.nospam> wrote in message
news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
> Ok, let me explain a little bit better.
> I am writing this sql code as part of a modification to a database. After
> this update query comes the drop tblPhones statement. This sql statement,
> in
> its final form, will handle the range of phone types.
> It seems to me that allowing a user an unlimited number of phone numbers
> will have little practical value, and will be more difficult to query.
> Modifying at this stage, while there are still few users, shouldn't cause
> too much of an impact.
>
> "Louis Davidson" wrote:
>|||This is exactly what he wants to avoid, though it is still not a good idea
:)
To answer your original question, you will have to define what data makes a
phone number first or second. If you go ahead with this change, I would at
least suggest you have a column for home phone, one for mobile phone, etc.
rather than phone1, phone2. This is really bad design. On the other hand,
limiting the user to only one phone number of a specific type is a valid
design that doesn't leave the end user guessing what they are storing where.
Post your table structures and someone will help write the query..
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <someone@.microsoft.com> wrote in message
news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
> On a conceptual level, SSN, LastName and Ethnicity are attributes of the
> Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
> are related to a specific Customer at a given moment in time, but could
> belong to an entirely different Customer tomorrow. Consider, relating them
> using a CustomerPhone table with StartDate and EndDate columns to keep an
> audit whenever their profile changes.
> "optidev" <optidev@.community.nospam> wrote in message
> news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
>|||Hi ,
Write a update trigger for your main table and put a code like this , i cant
exactly remember right now
if updated(firstPhone) || updated(secondPhone)
......
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||I *was* planning to have have different columns for the different phone
types. I just didn't want to make the question too complicated. :)
Do you still think it is better to completely normilize the tables? In that
case I would need to keep track of the current primary address for use in
display and reporting. I don't see much practicality in knowing a customer'
s
previous addresses. Either way, I would also split the phones table if I wa
s
going to keep it this way.
Here are the tables with the added columns for phone types:
tblFamilies: --
pkFamilyID
strLastName
strAddress1
strAddress2
strCity
strZip
strHomePhone1
strHomePhone2
strHomeFax
strFamilyNotes
tblCustomers: --
pkCustomerID
fkFamilyID
strTitle
strFirstName
strMiddleInitial
strPostTitle
strWorkPhone1
strWorkPhone2
strWorkFax
strMobilePhone
strPager
strVoiceMail
strCustomerNotes
tblPhones: --
pkPhoneID
fkPhoneTypeID
fkFamilyID
fkCustomerID (one of these is null - either family or customer)
strPhoneNumber
strExtension (I also want to combine the extension with the phone number)
tblPhoneTypes: --
pkPhoneTypeID
numOrder
strDescription
The primary key currently defines what makes the phone number first or secon
d.
This code, for example, retrives all home phone numbers:
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblFamilies ON tblPhones.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
UNION
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblCustomers ON tblPhones.fkCustomerID =
tblCustomers.pkCustomerID INNER JOIN
tblFamilies ON tblCustomers.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
I can add this kind of complexity to the statement after I know the coding
method.
Thanks,
Lomas
"Louis Davidson" wrote:

> This is exactly what he wants to avoid, though it is still not a good idea
> :)
> To answer your original question, you will have to define what data makes
a
> phone number first or second. If you go ahead with this change, I would a
t
> least suggest you have a column for home phone, one for mobile phone, etc.
> rather than phone1, phone2. This is really bad design. On the other hand
,
> limiting the user to only one phone number of a specific type is a valid
> design that doesn't leave the end user guessing what they are storing wher
e.
> Post your table structures and someone will help write the query..
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||Interesting... Now I am thinking of normalizing the addresses and
denormalizing the phones... I still don't really see a point in keeping a
history of phone numbers.
What would be the best way to set up the query for retriveing the current
primary address in such a case?
As for the normalized phones table, I ran into a problem when a customer
asked me to export a list of them. He needed a column for each phone type
(Home1, Home2, HomeFax, etc.). How would I set up the query to put the firs
t
home number (it found) into the Home1 column, the second into Home2, etc.
What it there are (let's say) 15 of them? Also, how would I set up the quer
y
for reporting purposes. (I don't want every one to print when I print the
report.)
Thanks,
Lomas
"JT" wrote:

> Ask the users if they need a history of changes to a user's profile. If yo
u
> are shipping product to a customer's address, then knowing the address whe
re
> you actually shipped a product on a specific date would not only be useful
> but critical. Also, the marketing department may be interested in datamini
ng
> the changes in their customer's demographics. If the customers who generat
e
> the most sales are migrating to another geographical area, then that would
> be significant.
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||What is is the customer's most recent address?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID
order by
StartDate desc
What was the customer's address on the date that the product was shipped?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID and
StartDate <= @.ShipDate
order by
StartDate desc
If you have a normalized database and want to return a crosstab query
(Home1,Home2,Cell1,Cell2,etc) for reporting purposes, you can use inline
sub-queries like below. However, this will perform better and is more
flexible when implemented on the client side using Crystal, Excel or
whatever your reporting tool happens to be.
select
CustomerName,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='H' order by StartDate desc) as Home1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home3,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='C' order by StartDate desc) as Cell1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell3
from
Customers
where
CustomerID = @.CustomerID
"optidev" <optidev@.community.nospam> wrote in message
news:A0E6085D-D6EE-42FC-BCC9-524FFFDEAF5A@.microsoft.com...
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the
> first
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the
> query
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>|||select bottom doesn't work
"optidev" wrote:
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the fi
rst
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the qu
ery
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>

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

CustomData on SQL Server connection string?

Analysis Services has a CustomData connection string property. You can put anything on it, then inside an MDX query you can use the CustomData() function to retrieve whatever was on the connection string. This is helpful for passing in a security token.

Is there anything equivalent for SQL Server?

I also posted this here, but got no response so thought I'd check in the security forum.

I'm not sure that there is a property which is exactly suited for passing custom user data, but there is a property which you might modify without affecting pretty much anything else.

The property is "Application Name" (SSPROP_INIT_APPNAME).

|||

I see where you're going with that, but is there a SQL function to detect the application name from the connection string of the currently connected person?

|||

Hi,

What you can do to get the application name provided in the property is to run a statement like this:

select program_name from master..sysprocesses where spid=@.@.spid

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

CustomData on SQL Server connection string?

Analysis Services has a CustomData connection string property. You can put anything on it, then inside an MDX query you can use the CustomData() function to retrieve whatever was on the connection string. This is helpful for passing in a security token.

Is there anything equivalent for SQL Server?

I also posted this here, but got no response so thought I'd check in the security forum.

I'm not sure that there is a property which is exactly suited for passing custom user data, but there is a property which you might modify without affecting pretty much anything else.

The property is "Application Name" (SSPROP_INIT_APPNAME).

|||

I see where you're going with that, but is there a SQL function to detect the application name from the connection string of the currently connected person?

|||

Hi,

What you can do to get the application name provided in the property is to run a statement like this:

select program_name from master..sysprocesses where spid=@.@.spid

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

custom(and dynamic) record fields query

Hello,
I need to create a query that depends on custom fields (virtual).
First I'll try to outline the background.
I got Nodes table, that has NodeID field, and other (not really important)
fields like name, some boolean fields and so on.
Lets define node as an item for sale (just for the example purposes).
User of the application can create custom attribute values for each each
item (node).
To make the attribute names consistant through application I got another
table Attributes (AttID, AttributeName)
And the last table is association table between Node and Attributes
containing the value of the attribute as well.
I'd like to get following resultset:
NodeID, NodeName, CustomField1, CustomField2, CustomField3, ... and so on
(names of the fields would depend on Attribute name)
with values:
1,FirstNode, 'one of the values', 'another value', NULL, ...
2,SecondNode, NULL, NULL, NULL , ...
3,ThirdNode, NULL, NULL, 'some value', ...
SQL for creation such tables would look like (I ommited constrains and
indexes for siplicity):
CREATE TABLE Nodes (
NodeID INT IDENTITY (1, 1) NOT NULL,
NodeName NVARCHAR(255) NULL
)
CREATE TABLE Attributes (
AttID INT IDENTITY (1, 1) NOT NULL,
AttributeName NVARCHAR(50) NOT NULL
)
CREATE TABLE NodeAttr (
NodeID INT NOT NULL,
AttID INT NOT NULL,
Value NVARCHAR(255)
)
some data insertion:
INSERT INTO Nodes(NodeName) VALUES ('FirstNode')
INSERT INTO Nodes(NodeName) VALUES ('SecondNode')
INSERT INTO Nodes(NodeName) VALUES ('ThirdNode')
INSERT INTO Nodes(NodeName) VALUES ('FourthNode')
INSERT INTO Nodes(NodeName) VALUES ('FifthNode')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField1')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField2')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,1,'My Value for Field1')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,2,'My Value for Field2')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,3,'My Value for Field3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (3,3,'My Value for Field3
in third node')
Second I describe my approach to the problem.
I created function that retrieves data based on nodeID and Attribute name:
CREATE FUNCTION myfun(@.NodeID INT, @.AttributeName NVARCHAR(50))
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @.Value NVARCHAR(255)
SET @.Value=NULL
SELECT @.Value=Value FROM NodeAttr NA
INNER JOIN Attributes A ON (NA.AttID=A.AttID)
WHERE NodeID=@.NodeID AND AttributeName=@.AttributeName
RETURN @.Value
END
Next, I create dynamic SQL for data retrieval:
declare @.query NVARCHAR(4000)
SELECT
@.query=isnull(@.query+',','SELECT NodeID,
')+'dbo.myfun(NodeID,'''+AttributeName+''') AS ['+AttributeName+']'
FROM Attributes
SET @.query = @.query + ' FROM Nodes'
and execute it:
EXECUTE sp_executesql @.query
Dynamicly generated SQL looks like this:
SELECT NodeID,
dbo.myfun(NodeID,'CustomField1') AS [CustomField1],
dbo.myfun(NodeID,'CustomField2') AS [CustomField2],
dbo.myfun(NodeID,'CustomField3') AS [CustomField3]
FROM Nodes
It is great when you deal with small number of nodes. When node number is
about 2000 the perfomarnce is really bad (executes over a minute). And I'd
like to have even more.
Anyone can point me to better solution? :)
regards,
Pawel Rogozinski
Software Engineer at Eracent Inc.
pawel.rogozinski@.eracent.com>> I need to create a query that depends on custom fields (virtual). <<
This flawed approach is called an EAV design and newbies re-invent it
about once a month here. The basic fallacy that leads to meltdown in
about a year is that you are mixing data and metadata. Also, columns
are not fields and rows are not records -- totally different concepts.
Then give the users a copy of SQL and get out of the way. If any
random user is a better database programmers and designer than you are,
you should not be programming.
It is not just exponently slow; since it has no data integrity, it is
error-prone, too!!|||>> I'd like to get following resultset:
It is better to use a client programming language or a report writer to
generate such cross tabulations. In t-SQL, most general approaches are
clumsy. For some methods refer to: http://tinyurl.com/ayjwa
Anith|||This sounds like an EAV (Entity-Attribute-Value) design. One of it's biggest
failings is that reporting is significantly more difficult and slow. Another
divanatage is that it requires dynamic SQL and all of its issues. Thus, y
ou
should be aware that the difficulties you are having in finding a solution a
re
directly related to the design.
That said and assuming you wanted to continue with this design, one solution
that takes the dynamic SQL out of the equation would be to make the equivale
nt
of a materialized view. In essence, create a permanent temp table where each
"attribute" in your design is a column in this temp table. You would then
populate and maintain this table with every entity's attribute values. To do
that you can use a massive case statement like so:
Select NA.NodeId
, Min(Case When NA.AttId = 1 Then NA.Value End) As Attribute1Name
, Min(Case When NA.AttId = 2 Then NA.Value End) As Attribute2Name
...
From NodeAttr As NA
Group By NA.NodeId
In order to maitain the contents of the table, you'll need to use triggers t
o
update the entity's appropriate attribute values in the temp table and so on
.
The solutions to the problems created by this type of design get uglier and
uglier and the performance gets worse and worse.
Thomas|||Celko,
thanks for your "constructive" comments.

> This flawed approach is called an EAV design
> and newbies re-invent it about once a month here.
Nice. I did not reinvent this, I'm just working on such flawed design,
as you called it.
Do you know better design, that could substitute EAV then?
[...cut...]

> It is not just exponently slow; since it has no data
> integrity, it is error-prone, too!!
Didn't I mention that I did not put integrity constraitns for simplicity
of the example? I believie I did.
I would be very gratefull if you could lead me to a better solution and
not criticizing my approach, as I am aware of the flaws that come with
my approach (and the design as well).
*** Sent via Developersdex http://www.examnotes.net ***|||I like the temporary table solution.
As the attributes are not added/deleted frequently it could be one of the
best approaches.
regards,
Pawel Rogozinski
Software Engineer at Eracent
pawel.rogozinski@.eracent.com|||>> Do you know better design, that could substitute EAV then? <<
The point everyone is trying to get across to you is that you need a
totally different approach; it is not a matter of finding a substutute.
Why not use an actual data model, based on the specifications from the
user. There is no "magical, one size fits all" answer that you s
with kludges like EAV. Each database is a thing in itself, but you can
look for patterns for reuse.
simplicity of the example? I believe I did. <<
Did I meniton that doing that is virtually impossible without a few
hundred lines of procedural code in triggers and procs if you have a
database of any size?
Let's say I have 50 attributes of type INTEGER in my EAV model. They
are crammed into one table. Each one has 1 or 2 CHECK() constraints
that are totally different. Now try to write the CASE expression with
50 WHEN clauses to validlate each one.
Let's say that I have a primary key among those 50 attributes. Can you
show me the uniquenss constraint to enforce that business rule?
Let's say that I have a a foreign key among those 50 attributes. Can
you show me the uniquness constraint to enforce that business rule?
And do the DRI Actions required to maintain this relationship?
Thomas Coleman had a recent posting where he worked out the equivalent
of a simple GROUP BY statement for an EAV. Google it.sql

Custom Select Statment

Hello,

Why when I make a custom select statment with multiple tables, that when I test my query I'm shown the same rolls multiple times. When I make a custom query with just one table everything works just fine. I don't have any primary keys or restraints or whatever.

SELECT * FROM table1, table2, table3 Does not work(shows each row multiple times)

SELECT * FROM table1 works just fine (shows each row just one time)

Thanks

Steve

By not specifying a Join, you end up with a cross join which will return a tremendous number of duplicates.
see:http://en.wikipedia.org/wiki/Join_%28SQL%29

You need to select which columns to use to join your tables together.

For example:

SELECT table1.*, table2.*, table3.*FROM table1INNERJOIN table2ON table1.SomeKey = table2.SomeKeyINNERJOIN table3ON table1.SomeKey = table3.SomeKey

Custom query string question

Hi there,

I have a custom query string that I placed as a varchar, for instance

@.UnknownColumnName varchar(100)
@.CostumQueryString varchar(1000)
@.Value int

SET @.UnknownColumnName = (SELECT ColName FROM Utility WHERE ID =1)

SET @.CustomQueryString = 'SELECT ' + @.UnknownColumnName + ' From Table2 WHERE .....'

I have to execute this custom querystring now:
SET @.Value = (EXEC sp_executesql @.CustomQueryString)

I get a syntax error....I'm not sure how i can execute this querystirng and set the result to a variable..any help much appreciated

Regards
Mike

you can try this one

SET @.CustomQueryString = 'SELECT ' + @.UnknownColumnName + ' INTO #Values From Table2 WHERE .....'

EXEC sp_executesql @.CustomQueryString

SELECT *
FROM #Values

DROP TABLE #Values|||Hi, thanks for the reply.

I actually found the solution in books online (ooops should have searched harder there) and the best way of doing this is to make use of OUTPUT parameters:

DECLARE @.IntVariable int;
DECLARE @.SQLString nvarchar(500);
DECLARE @.ParmDefinition nvarchar(500);
DECLARE @.max_title varchar(30);

SET @.IntVariable = 197;
SET @.SQLString = N'SELECT @.max_titleOUT = max(Title)
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @.level';
SET @.ParmDefinition = N'@.level tinyint, @.max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @.SQLString, @.ParmDefinition, @.level = @.IntVariable, @.max_titleOUT=@.max_title OUTPUT;

SELECT @.max_title;

Custom Query Notifications

When I first saw SqlCacheDependancy and Query Notifications I was really stoked. I began using these features in ASP.NET and some of this caching really sped up my site. However, I noticed some of my queries and results were not cached. This led me on a looonnnnggggg search of the internet for anything related to these topics.

Anyway, the problem is that the queries are very limited (http://msdn2.microsoft.com/en-us/library/ms181122.aspx) in what they can contain, no count(*), no top, no order by, etc etc. I understand that the reason for this may be that the Query Notifications is using some of the Indexed Views infrastructure and therefore has the same limitations. That's fine and all but caching the results of these types of queries is still very useful for a front page of a site where you want the top ten users by number of postings etc and if you use the TOP N clause to limit it to the top 10 then you lose the built in caching functionality because this query doesn't work with Query Notifications and the cache expires immediately.

Is there a way to just get notifications when the tables involved in a query changes? I don't need the granular level of the rowsets involved I just want to be notified if my table has changed so I can refresh my cached objects.

I know there is an overload for the constructor of the SqlCacheDependency class which takes the database and table names as parameters. MSDN states that this is for Sql Server 7 and 2000 but would this also work in my scenerio? I know it wouldn't be auto-magical but if I can still get pushed notifications of changes instead of polling for notifications I would believe this to be a more efficient solution.

Query Notifications are fantastic but the query limitations make real world usage a pain, it's like have a nice red sports car but finding out you can only drive it whens it is raining and then you can only make right turns.

RegardsBTW, I'm using ASP.NET 2.0 and SQL Server 2005.

Custom Query Designer from VS2003 to VS2005

Hello,

I have a Data Processing Extension (DPE) that worked quite well in VS2003 with MSSQL2000 RS. I'm trying to port it to VS2005 with MSSQL2005 RS and I'm having the following problem.

In 2003 I used Microsoft.ReportDesigner.Interfaces.IQueryDesigner to define the query designer inside of VS2003. In 2005, that line does not compile; I get “The type or namespace ‘interfaces’ does not

exist in the namespace ‘Microsoft.ReportDesigner’ (Are you missing an Assembly

reference?)”

What is the 2005 equivalent of that interface? What's the best way to port an 2003 DPE to 2005?

Thanks

The IQueryDesigner interface has been renamed to Microsoft.ReportingServices.Interfaces.IQueryDesigner and you should add a reference to Microsoft.ReportingServices.QueryDesigners.dll.

-Albert

|||Thanks, that was it.

Custom Query Designer

Hi,
I'm having some trouble with implementing my own Query Designer.
I have built a control that implements the IQueryDesigner Interface
and register it in the RSReportDesigner.config file.
However Visual Studio never calls it. When I click on the Generic Query
Designer Button to switch between the Generic Designer an my Custom Designer,
The Designer window simply Reads
"The Query could not be loaded."
"Verify your connection string and query string."
I have tried attaching the debugger and I can break on the other Data
Extension classes I have implemented. So it appears that Studio is erring
before it gets to loading my Control. Everything else works fine if I run it
through the generic query designer.
I have also tried uninstalling and reinstalling Reporting Services thinking
the Designer may be broken, but this hasn't helped.
Unless someone has a better Idea, I'm going to resort to reinstalling visual
studio.
Any Ideas?
Thanks
Adrian B.Testing the Query Designer Code on another machine has not helped. So
re-installing Visual Studio will pointless.
I am currently at a loss for where to go with this.
Does anyone have any ideas at all?
Adrian B.

Custom Query Designer

Hi,

I am trying to build queries dynamically through a custom interface very much the way Query Designer does. Looking at few of the posts and replies in the forums it seems that SMO does not facilitate building queries but just offers you the metadata of the relational objects.

Are there any other DLL's/API's available which anyone is aware of which faciliates this ?

Thanks

Sam

You could use ADO.NET and build the strings and execute that way.
Tim|||In short, no. There are 3rd party commercial query builder components out there though.

Custom Order By

Hi,

I want to ask something. Is there any possibility to use order by using our own criteria. Example: I want to order my query by ProductName with specified criteria. See below

Products:

ProductID ProductName

1 Book

2 Pen

3 Ruler

The query for "select * from Products order by ProductName" will actually result

1 Book

2 Pen

3 Ruler

I want the result be like this:

2 Pen

1 Book

3 Ruler

May be my crazy idea is using this kind of query:

"select * from Products order by ProductName ( 'Pen' , 'Book' , 'Ruler' )"

Thanks in advance .....

If you have a limited number of 'sort by' items, you could use a CASE structure.

SELECT
Col1,
Col2,
Col3,
etc
FROM MyTable
ORDER BY CASE Col2
WHEN 'Pen' THEN 1
WHEN 'Book' THEN 2
WHEN 'Ruler' THEN 3
END

|||

it works, great !!! ...

thanks ....

custom functions and query optimization

I am building some functions and have not found documentation telling me
if/how that are optimized. For example:
SELECT
companyName,
dbo.getAddress(companyID),
dbo.getContact(companyID)
FROM company
Will the query optimizer build a plan rolling the function SQL statements
into the main SQL statement?
Or will it perform seperate optimization plans for the functions? If this
is the case will I get better performance by not using functions?
Thanks.Hi,
If you are doing set based operations (selects) then you are better off not
using a functions, it will be quicker
Functions have differnet compile plans then the queries they are used in.
But if you are doing complex procedure type functions then you have no
option but to use them if you didn't want to use a Stored Procedure
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"astro" <astro@.bcmn.com> wrote in message
news:hMljf.201$f41.86@.tornado.rdc-kc.rr.com...
> I am building some functions and have not found documentation telling me
> if/how that are optimized. For example:
> SELECT
> companyName,
> dbo.getAddress(companyID),
> dbo.getContact(companyID)
> FROM company
>
> Will the query optimizer build a plan rolling the function SQL statements
> into the main SQL statement?
> Or will it perform seperate optimization plans for the functions? If this
> is the case will I get better performance by not using functions?
> Thanks.
>|||On Wed, 30 Nov 2005 17:59:41 GMT, astro wrote:

>I am building some functions and have not found documentation telling me
>if/how that are optimized. For example:
>SELECT
>companyName,
>dbo.getAddress(companyID),
>dbo.getContact(companyID)
>FROM company
>
>Will the query optimizer build a plan rolling the function SQL statements
>into the main SQL statement?
Hi astro,
Depends on the kind of user-defined function. There are three kinds of
user-defined functions: scalar functions, inline table-valued functions
and multi-statement table-valued functions. See CREATE FUNCTION in Books
Online for the details.
For inline table-valued functions, SQL Server will "roll the function
statements into the main statement", as you describe it. For multi-line
table-valued functions and scalar functions, it won't.
The function calls in the example above appear to be scalar, so in this
specific case, the answewr would be "no".

> will I get better performance by not using functions?
Quite probably. But the only way to know for sure is to test both
versions.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks both of you for your replies.
"astro" <astro@.bcmn.com> wrote in message
news:hMljf.201$f41.86@.tornado.rdc-kc.rr.com...
> I am building some functions and have not found documentation telling me
> if/how that are optimized. For example:
> SELECT
> companyName,
> dbo.getAddress(companyID),
> dbo.getContact(companyID)
> FROM company
>
> Will the query optimizer build a plan rolling the function SQL statements
> into the main SQL statement?
> Or will it perform seperate optimization plans for the functions? If this
> is the case will I get better performance by not using functions?
> Thanks.
>