Solved

Populate a multi-line formula field dynamically

Posted on 2004-07-30
14
941 Views
Last Modified: 2008-02-01
Hi,
I am trying to populate a CR formula field (from VB6) with a 'Select' statement followed by several 'Case' statements.  I have tried separating the 'Case' statements with spaces, commas, carriage-returns, line-feeds, and combination of carriage-return/line-feed.  I always get a formula error.  Here is a sample of what I am trying to do:

Select {qryQtrRpt_A999_Report.Type}
   case "  ": 880
   case "10": 17
   case "12": 5
   .
   .
   .
   default: 0;

I am dynamically determining both the 'case' selection item and its number.  Trying to send the entire 'Select' statement as a string for the formula results in an error.    Any ideas?

Ken
0
Comment
Question by:kenspencer
14 Comments
 
LVL 3

Author Comment

by:kenspencer
ID: 11680288
Sorry -- slightly mis-stated.  The numbers (after the : of the case statement) are being returned as strings (since I thought it would be simpler).  Same problem, though.
0
 
LVL 10

Expert Comment

by:ebolek
ID: 11680405
your formula select statement has no mistake

Select {Customer.Fax}[1 To 3]
   Case "604", "250" :
      "BC"
   Case "206", "509", "360" :
      "WA"
   Default :
      "";

If the type of the {qryQtrRpt_A999_Report.Type} is string then it should fine . if it is not covert it to string
write like this
Select cstr({qryQtrRpt_A999_Report.Type})

Regards
Emre
0
 
LVL 3

Author Comment

by:kenspencer
ID: 11680509
Emre,
I am trying to send the entire Select statement as a single string (from VB6 to Crystal), so in your example, what character(s) should I concatenate in the string after the : (colon)?

Ken
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 10

Expert Comment

by:ebolek
ID: 11680538
well if you are using vb6

then write it like this

text = "Select Select {qryQtrRpt_A999_Report.Type}" +
   " " + "case "  + """"+ " " + ": 880" +
.......
0
 
LVL 3

Author Comment

by:kenspencer
ID: 11680702
Here's what I am doing:

       Q = Chr$(34)
        x = 0
        tmp = ""
        With rs         'ADODB recordset
            .Open qry, g_cnReports, adOpenStatic, adLockReadOnly
            Do While Not .EOF
               '  In rs, !Type is a string, !TypeCnt is an integer
                tmp = tmp & vbLf & "   case " & Q & !Type & Q & ": " & Q & CStr(!TypeCnt) & Q
                x = x + !TypeCnt
                .MoveNext
                DoEvents
            Loop
            .Close
        End With

        tmp = "Select {qryQtrRpt_A999_Report.Type}" & vbLf & "   case " & Q & "  " & Q & ": " & Q & CStr(x) & Q & tmp
        tmp = tmp & vbLf & "   default  : " & Q & "0" & Q & ";"
        g_sCrystalFormulae(6) = tmp

       Then later I send the formula to Crystal as:
            g_crwRpt.FormulaFields.Item(x).Text = "'" & g_sCrystalFormulae(x) & "'"     <-- those are single quotes surrounded by double-quotes

What I am having trouble with is separating the 'case' statements.  In my example I am using a combination of a line-feed and 3 spaces, but Crystal seems to object to that.  If I use vbCr or vbCrLf, it seems to read the carriage-return as the end of the statement.  Unless I have made a typo somewhere, just spaces or commas also fail.

Ken
0
 
LVL 10

Expert Comment

by:ebolek
ID: 11680876
you are sure item 0 is the formula that you need right.Wht dont you refer to the formula with the name of it in the collection.

You are incrementing x in the while loop are you sure that you have that item in the collection

0
 
LVL 10

Accepted Solution

by:
ebolek earned 250 total points
ID: 11680928
Also this line seems wrong .
g_crwRpt.FormulaFields.Item(x).Text = "'" & g_sCrystalFormulae(x) & "'"
g_sCrystalFormulae(x) is already a string you dont need t ouse "" 

g_crwRpt.FormulaFields.Item(x).Text = g_sCrystalFormulae(x)

Regards
Emre


0
 
LVL 100

Expert Comment

by:mlmcc
ID: 11681364
instead of vblf try using
chr(13)

mlmcc
0
 
LVL 3

Author Comment

by:kenspencer
ID: 11693694
Emre,
Sorry I was not clear in my last post.  The last line, where I set g_crwRpt.FormulaFields.Item(x).Text to a value is in a different subroutine, and x begins with 1, not 0.  Also, when I take out the single quotes surrounding the string, Crystal gives me an error; but it does work with the string surrounded by the single quotes.

mimcc,
I have tried vbCrLf (which = Chr(13) & Chr(10)), vbCr (which is Chr(13)), vbLf (which is Chr(10)), commas, spaces and combinations thereof, but I have been unsuccessful in getting the formula to dynamically accept multiple 'case' statements with some form of new line indicator.  I would settle for 1 long line if I knew what I need to use to separate the 'case' statements.

Ken

0
 
LVL 13

Expert Comment

by:EwaldL
ID: 11730879
the following did the trick for me

  crReport.FormulaFields.Item(1).Text = "Select {Customer.Customer Name}" _
   + "case '  ': 880" _
   + "case 'Poser Cycles': 2222" _
   + "default: 0;"
0
 
LVL 3

Author Comment

by:kenspencer
ID: 11752884
Emre,
(I have been on vacation for a week)
It appears that you have provided a multi-line VB statement; what I need is a multi-line formula text as the value of crReport.FormulaFields.Item(x).Text.  From your example it appears that the formula would end up being:

Select {Customer.Customer Name}case ' ': 880case 'Poser Cycles': 2222default: 0;

Did Crystal actually interpret that correctly (in other words, have I somehow made a mountain out of a mole hill)?

Ken
0
 
LVL 10

Expert Comment

by:ebolek
ID: 11752917
Yes it would do the trick for you. The formula text will be like this then use the code that i posted to set the formla text dynamically

Select {Customer.Customer Name} case ' ': 880 case 'Poser Cycles': 2222 default: 0;

Regards
Emre

P.S. I was on vacation last week too. :)
0
 
LVL 3

Author Comment

by:kenspencer
ID: 11753578
Woops ... appears my mind was still on vacation.  Sorry EwaldL -- thought you were Emre.

Anyway, I found the problem.  Until now, all of my Crystal formula items were treated as text in my VB program and surrounded with a single quote when sent to Crystal (works fine).  However, since this is actually a *real* formula and not just a simple string, then removing the single quotes surrounding the entire statement was indeed the answer.  So, it seems that Emre's post from 7/30/2004 03:02PM was the right answer, although my feeble mind got confused.  :)

Therefore, the points will be going to Emre.  Thanks to everyone for their help.

Ken
0
 
LVL 10

Expert Comment

by:ebolek
ID: 11753620
Glad to help
Emre
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

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. …
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 …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

679 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