How to use the Top N in Filters

Hi,
I have created one report which uses the stored proc. it returns the data
more than 100 rows but i want to show only top 20 records in the graph( I
have to use that stored proc only. not possible to create another stored
proc)..
How can i achieve that? I found one property at filters sections i.e Top
N. Could any one let me know the usage of TOP N.
Regards,
SriHope you have used table control, just right click for properties. select the
filter tab In the expressions select the first column of your dataset. Select
the "Top N" from the operator. now in the value just type =20. You get first
20 rows.
Amarnath
"Sriman" wrote:
> Hi,
> I have created one report which uses the stored proc. it returns the data
> more than 100 rows but i want to show only top 20 records in the graph( I
> have to use that stored proc only. not possible to create another stored
> proc)..
> How can i achieve that? I found one property at filters sections i.e Top
> N. Could any one let me know the usage of TOP N.
> Regards,
> Sri

How to use SELECT UPPER

Hi

I want to return distinct values from a table in uppercase

Have tried

SELECT UPPER DISTINCT fieldname FROM tablename

But returns an error, what is the correct syntax.

ThanksNearly, upper is a function so you need to provide a column as an argument.
Select distinct upper(fieldname) from tablename|||Thanks,

However that now causes an error, my select statement is now:

SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479

This is used in a datareader i.e.

Dim MySQL As String = "SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479"
Dim MyConnection As New SqlConnection(ConnectionString)
Dim DataReader As SqlDataReader
Dim SQLCommand As New SqlCommand(MySQL, MyConnection)
MyConnection.Open()
DataReader = SQLCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
DropDownList1.DataSource = DataReader
DropDownList1.DataTextField = ("maketext")
DropDownList1.DataBind()

The error message is:

DataBinder.Eval: 'System.Data.Common.DbDataRecord' does not contain a property with the name maketext.

Thanks

Ben|||it doesnt seem to be the problem with the upper word. check if the col name is correct..

hth|||When you run a column through a function, you need to provide the result with an alias:

SELECT DISTINCT UPPER (maketext) AS maketext from VsVehicles WHERE dealerrefnum=8479

SELECT SUM(Volume) AS Volume FROM MilkBottles|||nope an alias is not necessary...unless you want to parse through the loop ( if the query returns one ) or get the value into a variable...you dont need an alias when you use a function...alias is only a way to identify the column...

hth|||Well, I could be wrong, but he is databinding to a datareader, and setting the DataTextField to a column named "maketext."

His query:

SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479

returns no columns named "maketext." It does include a column (with no column name) derived from the column named "maketext", but no actual column named "maketext".

My guess is that aliasing the column name will solve his problem...|||i see what you mean...i was looking at the select stmt all the while...

::nope an alias is not necessary...unless you want to parse through the loop ( if the query
::returns one ) or get the value into a variable...you dont need an alias when you use a
::function...alias is only a way to identify the column...

from my stmt, i meant the same thing by saying "get the value into a variable"

i was prbly not clear...good you clarified it out..

dinakar|||Yes that worked, thankyou

SELECT DISTINCT UPPER (maketext) AS maketext FROM VsVehicles

Thanks

Bensql

How to use parameterized UDF in a join query?

I have a table of employees and a User Defined Function udf_GetEmpQualifiedUnits which takes an employee ID and returns all the Units in which that employee can work. I want to get all the employees who are active and their qualified units. The UDF returns EmpID and UnitID (EmpID is same as given as parameter).

Now when I execute the following script;

Code Snippet

select distinct E.EmpID, EU.UnitID

from tblEmp E

inner join udf_GetEmpQualifiedUnits(E.EmpID) EU on E.EmpID = EU.EmpID and E.EmpActiveFlg <> 0

I get this error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "E.EmpID" could not be bound.

I do not want to use Cursors/loops as I am having the same above problem in many of my queries.

It is quite possible that I am unaware of the syntax or other things that could solve my problem.

Thank you.

You can't do this..Rewrite your logic of udf_GetEmpQualifiedUnits on your join query.

|||You can do this if you are using 2005...you will need to use the APPLY operator rather than a join operator. Heres an article I wrote a while back that explains how it works:
http://articles.techrepublic.com.com/5100-9592-6108869.html

Tim

How to use OPENXML to retrieve detail lineitems

