Parsing Varchar2

I need to parse out some alphabetic characters from a Varchar2 field to leave only the number values. The field values look like this $40-35 or say $40$35.
Is there a sql function or statement to do this. Any help is greatly appreciated.
andavianselect to_number(substr(substr('$40-35' ,2),1,2)), to_number(substr(substr('$40-35' ,2),4,2)) from dual

is your select statement to parse out your 2 dollar amounts.

Originally posted by andavian
I need to parse out some alphabetic characters from a Varchar2 field to leave only the number values. The field values look like this $40-35 or say $40$35.

Is there a sql function or statement to do this. Any help is greatly appreciated.

andavian|||This will work only if the position of $ sign is fixed and the length of the string is fixed. Is it So?|||Yes, this is because you have given a fixed length variable and did not mention about how dynamic your VARCHAR would be. This assumes ur variable will be either ''$40-35" or "$40-35" which you have mentioned in your Question.
Originally posted by Rushi
This will work only if the position of $ sign is fixed and the length of the string is fixed. Is it So?

Parsing values from sp_spaceused stored proc.

I am using sp_spaceused stored procedure to get the database_size result.
The result comes back as a string such as "512 KB" or "100 MB". I really
need a number value representation of the size. I though about parsing the
string, but I was not sure if there were any other results of the string
that I may not account for. Is there any standard way to parse this string,
or should I just assume that the format of the string can either have KB or
MB at the end and parse it based on that assumption?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
--You can look at the contents of the stored proc via the Enterprise Manager
or
via
use master
go
sp_Helptext sp_spaceused
I don't see any values represented other than KB and MB
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken
You can create a table and store an output from sp_spaceused there.
If I understood you need the number only. So see if this helps you.
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken,
To be sure, see the sp code from master database.
exec master..sp_helptext sp_spaceused
go
AMB
"Ken Varn" wrote:

> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing th
e
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this strin
g,
> or should I just assume that the format of the string can either have KB o
r
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>
>

Parsing string and Inserting each element?

Hi All,
I'll try and make this simple.
I have a column in a table that has one or more values separated by a comma.
Ex: 1234,456,322,33445,abce,ekksks
I want to go through each record and take this column data and parse it out,
then insert each element into another table.
ex:
INSERT Into GTable (RecordID, ItemValue)
Values (NewID(), <Parsed Values from each record's
Column> )
Any ideas would be greatly appreciated.
John.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"John Rugo" wrote:

> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a comm
a.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it ou
t,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>
>|||Do you mean inserting each value as a separate row? If I were to create the
insert statements manually for your example, would it look like:
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '1234')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '456')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '322')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '33445')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'abce')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'ekksks')
?
If so, then a cursor would probably be the best way to go (hopefully this is
a one time thing, because as we all know, cursors as the devil)...
If you want some quickly hacked code as an example of how the cursor would
work, post back and I'll see if I can put something together..
-Cliff
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:Ot0XwOsPFHA.4028@.tk2msftngp13.phx.gbl...
> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a
comma.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it
out,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>|||John,
Here is an efficient way to do this - it's buried in one of the articles
Alejandro referred you to.
/*
A table-valued function with one parameter, a delimited list,
that returns the separate distinct items of the list.
Steve Kass, Drew University
Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
for help and helpful discussions on this.
*/
--A table of integers is needed
create table Seq (
Nbr int not null
)
insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @.i int
set @.i = -1
update Seq
set @.i = Nbr = @.i + 1
alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go
--This makes things more readable. The list is easier
--to process if it begins and ends with a single comma
--As it turns out also, list items cannot
--have leading or trailing spaces (here any leading spaces
--in the first item or trailing spaces in the last are
--eliminated)
create function RegularizedList (@.List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@.List))+',', ',,', ',')
end
go
--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @.List, except the last
--This function returns a table containing the items in the list.
--The items are extracted by selecting those substrings of
--the list that begin immediately after a comma and end
--immediately before the next comma, then trimming spaces on
--both sides.
create function ListTable (@.List varchar(8000))
returns table as return
select
ltrim(rtrim(
substring(regL,
commaPos+1,
charindex(',', regL, commaPos+1) - (commaPos+1))))
as Item
from (
select Nbr as commaPos
from Seq, (
select dbo.RegularizedList(@.List) as regL
) R
where substring(regL,Nbr,1) = ','
and Nbr < len(regL)
) L, (
select dbo.RegularizedList(@.List) as regL
) R
go
--examples
declare @.x varchar(4000), @.time datetime
set @.time = getdate()
set @.x = replicate('foo,bar,foo,bar,ab,',30) + 'end'
select distinct Item from ListTable(@.x)
select datediff(ms,@.time,getdate())
set @.x = '10245 10345 98292 '
declare @.s varchar(400)
set @.s = replace(@.x,' ',',')
select * from ListTable(@.s)
--Note, if a list contains a non-comma delimiter, and contains no
--commas within items, this replacement allows the function to
--handle it. If a comma appears in an item, but some other non-
--delimiter is absent from the list, a three-step replacement can
--be made:
-- replace all commas with new character not in list
-- replace all delimiters with comma
-- Use (select replace(Item,<new>,<comma> ) from ListTable(@.List)) LT
-- where the list table is used.
go
--Since this is a repro script, delete everything!
--Keep them around if they are helpful, though.
DROP FUNCTION RegularizedList
DROP TABLE Seq
DROP FUNCTION ListTable
-- Steve Kass
-- Drew University
John Rugo wrote:

>Hi All,
>I'll try and make this simple.
>I have a column in a table that has one or more values separated by a comma
.
>Ex: 1234,456,322,33445,abce,ekksks
>I want to go through each record and take this column data and parse it out
,
>then insert each element into another table.
>ex:
>INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
>Column> )
>Any ideas would be greatly appreciated.
>John.
>
>

Parse values from delimited string

Hi. I have a string like this which will be passed into a SQL Server 2000 stored procedure,

[15438|39][21347|96][24198|23]....

I need to take the values in the above string and insert them into a temp table so they will look like this. How can a delimited string be parsed into this? Thanks.

Exam ID Branch Number 15438 39 21347 96 24198 23

Look at the use of Jens' Split function, available here.

