x Want to participate? Join the group to interact
Options
  • Replies 7 replies
  • Subscribers 119 subscribers
  • Views 3891 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
91
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
MJZ

Crystal Group by Date Weekly, pass value to subreport.

Posted By

Crystal XI

I am trying to do a Sales Analysis report by week comparing 2 years of data.

I have the main report grouping by Monday of the week and it shows a date, 1/4/2015, 01/11/2015

I am trying to get a date value of this so that I can pass it to a subreport and calculate the starting ending dates for the previous year.

for example. I want to calculates 01/05/2014 - 01/11/2014 for the subreport for the second grouping.

I tried using Date() and Datevalue(), and it passes the string variable of the group if I send GroupName, but I have been unsuccessful to calculating the previous year date range.

Thank you in advance.

  • Are you using Shared Variables?
  • in reply to BigLouie
    Seems like a group by week (using the group properties). You could do a minimum / maximum calculation, but that won't give you the correct date for the grouping if there was no transaction on the first / last date of the range.
    If the Group Name has a text string with a fixed format, you could use some text manipulation to extract the year / month / day and use them in a Date (YYYY, MM, DD) conversion to a date value.
    To calculate the previous year value, you can use the DateAdd function.
  • in reply to Kevin M
    You should be able to create one report, without the need for a subreport.
  • in reply to BShockley
    I did get the dates to work with a subreport. I would need more information on one report since I am grouping and displaying the Invoice date group for the current year. Thank you for all your help. I did move the subreport to the footer instead of the header and I do not know if that made a difference, but my numbers look good.
  • in reply to MJZ
    You would not need to group the report. Instead, you would have two formulas. One formula to calculate sales for the first date range, using a formula (if - then statement). Then, use another formula to calculate sales for the 2nd date range. Summarize each formula with a grand total.
  • in reply to BShockley
    I've done that for monthly reports, with monthly / MTD / YTD calculations compared to previous years, but I have no idea how that would work for a week-to-week breakdown comparison to previous years, without hard coding dates (or creating a lookup table with the date groupings listed).
  • in reply to Kevin M
    Sorry, I didn't notice your requirement to group by week. You may be able to create a formula that pulls out the month plus day combination. For example, today's date would give you 09/30. Maybe add 7 to the formula (based on invoice date) and see if you can group by the formula?

*Community Hub is the new name for Sage City