multi value parameter

I've been reading the forums regarding this but don't seem to understand
exactly how this is supposed to work. I have parameters list build from
query. Report Parameter is set to multi value. The SP that the report uses
has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
report and choose 'select all' I get no results though if I select just one,
it works fine. Selecting more than one also returns no result.
What can I possibly be doing wrong?SQL 2005, SP2
"brian" wrote:
> I've been reading the forums regarding this but don't seem to understand
> exactly how this is supposed to work. I have parameters list build from
> query. Report Parameter is set to multi value. The SP that the report uses
> has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
> report and choose 'select all' I get no results though if I select just one,
> it works fine. Selecting more than one also returns no result.
> What can I possibly be doing wrong?|||You answered yourself but unfortunately you answered incorrectly. Here is my
stock answer for this:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
One other option is to create dynamic SQL in your stored procedure as well.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"brian" <brian@.discussions.microsoft.com> wrote in message
news:F270F0D4-6551-444A-9711-E5B0FE938063@.microsoft.com...
> SQL 2005, SP2
> "brian" wrote:
>> I've been reading the forums regarding this but don't seem to understand
>> exactly how this is supposed to work. I have parameters list build from
>> query. Report Parameter is set to multi value. The SP that the report
>> uses
>> has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
>> report and choose 'select all' I get no results though if I select just
>> one,
>> it works fine. Selecting more than one also returns no result.
>> What can I possibly be doing wrong?|||Way over my head; I have more reading to do. Thanks Bruce.
"Bruce L-C [MVP]" wrote:
> You answered yourself but unfortunately you answered incorrectly. Here is my
> stock answer for this:
> What doesn't work has nothing really to do with RS but has to do with Stored
> Procedures in SQL Server. You cannot do the following in a stored procedure.
> Let's say you have a Parameter called @.MyParams
> Now you can map that parameter to a multi-value parameter but if in your
> stored procedure you try to do this:
> select * from sometable where somefield in (@.MyParams)
> It won't work. Try it. Create a stored procedure and try to pass a
> multi-value parameter to the stored procedure. It won't work.
> What you can do is to have a string parameter that is passed as a multivalue
> parameter and then change the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> GO
> One other option is to create dynamic SQL in your stored procedure as well.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "brian" <brian@.discussions.microsoft.com> wrote in message
> news:F270F0D4-6551-444A-9711-E5B0FE938063@.microsoft.com...
> > SQL 2005, SP2
> >
> > "brian" wrote:
> >
> >> I've been reading the forums regarding this but don't seem to understand
> >> exactly how this is supposed to work. I have parameters list build from
> >> query. Report Parameter is set to multi value. The SP that the report
> >> uses
> >> has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
> >> report and choose 'select all' I get no results though if I select just
> >> one,
> >> it works fine. Selecting more than one also returns no result.
> >>
> >> What can I possibly be doing wrong?
>
>

MSSQL2005 Analysis Service Distinct Count

hi,

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).

But after i added the discount count on the field i want to and start the processing, the following errors appear.

- Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

- Errors in the OLAP storage engine: An error occurred while processing the 'FACT VIEW STATISTIC' partition of the 'FACT VIEW STATISTIC 1' measure group for the 'Accident Statistic' cube from the OLAP_PROJECT database.

the count measure works fine.

will appreciate any help on this distinct count problem.

thanks in advance.

-

HY

Try and see what kind of query Analysis Services sends to the relational database during processing of distinct count measure.

You will see it sending a query containing ORDER BY clause asking relational database to sort results accourding to the distinct count measure.

It it possible the view you defined your partitions on, brings data sorted differently?
Any new data becomes avaliable during processing of the partition?

The error indicates Analysis Server detecting inconsistencies in sorting of data coming from relational database.

See if you might need to define collation correctly for your sort.

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

|||

Thanks edward.

as i drew data from Oracle view, the collation must be specify correctly.

check the Oracle collation and discovered it is binary.

changed the distinct count to binary collation and it works.

thanks.

-

HY

|||

Could any one explain about the error and solution elaborately. I am not sure how this can be rectified. Appreciate any help.

Thanks

|||

Hi there,

We encountered the same issue:

And changing the collation to binary allowed us to process the cube ...

But I still don't understand why I got the error with the collation set to SQL_Latin1_CI_AS

By the way, I also encountered a difference of 1 by browsing the cube and when I count on the table:

Browse on the measure with the distinct count = 800

Result of "select count (distinct (sessionid)) from dbo.facttransaction" = 799

(NB: some sessionid are NULL)

Does the cube take in consideration the NULL values ?

Thanks

|||

OK found why I got the diff :

Analysis Services handle a NULL value like a 0 value in a DISTINCT COUNT measure

MSSQL2005 Analysis Service Distinct Count

hi,

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).

But after i added the discount count on the field i want to and start the processing, the following errors appear.

- Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

- Errors in the OLAP storage engine: An error occurred while processing the 'FACT VIEW STATISTIC' partition of the 'FACT VIEW STATISTIC 1' measure group for the 'Accident Statistic' cube from the OLAP_PROJECT database.

the count measure works fine.

will appreciate any help on this distinct count problem.

thanks in advance.

-

HY

Try and see what kind of query Analysis Services sends to the relational database during processing of distinct count measure.

You will see it sending a query containing ORDER BY clause asking relational database to sort results accourding to the distinct count measure.

It it possible the view you defined your partitions on, brings data sorted differently?
Any new data becomes avaliable during processing of the partition?

The error indicates Analysis Server detecting inconsistencies in sorting of data coming from relational database.

See if you might need to define collation correctly for your sort.

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

