ala
asked on
How to apply filters in crystal report through asp or pass parameter value to stored procedure?
Hi,
I have crystal report 8.0, IE 6.0 and database sql server 2000.
I want to develop the following application.
A crystal report is comming from a table A. Through asp the user can select any column of table and then any values of this column, and these values will be applied to report as filter. Either it can be applied directly to report as filter or pass to stored procedure which can use these values into its where clause or any other easy solution. I need only end result, which is filter the reports data.
Table A
-------------------------- -
C1 C2 C3
1 alex 600
2 john 200
3 ami 700
4 frank 100
5 fred 500
user can select any column and then value for example he selects
C1(2,3) and C2(alex,john)
Now when user will click on report link and report will bring all data from table A
where c1 in (2,3) and C2(alex,john).
I need very urgent help.
Thanks in advance.
I have crystal report 8.0, IE 6.0 and database sql server 2000.
I want to develop the following application.
A crystal report is comming from a table A. Through asp the user can select any column of table and then any values of this column, and these values will be applied to report as filter. Either it can be applied directly to report as filter or pass to stored procedure which can use these values into its where clause or any other easy solution. I need only end result, which is filter the reports data.
Table A
--------------------------
C1 C2 C3
1 alex 600
2 john 200
3 ami 700
4 frank 100
5 fred 500
user can select any column and then value for example he selects
C1(2,3) and C2(alex,john)
Now when user will click on report link and report will bring all data from table A
where c1 in (2,3) and C2(alex,john).
I need very urgent help.
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Look at these examples
http://support.businessobjects.com/communityCS/FilesAndUpdates/aspxmps8.exe.asp
mlmcc
http://support.businessobjects.com/communityCS/FilesAndUpdates/aspxmps8.exe.asp
mlmcc
ASKER
Thanks,
I got idea from examples. But now I need first how to passing mulitples values to asp and then how stored procedure will handle it.
I am doing like this:
asp:
NewParamValue = "2,3"
NewParamValue2 = "'john','alex'"
' Save the new value for the Stored Procedure in the Store Procedure
ThisParam.SetCurrentValue cint(NewParamValue), 7
ThisParam2.SetCurrentValue cint(NewParamValue2), 12
and in stored procedure:
CREATE PROC dbo.GetOrderList1
(
@C1List varchar(500),@C2List varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL =
'SELECT proj_ID, ProjectName, PIN, BUDS
FROM dbo.actcodes
WHERE C1 IN (' + @C1List + ') and
C2 IN (' + @C2List + ')'
EXEC(@SQL)
END
GO
------------------
When I run this stored procedure for number value it works but when i run it for string value it gives me error.
Is it right way I am doing.
If you can give me code or some tips I wil be thankful to you.
Thanks
I got idea from examples. But now I need first how to passing mulitples values to asp and then how stored procedure will handle it.
I am doing like this:
asp:
NewParamValue = "2,3"
NewParamValue2 = "'john','alex'"
' Save the new value for the Stored Procedure in the Store Procedure
ThisParam.SetCurrentValue cint(NewParamValue), 7
ThisParam2.SetCurrentValue
and in stored procedure:
CREATE PROC dbo.GetOrderList1
(
@C1List varchar(500),@C2List varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL =
'SELECT proj_ID, ProjectName, PIN, BUDS
FROM dbo.actcodes
WHERE C1 IN (' + @C1List + ') and
C2 IN (' + @C2List + ')'
EXEC(@SQL)
END
GO
------------------
When I run this stored procedure for number value it works but when i run it for string value it gives me error.
Is it right way I am doing.
If you can give me code or some tips I wil be thankful to you.
Thanks
What error are you getting?
CR 8 is limited to character strings of 254 or shorter. Your procedure is expecting two 500 character parameters. I don't think Crystal can do that.
mlmcc
CR 8 is limited to character strings of 254 or shorter. Your procedure is expecting two 500 character parameters. I don't think Crystal can do that.
mlmcc
ASKER
And aslo please tell me how to call crystal report through asp?