Personalization, Customization and Productivity Tools

[CMEPCMEP]

Howdy,

I'm working in Crystal (V11.5 I believe).  I need a total for sales (issues, whatever) for the year (two years or older).  However, I'm just interested in the total and not the detail printing.  Just one total for each item number.

This version is new to me and I do not see separate periods so I'm using "IM_ItemWhseHistoryByPeriod.QuantitySold" and it prints the same item number as many times as there was activive periods.

I did a formula that sums them up and it gives me the correct total.  However, it's still duplicating the item number for as many periods had activity. I also did a running total but don't know how to grab the last number only.

These are my two best thoughts.  Any input or direction would be greatly appreciated.

[Heather]

Your IM data should be in the detail section. 

Group by item number. 

Put your item summary in the group footer. 

Hide the detail section.

That should give you just the totals.

[CMEPCMEP]

---

Heather wrote:

Your IM data should be in the detail section. 

Group by item number. 

Put your item summary in the group footer. 

Hide the detail section.

That should give you just the totals.

---

The running total or the formula total in the group footer?

Hide the IM data field in the detail section or hide the entire detail section (drill-down)?

I'm not sure what you're doing but either way I tried almost all those combinations and nothing yet.

But thanks anyways.

[Heather]

I would group by item number.

I would put the fields that I want totals for in the detail section.

I would put the total fields in the group footer.  I normally do this by right-clicking the field in the detail section and choosing "insert . . . summary".  I'm not sure if that's how you created your formula or not.  But yes, both it and the running total (if you really want it) would go in the group footer.

Hide the entire details section.

Put the item code & description in the group footer so that you can identify what the totals are for.

If this doesn't work, post back.  Perhaps I am mis-understanding the problem.

[CMEPCMEP]

Thanks for you input Heather.

Its sounds like we're starting to divert the entire report into the footer section.  I don't think that's something I would be happy with or something I would want to do.  There are going to be a lot of other things on that line as well.   And one of my primary prerequisites is to have one line for each item number.  That's critical for exporting to Excel.  And it makes the report easier to read and use.  I'd rather try to fix the problem rather than work around it.

Getting one total for one year is a real basic need in Accounting.  Unfortunately, it doesn't seem to be an easy thing to do in Crystal.  I've been going around in circles for months on this report.  And I have to make it happen in the end.

I did something that got the results but I'm reluctant to see how things behave with other additions on that line and conflicting selection criteria, of which I have been having problems with.

What I said in the original post above regarding the formula I used (not summary total) that gave me the total but kept duplicating the line for as many times as there were periods with activity.  Here's what I did.  I formatted everything in the details section to suppress if duplicated (Format Editor | Common).  That gave me blank lines.  Then I formatted the overall detail section to suppress blank sections (Section Expert | Details).  But like I said,  we'll see where this goes when I begin to complete the report.

[Heather]

If you put a field in the details section, it will print with every detail record that prints.  This isn't a problem, it is how it works.  

Designing a report that hides the details & displays only the group footer information is a perfectly legitimate way to design it & people do it all the time.  It is also a lot easier than what it sounds to me like you are trying to do. 

But again, maybe I am mis-understanding something.  My understanding is that you want a report that summarizes sales totals for your inventory & displays a single line for each item.  So, you want to take raw data that looks something like this . . .

ITEM YEAR PER QTY DOLLARS
AAA  2010 01   3   1500
AAA  2010 03   1   500
AAA  2010 06   1   500
BBB  2010 02   20  2000
BBB  2010 03   7   700
CCC  2010 01   5   10
CCC  2010 02   5   10
CCC  2010 03   10  20
CCC  2010 04   20  40
CCC  2010 06   10  20

. . .  and end up with a report that looks like this . . .

ITEM  QTY YTD   DOLLARS YTD
AAA   5         2500
BBB   27        2700
CCC   50        100

Do I have this right?  If so, what I've tried to describe is the way to do.  It exports nicely to Excel, too.  Get a feel for what I'm describing by starting a new report from scratch using just one data file and a few fields. 

[CMEPCMEP]

Heather, that's essentially what I'm looking for.

It sounds like what you're doing is moving the entire details section into a footer section.  And if I already have something in my footers, I guess I can just add another one and work in there?  Is that right?  If that's the case, I'll keep it in mind, but at the moment, based on my post above, I seem to have it working in the details section by using a sum formula and then eliminating all the duplicate lines it's showing for however many periods of activity there were.

[BShockley]

You have to create a group and then insert a summary (subtotal) based on your group.

Your group should be based on the item #.

Go to the Insert - Group menu and create the item # group.

Add your data to the Details section.

Right-click the field you wish to summary and select Insert Summary.  Create a sum of the field by item #.  The summary field will be inserted automatically into the group footer #1 section.

And yes, if you are using the page footer for something else, you can create another page footer section by right-clicking the gray area of the Design screen and select Insert Section Below.

[Heather]

Thank you, Brian, for clarifying what I meant by "group by item". 

[CMEPCMEP]

Hi Heather, I’m still with you.  Thanks for hanging in there.  I appreciate it greatly.

After I pretty much got it working with my original thought of using a formula to sum the periods, suppress the duplicate lines (for as many periods had activity) and then delete the blank lines, I ran into two more problems.  So I tried your suggestion of moving everything and the summary into a footer, that worked (and I understand), unfortunately I ran into the same two problems.

The first thing I have to tell you is that I need that total period number divided by two.  I was able to do that by incorporating that summary total in a formula, and using that in place of the summary itself (in the footer).  In the end, the net result was pretty much identical to my original way.

Here are the two problems.  After we calculate that number, it’s used in another four formulas (columns) to the right.  I also need report totals for that last column as well.  Firstly, even though the details section is hidden for the multiple periods, it’s still considered on the report and moving to every formula to the right of it.  Therefore, those numbers would be in the report totals erroneously.  (As well as the records count, which really isn’t important.)  The second problem is I cannot insert a summary for that field anyways.  The reason that I’m reading (on line) is something about “second pass functions or multi-pass process”!?!?  I’m reading it applies to totaling periods or using “sum” in a formula to total them (or any formula associated with it).  It apparently has something to do with the way Crystal reports processes the information.

I thought we made a lot of progress.  The report looked great.  But now I can’t finish it because of the totals.  That’s where I wound up.  So frustrating (and discouraging).  I may be able to do this quicker by hand with an abacus!  For me, trying to do anything in Crystal has been like getting blood from a stone.  And that’s been the simple, easy stuff, like this.  Please let me know what you think.  Any further input is greatly welcomed.