multiple resultsets: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

Multi-Value parameter syntax error

I am new to reporting services...
I've created a report that uses a multi-value parameter based ona dataset
usign a simple select statement. It works fine when a single value is
selected, but when multiple values are selected returns the error Incorrect
Syntax near ','.
How can the report code be modified to pass multiple values with the correct
syntax?See my response to your other posting (which is basically, show us what you
did). My guess is you have an incorrect SQL statement OR you are not going
against SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:7342117C-9715-477F-A9F4-E748C2EA3A77@.microsoft.com...
>I am new to reporting services...
> I've created a report that uses a multi-value parameter based ona dataset
> usign a simple select statement. It works fine when a single value is
> selected, but when multiple values are selected returns the error
> Incorrect
> Syntax near ','.
> How can the report code be modified to pass multiple values with the
> correct
> syntax?
>

Multi-Value parameter Select All

Is there a way to tell if a user has checked Select All for a multi-valued
parameter? I'd like my query to be different if they have.I recall from a previous post that this was not possible.
here is the link to the post.
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?&query=tell+if+user+selected+all&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=e86a166d-61fa-44d8-867c-fc4f29781b5b&mid=e86a166d-61fa-44d8-867c-fc4f29781b5b
"Michelle" wrote:
> Is there a way to tell if a user has checked Select All for a multi-valued
> parameter? I'd like my query to be different if they have.

Multi-Value Parameter Problem

I'm creating a report that selects data from one table. I'm trying to
add a parameter that will allow the user to select the data based on a
selection from one column (SpindleName). I've added this column in the
report parameters, checked the multi-value box, set the available
values to "from query" to the dataset & field, set the default values
to "from query" to the same dataset & field. When I execute the query,
I get asked for the SpindleName, but the box contains no values other
than "NULL" and "BLANK". If I type the spindle name into the box, I
get the results. Why isn't the list being populated with the values
from the spindlename column ?
Here is the query:
SELECT StationName, SpindleName, PartId, TimeStamp
FROM dbo.Readings
WHERE SpindleName IN (@.SpindleName)
I'm using SQL 2005 Express & VS 2005
Any advice would be appreciated.
Thank-you
JeffWhat is the source query for the parameter?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||I may be wrong but it seems to me there is some confusion here. Several
things:
1. Unless you have cascading parameters (the list to select from parameter B
is dependent on the selection from parameter A) then you should have a query
that returns the value you want to select from.
2. Default means a single value. If you point to a dataset with multiple
values I don't know what it does except that is incorrect.
So, for instance by my take on this you want to this.
Have two datasets. One dataset that returns the list of SpindleName. The
other that retrieves the data you desire.
Select distinct SpindleName from Readings
Set both your label and value for the SpindleName parameter to the field
SpindleName from the above query.
Then your second query you have below will return what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||For your point #2, if your parameter's default value points to a dataset
("From Query") with multiple values, they will show up with all of them
selected in the drop down list. This is desirable in some cases.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23P5CZRGrIHA.4376@.TK2MSFTNGP06.phx.gbl...
>I may be wrong but it seems to me there is some confusion here. Several
>things:
> 1. Unless you have cascading parameters (the list to select from parameter
> B is dependent on the selection from parameter A) then you should have a
> query that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvica1@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>> I'm creating a report that selects data from one table. I'm trying to
>> add a parameter that will allow the user to select the data based on a
>> selection from one column (SpindleName). I've added this column in the
>> report parameters, checked the multi-value box, set the available
>> values to "from query" to the dataset & field, set the default values
>> to "from query" to the same dataset & field. When I execute the query,
>> I get asked for the SpindleName, but the box contains no values other
>> than "NULL" and "BLANK". If I type the spindle name into the box, I
>> get the results. Why isn't the list being populated with the values
>> from the spindlename column ?
>> Here is the query:
>> SELECT StationName, SpindleName, PartId, TimeStamp
>> FROM dbo.Readings
>> WHERE SpindleName IN (@.SpindleName)
>> I'm using SQL 2005 Express & VS 2005
>> Any advice would be appreciated.
>> Thank-you
>> Jeff
>|||On May 2, 10:53=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I may be wrong but it seems to me there is some confusion here. Several
> things:
> 1. Unless you have cascading parameters (the list to select from parameter= B
> is dependent on the selection from parameter A) then you should have a que=ry
> that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvi...@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>
> > I'm creating a report that selects data from one table. I'm trying to
> > add a parameter that will allow the user to select the data based on a
> > selection from one column (SpindleName). I've added this column in the
> > report parameters, checked the multi-value box, set the available
> > values to "from query" to the dataset & field, set the default values
> > to "from query" to the same dataset & field. When I execute the query,
> > I get asked for the SpindleName, but the box contains no values other
> > than "NULL" and "BLANK". If I type the spindle name into the box, I
> > get the results. Why isn't the list being populated with the values
> > from the spindlename column ?
> > Here is the query:
> > SELECT StationName, SpindleName, PartId, TimeStamp
> > FROM dbo.Readings
> > WHERE SpindleName IN (@.SpindleName)
> > I'm using SQL 2005 Express & VS 2005
> > Any advice would be appreciated.
> > Thank-you
> > Jeff- Hide quoted text -
> - Show quoted text -
Thanks Bruce - it worked perfectly!
Regards
Jeff

Multi-Value Parameter

I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code:

Code Snippet

SE [RC_STAT]

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

what am i doing wrong?

You can concatenate the multi values in a comma delimited string and pass the string to the stored procedure. you can create a function in SS to split the string and return a table.

Expression used to populate the parameter

=Join(Parameters! <ParameterName> .Value,", ")

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

MultiValue Parameter

Hello,

I have in my report a text box that lists the user selection. When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".

Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.

Thank you,

Itzhak

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

|||

Thank you, this will solve my issue.

I put the following expression and it works well !!

