Advanced query in crystal report? 
Author Message
 Advanced query in crystal report?

Hi, VB programmers:
I have a problem on using a "Group by" query in crystal rpt.
Scenario as follow:
I have a table say called "Purchase" which has three columns :
purchaser, item, price
the query is like:
Select item, sum(price) from Purchase where purchaser ='SOMEBODY' group
by item

What I want to do is to pass the SOMEBODY at run-time.
I don't know how to do it . I tried selectionFormula without luck.

Any hints will be appreciated!

-QW



Fri, 02 Feb 2001 03:00:00 GMT  
 Advanced query in crystal report?

Quote:

> Hi, VB programmers:
> I have a problem on using a "Group by" query in crystal rpt.
> Scenario as follow:
> I have a table say called "Purchase" which has three columns :
> purchaser, item, price
> the query is like:
> Select item, sum(price) from Purchase where purchaser ='SOMEBODY' group
> by item

> What I want to do is to pass the SOMEBODY at run-time.
> I don't know how to do it . I tried selectionFormula without luck.

> Any hints will be appreciated!

> -QW

For the group by part, you can create a group in the report which prints
for every item.
This can sometimes be slow, depending on the db type/size/indexes. If
so, you may be better off creating a view (if your database supports
views) and reporting from that.

Remove the "where" piece of the query from the report (Report|Edit
Selection Formula|Record...) and pass it at runtime using either the OCX
property "SelectionFormula" or the CRPE32.DLL function
"PESetSelectionFormula".

Crystal requires a weird syntax that looks kind of like this:

  {table.field} = 'whatever'

(You must use the brackets. See the crystal help file for more
information.)

So your VB code to set the "Where" clause would look something like:
  Crystal1.SelectionFormula = "{Purchase.purchaser} = '" & sPurchaser &
"'"

Crystal can be a little confusing, but it does an O.K. job once you
understand how it works.

Hope this helps,
Dave
--
I am a resident of Washington state. In Washington, Spam
is subject to a $500 or greater penalty. You are warned.



Sat, 03 Feb 2001 03:00:00 GMT  
 Advanced query in crystal report?

Quote:

>Hi, VB programmers:
>I have a problem on using a "Group by" query in crystal rpt.
>Scenario as follow:
>I have a table say called "Purchase" which has three columns :
>purchaser, item, price
>the query is like:
>Select item, sum(price) from Purchase where purchaser ='SOMEBODY' group
>by item

>What I want to do is to pass the SOMEBODY at run-time.
>I don't know how to do it . I tried selectionFormula without luck.

>Any hints will be appreciated!

>-QW

**** IF YOU ARE USING THE PRINT ENGINE! ****
you may create a formula in your RPT file and use it as your group-by
field, and update its value at run-time -you can use a formula
as a sort or group by field. At run-time pass the formula
your entire sort or group-by string along with the variable
condition "SOMEBODY". In your routine that calls the report
pass the string to your formula using the following structure; place it just
before your PEStartPrintJob call. Keep the following commented notes in
mind when passing formulas string values at run-time.

IF YOU ARE USING THE OCX THEN JUMP TO THE PARAGRAPH BELOW THE FOLLOWING
SAMPLE CODE.

'**************************************
    'changing formula values at runtime through PE

    'and target formula requires datatype place holder
    'in the RPT file itself. For example - a formula
    'receiving a string must contain quotes " " with a
    'space as placeholder. A numeric datatype formula must
    'contain a numeral -ie: 0- to serve as a place holder.
    'A date serves as a place-holder to receive a date
    'ie: date(1990,12,31)

    'a hardcoded string value looks like "'my string'"
    'and a variable string "'" & myStringVariable & "'"
    'if myStringVariable = "my string"

    'a hardcoded number value looks like NUM
    'if string NUM = "1234"
    'and a variable number str$(NUM)
    'if integer NUM = 12

    'a hardcoded date value looks like "1995, 01, 31"
    'and a variable date "Date("& WHOLEDATE & ")"
    'if WHOLEDATE = "1995, 01, 31"

    'such as Date(1990,01,31) as a placeholder


    Dim formulaName, TextHandle, TextLength, criteria

    formulaName = "myFormula"