Seems like you need to split on the brackets, ][

and then again on the pipe, |

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||The Split function returns a table. How would I call the function if it needs to split the string twice? An example is very much appreciated. Also, I'm thinking of passing the data this way to make it even simpler,

|23415^33|23451^23|....
|||

I recommand to use the XML input rather than the delimted string. You already have a structured string. Just replace the string as follow as and utilize the OPENXML; Since your schema is simple it will be simple and faster. But String manipulation will hit your performance badly(bcs you have to do split of split of split).

Sample with your input...

Code Snippet

Declare @.Data as Varchar(100);

Declare @.XML as Varchar(8000);

Set @.Data = '[15438|39][21347|96][24198|23]'

Set @.XML = Replace(Replace(Replace(@.Data,'[','<row><col1>'),'|','</col1><col2>'),']','</col2></row>')

select @.XML = '<Data>' + @.XML + '</Data>'

Declare @.iDoc as Int;

Exec sp_xml_preparedocument @.iDoc OUTPUT, @.XML

Select * From OpenXML(@.iDoc, 'Data/row', 2) With (col1 int, col2 int)

Exec sp_xml_removedocument @.iDoc

You can pass the input from your server as follow as. (Advantage: You can pass text datatype from your UI to database, but delimted values wont allow more than 8000 chars)

Code Snippet

<Root>

<row>

<Col1>15438</Col1>

<Col2>39</Col2>

</row>

<row>

<Col1>21347</Col1>

<Col2>96</Col2>

</row>

<row>

<Col1>24198</Col1>

<Col2>23</Col2>

</row>

</Root>

|||Since you can alter the input string format, I suggest that you use comma delimited, and follow Mani's suggestion about using XML.|||

Mani,

You're quite right. String manipulation is not good with T-SQL. (Unfortunately, shredding xml isn't so hot either...)

But in this case, it may be the lesser of the evils...

|||

Yes Arnie. Since the schema is simple OPENXML perform well.

In future if they want to migrate to SQL Server 2005, they can utilize the XQuery featuer. Where we can avoid the preparedocument.

|||

As a quick aside, did you verify the code you posted for the OP?

It didn't run for me...

|||Yes. All tags are got supressed. Corrected Now. Thank you. |||You should fix the client or application sending the data to send it in a different format (xml, fixed length blob) or call the SP multiple times. You can also dump the rows into a temporary table and process the rows in the SP. Doing string manipulations is slow and kludgy.

Parent to Child mappings identification in a single query

Hi,

I want to retrive the output in a single SQL query.

I had two columns in a table; I used some values of column1 in column2.
I want to identify all the column1 vlaues in Left Hand side and column2 values in Right Hand side, with a single query.

example:

MasterTable

ParentRow ChildRow
--
A B
C D
E F
G C
H E
M G

Output required:

ParentRow ChildRow
--
A B
C D
E F
G D (C mapped tfrom D)
H F
M D (G mapped from C, and C mapped from D)

To get the above output, I written query using WHILE...[BREAK|CONTINUE] concept until I get @.@.rowcount become zero.

I am looking for better performace and to get the output in a single query, instead of multiple passes.

Can any one suggest good tools and articles on performance tuning techniques?

Thanks
Sreekanth

Waht is your database? SQL Server 2000 or 2005?

|||

If you use SQL Server 2005,

Code Snippet

Create Table #data (

[ParentRow] char ,

[ChildRow] char

);

Insert Into #data Values('A','B');

Insert Into #data Values('C','D');

Insert Into #data Values('E','F');

Insert Into #data Values('G','C');

Insert Into #data Values('H','E');

Insert Into #data Values('M','G');

;WITH TREE

as