=iif(CountRows("UsersDataSet")=Parameters!UserID.Count,"ALL",join(Parameters!UserID.Label,","))

Thanks,

Itzhak

Wednesday, March 28, 2012

Multitable Select

I have 2 tables involved in a select statement that I cannot figure out quite how to do.

Table 1- GroupedItems
int SharedId
int GroupId

Table 2- SharedItems
int SharedId
int ItemId

In Table 1 SharedId is unique
In Table 2 ItemId is unique

Table 1 represents all of a group's shared Items
Table 2 matches a group of Items to a common sharedId

Example
Table 1
1 - 1
2 - 1
3 - 2

Table 2
1 - 1
1 - 2
2 - 3
2 - 4
3 - 5

What I am trying to do is have a select command where you would pass in a GroupId and it would select just one Item from table 2 for each SharedId

If you were to specify Group 1 you would Get
ItemIds 1 and 3 from Table 2, although its not needed that the results are the top items.

You can use something like this query in 2005 to get a unique set of items:

create table SharedItems
(
sharedId int,
itemId int primary key
)
insert into SharedItems
select 1,1
union all
select 1,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
go

select sharedId, itemId
from ( select sharedId, itemId, row_number() over (partition by sharedId order by itemId) as rowNumber
from SharedItems ) as numbered
where numbered.rowNumber = 1

|||Thank you for your help.

This is exactley what I needed.
|||

More straight forward way to write the query is to do below:

select s1.sharedId, s1.itemId
from SharedItems as s1
where s1.itemId = (select min(s2.itemId) from SharedItems as s2 where s2.sharedId = s1.sharedId)

And depending on your indexes/data this might give better performance than the ROW_NUMBER approach. For this particular example, the above approach will be twice as fast as the ROW_NUMBER approach. And if you have an index on say sharedId then the performance difference will be more significant.

|||True enough. I am always just a bit concerned about duplicate and ordering so I just defaulted to doing it that way. I should have thought about this better way since there is a single value to correlate with. Thanks!|||Thanks to both of you.

This approach looks a little more like what I was expecting, but I'm glad I was able to learn about the row number

Multitable Select

I have 2 tables involved in a select statement that I cannot figure out quite how to do.

Table 1- GroupedItems
int SharedId
int GroupId

Table 2- SharedItems
int SharedId
int ItemId

In Table 1 SharedId is unique
In Table 2 ItemId is unique

Table 1 represents all of a group's shared Items
Table 2 matches a group of Items to a common sharedId

Example
Table 1
1 - 1
2 - 1
3 - 2

Table 2
1 - 1
1 - 2
2 - 3
2 - 4
3 - 5

What I am trying to do is have a select command where you would pass in a GroupId and it would select just one Item from table 2 for each SharedId

If you were to specify Group 1 you would Get
ItemIds 1 and 3 from Table 2, although its not needed that the results are the top items.

You can use something like this query in 2005 to get a unique set of items:

create table SharedItems
(
sharedId int,
itemId int primary key
)
insert into SharedItems
select 1,1
union all
select 1,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
go

select sharedId, itemId
from ( select sharedId, itemId, row_number() over (partition by sharedId order by itemId) as rowNumber
from SharedItems ) as numbered
where numbered.rowNumber = 1

|||Thank you for your help.

This is exactley what I needed.|||

More straight forward way to write the query is to do below:

select s1.sharedId, s1.itemId
from SharedItems as s1
where s1.itemId = (select min(s2.itemId) from SharedItems as s2 where s2.sharedId = s1.sharedId)

This approach looks a little more like what I was expecting, but I'm glad I was able to learn about the row numbersql

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner join
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get some
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resource
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/default.aspx?scid=kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:
> Thanks for the inputs everyone. After looking at your examples, I did get some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resource
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, looks
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was able
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BTW,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if this
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:
> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Monday, March 26, 2012

Multi-Select Value dropdown "Select All" Problem

Hi,
I hava report (RDL) which is having a parameter called Plan Owner, i
want the facality of "Select All" in the dropdown, this i can achive
through setting the parameter as Multi-Value. But the problem is if the
user select "select all" from dropdown, i uses the IN claues, i want to
avoid the use of IN clause if the "select all" is selected in
Multi-value dropdown. How can i programatically write the dataset to
avoid "select all"?
What is the internal value of "select all" which it added automatically
in the multi-value parameter dropdown.
I can provide more info if needed.
Regards,
SumitThis is my interpretation of the multivalue parameter.
The internal representation of a multivalue parameters is an array of
all the possible values for the parameter. So if your parameter has 3
values (A,B,C) if you do 'Select All', RS sees an array with values
(A,B,C)
The only way I know of to determine if someone has picked 'Select All'
is to test the array against the # of rows in the dataset populating
the parameters. It would look something like this:
=iif(Parameters!MultiValParam.Count = Count("datasetformvparam"), "",
"FIELD IN (" & join(Parameters!MultiValParam.Value, ", ") & ")")
Andy Potter|||Hi,
I tried the above solution but the count of my dataset always returns 0
even though there are records in there. Any suggestions?
=iif(Parameters!bus_group.Count = Count("business_group"),
"ALL",Join(Parameters!bus_group.Label, ", ") )
Thanks,
Melissa|||I ran into a similar problem in wanting to know if they had selected all
possible options. See the following for a further discussion, a feature
suggestion, and a workaround:
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=dfec87e1-a34d-4ff8-a480-fb1f8d4160ea
"Melissa" wrote:
> Hi,
> I tried the above solution but the count of my dataset always returns 0
> even though there are records in there. Any suggestions?
> =iif(Parameters!bus_group.Count = Count("business_group"),
> "ALL",Join(Parameters!bus_group.Label, ", ") )
> Thanks,
> Melissa
>

Multi-Select Value dropdown "Select All" in SP1