Hi there,
I was trying to retrieve all LineItems from the following query. However,
if I execute the following query, it only returns the First item. How could
I modify the OPENXML synatax so it will also return the Second item and Thir
d
item in one select statement like this:
VINET 1996-07-04 00:00:00.000 11 12 First item
VINET 1996-07-04 00:00:00.000 11 12 Second item
VINET 1996-07-04 00:00:00.000 11 12 Third item
VINET 1996-07-04 00:00:00.000 42 10 NULL
LILAS 1996-08-16 00:00:00.000 72 3 NULL
What I would like to do is to insert all fields on the XML doc including
these LineItem into a database table.
Thanks.
Abel Chan
-- ******************
-- ****** Query ******
-- ******************
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
<LineItem>Second item</LineItem>
<LineItem>Third item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
EXEC sp_xml_removedocument @.idocAbel
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10">
<LineItem>Second item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="12">
<LineItem>Third item</LineItem>
</OrderDetail>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- <LineItem>Second item</LineItem>
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
EXEC sp_xml_removedocument @.idoc
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:70D61777-6563-4323-B97C-310113BD2321@.microsoft.com...
> Hi there,
> I was trying to retrieve all LineItems from the following query. However,
> if I execute the following query, it only returns the First item. How
> could
> I modify the OPENXML synatax so it will also return the Second item and
> Third
> item in one select statement like this:
> VINET 1996-07-04 00:00:00.000 11 12 First item
> VINET 1996-07-04 00:00:00.000 11 12 Second item
> VINET 1996-07-04 00:00:00.000 11 12 Third item
> VINET 1996-07-04 00:00:00.000 42 10 NULL
> LILAS 1996-08-16 00:00:00.000 72 3 NULL
>
> What I would like to do is to insert all fields on the XML doc including
> these LineItem into a database table.
> Thanks.
> Abel Chan
> -- ******************
> -- ****** Query ******
> -- ******************
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> <LineItem>Second item</LineItem>
> <LineItem>Third item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> EXEC sp_xml_removedocument @.idoc
>
>|||Hi Uri,
Thanks to your prompt reply. However, the xml document I got has multiple
<LineItem> under each <OrderDetail>. I wish each <OrderDetail> has only one
<LineItem>.
Is there a way to extract all the <LineItem> out? Even it is in a plain xml
text. With that, I can use another OPENXML to parse it.
Thanks Uri.
Abel
"Uri Dimant" wrote:

> Abel
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10">
> <LineItem>Second item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="12">
> <LineItem>Third item</LineItem>
> </OrderDetail>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
>
> </Order>
> </Customer>
> </ROOT>'
> -- <LineItem>Second item</LineItem>
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> EXEC sp_xml_removedocument @.idoc
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:70D61777-6563-4323-B97C-310113BD2321@.microsoft.com...
>
>|||Hello,
I test the following code and it works fine on my side:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
<LineItem>Second item</LineItem>
<LineItem>Third item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail/LineItem')
WITH (
CustomerID varchar(10) '//CustomerID',
OrderDate datetime '//OrderDate',
ProdID int '//@.ProductID',
Qty int '//@.Quantity',
LineItem VARCHAR(50) 'text()')
union
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
order by customerID desc
EXEC sp_xml_removedocument @.idoc
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||That is exactly want I need. Union does the trick. :> Thanks so much Soph
ie.
BTW, I came up with an alternative while I was waiting for your reply.
I setup a #temp table by inserting all the LineItems. I also insert
customerId and Orderdate so I can find the LineItem with these two
identifiers. Then I use cursor to loop through the #temp. It works but
compare to yours, your solution is way cleaner and way easier to handle any
exception errors I will replace mine with your code.
Thanks again.
Abel Chan
"Sophie Guo [MSFT]" wrote:

> Hello,
> I test the following code and it works fine on my side:
>
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> <LineItem>Second item</LineItem>
> <LineItem>Third item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
>
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail/LineItem')
> WITH (
> CustomerID varchar(10) '//CustomerID',
> OrderDate datetime '//OrderDate',
> ProdID int '//@.ProductID',
> Qty int '//@.Quantity',
> LineItem VARCHAR(50) 'text()')
> union
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> order by customerID desc
>
> EXEC sp_xml_removedocument @.idoc
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello,
I am glad to hear that the informaion is helpful. Have a nice day!
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

How to use multiple recordsets from a stored procedure?

