Moving Log Files: format

Friday, March 30, 2012

MPG calculation in CR 9, simple? not for me!

I have what I thought was going to be a fairly simple task of writing a report to calculate Miles Per Gallon in a vehicle fleet. The data format goes like this:

Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220

I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.I have what I thought was going to be a fairly simple task of writing a report to calculate Miles Per Gallon in a vehicle fleet. The data format goes like this:

Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220

I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.

Format the Gals field. At the suppress formula: isnull (previous({galsfield}))

new formula, @.MPG: ({milesfield}-previous({milesfield}))/{galsfield)

new summary, grand total average @.MPG|||Jeffro308,

Thanks for responding to my post!

I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||I'm using 8.5 so there might be a difference. I need to try a couple things at work monday.

Jeffro308,

Thanks for responding to my post!

I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||bump|||Anybody else want to take a stab at this?|||I'll assume you're grouping by vehicle and ordering by odo, and that your 3 rows of sample data are for one vehicle.
I guess you want, per vehicle, the number of gals to be the sum of all records except the first, and the number of miles to be the last record minus the first.

So you could put a formula in the group header to subtract the gals and store the first odo, a formula in the details to add the gals, and a formula in the footer to subtract the stored first odo from the current odo.

e.g.
group header:
whileprintingrecords;
numbervar total_gals:= total_gals - {table.gals};
numbervar vehicle_gals := -{table.gals}; --or maybe {table.gals} * -1
numbervar first_vehicle_odo := {table.odo};

details:
whileprintingrecords;
numbervar total_gals := total_gals + {table.gals};
numbervar vehicle_gals := vehicle_gals + {table.gals};

group footer:
whileprintingrecords;
numbervar first_vehicle_odo;
numbervar vehicle_miles := {table.odo} - first_vehicle_odo;
numbervar total_miles := total_miles + vehicle_miles;
--and display miles/gallon for the vehicle
vehicle_miles / vehicle_gals --You should add a divide by zero check here for when there's only one record

report footer:
whileprintingrecords;
numbervar total_gals;
numbervar total_miles;
--and display miles/gallon for the report
total_miles / total_gals --divide by zero check again

By the way, I've not tried this explicitely but it might give you a start / ideas.sql

Friday, March 9, 2012

Moving reports from one server to another without VS2005?

This is a multi-part message in MIME format.
--=_NextPart_000_0001_01C61605.B665F2E0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
I have some reports that were developed using a beta version of
Reporting Services 2005 on a virtual server. We need the reports off of
the original virtual server, but do not have the project anymore that
the reports were created in. I need to basically move the reports from
the old server to this new one and have no way of getting the reports
out of the server apparently. Is there some method I can use to get the
RDL of those reports along with the shared data sources?
Thanks,
Rob
--=_NextPart_000_0001_01C61605.B665F2E0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" = xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>

</style>
</head>
<body lang=3DEN-US link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
I have some reports that were developed using a beta = version
of Reporting Services 2005 on a virtual server. We need the = reports off of the
original virtual server, but do not have the project anymore that the = reports
were created in. I need to basically move the reports from the old = server to
this new one and have no way of getting the reports out of the server
apparently. Is there some method I can use to get the RDL of those = reports
along with the shared data sources?<o:p></o:p>
<o:p> </o:p>
Thanks,<o:p></o:p>
<o:p> </o:p>
Rob<o:p></o:p>
</div>
</body>
</html>
--=_NextPart_000_0001_01C61605.B665F2E0--You can do this manually by using Report Manager and clicking on the
Properties tab. There is an edit link which will download the rdl. There
is no way from Report Manager to get the shared datasource definitions.
You could also do this programmatically, using either a compile app going
against the SOAP endpoint, or writing a script and using rs.exe.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Edwards" <REdwards@.quilogy.com> wrote in message
news:000501c6162f$9f3bfae0$64ca140a@.Qlou.com...
>I have some reports that were developed using a beta version of
> Reporting Services 2005 on a virtual server. We need the reports off of
> the original virtual server, but do not have the project anymore that
> the reports were created in. I need to basically move the reports from
> the old server to this new one and have no way of getting the reports
> out of the server apparently. Is there some method I can use to get the
> RDL of those reports along with the shared data sources?
>
> Thanks,
>
> Rob
>|||Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
Note that depending on what build the beta version is, you may get some
inconsistent results if the RDL schema or Web Service interfaces have
changes between it and RTM.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Rob Edwards" <REdwards@.quilogy.com> wrote in message
news:000501c6162f$9f3bfae0$64ca140a@.Qlou.com...
>I have some reports that were developed using a beta version of
> Reporting Services 2005 on a virtual server. We need the reports off of
> the original virtual server, but do not have the project anymore that
> the reports were created in. I need to basically move the reports from
> the old server to this new one and have no way of getting the reports
> out of the server apparently. Is there some method I can use to get the
> RDL of those reports along with the shared data sources?
>
> Thanks,
>
> Rob
>

Friday, March 30, 2012

MPG calculation in CR 9, simple? not for me!

Friday, March 9, 2012

Moving reports from one server to another without VS2005?

Moving Log Files

Blog Archive

About Me