Help with Crystal Reports - Sales analysis

For the field header you can put the headings in the group header and just display the value.

Are you using a cross tab for the report?

How is the selection formula written for the report?
I assume it is using the 1711to do the selection

The formula to get the previous year would be

    CStr(CInt({YourParameter}) - 100,0, '')

mlmcc

I think I have the selection formula written out now, I used:

totext((tonumber({?StartDate})-100),"#")

This will first convert the string to number, subtract 100 to drop it a year, then convert back to text dropping the decimals and separator. The only problem I am now experiencing is labeling the subtotals.

I actually want the label in the footer area for a particular group, it will look better there I think. From what I can figure out it looks like none of the detail lines I'm pulling from the selection has a value of 0 for the field that contains the descriptions. That appears to be preventing me from setting the subtotal name to the proper value.

What is your full selection formula

mlmcc

I have 5 tables: dbo.Documents, dbo.InvoiceHeader, dbo.InvoiceDetail, dbo.Inventory, and dbo.GroupSection. The first 3 are linked via a common field InternalDocID, and the last 3 are linked via the Inventory table.

Selection:

({Documents.DocYYMM} in {?StartDate} to {?EndDate} or
{Documents.DocYYMM} in {@PrevStartDate} to {@PrevEndDate}) and
{GroupSection.Group} in [14, 16, 2, 23, 26, 30, 39, 47, 50, 54, 55, 64, 7] and
{InvoicesHdr.InvoiceType}<>0 and
{InvoicesHdr.InvoiceType}<>3

The report will prompt for a start date and end date. The PrevStartDate and PrevEndDate fields contain the code I posted earlier. The GroupSection table is really just a listing of our inventory categorization system. The table is 3 fields: Group, Section and Description. Each Group is a number, as is the Section, and the Description is a string. It stores the group name as section 0. This is where I am running into a problem. In the group footer I want to display the description field for the record where section = 0. Since no records in the detail line ever have a 0 for the section field I am thinking it skips that record completely. I can't determine how to just specify "display GroupSection.Description where GroupSection.Section=0"

Are you sorting the groups on the section?

If so try this
In the group header add a formula

WhilePrintingRecords;
Global StringVar SectionName;

StringName := {GroupSection.Description};
''

Select allOpen in new window

In the group footer display the name as

WhilePrintingRecords;
Global StringVar SectionName;

StringName 

Select allOpen in new window

mlmcc

That almost works, it is displaying the first section name (usually Section 1). I need Section 0, which won't have any detail records associated with it.

Can you upload the report file?

mlmcc

OK, that worked! I created the subreport, linked the fields and set the subreport selection to Section=0. Little bit of formatting and it is displaying how I want now! Thanks!

Thanks for the help!

You're welcome.  Glad I could help.

 James