| Author |
Message |
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
  
Topic: Stumped
Posted: 06 Feb 2014 at 5:41am |
|
Hi everyone:
New to CR (2011). Trying to learn the basics. Already, I am at a standstill.
I am NOT into writing scripts or formulas, so I am finding the program has limited capabilities. For example:
When limiting a field to within a certain date range (using the Select Record Expert), I am not allowed to use any field more than once on a report. I want the sales amount field to show TWICE...once for this month's sales and once for last month's sales SIDE BY SIDE.
I hope this makes sense.
Is there a way to do this without writing code?
Thanks.
|
 IP Logged |
|
|
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 06 Feb 2014 at 6:06am |
|
Create two formula fields.
Call the first CurrentMonth. Call the second LastMonth.
In the first formula field editor type
{yourtable.field} = monthtodate
In the second formula editor type
{yourtable.field} = lastfullmonth
Now place your fields in your report where you want them.
This is assuming that you want a your "this month's sales" to be month to date.
|
| IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 06 Feb 2014 at 8:18am |
|
Thanks for the reply adavis.
I see what you are suggesting. Create a couple of formula fields that REPRESENT the field to reference.
But, the formula editor won't let me use my sales field in the place of {yourtable.field} because it is numeric. It wants only a date field.
|
| IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 06 Feb 2014 at 8:24am |
|
Okay, let's try this.
Create another formula field and name it ToDate.
Inside type DateTime({yourtable.field})
Now go back and replace {yourtable.field} in the other two formula fields with the ToDate field you just made.
Does that work?
There may be a cleaner way to achieve this, but this is what I would try first.
|
| IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 06 Feb 2014 at 9:05am |
|
hello adavis.
I tried the above and it DID let me save the formulas in the formula editor with NO ERRORS!
But, when I place them on the canvas, I do not get numerical values...instead, I get the word "False" displayed.
I think I may try something like:
If {sales_amt} = monthtodate
Then currentmonth = currentmonth + {sales_amt}
Else currentmonth = currentmonth + 0
I will have to play around with this as I am no programmer.
Thanks for any suggestions.
|
| IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 06 Feb 2014 at 9:16am |
|
if sales_amt is a numeric (currency, I assume) field, then I don't think that setting it to equal monthtodate will work. Again, Crystal will be looking for a date field to use with that. You don't have a date of sale field to use?
Edited by adavis - 06 Feb 2014 at 9:17am
|
| IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 06 Feb 2014 at 10:30am |
|
Here's what I SHOULD have typed:
If {sales_date} = monthtodate
Then currentmonth = currentmonth + {sales_amt}
Else currentmonth = currentmonth + 0
I think I will also have to declare a variable to use in the place of currentmonth. Then I'll have to research on how to place that variable on the CR canvas.
I have this HUGE book called Crystal Reports The Complete Reference. It's been a good friend for the last couple of weeks...although sometimes quite confusing.
Thanks again for any help.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 07 Feb 2014 at 5:50am |
|
I know that I am coming in late, but I am confused as to what you trying to accomplish.
if you are displaying this month's sales and last month's sales for say a part or a person, and you want them side by side, you need some way of 'telling' Crystal that the 2 values are related and should appear on the same row of data....because Crystal will only really display 1 row of data at a time.
|
| IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 07 Feb 2014 at 6:12am |
Originally posted by lockwelle
I know that I am coming in late, but I am confused as to what you trying to accomplish.
if you are displaying this month's sales and last month's sales for say a part or a person, and you want them side by side, you need some way of 'telling' Crystal that the 2 values are related and should appear on the same row of data....because Crystal will only really display 1 row of data at a time.
I agree with you lockwelle. CR will not let me manipulate my sales_amt field more than once per report (eg: Select/From/Where)...even if I place sales_amt multiple times on the canvas. I am guessing that there would have to be more than 1 file read pass through the table to accomplish that task, and CR only does 1 file read pass per report.
I did consider a subreport, but chose to use 2 formula fields instead. After much stress, I finally got it to work.
Thanks all for listening to my problem and for the help.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 07 Feb 2014 at 6:31am |
|
perhaps I misunderstood...if you were reading records from last month and this month, and were grouping, say by part number, you could use the formula method to separate the values to be last month and this month and display them in the group footer.
I was thinking stored proc, in which case you could do a self join to the table and have both values on 1 line...and while you're new, and if you have the time/teacher/permissions I always advise learning stored procedures for report writing as they will give you much more flexibility in how you manipulate your data for the report. Stored procedures allow you to manipulate the data in ways that are completely impossible to do in just Crystal alone...and all reporting tools allow for stored procedures, so the knowledge is transferable as well.
|
| IP Logged |
|
|
|
