I would recommend using a command for this - I think that's the only way you're going to be able to do what you need. A command is just a SQL Select statement. For information about how to work with them correctly in Crystal see my blog post here:
https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/
Working with just a single table that has the meter data, here's what the command I wrote looks like:
Select auditMonth, auditYear, EquipID,
max(DoubleClickMono) monoMax, max(DoubleClickColor) colorMax,
min(DoubleClickMono) monoMin, min(DoubleClickColor) colorMin
from (
Select lme.auditMonth, lme.auditYear,
md.EquipID, md.DoubleClickMono
md.DoubleClickColor, md.AuditID
from MeterData md
inner join (
Select
case MONTH([Audit Date])
when 1 then 12
else MONTH([Audit Date])-1
end auditMonth,
case MONTH([Audit Date])
when 1 then YEAR([Audit Date])-1
else YEAR([Audit Date])
end auditYear,
max([Audit Date]) maxDate
from MeterData
where [Audit Date] <= GetDate()
and [Audit Date] >= DATEADD(yy, -2, GetDate())
group by MONTH([Audit Date]), YEAR([Audit Date])
) lme
on md.[Audit Date] = lme.maxDate
UNION
Select
MONTH(md.[Audit Date]) auditMonth, YEAR(md.[Audit Date]) auditYear,
md.EquipID, md.DoubleClickMono, md.DoubleClickColor, md.AuditID
from MeterData md
where [Audit Date] <= GetDate()
and [Audit Date] >= DATEADD(yy, -2, GetDate())
) meterAudit
group by auditMonth, auditYear, EquipID
You would then create a formula that would append the year and month into a string. In order to get things to sort correctly, it would look like either one of the following:
Text
ToText({command.auditYear}, 0, '') + ToText({command.auditMonth}, 0, '')
Date
Date({command.auditYear},{command.auditMonth}, 1)
-Dell
Edited by hilfy - 01 Mar 2017 at 9:19am
IP Logged
