asking for user's input before report generation - plz help?

Hi guys,
I am very new to Rep Services so need your help or suggestion:
Question: Let's say I have a very simple set of data, but before the
report is generated, userviewing the report has to enter a number and
then the report takes this number and subtracts from it a number
sitting in the database - and displayes the result in the report.
Let me explain. Let's say here's my data:
Item Bill
1 $30
2 $50
3 $45
Before the report is generated, a popup will come up, asking the user
to input a Total Bill on Paper Invoice $.
Then the report will show:
Internal Total: $125 (this is the number calculated from the data
table.
Total Bill on the Paper Invoice: $130 (this is the number that the
report viewer inputted)
TOTAL AMOUNT DUE: ($130) - ($125) = $5 (this is the difference
between user's inputted number and the number from the table.
How would I create a report such as the above so that prior to report
generating, a popup would come up asking for the user to input $130 and
then the report would take the difference between user's input number
and the number sitting in the database table?
This should be very easy, but since I'm so new, I need your help.
Thanks in advance.The HTML report header can't pop-up a window to prompt users for input but
you can use a parameter that will show-up in the header toolbar. Add a
report parameter and then reference it in an expression as (textbox value)
=Parameters!TotalBill.Value-Fields!InternalTotal.Value
"Kartuli" <kartuli.com@.gmail.com> wrote in message
news:1114454790.571054.87820@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I am very new to Rep Services so need your help or suggestion:
> Question: Let's say I have a very simple set of data, but before the
> report is generated, userviewing the report has to enter a number and
> then the report takes this number and subtracts from it a number
> sitting in the database - and displayes the result in the report.
> Let me explain. Let's say here's my data:
> Item Bill
> 1 $30
> 2 $50
> 3 $45
> Before the report is generated, a popup will come up, asking the user
> to input a Total Bill on Paper Invoice $.
> Then the report will show:
> Internal Total: $125 (this is the number calculated from the data
> table.
> Total Bill on the Paper Invoice: $130 (this is the number that the
> report viewer inputted)
> TOTAL AMOUNT DUE: ($130) - ($125) = $5 (this is the difference
> between user's inputted number and the number from the table.
> How would I create a report such as the above so that prior to report
> generating, a popup would come up asking for the user to input $130 and
> then the report would take the difference between user's input number
> and the number sitting in the database table?
> This should be very easy, but since I'm so new, I need your help.
> Thanks in advance.
>

Asking for User input mid-execution

Is it possible to ask for user input in the middle of a stored procedure execution?

What I have is a single procedure that is going to do some deletes, and I want to receive confirmation from the user that they're ok with actually going through with the delete.

The procedure is being run from a Query Analyzer-type interface, and I have no access to tie proper code into the application this is being run from (hence why I'm trying to add some user-based conditional logic).

Thanks in advance for any assistance!1

No.

ASC / DESC based on CASE

Hi is it possible to sort data ASC or DESC using a CASE Statement based on
user input parameter ?
ThanksYes, use separate CASE expressions in the ORDER BY clause.
Anith|||On Tue, 3 Jan 2006 15:06:51 -0800, Vishal wrote:

>Hi is it possible to sort data ASC or DESC using a CASE Statement based on
>user input parameter ?
>Thanks
>
Hi Vishal,
Yes - but not the exact way that you think/hope for.
Example #1:
SELECT yadda, yadda
FROM whatever
ORDER BY CASE WHEN @.Direction = 'ASC' THEN SortColumn END ASC,
CASE WHEN @.Direction = 'DESC' THEN SortColumn END DESC
Example #2 (works only for numeric columns)
SELECT yadda, yadda
FROM whatever
ORDER BY SortColumn * CASE WHEN @.Direction = 'ASC' THEN 1 ELSE -1 END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||note that this one-size-fits-all approach may perform poorly, as
described here
http://www.devx.com/dbzone/Article/30149/0/page/2

array in store procedure

How to send an array list as an input variable into store procedure?
I have a list of UserName witch I would like to store into table through
store procedure.
HrckoYou can passed it as a string and get the parts out of the string in the SP.
There is no array or such type in TSQL. (AFAIK)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Hrvoje Voda" <hrvoje.voda@.luatech.com> schrieb im Newsbeitrag
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||http://www.sommarskog.se/arrays-in-sql.html
Jacco Schalkwijk
SQL Server MVP
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||Hrcko,
Here is an example that may help. ListTable() is an efficient
table-valued function that can take your list of UserName values
and return a table, so you can do something like
insert into X(UserName)
select Item from ListTable('Hrvoje, Kresimir, Josipa, Barbara, Vladen,
Vilko, Ksenija')
-- Definition and notes for ListTable()
/*
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(1000)
set @.x = 'Hrvoje, Kresimir, Josipa, Barbara, Vladen, Vilko, Ksenija'
select * from ListTable(@.x)
set @.x = 'Hrvoje|Kresimir|Josipa|Barbara|Vladen|V
ilko|Ksenija'
declare @.s varchar(1000)
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
Hrvoje Voda wrote:

>How to send an array list as an input variable into store procedure?
>I have a list of UserName witch I would like to store into table through
>store procedure.
>Hrcko
>
>|||You can pass the list as a comma separated list of values in a string, and
use a split technique to break them apart using a function similar to this
one:
-- Populate an auxiliary table of numbers
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('Nums') IS NOT NULL
DROP TABLE Nums
GO
CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)
DECLARE @.max AS INT, @.rc AS INT
SET @.max = 8000
SET @.rc = 1
BEGIN TRAN
INSERT INTO Nums VALUES(1)
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO Nums
SELECT n + @.rc FROM Nums
SET @.rc = @.rc * 2
END
INSERT INTO Nums
SELECT n + @.rc FROM Nums
WHERE n + @.rc <= @.max
COMMIT TRAN
GO
-- Create function
CREATE FUNCTION fn_SeparateElements
(@.arr AS VARCHAR(7999)) RETURNS TABLE
AS
RETURN
SELECT n - LEN(REPLACE(LEFT(arr, n), ',', '')) + 1 AS pos,
SUBSTRING(@.arr, n, CHARINDEX(',', @.arr + ',', n) - n) AS element
FROM (SELECT @.arr AS arr) AS A JOIN Nums
ON n <= LEN(@.arr) AND SUBSTRING(',' + @.arr, n, 1) = ','
GO
-- Test
SELECT * FROM fn_SeparateElements('user1,user2,user3')
pos element
-- --
1 user1
2 user2
3 user3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||
"Hrvoje Voda" wrote:

> How to send an array list as an input variable into store procedure?
>
Definitely more than one way here to skin that cat. :-)
Yet another solution not using a UDF utilizing SQL Server's own
master..spt_values. You can easily modify it to use your own numbers table.
DECLARE @.strComma VARCHAR(1000)
SET @.strComma = 'Hrvoje, Kresimir, Josipa, Barbara, Vladen, Vilko, Ksenija'
SET @.strComma = REPLACE(@.strComma,' ', '')
SELECT
CAST(RIGHT(LEFT(@.strComma,Number-1)
, CHARINDEX(',',REVERSE(LEFT(','+@.strComma
,Number-1)))) AS CHAR(30)) as
User__Name
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(@.strComma)+1
AND
(SUBSTRING(@.strComma,Number,1) = ',' OR SUBSTRING(@.strComma,Number,1) = '')
User__Name
--
Hrvoje
Kresimir
Josipa
Barbara
Vladen
Vilko
Ksenija
(7 row(s) affected)
--
Frank Kalis
SQL Server MVP
http://www.insidesql.de

Array in Store procedure

Dear all,

Sometimes I happened the require that the number of input parameters
of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
Does anybody ever used an array name as the input parameter to call
the SP?

thanks,

RobertSQL doesn't have arrays but see this article for some alternatives:

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

--
David Portas
SQL Server MVP
--|||Robert Song wrote:

> Dear all,
> Sometimes I happened the require that the number of input parameters
> of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
> Does anybody ever used an array name as the input parameter to call
> the SP?
> thanks,
> Robert
I had this problem a year ago and solved it using a udf named fn_Split
I found it pretty fast.
You give it a list of parameters as '1,2,3,4,5,6' and it returns a temp
table
so you can select from fn_Split() and have something like
1
2
3
4
5
6

i found a version of this udf at
http://www.umachandar.com/technical...tyFns/Main7.htm

hth

f.|||Hi David,

Thank you so much for your reply, i will read the article later on.

Cheers,

Robert Song

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Arithmetic operation failure. Is this a bug, or am I doing something wrong?

I used the following code in a function. The @.infloat is input parameter

For every other number than 16.31, 17.31, 18.31, 19.31 and 20.31 (at least those numbers I checked) the code runs ok. For those numbers the result is wrong.

What I want to do is return a ten digits number left filled with zeros, with the last two digits consitered as decimals.

What I do is multiply the given float with 100.00, and take the resulting integer

But when @.infloat is multiplied with 100.00 and @.infloat in (16.31, 17.31, 18.31, 19.31 and 20.31)

the result is (1630,1730,1830,1930,2030) instead of (1631,1731,1831,1931,2031)..

Can anyone try this ?

declare @.ingood int,

@.stuffme varchar(10),

@.infloat float, @.infl float

set @.infloat=18.31

set @.infloat=isnull(@.infloat,0)

set @.stuffme='0000000000'

set @.infl=@.infloat*(100.00)

select @.infl as infl

set @.ingood=cast(@.infl as int)

select @.ingood as ingood

set @.stuffme=stuff(@.stuffme,11-len(@.ingood),len(@.ingood),@.ingood)

select @.stuffme

Replace

set @.ingood=cast(@.infl as int)

WITH

set @.ingood=cast(@.infl as NUMeERIC (10,2))

|||Do you need high precision ? Floating point data is approximate; not all values in the data type range can be precisely represented.

See here :
select convert(float, 18.31)
Result : 18.309999999999999|||

Hi

I have tried MONEY and NUMERIC datatypes for @.infloat and @.infl from your example. They both work just fine.