using a complete sql-query as parameter?

i've got a client (c# winform, crystal reports .net2003-studio) that has complete informations to filter data provided by database. filter consists of parts of a complete sql-statement. now i want to give my filter to a crystal report that should provide me a list of entries with corresponding data. the entries should be filtered to show the same result like my client. my filter-criteria could change several times in future.

i have seen two ways to pass information into a report:
1.) give him param-fields
2.) set a formula (only cr-syntax?)

both versions do not seem to fit my needs, because
1.) i do not want to cut my filter into little pieces (if my client filter changes, my cr-params change, formulas change, ... - it's more than double-work to pass all parameters)
2.) i do not really like translating my sql into cr-formulas (same as above)

is there a way to pass the report my complete sql-statement or for example the WHERE-clause to minimize later following administrative work? or is there a possibility to cut the sql into pieces in cr so i have it separated from my code and do not have to care?
LVL 4
kolpdcAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
From VB with CR8 you can.  I suspect it is possible in C# with CR.Net

In VB there is a RecordSelectionFormula property.  Simply set it to the desired "WHERE" clause.

crRpt.RecordSelectiionFromula = "{ViewName.Field} = Value"

for example to find all records for user Smith

crRpt.RecordSelectiionFromula = "{MyView.UserName} = 'Smith'"

These samples should provide more info
http://support.businessobjects.com/communityCS/FilesAndUpdates/csharp_web_samples.exe.asp

mlmcc
0
 
kolpdcAuthor Commented:
mlmcc,  >> = "{MyView.UserName} = 'Smith'" << -> it's the reason for my question.

i have several modules. all of them using own filter-classes. easy filters just consist of an sql-where-clause with 5-20 attributes and order by. complex ones have extra joins and group by. if i have to cut my values of and translate to cr-syntax i'll have much work in front of my feet. having the possibility to only pass my sql would prevent much work now and for the future.

there is the possibility to pass a cr-selectionformula to the report. but sql?
0
 
TeddyZeroConnect With a Mentor Commented:
If you use a DataSet to show the data retrieved by your query to the user, you can set this on your report to use the same DataSet, all you need is to add your Report to the project, create a new var from it and set the Property ReportSource of your CrystalReportViewer object.

With sample code is more easy to understanding. :-)

    Private Sub SetMyReportWithMyDS(ByVal myDS As DataSet, ByVal CRV As CrystalDecisions.Windows.Forms.CrystalReportViewer)
        Dim xReport As New rptTest ' This is my report(rptTest.rpt) included in the project
        Try
            ' Try to set the DataSource
            xReport.SetDataSource(myDS)
        Catch ex As Exception
            MessageBox.Show("Unable to set the DataSource", "Error", MessageBoxButtons.OK)
        End Try
        ' OK, let's rock.
        CRV.ReportSource = xReport
    End Sub

Simple call this sub with your DataSet and your ReportViewer.
But remember, your report and your DataSet must have the same fields name or your report will not show anything or will throw an error to you.

I Hope this help's you.
Enjoy it. :-)
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
kolpdcAuthor Commented:
TeddyZero, it's a nice way. unfortunately not fitting my needs. i have got a business-layer that separates everything from database. datasets i do not use (readers are faster, less memoryconsuming, ...).

perhaps there is no other way than translating my sql-clauses into cr-code?

0
 
mlmccCommented:
To the best of my knowledge Crystal doesn't handle the type of where clauses you are referring to very well.  It is also my understanding that you cannot change the select part of the SQL which I believe you are implying by the need to add new joins.

Group by, Order by, where can all be changed quite easily from the application.  For my reports I write the SQL in the database without any where clause unless it will always be needed and no groups again unless they will always be used.

I generally put the grouping and sorting in the report and leave filtering to the application.

mlmcc
0
 
kolpdcAuthor Commented:
looking around i found out that somehow it seems possible to use sql. i will explain in following lines and increase points, too:

seems like crystal retrieves a special amount of entries from database. a bigger amount then required. then it takes this set of data and processes client-side-filtering (which i think takes much more time then done by db).

there is the possibility to enter a sql-command directly in the section where someone may select the tables and columns to be retrieved from database. it is stored there as a command which can later be used like a table.

my slightly changed question: it looks as if a parameter-field could be inserted in such an sql-statement. is there a way to
1.) put intelligence directly into sql-command (if paramfield-length, insert where-clause...)
2.) or else modify the sql-statement from somewhere within the formula-editor?

how would such a formula have to look like?

SQL-code...
...FROM table1, ....
************ formula part begin
if Length({?paramfield}) > 0 then
    ECHO?? {?paramfield}
************ formula part end
...ORDER BY...

is there a way to do this? directly in sql-command?
or else get the sql-command-object in formula-editor and modify it from there?
0
 
mlmccCommented:
You can do what you are trying in a stored procedure but I don't think SQL directly supports it.

You could so that in a CR formula though that would be after the data is retrieved.

I believe if you specify server side grouping CR does all the filtering on the server rather than the client.  I forget where it is but I believe it is under FILE --> OPTIONS

0
 
mlmccCommented:
Glad I could help

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.