Hi,
With SQL-Server 2005 SP1, the "Select All" in the dropdown does not appear.
Without SP1 the "Select All" in Multi-Select Value did appear. How can I fix
it?
Regards,
TomI just installed SP1 on a test machine and checked this out. I am getting
the same results as you. I am not happy about it at all. I will use my
contacts at MS to see what is happening.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> Hi,
> With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> appear.
> Without SP1 the "Select All" in Multi-Select Value did appear. How can I
> fix
> it?
> Regards,
> Tom
>|||Have you heard anything back from Microsoft on this? It is causing me real
problems with users.
"Bruce L-C [MVP]" wrote:
> I just installed SP1 on a test machine and checked this out. I am getting
> the same results as you. I am not happy about it at all. I will use my
> contacts at MS to see what is happening.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> > Hi,
> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> > appear.
> > Without SP1 the "Select All" in Multi-Select Value did appear. How can I
> > fix
> > it?
> > Regards,
> > Tom
> >
> >
>
>|||Me too... terrible bug! This is really hurting us too. Hope there's a hotfix
or a config option somewhere.
"Dan D" wrote:
> Have you heard anything back from Microsoft on this? It is causing me real
> problems with users.
> "Bruce L-C [MVP]" wrote:
> > I just installed SP1 on a test machine and checked this out. I am getting
> > the same results as you. I am not happy about it at all. I will use my
> > contacts at MS to see what is happening.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> > news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> > > Hi,
> > > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> > > appear.
> > > Without SP1 the "Select All" in Multi-Select Value did appear. How can I
> > > fix
> > > it?
> > > Regards,
> > > Tom
> > >
> > >
> >
> >
> >|||Found some more info:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=f68f4903-5171-4fff-9d70-1b4342b03a35
Please add comments and vote for it and yell loud so MS changes this!
"Bruce L-C [MVP]" wrote:
> I just installed SP1 on a test machine and checked this out. I am getting
> the same results as you. I am not happy about it at all. I will use my
> contacts at MS to see what is happening.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> > Hi,
> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> > appear.
> > Without SP1 the "Select All" in Multi-Select Value did appear. How can I
> > fix
> > it?
> > Regards,
> > Tom
> >
> >
>
>|||I just got info on this. What happened is the Select All parameter caused
problems with Analysis Services. So, this is by design. What I will be doing
is rewriting my queries and stored procedures that use this to use a new
parameter I will add to the list called All.
If a query it would look like this:
select somefields from sometable where (@.Param='All' or somefield in
(@.Param))
This will result in better performance anyway but not as nice a user
interface for multi-select. However, most of my non-multi-select parameters
off an All option so my users are used to looking for it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dan D" <Dan D@.discussions.microsoft.com> wrote in message
news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
> Have you heard anything back from Microsoft on this? It is causing me
> real
> problems with users.
> "Bruce L-C [MVP]" wrote:
>> I just installed SP1 on a test machine and checked this out. I am getting
>> the same results as you. I am not happy about it at all. I will use my
>> contacts at MS to see what is happening.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
>> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
>> > Hi,
>> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
>> > appear.
>> > Without SP1 the "Select All" in Multi-Select Value did appear. How can
>> > I
>> > fix
>> > it?
>> > Regards,
>> > Tom
>> >
>> >
>>|||Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have any more
specifics? How was it causing problems? Is it just simply an issue of users
choosing Select All which made the list of selected items huge causing
performance problems?
Do you mind sharing who at MS you got that answer from?
Unless there's some bug that it caused that I don't know about, my strong
opinion is that they shouldn't remove a feature just because some dumb
developers abused it and caused performance problems.
"Bruce L-C [MVP]" wrote:
> I just got info on this. What happened is the Select All parameter caused
> problems with Analysis Services. So, this is by design. What I will be doing
> is rewriting my queries and stored procedures that use this to use a new
> parameter I will add to the list called All.
> If a query it would look like this:
> select somefields from sometable where (@.Param='All' or somefield in
> (@.Param))
> This will result in better performance anyway but not as nice a user
> interface for multi-select. However, most of my non-multi-select parameters
> off an All option so my users are used to looking for it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
> > Have you heard anything back from Microsoft on this? It is causing me
> > real
> > problems with users.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I just installed SP1 on a test machine and checked this out. I am getting
> >> the same results as you. I am not happy about it at all. I will use my
> >> contacts at MS to see what is happening.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> >> > Hi,
> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> >> > appear.
> >> > Without SP1 the "Select All" in Multi-Select Value did appear. How can
> >> > I
> >> > fix
> >> > it?
> >> > Regards,
> >> > Tom
> >> >
> >> >
> >>
> >>
> >>
>
>|||>>>>>>
This is from Brian Welcker, the program manager for Reporting Services: When
used in conjunction with Analysis Services, the Select All feature
circumvents the built-in Analysis Services "All" member, individually
selecting every member in a dimension. This causes such a massive
performance degradation for AS queries (and is likely for users to use since
all AS parameters are multi-valued). For SQL queries, it is not as impactful
on performance (although an IN clause with 100 values is not very
efficient).
>>>>>>
Given the above it was not an unreasonable way to go. Except, I don't care
about Analysis Services personally so my preference would have been to leave
it alone. Kindof a NIMBY feeling. Now that I know it is by design then I
will work around it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:C3A68DD9-9C3C-4111-89E7-436539274863@.microsoft.com...
> Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have any
> more
> specifics? How was it causing problems? Is it just simply an issue of
> users
> choosing Select All which made the list of selected items huge causing
> performance problems?
> Do you mind sharing who at MS you got that answer from?
> Unless there's some bug that it caused that I don't know about, my strong
> opinion is that they shouldn't remove a feature just because some dumb
> developers abused it and caused performance problems.
> "Bruce L-C [MVP]" wrote:
>> I just got info on this. What happened is the Select All parameter caused
>> problems with Analysis Services. So, this is by design. What I will be
>> doing
>> is rewriting my queries and stored procedures that use this to use a new
>> parameter I will add to the list called All.
>> If a query it would look like this:
>> select somefields from sometable where (@.Param='All' or somefield in
>> (@.Param))
>> This will result in better performance anyway but not as nice a user
>> interface for multi-select. However, most of my non-multi-select
>> parameters
>> off an All option so my users are used to looking for it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
>> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
>> > Have you heard anything back from Microsoft on this? It is causing me
>> > real
>> > problems with users.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> I just installed SP1 on a test machine and checked this out. I am
>> >> getting
>> >> the same results as you. I am not happy about it at all. I will use my
>> >> contacts at MS to see what is happening.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
>> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
>> >> > Hi,
>> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
>> >> > appear.
>> >> > Without SP1 the "Select All" in Multi-Select Value did appear. How
>> >> > can
>> >> > I
>> >> > fix
>> >> > it?
>> >> > Regards,
>> >> > Tom
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Microsoft made a BAD Choice in resolving this problem. An option to disable
the select all could be made available when setting up parameters. This would
allow those that run into the problem to disable the functionality while
leaving it available for the remaining users.
PUT IT BACK
"Bruce L-C [MVP]" wrote:
> >>>>>>
> This is from Brian Welcker, the program manager for Reporting Services: When
> used in conjunction with Analysis Services, the Select All feature
> circumvents the built-in Analysis Services "All" member, individually
> selecting every member in a dimension. This causes such a massive
> performance degradation for AS queries (and is likely for users to use since
> all AS parameters are multi-valued). For SQL queries, it is not as impactful
> on performance (although an IN clause with 100 values is not very
> efficient).
> >>>>>>
> Given the above it was not an unreasonable way to go. Except, I don't care
> about Analysis Services personally so my preference would have been to leave
> it alone. Kindof a NIMBY feeling. Now that I know it is by design then I
> will work around it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
> news:C3A68DD9-9C3C-4111-89E7-436539274863@.microsoft.com...
> > Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have any
> > more
> > specifics? How was it causing problems? Is it just simply an issue of
> > users
> > choosing Select All which made the list of selected items huge causing
> > performance problems?
> >
> > Do you mind sharing who at MS you got that answer from?
> >
> > Unless there's some bug that it caused that I don't know about, my strong
> > opinion is that they shouldn't remove a feature just because some dumb
> > developers abused it and caused performance problems.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I just got info on this. What happened is the Select All parameter caused
> >> problems with Analysis Services. So, this is by design. What I will be
> >> doing
> >> is rewriting my queries and stored procedures that use this to use a new
> >> parameter I will add to the list called All.
> >>
> >> If a query it would look like this:
> >> select somefields from sometable where (@.Param='All' or somefield in
> >> (@.Param))
> >>
> >> This will result in better performance anyway but not as nice a user
> >> interface for multi-select. However, most of my non-multi-select
> >> parameters
> >> off an All option so my users are used to looking for it.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
> >> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
> >> > Have you heard anything back from Microsoft on this? It is causing me
> >> > real
> >> > problems with users.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> I just installed SP1 on a test machine and checked this out. I am
> >> >> getting
> >> >> the same results as you. I am not happy about it at all. I will use my
> >> >> contacts at MS to see what is happening.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> >> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> >> >> > Hi,
> >> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> >> >> > appear.
> >> >> > Without SP1 the "Select All" in Multi-Select Value did appear. How
> >> >> > can
> >> >> > I
> >> >> > fix
> >> >> > it?
> >> >> > Regards,
> >> >> > Tom
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Just found out from Chris Hays at MS that there's going to be a hotfix to
reenable the "select/unselect all" feature for parameters in SSRS. No date
announced yet, but I think it's coming. They'll eventually (whether in a SP
or the next release, I don't know) make it configurable.
"Big John" wrote:
> Microsoft made a BAD Choice in resolving this problem. An option to disable
> the select all could be made available when setting up parameters. This would
> allow those that run into the problem to disable the functionality while
> leaving it available for the remaining users.
> PUT IT BACK
>
> "Bruce L-C [MVP]" wrote:
> > >>>>>>
> > This is from Brian Welcker, the program manager for Reporting Services: When
> > used in conjunction with Analysis Services, the Select All feature
> > circumvents the built-in Analysis Services "All" member, individually
> > selecting every member in a dimension. This causes such a massive
> > performance degradation for AS queries (and is likely for users to use since
> > all AS parameters are multi-valued). For SQL queries, it is not as impactful
> > on performance (although an IN clause with 100 values is not very
> > efficient).
> > >>>>>>
> >
> > Given the above it was not an unreasonable way to go. Except, I don't care
> > about Analysis Services personally so my preference would have been to leave
> > it alone. Kindof a NIMBY feeling. Now that I know it is by design then I
> > will work around it.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
> > news:C3A68DD9-9C3C-4111-89E7-436539274863@.microsoft.com...
> > > Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have any
> > > more
> > > specifics? How was it causing problems? Is it just simply an issue of
> > > users
> > > choosing Select All which made the list of selected items huge causing
> > > performance problems?
> > >
> > > Do you mind sharing who at MS you got that answer from?
> > >
> > > Unless there's some bug that it caused that I don't know about, my strong
> > > opinion is that they shouldn't remove a feature just because some dumb
> > > developers abused it and caused performance problems.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> I just got info on this. What happened is the Select All parameter caused
> > >> problems with Analysis Services. So, this is by design. What I will be
> > >> doing
> > >> is rewriting my queries and stored procedures that use this to use a new
> > >> parameter I will add to the list called All.
> > >>
> > >> If a query it would look like this:
> > >> select somefields from sometable where (@.Param='All' or somefield in
> > >> (@.Param))
> > >>
> > >> This will result in better performance anyway but not as nice a user
> > >> interface for multi-select. However, most of my non-multi-select
> > >> parameters
> > >> off an All option so my users are used to looking for it.
> > >>
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
> > >> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
> > >> > Have you heard anything back from Microsoft on this? It is causing me
> > >> > real
> > >> > problems with users.
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> >> I just installed SP1 on a test machine and checked this out. I am
> > >> >> getting
> > >> >> the same results as you. I am not happy about it at all. I will use my
> > >> >> contacts at MS to see what is happening.
> > >> >>
> > >> >>
> > >> >> --
> > >> >> Bruce Loehle-Conger
> > >> >> MVP SQL Server Reporting Services
> > >> >>
> > >> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> > >> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> > >> >> > Hi,
> > >> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> > >> >> > appear.
> > >> >> > Without SP1 the "Select All" in Multi-Select Value did appear. How
> > >> >> > can
> > >> >> > I
> > >> >> > fix
> > >> >> > it?
> > >> >> > Regards,
> > >> >> > Tom
> > >> >> >
> > >> >> >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||Thanks for sharing with us.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:8000BBCE-9564-4D49-9D7A-B3DABBD62F5A@.microsoft.com...
> Just found out from Chris Hays at MS that there's going to be a hotfix to
> reenable the "select/unselect all" feature for parameters in SSRS. No date
> announced yet, but I think it's coming. They'll eventually (whether in a
> SP
> or the next release, I don't know) make it configurable.
> "Big John" wrote:
>> Microsoft made a BAD Choice in resolving this problem. An option to
>> disable
>> the select all could be made available when setting up parameters. This
>> would
>> allow those that run into the problem to disable the functionality while
>> leaving it available for the remaining users.
>> PUT IT BACK
>>
>> "Bruce L-C [MVP]" wrote:
>> > >>>>>>
>> > This is from Brian Welcker, the program manager for Reporting Services:
>> > When
>> > used in conjunction with Analysis Services, the Select All feature
>> > circumvents the built-in Analysis Services "All" member, individually
>> > selecting every member in a dimension. This causes such a massive
>> > performance degradation for AS queries (and is likely for users to use
>> > since
>> > all AS parameters are multi-valued). For SQL queries, it is not as
>> > impactful
>> > on performance (although an IN clause with 100 values is not very
>> > efficient).
>> > >>>>>>
>> >
>> > Given the above it was not an unreasonable way to go. Except, I don't
>> > care
>> > about Analysis Services personally so my preference would have been to
>> > leave
>> > it alone. Kindof a NIMBY feeling. Now that I know it is by design then
>> > I
>> > will work around it.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
>> > news:C3A68DD9-9C3C-4111-89E7-436539274863@.microsoft.com...
>> > > Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have
>> > > any
>> > > more
>> > > specifics? How was it causing problems? Is it just simply an issue of
>> > > users
>> > > choosing Select All which made the list of selected items huge
>> > > causing
>> > > performance problems?
>> > >
>> > > Do you mind sharing who at MS you got that answer from?
>> > >
>> > > Unless there's some bug that it caused that I don't know about, my
>> > > strong
>> > > opinion is that they shouldn't remove a feature just because some
>> > > dumb
>> > > developers abused it and caused performance problems.
>> > >
>> > > "Bruce L-C [MVP]" wrote:
>> > >
>> > >> I just got info on this. What happened is the Select All parameter
>> > >> caused
>> > >> problems with Analysis Services. So, this is by design. What I will
>> > >> be
>> > >> doing
>> > >> is rewriting my queries and stored procedures that use this to use a
>> > >> new
>> > >> parameter I will add to the list called All.
>> > >>
>> > >> If a query it would look like this:
>> > >> select somefields from sometable where (@.Param='All' or somefield in
>> > >> (@.Param))
>> > >>
>> > >> This will result in better performance anyway but not as nice a user
>> > >> interface for multi-select. However, most of my non-multi-select
>> > >> parameters
>> > >> off an All option so my users are used to looking for it.
>> > >>
>> > >>
>> > >> --
>> > >> Bruce Loehle-Conger
>> > >> MVP SQL Server Reporting Services
>> > >>
>> > >> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
>> > >> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
>> > >> > Have you heard anything back from Microsoft on this? It is
>> > >> > causing me
>> > >> > real
>> > >> > problems with users.
>> > >> >
>> > >> > "Bruce L-C [MVP]" wrote:
>> > >> >
>> > >> >> I just installed SP1 on a test machine and checked this out. I am
>> > >> >> getting
>> > >> >> the same results as you. I am not happy about it at all. I will
>> > >> >> use my
>> > >> >> contacts at MS to see what is happening.
>> > >> >>
>> > >> >>
>> > >> >> --
>> > >> >> Bruce Loehle-Conger
>> > >> >> MVP SQL Server Reporting Services
>> > >> >>
>> > >> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
>> > >> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
>> > >> >> > Hi,
>> > >> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does
>> > >> >> > not
>> > >> >> > appear.
>> > >> >> > Without SP1 the "Select All" in Multi-Select Value did appear.
>> > >> >> > How
>> > >> >> > can
>> > >> >> > I
>> > >> >> > fix
>> > >> >> > it?
>> > >> >> > Regards,
>> > >> >> > Tom
>> > >> >> >
>> > >> >> >
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >|||Has anybody used tried using this multi-select with dependent
parameters? It doesn't seem to work. For example, I have a query for
country and one for province:
SELECT '0' AS SortOrder, 'All' AS COUNTRY
UNION
SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
FROM Province
ORDER BY SortOrder
SELECT '0' AS SortOrder, 'All' AS PROVINCE
UNION
SELECT PROVINCE AS SortOrder, PROVINCE
FROM Province
WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
ORDER BY SortOrder
When the report renders, I can choose USA, and have all 50 states show
up. However, if I choose USA and Canada, the "Province" query breaks.
I also tried using the following query:
SELECT '0' AS SortOrder, 'All' AS PROVINCE
UNION
SELECT PROVINCE AS SortOrder, PROVINCE
FROM Province
WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
ORDER BY SortOrder
It didn't work any better. Without these dependent parameters, my
reports are basically useless. Any ideas? PLEASE?
Joe|||This is slightly off topic, I am MAD really MAD!
This is total BS it's akin to removing tires from a car after you purchased
it becuase some people like bigger tires!
I have lists of 200 plus selections that people are KILLING me over, because
the Default was to include all off their Units, now if they want to reduce
scope, they have to click 200 times! MICROSOFT YOU DID ME WRONG! I am very
upset, not only did I waste 3 hours trying to figure this out, I wasted other
peoples time as well. You'd think for the PRICES you charge you could figure
out something better than this...I am contacting Mr. Scoble and letting him
know what a bunch of idiots you are.
"joe nedumgottil" wrote:
> Has anybody used tried using this multi-select with dependent
> parameters? It doesn't seem to work. For example, I have a query for
> country and one for province:
> SELECT '0' AS SortOrder, 'All' AS COUNTRY
> UNION
> SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
> FROM Province
> ORDER BY SortOrder
> SELECT '0' AS SortOrder, 'All' AS PROVINCE
> UNION
> SELECT PROVINCE AS SortOrder, PROVINCE
> FROM Province
> WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
> ORDER BY SortOrder
>
> When the report renders, I can choose USA, and have all 50 states show
> up. However, if I choose USA and Canada, the "Province" query breaks.
> I also tried using the following query:
> SELECT '0' AS SortOrder, 'All' AS PROVINCE
> UNION
> SELECT PROVINCE AS SortOrder, PROVINCE
> FROM Province
> WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
> ORDER BY SortOrder
> It didn't work any better. Without these dependent parameters, my
> reports are basically useless. Any ideas? PLEASE?
> Joe
>|||Joe, You are right. I too have the same problem can you please let me know if
you slove this problem?
Thanks!
Reddy
"joe nedumgottil" wrote:
> Has anybody used tried using this multi-select with dependent
> parameters? It doesn't seem to work. For example, I have a query for
> country and one for province:
> SELECT '0' AS SortOrder, 'All' AS COUNTRY
> UNION
> SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
> FROM Province
> ORDER BY SortOrder
> SELECT '0' AS SortOrder, 'All' AS PROVINCE
> UNION
> SELECT PROVINCE AS SortOrder, PROVINCE
> FROM Province
> WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
> ORDER BY SortOrder
>
> When the report renders, I can choose USA, and have all 50 states show
> up. However, if I choose USA and Canada, the "Province" query breaks.
> I also tried using the following query:
> SELECT '0' AS SortOrder, 'All' AS PROVINCE
> UNION
> SELECT PROVINCE AS SortOrder, PROVINCE
> FROM Province
> WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
> ORDER BY SortOrder
> It didn't work any better. Without these dependent parameters, my
> reports are basically useless. Any ideas? PLEASE?
> Joe
>|||Joe, Try this it will work.
WHERE (COUNTRY IN (@.Country) or 'All' in(@.Country))
ORDER BY SortOrder
"Reddy" wrote:
> Joe, You are right. I too have the same problem can you please let me know if
> you slove this problem?
> Thanks!
> Reddy
> "joe nedumgottil" wrote:
> > Has anybody used tried using this multi-select with dependent
> > parameters? It doesn't seem to work. For example, I have a query for
> > country and one for province:
> >
> > SELECT '0' AS SortOrder, 'All' AS COUNTRY
> > UNION
> > SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
> > FROM Province
> > ORDER BY SortOrder
> >
> > SELECT '0' AS SortOrder, 'All' AS PROVINCE
> > UNION
> > SELECT PROVINCE AS SortOrder, PROVINCE
> > FROM Province
> > WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
> > ORDER BY SortOrder
> >
> >
> > When the report renders, I can choose USA, and have all 50 states show
> > up. However, if I choose USA and Canada, the "Province" query breaks.
> > I also tried using the following query:
> >
> > SELECT '0' AS SortOrder, 'All' AS PROVINCE
> > UNION
> > SELECT PROVINCE AS SortOrder, PROVINCE
> > FROM Province
> > WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
> > ORDER BY SortOrder
> >
> > It didn't work any better. Without these dependent parameters, my
> > reports are basically useless. Any ideas? PLEASE?
> >
> > Joe
> >
> >|||FYI, MSFT cosed this issue as "by design" in August.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124729
Not cool. My first disappointment with SQL Server in a long time
"Reddy" <Reddy@.discussions.microsoft.com> wrote in message
news:4466EA95-5E12-4497-BF35-2B0FC6AA9B38@.microsoft.com...
> Joe, Try this it will work.
> WHERE (COUNTRY IN (@.Country) or 'All' in(@.Country))
> ORDER BY SortOrder
>
> "Reddy" wrote:
>> Joe, You are right. I too have the same problem can you please let me
>> know if
>> you slove this problem?
>> Thanks!
>> Reddy
>> "joe nedumgottil" wrote:
>> > Has anybody used tried using this multi-select with dependent
>> > parameters? It doesn't seem to work. For example, I have a query for
>> > country and one for province:
>> >
>> > SELECT '0' AS SortOrder, 'All' AS COUNTRY
>> > UNION
>> > SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
>> > FROM Province
>> > ORDER BY SortOrder
>> >
>> > SELECT '0' AS SortOrder, 'All' AS PROVINCE
>> > UNION
>> > SELECT PROVINCE AS SortOrder, PROVINCE
>> > FROM Province
>> > WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
>> > ORDER BY SortOrder
>> >
>> >
>> > When the report renders, I can choose USA, and have all 50 states show
>> > up. However, if I choose USA and Canada, the "Province" query breaks.
>> > I also tried using the following query:
>> >
>> > SELECT '0' AS SortOrder, 'All' AS PROVINCE
>> > UNION
>> > SELECT PROVINCE AS SortOrder, PROVINCE
>> > FROM Province
>> > WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
>> > ORDER BY SortOrder
>> >
>> > It didn't work any better. Without these dependent parameters, my
>> > reports are basically useless. Any ideas? PLEASE?
>> >
>> > Joe
>> >
>> >sql

Multiselect problem

hi ,
following parameter @.a has multi values. but when i select the multi value it gives some error.
is this the correct method i use? and is there any differrent way that i should use in the report designer when i use the value of that parameter ... Field!a.value etc.?\

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

The descendants function expects a single member for the first parameter, to get this to work with mulitple parameters you would need to use the generate function. I'm not exactly sure what your structures look like so you will need to fill in the dimension and hierarchy of the members in @.a in the code below

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.
|||hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set Angel as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:
that is fine. but how can i get only the selected value.
WITH
MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'
SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))
SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows
FROM Profitability
this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