(

Select

[ParentRow],

[ChildRow],

[ChildRow] as [Alternate],

Cast('*' + '\' + '*' as varchar(max)) Path

from #Data

Union ALL

Select

data.[ParentRow],

data.[ChildRow],

Tree.[Alternate],

Cast(Path + '\' + '*' as varchar(max)) Path

from #Data as data

Join Tree On Data.[ChildRow] = Tree.[ParentRow]

)

,Tree2 as

(

Select

[ParentRow],

[ChildRow],

[Alternate],

Len(path) as PathLen,

Max(Len(path)) Over(Partition BY [ParentRow], [ChildRow]) MaxLen

from Tree

)

select

[ParentRow],

[Alternate]

from

Tree2

Where

PathLen=MaxLen

Drop table #data

|||

If you use SQL server 2000,

Code Snippet

create table datatable (

[parentrow] char ,

[childrow] char

);

insert into datatable values('A','B');

insert into datatable values('C','D');

insert into datatable values('E','F');

insert into datatable values('G','C');

insert into datatable values('H','E');

insert into datatable values('M','G');

go

create function findalternate(@.childvalue char)

returns char

as

begin

declare @.return as char;

select @.return = childrow from datatable where parentrow=@.childvalue;

if @.return is null

return @.childvalue

else

return dbo.findalternate(@.return)

return null;

end

go

select *, dbo.findalternate(childrow) from datatable

|||

I am using SQL 2005 Server.

Thank you for providing the T-SQL.

Parent Package variable assignment issue in Child Package.

We

have one main package from which 7 other child packages are called. We are using

ParentPackage variables to assign values for variables and database connections.

While the values from ParentPackage variable get assigned to some of the

packages properly, to others it doesn’t assign the value.

For example:

Except for one of the packages the database connection string gets assigned

properly to all other packages.

Similarly, in another package one of the

variables doesn’t get assigned. In other packages it is assigned properly.

We

have checked all the other property values and they are exactly the same.

We cannot make any head or tail of this erratic behavior.

Please Help.

The only known issue I am aware of is that Package configurations based on parent package variables always occour last no matter its place in the configuration organizer.

This represent an issue when you use this method to configure a connection string that would be used by other configuration (when using SQL Server based package configurations). But this should not be the case when it is used to assign variable values, etc.

Make sure the spelling of the variable name in the child package is right; also enable package logging an see is there is any 'Warning' at the begining of the execution log. When a package configuration does not occur a warning is generated. If you are running the package via BIDS review the progress tab as it offers the same info than the logging.

|||Thanks,

The issue has got solved. There was the problem of Case of the variable names only and I also came to know by checking the warnings.

Thanks again.

Partitioning error

Hi,

I am trying to implement partitioning on a table
depending upon the fiscal_month value...

The current values are from 1-6...

Create partition function LoadDataPartitionFunction ( smallint)
as
Range for values (1,2,3,4,5,6)

-- drop partition scheme LoadDataPartitionScheme
create partition scheme LoadDataPartitionScheme
as
Partition LoadDataPartitionFunction ALL to ([PRIMARY])

CREATE TABLE Load_Data_Partition (
[RowID] [int] NOT NULL,
[Fiscal_Month] [smallint] NOT NULL,
[Fiscal_Year] [smallint] NOT NULL,
...

[Service] [nvarchar](100) COLLATE
) ON LoadDataPartitionScheme (Fiscal_Month)

truncate table Load_Data_old -- same schema as load_data_partition
Alter table load_data_partition switch partition 1 to Load_Data_old

-- which month's data to be moved out
alter partition function LoadDataPartitionFunction () merge range (1)

Alter partition scheme LoadDataPartitionScheme next used [primary]

-- which months data to be moved in
alter partition function LoadDataPartitionFunction () split range(7)

Select * from sys.partition_range_values

function_id boundary_id parameter_id value
---- ---- ---- --
65545 1 1 2
65545 2 1 3
65545 3 1 4
65545 4 1 5
65545 5 1 6
65545 6 1 7

Alter table [Load_Data_new] switch to [Load_Data_partition] partition 6

ALTER TABLE SWITCH statement failed. Check constraints of source table Load_Data_new' allow values that are not allowed by range defined by partition 6 on target table 'Load_Data_partition'.

Values in Load_Data_new for fiscal_month is 7

But when i try

Insert into [Load_Data_partition]
Select * from [Load_Data_new]
where fiscal_month = 7

it works fine...

reference used : http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htmI got the answer..

Alter table Load_Data_new add constraint load_data_new_month check ( fiscal_month =7)

even though the Load_Data_new table has only month = 7 data...
a constraint is mandatory....

Parse Column and Sort

Hi can anyone give your idea on how this can be implemented.
I have a table which has a column whose values looks like
Page 10 Line 1
Document
1
3
Sheet: Landscape
Sheet: Part 2
Page: 29 Line: 2
Page91
Page 10
Sheet: Sheet1
i need to sort by Page number and line number if no line number then page
number alone or if no page number then sort by sheet number else normal sort
Can anyone throw some light on itBoss
Why not do that on client?
CREATE TABLE #Test
(
col1 VARCHAR(15)
)
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END DESC
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> Hi can anyone give your idea on how this can be implemented.
> I have a table which has a column whose values looks like
> Page 10 Line 1
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Part 2
> Page: 29 Line: 2
> Page91
> Page 10
> Sheet: Sheet1
> i need to sort by Page number and line number if no line number then page
> number alone or if no page number then sort by sheet number else normal
sort
>
> Can anyone throw some light on it
>|||Can't you clean up the data and create a table where these attributes
are properly represented in atomic columns? Storing meaningful data in
this form is an extremely poor and inconvenient design. That's why it's
usual to scrub and transform external data before it is loaded into
tables.
If a redesign is impossible for you then take a look at CHARINDEX and
PATINDEX. You can extract the different string components into separate
calculated columns and then sort on those.
If you need more help, please post DDL (CREATE TABLE statement
including keys and constraints), some sample data (as INSERT
statements) and show your required end result.
David Portas
SQL Server MVP
--|||Hi Thanks for your reply
It dosent seem to return correct records
CREATE TABLE #Test
(
col1 VARCHAR(25)
)
-- drop table #test
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page: 29 Line: 1')
INSERT INTO #Test VALUES ('Page: 29 Line: 10')
INSERT INTO #Test VALUES ('Page: 2 Line: 18')
INSERT INTO #Test VALUES ('Page: 71 Line: 1')
INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page1')
INSERT INTO #Test VALUES ('Page 20')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Page 8')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
INSERT INTO #Test VALUES ('Sheet: Sheet21')
INSERT INTO #Test VALUES ('Sheet: Sheet10')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
The result is
Document
1
3
Sheet: Landscape
Sheet: Sheet1
Sheet: Sheet21
Sheet: Sheet10
Page: 201 Line: 1000 - this is in wrong order
Page1
Page 8
Page91
Page 10
Page 20
Page 10 Line 1
Page 10 Line 1
Page 10 Line 1
Page: 2 Line: 18 - this is in wrong order
Page: 29 Line: 1
Page: 29 Line: 2
Page: 71 Line: 1 - this is in wrong order
Page: 29 Line: 10
"Uri Dimant" wrote:

> Boss
> Why not do that on client?
> CREATE TABLE #Test
> (
> col1 VARCHAR(15)
> )
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END DESC
>
>
>
> "Boss" <Boss@.discussions.microsoft.com> wrote in message
> news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> sort
>
>|||SELECT col1 FROM
(
SELECT replace(col1,' ','')as col1 FROM #Test
) AS Der
ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
If it does not help ,post expected result, as I and David stated you will be
better off to do that on the client side, but befor that clean up the table
from redundant data
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:B175FA7A-14E9-4A57-A018-F7B48C805937@.microsoft.com...
> Hi Thanks for your reply
> It dosent seem to return correct records
> CREATE TABLE #Test
> (
> col1 VARCHAR(25)
> )
> -- drop table #test
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page: 29 Line: 1')
> INSERT INTO #Test VALUES ('Page: 29 Line: 10')
> INSERT INTO #Test VALUES ('Page: 2 Line: 18')
> INSERT INTO #Test VALUES ('Page: 71 Line: 1')
> INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page1')
> INSERT INTO #Test VALUES ('Page 20')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Page 8')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> INSERT INTO #Test VALUES ('Sheet: Sheet21')
> INSERT INTO #Test VALUES ('Sheet: Sheet10')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END ASC
> The result is
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Sheet1
> Sheet: Sheet21
> Sheet: Sheet10
> Page: 201 Line: 1000 - this is in wrong order
> Page1
> Page 8
> Page91
> Page 10
> Page 20
> Page 10 Line 1
> Page 10 Line 1
> Page 10 Line 1
> Page: 2 Line: 18 - this is in wrong order
> Page: 29 Line: 1
> Page: 29 Line: 2
> Page: 71 Line: 1 - this is in wrong order
> Page: 29 Line: 10
>
> "Uri Dimant" wrote:
>
page
normal

Parse Array Data Type to Rows

Hi,

We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.

Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.

Any Ideas?

Thanks

Emilio

In a data flow, use a script task to parse and pivot the array values, then send them to a asynchronous output that writes to the destination table.|||

Hi,

Thank you for the reply.

Do you know of any samples that I can have a look at to get me going in the right direction?

Warm Regards

Emilio

|||

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/17/dynamically-pivoting-columns-to-rows.aspx

Hopefully that will provide a good starting point.

|||

Excellent!!!

I can say that this is the answer to handling those db's that have an array datatype!

Paramters with Multiple Values

I know I must be missing something obvious but I cannot find it in the
documentation. How do I allow the user to input multiple values for a report
parameter. For example, the main query has this for test criteria:
where CustID in(100, 200, 300)
so I changed to:
IN(@.CustId) expecting to input 100,200,300
but instead I get an error "Application uses a value of the wrong type for
the current operation"
? ThanksTo do that you have to write dynamic sql and your parameter has to be a
varchar... the reason you are getting an error is you are comparing an
INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
"100,200,300" to integers.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> I know I must be missing something obvious but I cannot find it in the
> documentation. How do I allow the user to input multiple values for a
report
> parameter. For example, the main query has this for test criteria:
> where CustID in(100, 200, 300)
> so I changed to:
> IN(@.CustId) expecting to input 100,200,300
> but instead I get an error "Application uses a value of the wrong type for
> the current operation"
> ? Thanks
>|||So does that mean if it were a varchar column it would accept the commas and
treat them as delimiters between separate values ie 100 OR 200 OR 300?
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> To do that you have to write dynamic sql and your parameter has to be a
> varchar... the reason you are getting an error is you are comparing an
> INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> "100,200,300" to integers.
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
>> I know I must be missing something obvious but I cannot find it in the
>> documentation. How do I allow the user to input multiple values for a
> report
>> parameter. For example, the main query has this for test criteria:
>> where CustID in(100, 200, 300)
>> so I changed to:
>> IN(@.CustId) expecting to input 100,200,300
>> but instead I get an error "Application uses a value of the wrong type
>> for
>> the current operation"
>> ? Thanks
>>
>|||No, it would treat it as one string... the easiest way to do a query with a
parameter that contains multiple integer values is dynamic sql like...
DECLARE @.CustID VARCHAR(100)
DECLARE @.sql VARCHAR(1000)
SET @.CustID = '100,200,300'
SELECT @.sql = 'SELECT * FROM Customer WHERE CustID IN (' + @.CustID + ')'
EXEC(@.SQL)
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:O1XluJDyEHA.2212@.TK2MSFTNGP15.phx.gbl...
> So does that mean if it were a varchar column it would accept the commas
and
> treat them as delimiters between separate values ie 100 OR 200 OR 300?
> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
> news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> > To do that you have to write dynamic sql and your parameter has to be a
> > varchar... the reason you are getting an error is you are comparing an
> > INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> > "100,200,300" to integers.
> >
> >
> > "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> > news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> >> I know I must be missing something obvious but I cannot find it in the
> >> documentation. How do I allow the user to input multiple values for a
> > report
> >> parameter. For example, the main query has this for test criteria:
> >> where CustID in(100, 200, 300)
> >> so I changed to:
> >> IN(@.CustId) expecting to input 100,200,300
> >> but instead I get an error "Application uses a value of the wrong type
> >> for
> >> the current operation"
> >>
> >> ? Thanks
> >>
> >>
> >
> >
>

Paramter posting back?

I have a report with four paramters.
A "Start Year" and "End Year" that are strings with values from a query, so
available valuse are 2003,2004. 2005, and 2006.
Two datetime for a "Start Transaction Date" and "End Transaction Date".
When I set the "Start Year" parameter, the report seems to post back and I
get the error:
The value provided for the report parameter 'TransStart' is not valid
for its type. (rsReportParameterTypeMismatch) Get Online Help
The paramter was empty, so of course it is not valid. How do I stop this
behavior? The paramters are not dependant on each other and do not use
dynamic SQL.
Any help would be appreciated.
Thanks Shannondid you set allow blank for the parameter that you allow it to be empty?

Parametrized Report with Analysis Services didn't work

Y made 2 report with parametrized MDX queries and they only work with the default values. But when I select some values from the combos with legal values the report is empty.
The weird thing is when I test the report in visual studio it works really fine. But when I go from http://somemachine/reports it fails.
Can anybody try this?
The MDX querie is ok. I have a lot of experience with MDX in SQL 2000.
It says something like this.

WITH
MEMBER [Measures].[Caida] AS 'IIF([Measures].[Variacion]<-0.5, [Measures].[Variacion], NULL)'
SELECT
NON EMPTY { [Measures].[Caida]} ON COLUMNS,
NON EMPTY { STRTOMEMBER(@.Grupo).CHILDREN } ON ROWS
FROM Ventas
WHERE ( {STRTOMEMBER(@.Mes)} )

I made others easier queries with the same behavior of the empty report.
Can anybody help me or know what happend?
Thanks in advanceCan you post an RDL / query that runs against the AdventureWorks sample cube and exposes the same issue?
Additional questions:
* Which build of BI Development Studio are you running?
* Which build of RS 2005 is running on the report server?
* Which build of AS 2005 is running on the AS server?

-- Robert|||

I'm not using adventure works. I'm using all of my own. But if you see the query is really simple (no tricks). Can you help me?

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="OLAP_INSA">

<rd:DataSourceID>b60468c5-c60b-47e2-942d-eb4686e92533</rd:DataSourceID>

<DataSourceReference>OLAP_INSA</DataSourceReference>

</DataSource>

</DataSources>

<InteractiveHeight>11in</InteractiveHeight>

<ReportParameters>

<ReportParameter Name="Anio">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>[Fecha].[Standard].[Anio].&amp;[15]</Value>

</Values>

</DefaultValue>

<Prompt>A¤o</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>Anio</DataSetName>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaption</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<FontWeight>700</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<BorderColor>

<Bottom>Black</Bottom>

</BorderColor>

<BorderWidth>

<Bottom>3pt</Bottom>

</BorderWidth>

<PaddingTop>2pt</PaddingTop>

<FontSize>18pt</FontSize>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<BackgroundColor>DeepSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Height>0.8381cm</Height>

<Value>Caida de las Ventas respecto a¤o anterior</Value>

</Textbox>

<List Name="List1">

<Width>14.07936cm</Width>

<Grouping Name="list1_Anio">

<GroupExpressions>

<GroupExpression>=Fields!Anio.Value</GroupExpression>

</GroupExpressions>

<PageBreakAtEnd>true</PageBreakAtEnd>

</Grouping>

<Sorting>

<SortBy>

<SortExpression>=Fields!Anio.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<DataSetName>OLAP_INSA</DataSetName>

<ReportItems>

<Textbox Name="Anio">

<Width>12.69841cm</Width>

<rd:DefaultName>Anio</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<FontWeight>900</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<PaddingTop>2pt</PaddingTop>

<FontSize>18pt</FontSize>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Height>0.8381cm</Height>

<Value>="A¤o: "+Fields!Anio.Value</Value>

</Textbox>

<Matrix Name="matrix1">

<MatrixColumns>

<MatrixColumn>

<Width>3.5cm</Width>

</MatrixColumn>

</MatrixColumns>

<Width>7cm</Width>

<DataSetName>OLAP_INSA</DataSetName>

<RowGroupings>

<RowGrouping>

<DynamicRows>

<Grouping Name="matrix1_Grupo">

<GroupExpressions>

<GroupExpression>=Fields!Grupo.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<Sorting>

<SortBy>

<SortExpression>=Fields!Grupo.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<ReportItems>

<Textbox Name="Grupo">

<rd:DefaultName>Grupo</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<FontWeight>700</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<PaddingTop>2pt</PaddingTop>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!Grupo.Value</Value>

</Textbox>

</ReportItems>

</DynamicRows>

<Width>3.5cm</Width>

</RowGrouping>

</RowGroupings>

<ColumnGroupings>

<ColumnGrouping>

<Height>0.60952cm</Height>

<DynamicColumns>

<Grouping Name="matrix1_Mes">

<GroupExpressions>

<GroupExpression>=Fields!Mes.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<ReportItems>

<Textbox Name="Mes">

<rd:DefaultName>Mes</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<FontWeight>700</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<PaddingTop>2pt</PaddingTop>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!Mes.Value</Value>

</Textbox>

</ReportItems>

</DynamicColumns>

</ColumnGrouping>

<ColumnGrouping>

<Height>0.53333cm</Height>

<StaticColumns>

<StaticColumn>

<ReportItems>

<Textbox Name="textbox8">

<rd:DefaultName>textbox8</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<FontWeight>600</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<PaddingTop>2pt</PaddingTop>

<FontSize>9pt</FontSize>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Right</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value>Ca?da mas de 25%</Value>

</Textbox>

</ReportItems>

</StaticColumn>

</StaticColumns>

</ColumnGrouping>

</ColumnGroupings>

<MatrixRows>

<MatrixRow>

<MatrixCells>

<MatrixCell>

<ReportItems>

<Textbox Name="Caida_mas_de_25_">

<rd:DefaultName>Caida_mas_de_25_</rd:DefaultName>

<Action>

<Drillthrough>

<ReportName>Caida Ventas por Producto</ReportName>

<Parameters>

<Parameter Name="Mes">

<Value>=Fields!Mes.Value</Value>

</Parameter>

<Parameter Name="Grupo">

<Value>=Fields!Grupo.Value</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<ToolTip>Ver detalle caida de las ventas para este mes y este grupo</ToolTip>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<TextDecoration>Underline</TextDecoration>

<PaddingTop>2pt</PaddingTop>

<Format>#.00%</Format>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<Color>Blue</Color>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!Caida_mas_de_25_.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

<Height>0.60952cm</Height>

</MatrixRow>

</MatrixRows>

<Top>1.26984cm</Top>

<Corner>

<ReportItems>

<Textbox Name="textbox5">

<rd:DefaultName>textbox5</rd:DefaultName>

<ZIndex>4</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

</ReportItems>

</Corner>

<Style />

</Matrix>

</ReportItems>

<Top>0.8381cm</Top>

<Style>

<FontWeight>900</FontWeight>

<FontFamily>Times New Roman</FontFamily>

<FontSize>18pt</FontSize>

<Color>Maroon</Color>

</Style>

</List>

</ReportItems>

<Height>3.86032cm</Height>

<Style />

</Body>

<LeftMargin>2.5cm</LeftMargin>

<BottomMargin>2.5cm</BottomMargin>

<rd:ReportID>bc8342a4-de2c-4d93-ab3c-f257505a4aa9</rd:ReportID>

<PageWidth>21cm</PageWidth>

<DataSets>

<DataSet Name="OLAP_INSA">

<Fields>

<Field Name="Anio">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Fecha].[Standard].[Anio]" /&gt;</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Mes">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Fecha].[Standard].[Mes]" /&gt;</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Grupo">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Producto].[Grupo].[Grupo]" /&gt;</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Caida_mas_de_25_">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Caida mas de 25%]" /&gt;</DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<DataSourceName>OLAP_INSA</DataSourceName>

