Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to apply filters in crystal report through asp or pass parameter value to stored procedure?

Posted on 2004-03-31
5
Medium Priority
?
195 Views
Last Modified: 2008-03-06
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.
0
Comment
Question by:ala
[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
  • 2
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
EwaldL earned 2000 total points
ID: 10728121
create a multi value parameter which has the same data type as c1. then include this parameter into the record selection formula. i believe the syntax is ic equal icparamter.

do the same for the c2 field.

now you should be able to enter the parameter values (2,3 / alex, john) and the report should show up with only the records that match the criteria.
0
 

Author Comment

by:ala
ID: 10728954
Can you send me code please. I am not  programmer of crystal report.
And aslo please tell me how to call crystal report through asp?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 10729418
0
 

Author Comment

by:ala
ID: 10732306
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
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 10745990
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
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

670 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