Also I would not do the "if empty return 0" logic in the MDX, if you are using Reporting Services, you can use a format string in the report to return 0 for null values. I often use format strings like "$0;($0);-;-" which returns negative values in brackets and 0 and null as dashes.|||

lk_wick wrote:
hey darren
u knw how to solve following problem?
HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?
i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

set [test ] as strtoset(@.BU) bu query parameter is in differrent datset.
|||Query parameters are fed from Report parameters, so you should be able to map both datasets to using the same report parameter.

Multiselect problem

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

i need to access it thru the mdx query like...
set Angel as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:
that is fine. but how can i get only the selected value.
WITH
MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'
SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))
SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows
FROM Profitability
this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

lk_wick wrote:
hey darren
u knw how to solve following problem?
HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?
i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

Multi-Select in Query Possible?

Hi NG,
I want to make a query where I want to let the user select turnovers
within a month, w

, day of w

aso. and combinated, eg.
give me alle turnovers from month = December and Day of W

= Monday
or just all turnovers from w

50
I want to do this within a stored procedure, with the parameters @.year,
@.month, @.w

, @.day, @.region. If the value of the parameter is 0, it is
not important.
So an execute could be EXEC sp_GetTurnover 0, 12, 0, 1, 0 for all
Mondays in December
How can I do a Select with this on the Turnover table?
SELECT sum(Amount)
FROM _Turnover
WHERE Year = @.year
AND ...
doesnt work, because it would result Year = 0, thats incorrect.
Thank you very much
Rudiwell, you could go with the following but exec plan probably wouldnt be
efficient:
SELECT sum(Amount)
FROM _Turnover
WHERE
(Year = @.year OR @.year = 0) AND .
(Month = @.month OR @.month = 0)
and so on...
MC
<rudolf.ball@.asfinag.at> wrote in message
news:1133957507.584643.151700@.f14g2000cwb.googlegroups.com...
> Hi NG,
> I want to make a query where I want to let the user select turnovers
> within a month, w