<CommandText>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]&lt;-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@.Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</CommandText>

<QueryParameters>

<QueryParameter Name="Anio">

<Value>=Parameters!Anio.Value</Value>

</QueryParameter>

</QueryParameters>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Mes</LevelName><UniqueName>[Fecha].[Standard].[Mes]</UniqueName></ID><ItemCaption>Mes</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Producto</DimensionName><HierarchyName>Grupo</HierarchyName><HierarchyUniqueName>[Producto].[Grupo]</HierarchyUniqueName><LevelName>Grupo</LevelName><UniqueName>[Producto].[Grupo].[Grupo]</UniqueName></ID><ItemCaption>Grupo</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>Caida mas de 25%</MeasureName><UniqueName>[Measures].[Caida mas de 25%]</UniqueName></ID><ItemCaption>Caida mas de 25%</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]&lt;-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@.Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</Statement><ParameterDefinitions><ParameterDefinition><Name>Anio</Name><DefaultValues><DefaultValue>[Fecha].[Standard].[Anio].&amp;[15]</DefaultValue></DefaultValues><Caption>Anio</Caption><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Fecha].[Standard].ALLMEMBERS ON ROWS FROM [Ventas]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

<DataSet Name="Anio">

<Fields>

<Field Name="Anio">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Fecha].[Standard].[Anio]" /&gt;</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="ParameterCaption">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ParameterCaption]" /&gt;</DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Field Name="ParameterValue">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ParameterValue]" /&gt;</DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Field Name="ParameterLevel">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ParameterLevel]" /&gt;</DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<DataSourceName>OLAP_INSA</DataSourceName>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</CommandText>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterCaption</MeasureName><UniqueName>[Measures].[ParameterCaption]</UniqueName></ID><ItemCaption>ParameterCaption</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterValue</MeasureName><UniqueName>[Measures].[ParameterValue]</UniqueName></ID><ItemCaption>ParameterValue</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterLevel</MeasureName><UniqueName>[Measures].[ParameterLevel]</UniqueName></ID><ItemCaption>ParameterLevel</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

