[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

FIELD AND LABEL MOVED TO UP IF NOT <> 0

Hi
i have created a report..which have following Design.

Amount: 1140.00
HC.Chg:        0.00
ST.Chg.:        0.00
Milne:        50.00
C.C.:             0.00
DD.:           15.00
INSU:         20.00
ATChg:         0.00
S.Tax:         45.00
ADV:          55.00

What i want is if any of field have 0 then its label suppress and next field and lable move to up.
you can say that, i dont want to print any zero fields but dont wanted to be keep that field on its original space.

for example.

Amount: 1140.00
Milne:        50.00
DD.:           15.00
INSU:         20.00
S.Tax:         45.00
ADV:          55.00


Thanks in advance.
0
crystal_Tech
Asked:
crystal_Tech
  • 18
  • 14
  • 5
  • +1
2 Solutions
 
mlmccCommented:
The only way I know to do that is to use several detail sections.
Right click the detail section in the left margin
Click INSERT SECTION BELOW
Repeat until you have 10 detail sections (DetailA - DetailJ)
Put 1 label and field in each section
Conditionally suppress the fields with
{FieldName} = 0
You can then suppress the detail section in the section expert (format section in CR7)
You can use the same formula to conditionally suppress the section or you can use the SUPPRESS BLANK SECTION option (if CR7 has it)

mlmcc
0
 
mlmccCommented:
I guess you could also use a formula that builds a string as

WhilePrintingRecords;
Local StringVar strOut;

If {AmountField} <> 0 then
    strOut := "Amount:     " & CStr({AmountFIeld},2,'') & chr(13);

If {HC.chgField} <> 0 then
    strOut := "Amount:     " & CStr({HC.chgField},2,'') & chr(13);

etc for each field in turn

strOut

mlmcc
0
 
GJParkerCommented:
If these are fields you can filter the records from teh recordset by adding a selection formula i.e.

{MyField} <>  0

or try suppressing the section the values are in

{MyField} = 0
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
crystal_TechAuthor Commented:
@ mlmcc
I think First suggestion will not work because there is so many other fields also fixed as per pre printed stationery,

I am trying Your second suggestion, but Getting an error "The Remaining Text Does Not Appear to be part of the formula.
and through me before & 


formula name = WhilePrinting
Local StringVar strOut;

If {AmountField} <> 0 then
    strOut := "Amount:     " & CStr({AmountFIeld},2,'') & chr(13);

If {HC.chgField} <> 0 then
    strOut := "Amount:     " & CStr({HC.chgField},2,'') & chr(13);

If {ST.chgField} <> 0 then
    strOut := "Amount:     " & CStr({ST.chgField},2,'') & chr(13);

If {Milne.chgField} <> 0 then
    strOut := "Amount:     " & CStr({Milne.chgField},2,'') & chr(13);

strOut
0
 
mlmccCommented:
WhilePrintingRecords is part of the formula not the formula name.

Fix the field names to be the proper name of the field.  They should be {TableName.FieldName}

  Also it should be concatenating to  strOut

 WhilePrintingRecords;
Local StringVar strOut;

If {AmountField} <> 0 then
    strOut := "Amount:     " & CStr({AmountField},2,'') & chr(13);

If {HC.chgField} <> 0 then
    strOut := strOut  & "HC.chgField:     " & CStr({HC.chgField},2,'') & chr(13);

If {ST.chgField} <> 0 then
    strOut :=  strOut  & "ST.chgField:     " & CStr({ST.chgField},2,'') & chr(13);

If {Milne.chgField} <> 0 then
    strOut := strOut  &  "Milne.chgField:     " & CStr({Milne.chgField},2,'') & chr(13);

strOut 

Open in new window


mlmcc
0
 
crystal_TechAuthor Commented:
still same error
"The Remaining Text Does Not Appear to be part of the formula.
and
through me on first line before &
0
 
GJParkerCommented:
can you re paste your formula, is your formula set to  crystal or basic syntax ?
0
 
crystal_TechAuthor Commented:
WhilePrintingRecords;
Local StringVar strOut;

If {BD.CR} <> 0 then
    strOut := "Ham:     " & CStr({BD.CR},2,'') & chr(13);

If {BD.HC} <> 0 then
    strOut := "St.Chg:     " & CStr({BD.HC},2,'') & chr(13);

If {BD.Otr} <> 0 then
    strOut := "Otr:     " & CStr({BD.Otr},2,'') & chr(13);

If {BD.BC} <> 0 then
    strOut := "C.C:     " & CStr({BD.BC},2,'') & chr(13);

If {BD.DD} <> 0 then
    strOut := "DD:     " & CStr({BD.DD},2,'') & chr(13);

strOut
0
 
mlmccCommented:
Sounds like you have BASIC syntax chosen.  Try it this way

WhilePrintingRecords
Dim strOut as String

If {BD.CR} <> 0 then 
    strOut = "Ham:     " & CStr({BD.CR},2,'') & chr(13)
End If

If {BD.HC} <> 0 then
    strOut = "St.Chg:     " & CStr({BD.HC},2,'') & chr(13)
End If

If {BD.Otr} <> 0 then
    strOut = "Otr:     " & CStr({BD.Otr},2,'') & chr(13)
End If

If {BD.BC} <> 0 then
    strOut = "C.C:     " & CStr({BD.BC},2,'') & chr(13)
End If

If {BD.DD} <> 0 then
    strOut = "DD:     " & CStr({BD.DD},2,'') & chr(13)
End If

formula = strOut 

Open in new window


mlmcc
0
 
crystal_TechAuthor Commented:
Same error But
now its throwing next to WhilePrintingRecords;
0
 
mlmccCommented:
Can you upload the report?

mlmcc
0
 
crystal_TechAuthor Commented:
Sorry :-(

i want to upload but
all posts are Publicly viewable so for some privacy reason i can not upload my report here.
0
 
crystal_TechAuthor Commented:
Hi Michael

Please check attached file.
Sample.rpt
0
 
mlmccCommented:
The problem with the BASIC syntax is you need to use " " in place of the ' ' that I provided

The one you provided needs to use the correct field names.

mlmcc
0
 
crystal_TechAuthor Commented:
I tried
but same error

If {BD.CR} <> 0 then 
    strOut = "Hamali:     " & CStr({BD.CR},2,"") & chr(13)
End If

Open in new window

0
 
mlmccCommented:
BD.CR is not the name of the field.

You need the full table name.
{Bilty_Detail.CR}

When you put fields on the report choose them from the field list.

mlmcc
0
 
crystal_TechAuthor Commented:
did that same error
0
 
mlmccCommented:
I have CR7 on a machine at home.  I won't be there until the weekend.  I will pull the report up in CR7 and see what happens.

Can you upload the latest version by Friday.

mlmcc
0
 
crystal_TechAuthor Commented:
only this part of the report is left..
i think i can solve this problem using vb6 code... but trying to avoid an another module in my  project just for this problem.
i will upload sample report by Friday.
0
 
James0628Commented:
In Sample.rpt, the WhilePrinting formula contains the Basic syntax formula that mlmcc posted, but the formula is set to use Crystal syntax, not Basic.  That will give you the "remaining text ..." error, because Crystal syntax wants a ; at the end of each statement, and you don't have those in Basic syntax.

 I assume that CR 7 has an option in the formula editor to choose the syntax.  Change it to Basic for that formula.  Or, if you're using Crystal syntax for your other formulas, we can go back to that.

 Assuming that you stick with Basic syntax, if the formula still has any single quotes in it (eg. CStr({BD.CR},2,'')), you'll need to replace those with double quotes (as already mentioned).

 Also, mlmcc's Basic syntax formula is not appending the values.  Every line that sets strOut, except the first one, needs to be changed.  For example,

    strOut = "St.Chg:     " & CStr({BD.HC},2,'') & chr(13)

  should be

    strOut = strOut & "St.Chg:     " & CStr({BD.HC},2,'') & chr(13)


 James
0
 
crystal_TechAuthor Commented:
Thanks James.
you mean like this..?
if yes then on Check (Alt + C ).. getting same error


WhilePrintingRecords;
Dim strOut as String

If {BD.CR} <> 0 then 
    //strOut = "Hamali:     " & CStr({BD.CR},2,'') & chr(13)
    strOut = strOut & "Hamali:     " & CStr({BD.CR},2,'') & chr(13)
End If

If {BD.HC} <> 0 then
    strOut = strOut & "St.Chg.:     " & CStr ({BD.HC},2,'') & chr(13)
    //strOut = "St.Chg.:     " & CStr({BD.HC},2,'') & chr(13)
End If

If {BD.Others} <> 0 then
    strOut = strOut & "Other:     " & CStr ({BD.Others},2,'') & chr(13)
    //strOut = "Other:     " & CStr({BD.Others},2,'') & chr(13)
End If

If {BD.BC} <> 0 then
    strOut = strOut & "C.C:     " & CStr ({BD.BC},2,'') & chr(13)
    //strOut = "C.C:     " & CStr({BD.BC},2,'') & chr(13)
End If

If {BD.Door_Delv} <> 0 then
    strOut = strOut & "DD.Charge:     " & CStr ({BD.Door_Delv},2,'') & chr(13)
//    strOut = "DD.Charge:     " & CStr({BD.Door_Delv},2,'') & chr(13)
End If

formula = strOut 

Open in new window

0
 
mlmccCommented:
Try removing the ; on the first line.

mlmcc
0
 
crystal_TechAuthor Commented:
Tried following line but same error..

WhilePrintingRecords
0
 
mlmccCommented:
Are you sure the fields you are using are the correct field names?

The sample uses Bilty_Detail.CR as the field name not BD.CR

ALso replace the ' ' with " "

mlmcc
0
 
crystal_TechAuthor Commented:
Sir Fields names are ok
replaced the ' ' with " "
Same Error :-(


WhilePrintingRecords

Dim strOut as String

If {BD.CR} <> 0 then 
    	//strOut = "Hamali:     " & CStr({BD.CR},2,'') & chr(13)
    	strOut = strOut & "Hamali:     " & CStr({BD.CR},2,"") & chr(13)
End If

If {BD.HC} <> 0 then
	//strOut = "St.Chg.:     " & CStr({BD.HC},2,'') & chr(13)    
	strOut = strOut & "St.Chg.:     " & CStr ({BD.HC},2,"") & chr(13)    
End If

If {BD.Others} <> 0 then
    	//strOut = "Other:     " & CStr({BD.Others},2,'') & chr(13)
    	strOut = strOut & "Other:     " & CStr ({BD.Others},2,"") & chr(13)

End If

If {BD.BC} <> 0 then
    	//strOut = "C.C:     " & CStr({BD.BC},2,'') & chr(13)
    	strOut = strOut & "C.C:     " & CStr ({BD.BC},2,"") & chr(13)

End If

If {BD.Door_Delv} <> 0 then
	//strOut = "DD.Charge:     " & CStr({BD.Door_Delv},2,'') & chr(13)
    	strOut = strOut & "DD.Charge:     " & CStr ({BD.Door_Delv},2,"") & chr(13)

End If

formula = strOut 

Open in new window

0
 
mlmccCommented:
Can you provide a sreen shot of the error?  I want to see what is highlighted.

mlmcc
0
 
crystal_TechAuthor Commented:
Please check here with file attached
error.doc
0
 
James0628Commented:
I looked at the screenshot.  CR is using Crystal syntax for that formula.  The "Dim strOut as String" line is all in black, meaning that CR doesn't understand it.  If it did, the keywords ("Dim", "as" and "string") would be in blue, like the other keywords in the formula.  The Dim command is Basic syntax.  The fact that CR doesn't recognize it means that CR is using Crystal syntax.

 I haven't used CR 7.  I don't know if it even supports Basic syntax.

 I've converted the last formula that you posted to Crystal syntax.  Try this.

WhilePrintingRecords;

Local StringVar strOut;

If {BD.CR} <> 0 then
  strOut := "Hamali:     " & CStr({BD.CR},2,"") & chr(13);

If {BD.HC} <> 0 then
  strOut := strOut & "St.Chg.:     " & CStr ({BD.HC},2,"") & chr(13);

If {BD.Others} <> 0 then
  strOut := strOut & "Other:     " & CStr ({BD.Others},2,"") & chr(13);

If {BD.BC} <> 0 then
  strOut := strOut & "C.C:     " & CStr ({BD.BC},2,"") & chr(13);

If {BD.Door_Delv} <> 0 then
  strOut := strOut & "DD.Charge:     " & CStr ({BD.Door_Delv},2,"") & chr(13);

strOut

Open in new window


 If you still get an error on that, it may be an issue with CR 7.  Maybe there's something in there that it doesn't support.


 In case you're wondering, under the {BD.CR} test, I just used strOut := "Hamali:     " instead of strOut := strOut & "Hamali:     ", because that's the first assignment.  strOut will always be empty at that point, so there's no need to concatenate the new string to it.  You can include the concatenation if you like.  It won't do any harm.  It's just not necessary.


 FWIW, here's a screenshot from the formula editor in CR 10.  There's a dropdown box at the top that lets you select Crystal or Basic syntax.  I don't see anything like that in your screenshot.  Maybe there's another way to select the syntax, or maybe CR 7 only supports Crystal syntax.  I don't know.

 James
cr-syntax.jpg
0
 
crystal_TechAuthor Commented:
Still no luck..
anyway..

May be from vb we can use array or something like this.. but no idea.. :-(

If Text27.Text = 0 Then
        If Text28.Text = 0 Then
            CrystalReport1.Formulas(40) = "ChgSecond = " & Text29.Text & ""
        Else
            CrystalReport1.Formulas(39) = "ChgFirst = " & Text28.Text & ""
'            CrystalReport1.Formulas(40) = "ChgSecond = " & Text29.Text & ""
        End If

Open in new window

0
 
James0628Commented:
crystal_Tech,

 I've never run reports from code, so I can't really help with that.


 mlmcc,

 Any luck trying the formula in CR 7?  It seems like there may be something in the formula that CR 7 doesn't like.

 James
0
 
mlmccCommented:
CR7 doesn't understand CStr

Try this

WhilePrintingRecords;

Local StringVar strOut;

If {BD.CR} <> 0 then
  strOut := "Hamali:     " & ToText({BD.CR},2,"") & chr(13);

If {BD.HC} <> 0 then
  strOut := strOut & "St.Chg.:     " & ToText({BD.HC},2,"") & chr(13);

If {BD.Others} <> 0 then
  strOut := strOut & "Other:     " & ToText({BD.Others},2,"") & chr(13);

If {BD.BC} <> 0 then
  strOut := strOut & "C.C:     " & ToText({BD.BC},2,"") & chr(13);

If {BD.Door_Delv} <> 0 then
  strOut := strOut & "DD.Charge:     " & ToText({BD.Door_Delv},2,"") & chr(13);

strOut 

Open in new window


mlmcc
0
 
crystal_TechAuthor Commented:
The remaining Text does not appear to be part of the formula
Highlighted on following line and cursor goes to before  &


strOut := "Hamali:     " & ToText({BD.CR},2,"") & chr(13);
0
 
mlmccCommented:
Try using + instead of &

mlmcc
0
 
crystal_TechAuthor Commented:
Ok.. No error found

now where i placed this formula..?
all sections are suppressed except Detail section
0
 
crystal_TechAuthor Commented:
sorry i got it. i got it..
0
 
crystal_TechAuthor Commented:
Thanks
Guys........ 1000 out of 1000  :-D

Honestly speaking.. i wasn't expecting this type of accurate Result.
But you guys are just super human.
0
 
James0628Commented:
I'm glad you finally got it working.  I've never used CR 7, so I didn't know about those restrictions.

 FWIW, I think mlmcc's post # 38426906 should be the main part of the solution (since that has the formula in it), along with post # 38427617.  Those two posts appear to be the final solution.  And , while I appreciate the thought, I'm not sure if my post is really part of the solution.  It could be seen that way, since it mentions that CR 7 may not understand something in the formula, but then mlmcc confirmed that CR 7 doesn't understand CStr (or &), so I don't know if my post really needs to be included.


 mlmcc?  Any thoughts?

 James
0
 
mlmccCommented:
It is solely at his discretion.  Though it might be better served to get the full solution into a single comment, I have no problem with his choice of comments.

mlmcc
0
 
James0628Commented:
If you're OK with it, I'm OK with it.  The earlier post was the main part of the solution, but anyone looking at this shouldn't have any problem finding it, so I don't suppose it's really a problem.

 James
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 18
  • 14
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now