, day of w

aso. and combinated, eg.
> give me alle turnovers from month = December and Day of W

= Monday
> or just all turnovers from w

50
> I want to do this within a stored procedure, with the parameters @.year,
> @.month, @.w

, @.day, @.region. If the value of the parameter is 0, it is
> not important.
> So an execute could be EXEC sp_GetTurnover 0, 12, 0, 1, 0 for all
> Mondays in December
> How can I do a Select with this on the Turnover table?
> SELECT sum(Amount)
> FROM _Turnover
> WHERE Year = @.year
> AND ...
> doesnt work, because it would result Year = 0, thats incorrect.
> Thank you very much
> Rudi
>|||Read this article
http://www.sommarskog.se/dyn-search.html
<rudolf.ball@.asfinag.at> wrote in message
news:1133957507.584643.151700@.f14g2000cwb.googlegroups.com...
> Hi NG,
> I want to make a query where I want to let the user select turnovers
> within a month, w

, day of w

aso. and combinated, eg.
> give me alle turnovers from month = December and Day of W

= Monday
> or just all turnovers from w

50
> I want to do this within a stored procedure, with the parameters @.year,
> @.month, @.w

MultiRow select to SingleRow output?

Here is what I want to do.

I have a customer table with say id, firstname, lastname. I also have another table called phone number that contains multiple phone numbers based on the customer id (id, customerid, phonenumber).