</DataSets>

<RightMargin>2.5cm</RightMargin>

<Width>16cm</Width>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

|||After 2 days of research and kicking my desktop.
I found finnally that to build parameters you must not use UNIQUENAME. Instead use MEMBER [Measures].[ParameterValue] AS ' "[DimensionName].["+[DimensionName].CURRENTMEMBER.NAME+"]" '
It's works fine.
I think there is a bug using STRTOMEMBER(@.parameter) when @.parameter is in the UNIQUENAME form.
I hope it helps you too.|||I edit the previous post where i said that you can only put dimensions on columns. That's wrong, i'm using dimension in columns and rows and works really fine.

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
Yahya
Yahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>
|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User
defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...een">
> Can I create parameterized view, in other words like stored procedures whe
re
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parameters with all values as default

Hi all.
Have a report with 2 parameters StartDate and SessionName. Both are non-query
based. So, the user runs the report and has to enter values in both fields in
order to run the report. I would like to have by default to have the report
run all values like as in (select * from etc) and then if user decides to
hone in on any StartDate/Session then they can used the parameter fields on
the report and then click view report.
Thanks
James
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1You'll need to allow Null values for each parameter. There's an example on
www.msbicentral.com. If you login and go to Downloads, Reporting Services,
RDL files, the example is Matrix.Param.RDL. There are other useful examples
there, also.
"James Woo via SQLMonster.com" wrote:
> Hi all.
> Have a report with 2 parameters StartDate and SessionName. Both are non-query
> based. So, the user runs the report and has to enter values in both fields in
> order to run the report. I would like to have by default to have the report
> run all values like as in (select * from etc) and then if user decides to
> hone in on any StartDate/Session then they can used the parameter fields on
> the report and then click view report.
> Thanks
> James
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>|||COOL SITE.. Thanks. I will have to spend hours there.
daw wrote:
>You'll need to allow Null values for each parameter. There's an example on
>www.msbicentral.com. If you login and go to Downloads, Reporting Services,
>RDL files, the example is Matrix.Param.RDL. There are other useful examples
>there, also.
>> Hi all.
>[quoted text clipped - 7 lines]
>> Thanks
>> James
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Easiest way to do this...
Create a new dataset for your Parameter:
e.g
SELECT Product,NAME
FROM Products
UNION
SELECT '***' AS Product,'All Managers' AS Name
This creates a *** as the ALL for products...
Then in your main report dataset...
Make sure you are using the generic query designer (button above the query)
Then as your statement:
WHERE ( Product = @.ProductParam or @.ProductParam='***')
Good luck
"James Woo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:52B3D2CE3710C@.SQLMonster.com...
> COOL SITE.. Thanks. I will have to spend hours there.
> daw wrote:
> >You'll need to allow Null values for each parameter. There's an example
on
> >www.msbicentral.com. If you login and go to Downloads, Reporting
Services,
> >RDL files, the example is Matrix.Param.RDL. There are other useful
examples
> >there, also.
> >
> >> Hi all.
> >>
> >[quoted text clipped - 7 lines]
> >> Thanks
> >> James
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

