Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to pass parameter array to Crystal Report stored procedure from VB.NET?

Posted on 2010-09-06
6
Medium Priority
?
2,237 Views
Last Modified: 2012-05-10
Hi,

I have a stored procedure e.g

Create StoredProcedure ABC
@ID INT,
@myDate DATE
AS
Begin
Select FirstName,LastName,(Select SUM(Salary) from t2 where t2.paymentDate=@myDate And t2.ID=@ID) from t1 Where t1.ID=@ID
END
============================

Now my question is how to set stored procedure as crystal report data source and then how to pass parameter array to crystal report or stored procedure?

Thanks,
0
Comment
Question by:concern_support
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:himanshut
ID: 33614927
View this turoial,its in VB.NET so it might be much clear to your for passign params to CR programatically

http://www.c-sharpcorner.com/UploadFile/mahesh/paramInCR11012006132839PM/paramInCR.aspx

Cheers!
0
 

Author Comment

by:concern_support
ID: 33615017
i can pass parameter to CR programatically and also i can pass parameter array, but i am concern with Stored Procedure that how to set it as CR data source. And then pass parameter to CR parameter or stored procedure?
0
 
LVL 4

Expert Comment

by:tabish
ID: 33615426
The SP as is wont work with array or string of values as it expects parameter of a specific type (in your case int and date).

In the SP you need to accept a string where you expect to pass an array. Normally the string would be "," saperated.

Create a #Temptable and write a code to put every single vale in that table.

later join that #TempTable to the query.

to setup your report with SP please see this
http://www.pdssoftware.com/newsletter/mar07/page6.htm

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 4

Accepted Solution

by:
tabish earned 2000 total points
ID: 33615467
See an example SQL below:

in that code, it parses the "," saperated string and put the values in the #TempTable within the SP. But you can also write a UDF to parse the string and return a table. It will be just another way.
CREATE PROC dbo.GetOrderList2
(
	@OrderList varchar(500)
)
AS
BEGIN
	SET NOCOUNT ON

	CREATE TABLE #TempList
	(
		OrderID int
	)

	DECLARE @OrderID varchar(10), @Pos int

	SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
	SET @Pos = CHARINDEX(',', @OrderList, 1)

	IF REPLACE(@OrderList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
			IF @OrderID <> ''
			BEGIN
				INSERT INTO #TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
			END
			SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
			SET @Pos = CHARINDEX(',', @OrderList, 1)

		END
	END	

	SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
	FROM 	dbo.Orders AS o
		JOIN 
		#TempList t
		ON o.OrderID = t.OrderID
		
END

Open in new window

0
 

Author Comment

by:concern_support
ID: 33723921
Now how to pass value to this i mean how we can call this procedure
0
 
LVL 4

Expert Comment

by:tabish
ID: 33731907
Normally the Reporting Tools (Crystal, SSRS etc) pass the multiple value parameters as a "," saperated string. you need is to setup your report to call that SP passing the multiple value parameters to it.

Here is the little exract from the link I posted before to setup your report with SP:
When creating a Crystal Report, make it like any other report, except for the following.
1. When specifying a data source, instead of selecting Tables or Views, select Procedures, and then select the newly created procedure.
2. Crystal will immediately prompt you to enter values for the procedure’s input parameters. Do NOT do so; rather, check “Set as Null” and click OK so Crystal will know to prompt for these as input parameters each time the report is run.
3. If you are using SQLServer, click on the Field Explorer and rename the fields to remove the “@” prefix that Crystal automatically adds to conform to SQLServer parameter/variable naming conventions

To call it in SQL Server, go:
execute GetOrderList2("1, 3, 8 , 9, 30, 22, 89 ")
assuming we wnated results for order ids 1, 3, 8 , 9, 30, 22 and 89

Hope it will help
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question