Ok I want to output a record like this

firstname, lastname, phone1, phone2, phone3

I need one record per customer with as many phone numbers as they may have, I could always limit the numbers to 3 or better yet return a null if there are less then the predefined number of phone numbers.

If I try a simple join I get multiline output which is no good for me.

SELECT firstname, lastname, phonenumber
FROM customer, phone
WHERE phone.customerid = customer.id

Thanks.---------------
create function dbo.PhoneString(@.customerid int)
returns varchar(500)
as
begin
declare @.PhoneString varchar(500)

set @.PhoneString = ''

select @.PhoneString = @.PhoneString + phonenumber + ', '
from PhoneNumberTable
where customerid = @.customerid

set @.PhoneString = left(@.PhonesString, len(@.PhoneString)-1)

return @.PhoneString
end
---------------
select firstname, lastname, dbo.PhoneString(ID) from customers

multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

Many thanks,
AlexAlessandro (giumalex@.tiscali.it) writes:
> I must create a store procedure that do the following...
> 1. select id_name,...'some other field' from table 'data' where
> 'criteria' = @.parameter
> 2. use the result of the first selection to create another selection
> combining that results with the table 'name' where id_name is null in the
> result selection...
> In other word I have a first query that use a passed parameter, the result
> of that query should be combined this another query, and the result should
> be return from the store procedure...

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Alessandro" <giumalex@.tiscali.it> wrote in message news:<g8tkc.30640$eR5.16238@.tornado.fastwebnet.it>...
> Hi, here is the problem...
>...
> I can't realize how to write this 'simple' problem... can anyone help me?
> Many thanks,
> Alex