Parameters will not Default

Greetings

I am using SQL 2005 Reporting Services and I have a problem with parameters. If I leave 3 of the 4 values as non queried in the Available values section and mark them as a Default Values: of NULL, then the report works fine. However if I set the Available values to a query I have written and leave the Default values as NULL, when I view the report in either preview mode or on the reports server, it wants me to specify a value from the boxes and the tick box for NULL has disappeared. Allow NULL value is ticked inthe first section, Properties.

Anybody got any suggestions?

If you have available values, then the allowable values of the parameter are limited to those returned by your query. If you want to allow the parameter to be null, then null must be one of the available values returned by your query.

-Albert

Parameters using like

Hi,
I have a parameter named County. County, can have multiple values in it -
ex: 01, 02, 03.
I need to somehow use a like statement with this parameter so that it will
find all the counties in this field. I tried like%@.County% but this doesn't
work.
Anybody have any suggestions'
ThanksJill,
I suspect that what you'd like to do is something like this:-
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (1,2,3,4,5)
and then your thinking probably goes that you'd want to replace (1,2,3,4,5)
with a parameter such as:
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (@.CountyParameter)
You could make this work like I'm about to show you. BUT DONT!
EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (' + @.CountyParameter+ ')')
The reason not to do this is that this is very insecure from attacks from
SQL INJECTION. Just consider what would happen if someone passed
4);INSERT INTO CountiesTable(CountyName,
CountyID)VALUES('DisneyFantasyCounty',667)--
as the value for @.CountyParameter (or something far worse).
So having told you what not to do - the correct thing to do is create a
function on the Server such as this one taken directly from the
'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages 534 -
537)
CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
RETURNS @.paramtable
TABLE ( Id int )
AS BEGIN
DECLARE @.len int,
@.index int,
@.nextindex int
SET @.len = DATALENGTH(@.delimString)
SET @.index = 0
SET @.nextindex = 0
WHILE (@.len > @.index )
BEGIN
SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
INSERT @.paramtable
SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
SET @.index = @.nextindex + 1
END
RETURN
END
What this function does is that you pass it a parameter of a delimited
string such as your counties '1;2;6;' and returns a table. This table can
then be joined into your query. This approach is safer from SQL Injection
attacks. And so your Query for the DataSet becomes:
SELECT CountyID, CountyName FROM CountiesTable
INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
ON CountiesTable.CountyID= ParamParserFn.Id
I hope this is able to help you. More details as I mentioned are available
in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
Services. (see http://www.sqlreportingservices.net ). I would heartily
recommend our book to you as a valuable resource that comes with 2.5 GB of
DVD content and video demonstrations, and we especially concentrate on
security matters throughout.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
> Hi,
> I have a parameter named County. County, can have multiple values in it -
> ex: 01, 02, 03.
> I need to somehow use a like statement with this parameter so that it will
> find all the counties in this field. I tried like%@.County% but this
> doesn't
> work.
> Anybody have any suggestions'
> Thanks
>|||We will also natively support multiple values in queries in the next release
of Reporting Services.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Blackburn (www.sqlreportingservices.net)"
<http://www.sqlreportingservices.net> wrote in message
news:u6Py%239gxEHA.2040@.tk2msftngp13.phx.gbl...
> Jill,
> I suspect that what you'd like to do is something like this:-
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (1,2,3,4,5)
> and then your thinking probably goes that you'd want to replace
> (1,2,3,4,5) with a parameter such as:
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (@.CountyParameter)
> You could make this work like I'm about to show you. BUT DONT!
>
> EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (' + @.CountyParameter+ ')')
> The reason not to do this is that this is very insecure from attacks from
> SQL INJECTION. Just consider what would happen if someone passed
> 4);INSERT INTO CountiesTable(CountyName,
> CountyID)VALUES('DisneyFantasyCounty',667)--
> as the value for @.CountyParameter (or something far worse).
>
>
> So having told you what not to do - the correct thing to do is create a
> function on the Server such as this one taken directly from the
> 'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages
> 534 - 537)
>
> CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
> RETURNS @.paramtable
> TABLE ( Id int )
> AS BEGIN
> DECLARE @.len int,
> @.index int,
> @.nextindex int
> SET @.len = DATALENGTH(@.delimString)
> SET @.index = 0
> SET @.nextindex = 0
> WHILE (@.len > @.index )
> BEGIN
> SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
> if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
> INSERT @.paramtable
> SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
> SET @.index = @.nextindex + 1
> END
> RETURN
> END
> What this function does is that you pass it a parameter of a delimited
> string such as your counties '1;2;6;' and returns a table. This table can
> then be joined into your query. This approach is safer from SQL Injection
> attacks. And so your Query for the DataSet becomes:
> SELECT CountyID, CountyName FROM CountiesTable
> INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
> ON CountiesTable.CountyID= ParamParserFn.Id
>
> I hope this is able to help you. More details as I mentioned are available
> in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
> Services. (see http://www.sqlreportingservices.net ). I would heartily
> recommend our book to you as a valuable resource that comes with 2.5 GB of
> DVD content and video demonstrations, and we especially concentrate on
> security matters throughout.
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
>
>
>
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
>> Hi,
>> I have a parameter named County. County, can have multiple values in
>> it -
>> ex: 01, 02, 03.
>> I need to somehow use a like statement with this parameter so that it
>> will
>> find all the counties in this field. I tried like%@.County% but this
>> doesn't
>> work.
>> Anybody have any suggestions'
>> Thanks
>

Parameters not working

Hello,
I have created a report based on a stored procedure which uses 13
parameters. The 13 parameter values are mapped to the report parameter
selections (for example: Parameter @.CATEGORY, Value
=Parameters!Category.Value).
When the stored procedure is executed from the 'Data' tab of RS and the
parameters are typed in, the result set is correct.
When the report is executed in 'Preview' tab of RS only the first parameter
is evaluated. The rest of the parameters are not evaluated and the result
set brings everything back regardless of the selections.
Is this a bug or is there a way to enforce report parameter selections to be
passed into the stored procedure?
Thanks in Advance,
JohnParameters are case sensitive. If this is happening to you then something is
wrong with your mapping. I always use the expression builder and select the
report parameter just for this reason.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> Hello,
> I have created a report based on a stored procedure which uses 13
> parameters. The 13 parameter values are mapped to the report parameter
> selections (for example: Parameter @.CATEGORY, Value
> =Parameters!Category.Value).
> When the stored procedure is executed from the 'Data' tab of RS and the
> parameters are typed in, the result set is correct.
> When the report is executed in 'Preview' tab of RS only the first
parameter
> is evaluated. The rest of the parameters are not evaluated and the result
> set brings everything back regardless of the selections.
> Is this a bug or is there a way to enforce report parameter selections to
be
> passed into the stored procedure?
> Thanks in Advance,
> John|||Thanks Bruce,
Actually parameters were selected with expression builder and are correct.
The values for some of these parameters are coming from other stored
procedures. I am wondering if all these values are pulled once prior to
report execution and anything that is changed afterwards is not used.
John K.
===
"Bruce L-C [MVP]" wrote:
> Parameters are case sensitive. If this is happening to you then something is
> wrong with your mapping. I always use the expression builder and select the
> report parameter just for this reason.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John K" <JohnK@.discussions.microsoft.com> wrote in message
> news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > Hello,
> >
> > I have created a report based on a stored procedure which uses 13
> > parameters. The 13 parameter values are mapped to the report parameter
> > selections (for example: Parameter @.CATEGORY, Value
> > =Parameters!Category.Value).
> >
> > When the stored procedure is executed from the 'Data' tab of RS and the
> > parameters are typed in, the result set is correct.
> >
> > When the report is executed in 'Preview' tab of RS only the first
> parameter
> > is evaluated. The rest of the parameters are not evaluated and the result
> > set brings everything back regardless of the selections.
> >
> > Is this a bug or is there a way to enforce report parameter selections to
> be
> > passed into the stored procedure?
> >
> > Thanks in Advance,
> > John
>
>|||The parameters that you see in the toolbar prior to clicking on view report
are what should be getting sent to your stored procedure. One thing to keep
in mind, if the parameters are not changed then the data is cached (in the
development environment) and then next time you preview it uses the cached
data (look where you have your rdl files and you will see
reportname.rdl.data, that is what the .data files are.
I am not really sure what you are seeing but it could be a side affect of
that.
If the parameters are supposed to be done in a certain execution order then
you need to make them cascading parameters. Search books on line for
cascading parameter
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:35EA39B3-D88E-4EE4-973B-D162686C48EA@.microsoft.com...
> Thanks Bruce,
> Actually parameters were selected with expression builder and are correct.
> The values for some of these parameters are coming from other stored
> procedures. I am wondering if all these values are pulled once prior to
> report execution and anything that is changed afterwards is not used.
> John K.
> ===> "Bruce L-C [MVP]" wrote:
> > Parameters are case sensitive. If this is happening to you then
something is
> > wrong with your mapping. I always use the expression builder and select
the
> > report parameter just for this reason.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "John K" <JohnK@.discussions.microsoft.com> wrote in message
> > news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > > Hello,
> > >
> > > I have created a report based on a stored procedure which uses 13
> > > parameters. The 13 parameter values are mapped to the report
parameter
> > > selections (for example: Parameter @.CATEGORY, Value
> > > =Parameters!Category.Value).
> > >
> > > When the stored procedure is executed from the 'Data' tab of RS and
the
> > > parameters are typed in, the result set is correct.
> > >
> > > When the report is executed in 'Preview' tab of RS only the first
> > parameter
> > > is evaluated. The rest of the parameters are not evaluated and the
result
> > > set brings everything back regardless of the selections.
> > >
> > > Is this a bug or is there a way to enforce report parameter selections
to
> > be
> > > passed into the stored procedure?
> > >
> > > Thanks in Advance,
> > > John
> >
> >
> >|||Yes, some of the parameters are cascading.
I tried to change the type from Stored Procedure to Text and typed the
parameters out instead of using the paramter tab (like so: exec usp_x
@.Status=1, @.Project='HELLO') which works fine.
I just need the hardcoded 'HELLO' to be the value selected on the report
parameter which has the same name as the expected stored procedure parameter
(@.Project).
I tried exec usp_x @.Status=1, @.Project=Parameters!Project.Value!
which returns incorrect syntax near '!'
Thanks again.
"Bruce L-C [MVP]" wrote:
> The parameters that you see in the toolbar prior to clicking on view report
> are what should be getting sent to your stored procedure. One thing to keep
> in mind, if the parameters are not changed then the data is cached (in the
> development environment) and then next time you preview it uses the cached
> data (look where you have your rdl files and you will see
> reportname.rdl.data, that is what the .data files are.
> I am not really sure what you are seeing but it could be a side affect of
> that.
> If the parameters are supposed to be done in a certain execution order then
> you need to make them cascading parameters. Search books on line for
> cascading parameter
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John K" <JohnK@.discussions.microsoft.com> wrote in message
> news:35EA39B3-D88E-4EE4-973B-D162686C48EA@.microsoft.com...
> > Thanks Bruce,
> >
> > Actually parameters were selected with expression builder and are correct.
> > The values for some of these parameters are coming from other stored
> > procedures. I am wondering if all these values are pulled once prior to
> > report execution and anything that is changed afterwards is not used.
> >
> > John K.
> >
> > ===> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Parameters are case sensitive. If this is happening to you then
> something is
> > > wrong with your mapping. I always use the expression builder and select
> the
> > > report parameter just for this reason.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "John K" <JohnK@.discussions.microsoft.com> wrote in message
> > > news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > > > Hello,
> > > >
> > > > I have created a report based on a stored procedure which uses 13
> > > > parameters. The 13 parameter values are mapped to the report
> parameter
> > > > selections (for example: Parameter @.CATEGORY, Value
> > > > =Parameters!Category.Value).
> > > >
> > > > When the stored procedure is executed from the 'Data' tab of RS and
> the
> > > > parameters are typed in, the result set is correct.
> > > >
> > > > When the report is executed in 'Preview' tab of RS only the first
> > > parameter
> > > > is evaluated. The rest of the parameters are not evaluated and the
> result
> > > > set brings everything back regardless of the selections.
> > > >
> > > > Is this a bug or is there a way to enforce report parameter selections
> to
> > > be
> > > > passed into the stored procedure?
> > > >
> > > > Thanks in Advance,
> > > > John
> > >
> > >
> > >
>
>|||Corrected the syntax to:
exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
but it still not used in the reporting window.
Also tried: exec usp_X @.Status, @.Project to reference the parameters
directly. Still having problems.
If there are any examples out there please point me that direction.
Thanks.|||When you do a stored procedure do the following steps. I tend to use command
type as text but that is because I tend to go against Sybase a lot (I am
currently creating a datamart and will be doing most of my reporting against
SQL Server). First thing at work tomorrow I'll give you a stored procedure
example with SQL Server. The format
exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
will not work. I am surprised it didn't error out for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:9A94F3C6-54BE-487D-9C43-359F806FE7B9@.microsoft.com...
> Corrected the syntax to:
> exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
> but it still not used in the reporting window.
> Also tried: exec usp_X @.Status, @.Project to reference the parameters
> directly. Still having problems.
> If there are any examples out there please point me that direction.
> Thanks.|||Bruce thanks again,
Unfortunately the attachment did not make it, can you please e-mail it to
yiannino@.hotmail.com?
John K.|||In Outlook express the attachment shows as a paperclip. I know some people
use web based readers and seem to not see attachments. I prefer to do all my
responses here but I will email it to you. If you have additional questions
about the report please just respond here so others can benefit as well.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:60B099C4-5A3F-41FD-B130-24226143E8C5@.microsoft.com...
> Bruce thanks again,
> Unfortunately the attachment did not make it, can you please e-mail it to
> yiannino@.hotmail.com?
> John K.|||Hi Bruce,
Sorry it took a while. I was able to recreate the issue and it seems that
the problem lies with the way stored procedures are evaluated by Reporting
Services in terms of what parameters are used in the preview.
Specifically, the original stored procedure declared a cursor (which
required a select from another table) and then called another stored
procedure within the cursor. Seems that Reporting Services evalutated this
first select statement to decide what parameters would be applicable for the
report.
I changed the stored procedure to have as the first statement the select
(dynamically build) with all the parameters that are being passed in, and
that seems to work.
So is this some type of RS limitation where parameters to be evaluated at
execution time are defined by the first sql statement found/returned?
Is there anything coming in SQL Server 2005 that would allow a Cursor based
on another table to be used?
Any other ideas that would allow me to use the stored procedures as they
exist today instead of having to rewrite them?
Thanks again for all your help,
John