I have one stored procedure which has 3 select statements inside it. So this
stored procedure returns 3 recordsets. I need to print all of these 3
recordsets onto the report.
Are there any way to print these 3 recordsets onto this report without
splitting this stored procedure into 3 stored procedures?
Thanks a lot.No, RS only supports one recordset being returned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BF" <BF@.discussions.microsoft.com> wrote in message
news:71BD33B4-2B04-4188-9814-8360BED57B87@.microsoft.com...
>I have one stored procedure which has 3 select statements inside it. So
>this
> stored procedure returns 3 recordsets. I need to print all of these 3
> recordsets onto the report.
> Are there any way to print these 3 recordsets onto this report without
> splitting this stored procedure into 3 stored procedures?
> Thanks a lot.
>

How to use CASE WHEN statement in Function

Hi,
My Function as follow-->
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
IF @.cemk = '1'
BEGIN
RETURN 'XX0400100'
END
RETURN 'XX0400200'
END
My Case When statement like this-->
CASE
WHEN iv_cemk = 0 THEN 'XX0400200'
ELSE 'XX0400100'
END AS iv_cemk
And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
Function?
How should I do?
Thanks!
AngiHi Angi,
what about
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
declare @.cReturn char(9)
set @.cReturn = (case @.cemk
when '1' then 'XX0400100'
else 'XX0400200'
end)
return @.cReturn
END
HTH
Meinhard
"angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> My Function as follow-->
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> IF @.cemk = '1'
> BEGIN
> RETURN 'XX0400100'
> END
> RETURN 'XX0400200'
> END
> My Case When statement like this-->
> CASE
> WHEN iv_cemk = 0 THEN 'XX0400200'
> ELSE 'XX0400100'
> END AS iv_cemk
> And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
> Function?
> How should I do?
> Thanks!
> Angi
>|||Hi, Meinhard
Thank you very much!
And there is aother way like follow..
BEGIN
RETURN CASE WHEN @.cemk='1' THEN 'XX0400100'
ELSE 'XX0400200'
END
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And I have another question there, syntax like follow..
CREATE FUNCTION fnXY13 (@.iden CHAR(4), @.csct CHAR(2), @.cect CHAR(2))
RETURNS CHAR(9)
AS
BEGIN
RETURN
CASE--XY13AA
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.csct = 'f1' THEN 'XY1300100'
WHEN @.csct = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13BB
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13CC
WHEN @.iden IN ('0406','0408','0409','0419','0501') THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
END
There are 3 different CASE WHEN conditions and how could I combine it in a
Function?
Thanks!
Angi
"Meinhard Schnoor-Matriciani" <codehack@.freenet.de> ¼¶¼g©ó¶l¥ó·s»D
:2s4ev1F1h4vb5U1@.uni-berlin.de...
> Hi Angi,
> what about
>
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> declare @.cReturn char(9)
> set @.cReturn = (case @.cemk
> when '1' then 'XX0400100'
> else 'XX0400200'
> end)
> return @.cReturn
> END
> HTH
> Meinhard
> "angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
> news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > My Function as follow-->
> >
> > CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> > RETURNS CHAR(9)
> > AS
> > BEGIN
> > IF @.cemk = '1'
> > BEGIN
> > RETURN 'XX0400100'
> > END
> > RETURN 'XX0400200'
> > END
> >
> > My Case When statement like this-->
> >
> > CASE
> > WHEN iv_cemk = 0 THEN 'XX0400200'
> > ELSE 'XX0400100'
> > END AS iv_cemk
> >
> > And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
> > Function?
> > How should I do?
> >
> > Thanks!
> > Angi
> >
> >
>

How to use CASE WHEN statement in Function

