Go Premium for a chance to win a PS4. Enter to Win


using a complete sql-query as parameter?

Posted on 2004-03-22
Medium Priority
Last Modified: 2008-03-03
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?
Question by:kolpdc
  • 4
  • 3
LVL 101

Accepted Solution

mlmcc earned 320 total points
ID: 10649594
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


Author Comment

ID: 10650084
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?

Assisted Solution

TeddyZero earned 80 total points
ID: 10651011
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 to set the DataSource
        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. :-)
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.


Author Comment

ID: 10655881
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?

LVL 101

Expert Comment

ID: 10664135
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.


Author Comment

ID: 10675711
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?

...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?
LVL 101

Expert Comment

ID: 10683541
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

LVL 101

Expert Comment

ID: 10710495
Glad I could help


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

824 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