Help with Crystal Reports - Sales analysis

Trying to build a report but I am running into several issues. The first should be simple: I need to label the total field for a group of items. The group name is in the database, as an entry of 0 for a section. I have attached an example of how the database table is laid out. Basically, in the group footer with the subtotals I want to pull a record for the description where the section entry is a 0.

The second issue is more complex: I want to show previous years sales data and compare it with current year. the date selection in the report has a start date and an end date. This field is set up YYMM, like 1711 for November 2017. The record is stored as a character type though, which makes subtracting a digit difficult. I already have the report built to show current year data for whatever date range is entered, an example is attached
GroupDB.PNG
SaleReport.PNG
Chris ThomasISSAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
Chris ThomasISSAuthor Commented:
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.
0
mlmccCommented:
What is your full selection formula

mlmcc
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Chris ThomasISSAuthor Commented:
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"
0
mlmccCommented:
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};
''

Open in new window


In the group footer display the name as

WhilePrintingRecords;
Global StringVar SectionName;

StringName 

Open in new window


mlmcc
0
Chris ThomasISSAuthor Commented:
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.
0
mlmccCommented:
Can you upload the report file?

mlmcc
0
James0628Commented:
From the sound of it, I'd say that you're correct and the problem is that you're looking for a record in GroupSection (Section 0) that isn't referenced by your other data.  I thought about trying to change the link to GroupSection, and the record selection formula, so that the report also includes the 0 records, but then I think your data would be doubled (each record included an extra time for the 0 Section).

 What db are you using?  Depending on the db, a SQL Expression might be an option.  A SQL Expression is basically a subquery that you create, which is added to the main report query, to pull in some additional information.  But CR doesn't support them for every db.  Assuming that you can use one ...

 I assume that there is a group # in one of the other tables, so you have a link from that field to GroupSection.Group .  The SQL Expression would be something like:

(Select Description as Group_Name From GroupSection
Where GroupSection.Group = other_table.Group and
GroupSection.Section = 0)

Open in new window


 Replace other_table.Group with whatever field you're using to link to GroupSection.Group, and you may need to add that field to the expression by double-clicking the name in the formula editor Field Explorer, as opposed to just typing it in.

 If that works, you would have a Group_Name field that you could add to the report.

 Another option would be a subreport.  Basically, it would be a simple report that just reads GroupSection and outputs GroupSection.Description .  Put that subreport where you want to see the group name, link the Group field in the main report to GroupSection.Group in the subreport, and add {GroupSection.Section} = 0 to the subreport record selection formula.  That wouldn't be as efficient as a SQL Expression, but it may not noticeably affect the report performance.  It depends on your data, etc.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris ThomasISSAuthor Commented:
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!
0
Chris ThomasISSAuthor Commented:
Thanks for the help!
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.