Hi Alex.. to work with SQL , query and paramter look at this site :

Http://www.RealTimeInformatica.it/Stany

Bye Bye ...

ps: anche in Italiano !;)

Friday, March 23, 2012

multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Multipul Selections in a Parameter field

I have a report that I would like to allow the user to use the Ctrl key to select mutiple cost centers in a report parameter field. Does anyone know how this would be done
From http://www.developmentnow.com/g/115_2004_11_0_4_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf you are using 2005 why do you need a ctrl key for selection, instead you
can use the multiple check box to pick multiple values.
Amarnath
"Duvon Harper" wrote:
> I have a report that I would like to allow the user to use the Ctrl key to select mutiple cost centers in a report parameter field. Does anyone know how this would be done?
> From http://www.developmentnow.com/g/115_2004_11_0_4_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

Multiples in a select statement

Is it possible to write a single select statement that would pull the following from a table named tblPayments.

I need the 'first payment date and amount', 'last payment date and amount', 'largest payment date and amount', and 'total payments' for each client by a date range.

tblPayments
ClientID
PaymentDate
PaymentAmount

Thanks for any suggestions.

create table p (ClientID int, PaymentDate datetime, PaymentAmount numeric(10,2))

insert into p
select 0,'1/1/2004',100 union all
select 0,'1/2/2004',80 union all
select 0,'1/3/2004',110 union all
select 1,'1/5/2004',87 union all
select 1,'1/12/2004',180 union all
select 1,'1/13/2004',10

