Monthly Meter Usage
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22252
Printed Date: 01 May 2024 at 3:59pm
Topic: Monthly Meter Usage
Posted By: jmallet74
Subject: Monthly Meter Usage
Date Posted: 27 Feb 2017 at 5:12am
I have a data collection agent that runs a daily sync with 20,000 devices that have meters on them.
One audit pulls the device id and collects any available meter.
Many of my customers ask me for a report that shows their last 24 months of "monthly usage" in a bar chart. They want to visualize their usage and see if it is trending up or down overall, over time.
So one audit gives me a date/time, an equipment id, and any meters, typically black/white and/or color.
If I use a formula like "Maximum({b/w meter},{auditdate},"monthly")-Minimum({b/w meter},{auditdate},"monthly")" I get a pretty accurate trend graph...but it's not perfect
This formula excludes any volume between the last audit of any month and the first audit of the next month.
So if 1/31/2017 shows a meter of 401, and 2/1/2017 shows 408, and 2/27/2017 shows 749, we know we have a total meter usage from inception of 749, but it shows the monthly volume for January is 401, and february is 341...341+401=742. So we lose 7 clicks of the meter. It gets out of hand if people are doing thousands of pages per day..over 24 months.
The reason I am pulling from 2/27/2017 instead of 28th is because sometimes audits fail on the most important day of the month and we have to rely on previous information. So please consider that we're looking for the max date or max meter value of any month, not specific dates.
So my current workaround to this is working well enough, but I feel like it should be a lot easier to get this data into a visual chart.
My formula tests if the month of auditdate of a record equals the month of previous records date. If it does, it returns a value of 0, if not it returns the previous meter value. This test basically takes the maximum of the previous month and puts in in the month being calculated. Then using a formula of "Maximum({b/w Meter},{auditDate},"monthly")-{@ Previous Maximum} I get the monthly usage value.
This basically does all of the math in the first record of the month, so I'm able to suppress details and put these formulas in the header of the group, grouped by audit date monthly.
Since I'm using some conditional formulas, I cannot use the values in a chart because they must be evaluated later. I have to export the data to excel, build a pivot chart/table from template.
Would anyone be willing to take a crack at this to help me get a value of "Monthly usage" that doesn't skip the data between last/first of months and can be charted?
Here is a link to a sample of meter pulls.
https://docs.google.com/spreadsheets/d/1BY0stpbuiMHQrIo3tq0aTphUt3mVrbBtSaQm42fexdU/edit#gid=0
-------------
JM@Proven
|
Replies:
Posted By: hilfy
Date Posted: 01 Mar 2017 at 8:10am
I've downloaded your data and I'll take a look at it. How are you pulling the data into your report? Is it in an Excel file or tables in the database? If it's in tables, are you just joining tables together in your report? Or do you have a stored proc, view, or command for getting the data?
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: hilfy
Date Posted: 01 Mar 2017 at 9:10am
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/ - 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|