|||

Thanks edward.

as i drew data from Oracle view, the collation must be specify correctly.

check the Oracle collation and discovered it is binary.

changed the distinct count to binary collation and it works.

thanks.

-

HY

|||

Could any one explain about the error and solution elaborately. I am not sure how this can be rectified. Appreciate any help.

Thanks

|||

Hi there,

We encountered the same issue:

And changing the collation to binary allowed us to process the cube ...

But I still don't understand why I got the error with the collation set to SQL_Latin1_CI_AS

By the way, I also encountered a difference of 1 by browsing the cube and when I count on the table:

Browse on the measure with the distinct count = 800

Result of "select count (distinct (sessionid)) from dbo.facttransaction" = 799

(NB: some sessionid are NULL)

Does the cube take in consideration the NULL values ?

Thanks

|||

OK found why I got the diff :

Analysis Services handle a NULL value like a 0 value in a DISTINCT COUNT measure

MSSQL2005 Analysis Service Distinct Count

hi,

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).

But after i added the discount count on the field i want to and start the processing, the following errors appear.

- Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

- Errors in the OLAP storage engine: An error occurred while processing the 'FACT VIEW STATISTIC' partition of the 'FACT VIEW STATISTIC 1' measure group for the 'Accident Statistic' cube from the OLAP_PROJECT database.

the count measure works fine.

will appreciate any help on this distinct count problem.

thanks in advance.

-

HY

Try and see what kind of query Analysis Services sends to the relational database during processing of distinct count measure.

You will see it sending a query containing ORDER BY clause asking relational database to sort results accourding to the distinct count measure.

It it possible the view you defined your partitions on, brings data sorted differently?
Any new data becomes avaliable during processing of the partition?

The error indicates Analysis Server detecting inconsistencies in sorting of data coming from relational database.

See if you might need to define collation correctly for your sort.

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

|||

Thanks edward.

as i drew data from Oracle view, the collation must be specify correctly.

check the Oracle collation and discovered it is binary.

changed the distinct count to binary collation and it works.

thanks.

-

HY

|||

Could any one explain about the error and solution elaborately. I am not sure how this can be rectified. Appreciate any help.

Thanks

|||

Hi there,

We encountered the same issue:

And changing the collation to binary allowed us to process the cube ...

But I still don't understand why I got the error with the collation set to SQL_Latin1_CI_AS

By the way, I also encountered a difference of 1 by browsing the cube and when I count on the table:

Browse on the measure with the distinct count = 800

Result of "select count (distinct (sessionid)) from dbo.facttransaction" = 799

(NB: some sessionid are NULL)

Does the cube take in consideration the NULL values ?

Thanks

|||

OK found why I got the diff :

Analysis Services handle a NULL value like a 0 value in a DISTINCT COUNT measure

MSSQL Questions - Images and Frontpage integration

I'm currently a computer networking student, and a semester long project this year requires us to build an online store using IIS6 and MSSQL. I have run into a dead end in this project and cannot find information or documentation that answers my questions.

So as follows.

How do I store an image in the MSSQL database, preferably through a frontpage-created form with upload boxes?


I am working with forms that I create in Frontpage (a requirement of the course) to add entries to the database. Working with just text boxes and text areas I have had much success with this, but I cannot figure out, when using upload boxes, how to direct the file uploaded to be stored in the database instead of a folder on the local machine (I have associated these upload boxes with the image and thumbnail fields on the appropriate table, with no success)

How do I use dropdown boxes to limit the number of selections on a backend form which inserts data into the SQL database but pulls it's selections from the SQL database instead of generating list entries myself.


Currently my attempts have been to insert into the form a database results entry which pulls the data from appropriate tables, lists by name, but uses the associated numerical key to store the data, with no success)

These issues have been driving me crazy and with 4 weeks left before it's due, I'm pulling my hair out trying to figure it out.

Thanks in advance for any help you can give, if I've not been 100% clear with my explanations, please contact me and I will clarify to the best of my ability.

~Ryan F. Bracy "Angel"1. If I find my old code I will share it with you. The thing is pictures are not stored in SQL serve even thou it is possible as binary. It puts strain on a server and works terribly. What usually is done picture is stored on sharable server drive not on local machine and full path to that picture is stored in a database.

2. I need more information on table definitions and code you use.

I think I saw these questions before but you keep changing your nick and I am not sure if it is the same person.

Irina.|||By the way, how did you put picture next to your nick here?
I don't see how to do it... :)|||

Quote:

Originally Posted by iburyak

I think I saw these questions before but you keep changing your nick and I am not sure if it is the same person.
Irina.

This is my first time ever posting here.

and I will try to remember to post the code we're using presently.

Today our professor decided that we need to use all ASP to make the site, as frontpage doesn't have the capabilities to do what he wants us to do...with 3 1/2 weeks left before the project is due, and now he tells us that we have to learn ASP and code our whole site with it. When I suggested ASP from the beginning and was shot down...|||

Quote:

Originally Posted by iburyak

By the way, how did you put picture next to your nick here?
I don't see how to do it... :)

it's under the control panel, under edit avatar...|||I did find code how to convert picture into a binary stream. But I am sure if you'll see complexity of it you will change your mind to use it... :)

Suggest your professor to FTP picture to the server and save it their as I originally suggested on server side and save full path to the picture in a database.

If you still want code I found - send me private message with your direct e-mail.
I am not going to post this code on forum.

Thank you.

P.S. Is it possible that your professor was asking the same thing on forum here? :)|||

Quote:

Originally Posted by Ryan F Bracy

it's under the control panel, under edit avatar...

Thank you... :)