Nested Interactive sorting

Hi,
Is it possible to have nested interactive sorting in RS2005?

eg.
My report displays COUNTRY | STATE | CITY | SALES
I enable interactive sorting on COUNTRY and CITY.
First the I sort on COUNTRY and then if a user clicks on CITY, then it should be sort combination of COUNTRY and CITY.

Is this possible?AFAIK, you will have to group that to make a nested sorting.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

You have to use shift+click to sort by multiple items at the same time if they share the same context.

-- Robert

nested for loop with xquery

What is wrong with this snippet of XQuery like this
declare @.city xml
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>
{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}
}</Regions>
') as cities)
select @.city
It works fine without the <Region>{$r}</Region> and the pair of
brackets after itThe problem is that you have too many {}... {} are used in XQuery to switch
from the lexical XML construction syntax into XQuery syntax...
so in your case you have:
XQUERY CONTEXT 0> for $r in distinct-values(//Region)
> return
THIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
THIS INTO XQUERY CONTEXT 1> {
TO XML CONSTRUCTION CONTEXT 2> <Region>
XQUERY CONTEXT 1> {$r
BACK OUT INTO OUTER XML CONTEXT 2> }
CLOSES XML CONTEXT 2> </Region>
TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
So you write either
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>,
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
or
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region>{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169755098.049990.46950@.l53g2000cwa.googlegroups.com...
> What is wrong with this snippet of XQuery like this
> declare @.city xml
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>
> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
> ') as cities)
> select @.city
> It works fine without the <Region>{$r}</Region> and the pair of
> brackets after it
>|||hi Michael
Thank you for all of your help.
I have some more questions re xpath/xquery
1. What is the comma <Region>{$r}</Region>,
2. Continuing from the query I asked about before, if i want to
return a count, how would I do that? I am doing this, but it returns
0
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region> {
for $mt in distinct-values(//MatterType)
return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
count></MatterType>
}</Regions>
'))
On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The problem is that you have too many {}... {} are used in XQuery to switc
h
> from the lexical XML construction syntax into XQuery syntax...
> so in your case you have:
> XQUERY CONTEXT 0> for $r in distinct-values(//Region)> returnTHIS SWITCHES
TO XML CONSTRUCTION CONTEXT 1> <Regions>
> THIS INTO XQUERY CONTEXT 1> {
> TO XML CONSTRUCTION CONTEXT 2> <Region>
> XQUERY CONTEXT 1> {$r
> BACK OUT INTO OUTER XML CONTEXT 2> }
> CLOSES XML CONTEXT 2> </Region>
> TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
>
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>,
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> or
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region>{
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> Best regards
> Michael
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169755098.0499
90.46950@.l53g2000cwa.googlegroups.com...
>
>
>|||and also
3. how would i create the xquery string to return a value as an
attribute rather than as an element as I've done before? (return
<MatterType>{$mt}</MatterType> )
Thanks!
On Jan 29, 11:51 am, "joyce" <joycesc...@.fastmail.fm> wrote:
> hi Michael
> Thank you for all of your help.
> I have some more questions re xpath/xquery
> 1. What is the comma <Region>{$r}</Region>,
> 2. Continuing from the query I asked about before, if i want to
> return a count, how would I do that? I am doing this, but it returns
> 0
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region> {
> for $mt in distinct-values(//MatterType)
> return <MatterType ><count>{count(//Region[.=$r]/MatterTyp
e)}</
> count></MatterType>
> }</Regions>
> '))
> On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

nested for loop with xquery

What is wrong with this snippet of XQuery like this
declare @.city xml
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>
{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}
}</Regions>
') as cities)
select @.city
It works fine without the <Region>{$r}</Region> and the pair of
brackets after it
The problem is that you have too many {}... {} are used in XQuery to switch
from the lexical XML construction syntax into XQuery syntax...
so in your case you have:
XQUERY CONTEXT 0> for $r in distinct-values(//Region)
> return
THIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
THIS INTO XQUERY CONTEXT 1> {
TO XML CONSTRUCTION CONTEXT 2> <Region>
XQUERY CONTEXT 1> {$r
BACK OUT INTO OUTER XML CONTEXT 2> }
CLOSES XML CONTEXT 2> </Region>
TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
So you write either
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>,
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
or
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region>{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169755098.049990.46950@.l53g2000cwa.googlegro ups.com...
> What is wrong with this snippet of XQuery like this
> declare @.city xml
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>
> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
> ') as cities)
> select @.city
> It works fine without the <Region>{$r}</Region> and the pair of
> brackets after it
>
|||hi Michael
Thank you for all of your help.
I have some more questions re xpath/xquery
1. What is the comma <Region>{$r}</Region>,
2. Continuing from the query I asked about before, if i want to
return a count, how would I do that? I am doing this, but it returns
0
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region> {
for $mt in distinct-values(//MatterType)
return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
count></MatterType>
}</Regions>
'))
On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> The problem is that you have too many {}... {} are used in XQuery to switch
> from the lexical XML construction syntax into XQuery syntax...
> so in your case you have:
> XQUERY CONTEXT 0> for $r in distinct-values(//Region)> returnTHIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
> THIS INTO XQUERY CONTEXT 1> {
> TO XML CONSTRUCTION CONTEXT 2> <Region>
> XQUERY CONTEXT 1> {$r
> BACK OUT INTO OUTER XML CONTEXT 2> }
> CLOSES XML CONTEXT 2> </Region>
> TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
>
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>,
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> or
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region>{
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> Best regards
> Michael
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169755098.049990.46950@.l53g2000cwa.go oglegroups.com...
>
|||and also
3. how would i create the xquery string to return a value as an
attribute rather than as an element as I've done before? (return
<MatterType>{$mt}</MatterType>)
Thanks!
On Jan 29, 11:51 am, "joyce" <joycesc...@.fastmail.fm> wrote:[vbcol=seagreen]
> hi Michael
> Thank you for all of your help.
> I have some more questions re xpath/xquery
> 1. What is the comma <Region>{$r}</Region>,
> 2. Continuing from the query I asked about before, if i want to
> return a count, how would I do that? I am doing this, but it returns
> 0
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region> {
> for $mt in distinct-values(//MatterType)
> return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
> count></MatterType>
> }</Regions>
> '))
> On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>

Needs Help with SQL Query

I need help with fixing a sql query.My goal is for this to show the advertiser for a county regardless of which city it is located in. Please seehttp://www.explorencmountains.com/County.aspx?CountyID=7. This works fine. But I would like for the advertiser to show up under all of the towns listed under Macon County when you go to their page. It currently will only show the advertiser if the city is Franklin.
select * from Advertiser, Categories, Subcategories, County, City
where Advertiser.CategoryID=Categories.CategoryID
and Advertiser.SubcategoryID=SubCategories.Subcategory ID
and County.CountyID=City.CountyID
and Advertiser.CityID=City.CityID
and City.CountyID = '7'
and Advertiser.SubCategoryID = '27'
and Advertiser.CityID='31' -won't work unless the cityid is 32
and Approve='1'
Order By ListingType, BusinessName,City
select * from County, City
where County.CountyID=City.CountyID
and City.CountyID = '7'
and CityID='31' -Will work fine
I've restructured your query to use standard ANSI joins. Couldyou explain your query a little more. Why do you need the line inred at all? What is it supposed to be doing for you?
SELECT
*
FROM
Advertiser
INNER JOIN
Categories ON Advertiser.CategoryID=Categories.CategoryID
INNER JOIN
Subcategories ON Advertiser.SubcategoryID=SubCategories.SubcategoryID
INNER JOIN
City ON Advertiser.CityID=City.CityID
INNER JOIN
County ON County.CountyID=City.CountyID
WHERE
City.CountyID = '7' AND
Advertiser.SubCategoryID = '27' AND
Advertiser.CityID='31' AND -won't work unless the cityid is 32--Why do you need this line??
Approve='1'
ORDER BY
ListingType,
BusinessName,
City