Solved

using a complete sql-query as parameter?

Posted on 2004-03-22
8
1,076 Views
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?
0
Comment
Question by:kolpdc
  • 4
  • 3
8 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 80 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
http://support.businessobjects.com/communityCS/FilesAndUpdates/csharp_web_samples.exe.asp

mlmcc
0
 
LVL 4

Author Comment

by:kolpdc
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?
0
 
LVL 2

Assisted Solution

by:TeddyZero
TeddyZero earned 20 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
            ' 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
 
LVL 4

Author Comment

by:kolpdc
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?

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Expert Comment

by:mlmcc
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.

mlmcc
0
 
LVL 4

Author Comment

by:kolpdc
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?

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
 
LVL 100

Expert Comment

by:mlmcc
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

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 10710495
Glad I could help

mlmcc
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now