Hi,
My Function as follow-->
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
IF @.cemk = '1'
BEGIN
RETURN 'XX0400100'
END
RETURN 'XX0400200'
END
My Case When statement like this-->
CASE
WHEN iv_cemk = 0 THEN 'XX0400200'
ELSE 'XX0400100'
END AS iv_cemk
And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
Function?
How should I do?
Thanks!
Angi
Hi Angi,
what about
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
declare @.cReturn char(9)
set @.cReturn = (case @.cemk
when '1' then 'XX0400100'
else 'XX0400200'
end)
return @.cReturn
END
HTH
Meinhard
"angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> My Function as follow-->
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> IF @.cemk = '1'
> BEGIN
> RETURN 'XX0400100'
> END
> RETURN 'XX0400200'
> END
> My Case When statement like this-->
> CASE
> WHEN iv_cemk = 0 THEN 'XX0400200'
> ELSE 'XX0400100'
> END AS iv_cemk
> And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
> Function?
> How should I do?
> Thanks!
> Angi
>
|||Hi, Meinhard
Thank you very much!
And there is aother way like follow..
BEGIN
RETURN CASE WHEN @.cemk='1' THEN 'XX0400100'
ELSE 'XX0400200'
END
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
And I have another question there, syntax like follow..
CREATE FUNCTION fnXY13 (@.iden CHAR(4), @.csct CHAR(2), @.cect CHAR(2))
RETURNS CHAR(9)
AS
BEGIN
RETURN
CASE--XY13AA
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.csct = 'f1' THEN 'XY1300100'
WHEN @.csct = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13BB
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13CC
WHEN @.iden IN ('0406','0408','0409','0419','0501') THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
END
There are 3 different CASE WHEN conditions and how could I combine it in a
Function?
Thanks!
Angi
"Meinhard Schnoor-Matriciani" <codehack@.freenet.de> glsD
:2s4ev1F1h4vb5U1@.uni-berlin.de...
> Hi Angi,
> what about
>
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> declare @.cReturn char(9)
> set @.cReturn = (case @.cemk
> when '1' then 'XX0400100'
> else 'XX0400200'
> end)
> return @.cReturn
> END
> HTH
> Meinhard
> "angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
> news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
>
|||Same as the answer in the previous post. Just assign to variables in your
logic, then RETURN the variable at the end.
Get the CASE statements working stand-alone in QA first.
Jeff
"angi" <angi@.microsoft.public.sqlserver.olap> wrote in message
news:eqMxG95pEHA.2484@.TK2MSFTNGP09.phx.gbl...
> Hi, Meinhard
> Thank you very much!
> And there is aother way like follow..
> BEGIN
> RETURN CASE WHEN @.cemk='1' THEN 'XX0400100'
> ELSE 'XX0400200'
> END
> END
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> And I have another question there, syntax like follow..
> CREATE FUNCTION fnXY13 (@.iden CHAR(4), @.csct CHAR(2), @.cect CHAR(2))
> RETURNS CHAR(9)
> AS
> BEGIN
> RETURN
> CASE--XY13AA
> WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
> CASE
> WHEN @.csct = 'f1' THEN 'XY1300100'
> WHEN @.csct = 'f2' THEN 'XY1300200'
> ELSE 'XY1300500'
> END
> ELSE 'X99999999'
> END
> CASE--XY13BB
> WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
> CASE
> WHEN @.cect = 'f1' THEN 'XY1300100'
> WHEN @.cect = 'f2' THEN 'XY1300200'
> ELSE 'XY1300500'
> END
> ELSE 'X99999999'
> END
> CASE--XY13CC
> WHEN @.iden IN ('0406','0408','0409','0419','0501') THEN
> CASE
> WHEN @.cect = 'f1' THEN 'XY1300100'
> WHEN @.cect = 'f2' THEN 'XY1300200'
> ELSE 'XY1300500'
> END
> ELSE 'X99999999'
> END
> END
> There are 3 different CASE WHEN conditions and how could I combine it in a
> Function?
> Thanks!
> Angi
>
> "Meinhard Schnoor-Matriciani" <codehack@.freenet.de> glsD
> :2s4ev1F1h4vb5U1@.uni-berlin.de...
>

How to use a stored procedure with parameters to create the datasource view for report model bui

I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?

Please refer to the following post in this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=533787&SiteID=1

Shyam

|||

Hi Troy and Shyam,

I looked at the post it simply says use OPENROWSET. But I dont know where I should use OPENROWSET in the Report Model Project to get my stored procedures as datasource views.

Can one of you please provide some direction on this or a simple example.

Appreciate your help guys!

How to use a stored procedure with parameters to create the datasource view for report model bui

I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?

Please refer to the following post in this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=533787&SiteID=1

Shyam

|||

Hi Troy and Shyam,

I looked at the post it simply says use OPENROWSET. But I dont know where I should use OPENROWSET in the Report Model Project to get my stored procedures as datasource views.

Can one of you please provide some direction on this or a simple example.

Appreciate your help guys!