'note: the following criteria string must work in the Crystal Report formula
editor, proper syntax etc.. This 'sample string does NOT contain proper
crystal syntax! it is psudo code only, re-write in formula editor
 criteria = "Select {item}, sum({price}) from {Purchase} where {purchaser}
=" & SOMEBODY & "group by {item}"

        'set formula
        result = PESetFormula(jobnum, formulaName, "'" & criteria & "'")
        If result <> 1 Then 'an error
            ErrorTrap "SetFormula in call_Report", jobnum
        End If

        'check formula
        result = PECheckFormula(jobnum, formulaName)
        If result <> 1 Then
            ErrorTrap "CheckFormula in call_report", jobnum
        End If

**** IF YOU ARE USING THE OCX! ****
then you will have to pass your formula the string using the Formulas
property:

Formulas Property
Usage

[form.]Report.Formulas(ArrayIndex)[= "FormulaName=  FormulaText"]

Enter the formula name and the string that you want to replace the existing
string for each formula that you want to change in your report.

Example:

'note: the following criteria string used in my sample:

'"Select {item}, sum({price}) from {Purchase} where  {purchaser} ='" &
SOMEBODY & "' group by {item}"

must work in the Crystal Report formula editor, proper syntax etc.. This
sample string is NOT proper crystal syntax! it is psudo code only, re-write
in formula editor

CrystalReport1.Formulas(0) = "MYFORMULA= "Select {item}, sum({price}) from
{Purchase} where
  {purchaser} ='" & SOMEBODY & "' group by {item}"

Remarks(copied from Crystal help)

Formulas is an array property that is available at runtime only.
Use a separate line of code for each formula you want to change.
Change only those formulas that you want to change.
The first formula you change must be assigned array index (0), the second
must be assigned array index (1), etc.
The new formula string must conform to Crystal Reports syntax requirements.
This property is cleared once the print job is printed. If you print a
second time, the program reverts to the formulas as originally specified in
the report.

NOTE:  Spaces are significant in formula names. For this reason, the equal
sign must follow the formula name with no intervening spaces.

property.
NOTE:  You cannot use this property to create new formulas. You can only use
it to change existing formulas.

Data Type
Array of strings

Availability
Runtime

Remember: your criteria string that you pass must work in the crystal
reports formula editor, in other words the syntax must be correct as per
crystal reports conventions. The syntax used for the criteria string in my
examples are written in psudo code only. You will have to write these in the
formula editor.

I hope this helps!

Rob Waller



Sat, 03 Feb 2001 03:00:00 GMT  
 Advanced query in crystal report?
   Assume your CrystalReport object is called CR1. Formulate your string.
You can do this in the Crystal Reports development environment to ensure
your syntax is correct. Assign this to a string, for this example I used
strSelection and place the following code before your action command

     CR1.ReplaceSelectionFormula strSelection

Quote:

>Hi, VB programmers:
>I have a problem on using a "Group by" query in crystal rpt.
>Scenario as follow:
>I have a table say called "Purchase" which has three columns :
>purchaser, item, price
>the query is like:
>Select item, sum(price) from Purchase where purchaser ='SOMEBODY' group
>by item

>What I want to do is to pass the SOMEBODY at run-time.
>I don't know how to do it . I tried selectionFormula without luck.

>Any hints will be appreciated!

>-QW



Tue, 06 Feb 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Using Sub Query in Sql Query - Crystal Report 5.0

2. Must Sell Crystal Reports Advanced 9.0

3. Must Sell Crystal Reports Advanced 9.0

4. Crystal report based on Crystal query, doesnt work

5. CRYSTAL REPORT, run time query report by date

6. Q: Pass a SQL query from VB to crystal report sub-report

7. sub-report (2 query's ) in crystal reports

8. Help with a join query into Crystal Reports 6 Exception Report

9. Crystal Reports bug - report off new query

10. sub-report (2 query's ) in crystal reports

11. Printing Crystal Reports without Crystal Control ActiveX or Crystal Reports API call

 

 
Powered by phpBB® Forum Software