How can I create a group based on the 365 dates in a calendar year?

I need to create a group based on the consecutive calendar days in a year....these values must be derived since no single data field is provided that will cover all values.  There are paydates available from each of the datasources I have linked, but In order to group the debits and expenses by month, I think I need a constant value to group by so that as each paydate is encountered the appropriate amounts will populate based on a paydate - to - calendar date match.
Seems simple to just group by paydate, but fiscal year runs July-Jun....not the same as a calendar year...so I'm using multiple sources.  I have been able to work out multiple groupings using counter fields to determine what to hide and what to sum....but because of the repetition for each group it takes forever to run.  I'm also running into some difficulty when finding my totals for the 3rd group.

Technically, each calendar date should be a new entry in the group and display even if empty.  I will use the section expert to hide the empty rows afterwards.
Thank you in advance!!
sp
sptech222Asked:
Who is Participating?
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.

sptech222Author Commented:
To clarify....my last statement should read '...and display even if *no data matches the calendar date*'.
sptech222Author Commented:
Thank you for the quick response!

I noticed at the bottom of the link a statement that reads:
"NOTE ======
· @date cannot be used for grouping or summaries because it is a print-time formula."

My primary goal is to use the calendar dates for grouping, which the NOTE indicates cannot be done.  Once working properly, I was going to hide the dates that had no matching data....this option seems to add the parts that I ultimately do not need.

I was thinking of simply using a counter to add 1 day to a starting date for a range of 365, but before creating those series of formulas I want to make sure it can be used to group on.  The grouping of a formula field is my roadblock.

any other ideas?
sp
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Mike McCrackenSenior ConsultantCommented:
Can you build a table with all the dates in it and use that as the master table?

If you are goinfg to hide the parts with no data why do you need the formula?

mlmcc
sptech222Author Commented:
The additional table is a great idea and should simplify the report significantly.  Sometimes it takes another set of eyes, or brains, to see the forest through the trees.

The reason for the formula is because there are, in effect, 4 starting points for the report...so in short I need to handle grouping by employee in transaction date order.  Without grouping, I can't guarantee the sort order of the data.

The longer answer:

* Contribution table with transaction dates for Jul 1, 2009-June 30, 2010 for fiscal year 2010.
* Contribution table with transaction dates for Jul 1, 2010-June 30, 2011 for fiscal year 2011.
* Withdrawal table with transaction date for Jul 1, 2009-June 30, 2010 for fiscal year 2010.
* Withdrawal table with transaction date for Jul 1, 2010-June 30, 2011 for fiscal year 2011.

 I'm trying to show the Ins and Outs starting with Jan 2010 - December 2011, grouped by employee/deduction code/Month so I can show monthly activity and a monthly balance for each employee.

So in addition to grouping by employee for each of their deductions, I created 4 groups....1 for each of the tables above....in that order.  This allows me to use the counter formulas to eliminate the massive duplication and provide the correct value in the group footer for each of the 4 values.  Using this order shows all Contributions first, then Withdrawals...then totals.  If I add more groups for Months it would take days to run.

By using the calendar date as a separate set of data, I can eliminate these groups and just total the detail fields for the employee.  

This is my first question to this forum since I just signed up today.  If the questions are too long or are confusing....I will readily take any advice that will make my experience with this site more beneficial to all.

I'll add a comment after trying this to let you know if it is successful.

sp
Mike McCrackenSenior ConsultantCommented:
You could sort the data by the date into the appropriate order.

mlmcc
sptech222Author Commented:
Do you mean sort without using the extra table you suggested?
If so...that is my problem...sort by which date?  I have 4 tables wtih their own dates.  Sorting by one of them will limit to a specific fiscal year....and then that data will repeat for each of the other 3 tables.  Multiple sorting with each of the dates does not keep things in consecutive order which is why I had to revert to grouping.
Mike McCrackenSenior ConsultantCommented:
Can you use a union on the tables to create one big table?

mlmcc
sptech222Author Commented:
I am unfamiliar with that.  I can't modify the tables, but if this is something that is done while in Crystal I could try it.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
A separate calendar table is so useful that Data Warehouses typically have it as standard feature (for the reason above as well as for other reasons).  In Data Warehousing jargon it's called the Time or Date dimension.

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
Mike McCrackenSenior ConsultantCommented:
What version of Crystal?

Do you have 1 set of data or are you using several?

Do they all use the same date field?

mlmcc
sptech222Author Commented:
I think I'll take that approach first...will post the results when finished.  Thank you both.
Mike McCrackenSenior ConsultantCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
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
Crystal Reports

From novice to tech pro — start learning today.