select
d.clientID,
d.firstPmtDate,
p1.PaymentAmount,
d.lastPmtDate,
p2.PaymentAmount,
p3.PaymentDate maxPmtDate,
d.maxPmt,
d.ttlPmt
from
(
select
clientID,
min(paymentdate) firstPmtDate,
max(paymentDate) lastPmtDate,
max(PaymentAmount) maxPmt,
sum(PaymentAmount) ttlPmt
from p
--where PaymentDate between @.startDate and @.endDate
group by clientid
) d
join p p1 on p1.clientid = d.clientid and p1.paymentdate = d.firstPmtDate
join p p2 on p2.clientid = d.clientid and p2.paymentdate = d.lastPmtDate
join p p3 on p3.clientid = d.clientid and p3.PaymentAmount = d.maxPmt
--where p3.PaymentDate between @.startDate and @.endDate

|||Thank you, but this does not appear to return the amounts associated with each last and first payment dates. That is where I run into problems. I could be reading it bad?

Thanks again,|||Did you run the query in QA with the sample data?|||Ok, It worked in QA. I'm having some trouble understanding how you did this. How can I learn to understand this type of process?

Thanks for any suggestions,|||I'm having trouble converting this to my data. Is there anything specific I should know to do this?

Thanks again,|||What are you having trouble with specifically?|||Well, I get no errors but there is no data being returned.

Thanks,|||To demonstrate the query, I created a table called p, which represents your table tblPayments, it is an example. Then the table p is aliased as p1, p2,p3 where the joins are occuring to simplify the columns assignments. The alias d is used to declare the derived table which perform the aggregate functions to obtain max,min,etc...

If you want to use this query for you purposes you can change the table assignment of p in the from and join clauses to tblPayments.

Does that help to clarify?|||Yes, and that is what I did and I know there is data but none is being returned. This very puzzling.

Thanks again,|||I had not changed the table name in the joins and had listed my table AS p in the From clause. Now I get data, but I get many records for each client. I was hoping to end up with one row for each client. Any thoughts on this?

Thanks again,|||Can you post the revised query you are using..|||Here it is. Thanks,

select

d.Client_ID,

d.firstPmtDate,

p1.AmountPaid,

d.lastPmtDate,

p2.AmountPaid,

p3.PaymentDate maxPmtDate,

d.maxPmt,

d.ttlPmt

from

(

select

Client_ID,

min(paymentdate) firstPmtDate,

max(paymentDate) lastPmtDate,

max(AmountPaid) maxPmt,

sum(AmountPaid) ttlPmt

from tblPayments

--where PaymentDate between @.startDate and @.endDate

group by Client_ID

) d

join tblPayments p1 on p1.Client_ID = d.Client_ID and p1.paymentdate = d.firstPmtDate

join tblPayments p2 on p2.Client_ID = d.Client_ID and p2.paymentdate = d.lastPmtDate

join tblPayments p3 on p3.Client_ID = d.Client_ID and p3.AmountPaid = d.maxPmt

--where p3.PaymentDate between @.startDate and @.endDate|||It is somewhat difficult to fully understand the data behavior w/o seeing the data and variations that occur. If the rows are displaying duplicate data for each client you can modify the main select to select distinct which will limit dups from the results set.

Hope that helps.sql

Wednesday, March 21, 2012

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?
Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare one
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare on
e
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Friday, March 30, 2012

Multi-Value parameter syntax error

Multi-Value parameter Select All

Multi-Value Parameter Problem

Multi-Value Parameter

MultiValue Parameter

Wednesday, March 28, 2012

Multitable Select

Multitable Select

Multi-Table Linked Query?

Monday, March 26, 2012

Multi-Select Value dropdown "Select All" Problem

Multi-Select Value dropdown "Select All" in SP1

Multiselect problem

Multiselect problem

Multi-Select in Query Possible?

MultiRow select to SingleRow output?

multiquery in a store procedure...

Friday, March 23, 2012

multiquery in a store procedure...

Multipul Selections in a Parameter field

Multiples in a select statement

Wednesday, March 21, 2012

multiple xml namespaces

multiple xml namespaces

multiple resultsets

Blog Archive

About Me