Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to code a percent change on a group summary?

Posted on 2008-10-07
4
Medium Priority
?
668 Views
Last Modified: 2013-11-15
I am reporting for a bank on deposit history.  I have the total deposits broken into the following groups:

Region
Deposit Group (ie ck, sav, cd, etc)
Date (I have selected records for end of month)

Total deposits are reported for each end of month period for each deposit group for each region by using a running summary on the detail level, which is the month end account balance of each account.  I would like to calculate a percent change on each period to graph.  

A previous post on reporting percent change gave the following code:

shared numberVar LineCount;
LineCount := LineCount + 1;
If LineCount = 1 then
   0
Else
   (
   if Previous({fieldname}) = 0 then
      0
   else
      ({fieldname}- Previous{fieldname})) / Previous({fieldname})
   )

This code works if it is a database record, but it doesn't like the summary operation I am using.  It reports:

"This Field has no previous or next value."

Thanks for any assistance!
0
Comment
Question by:Rilken
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 22665711
You can check if OnFirstRecord before using Previous.

Crsytal also has a PreviousIsNull test you can use.

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 22674513
If you're saying that you're trying to use Previous () on a summary or running total, instead of on a field, then, as CR said, you can't do that.  Unless I'm mistaken, Previous () only works on fields in your datasource, or formulas that reference fields.  Assuming that I'm correct about what you're trying to do, I think you're going to have to do this another way.

 You say that you have three groups and the last one is Date.  Is that your "period" group?  So, you're looking for the % change in some total for the Date (period) group, between one "period" and the next in the "Deposit Group" group?

 If so, you'll probably need to use a formula in the Date group footer that will save the total at the end of each "period" in a variable and compare the current total to a variable containing the total from the previous "period" (if any).

 You also mentioned that you wanted to graph the % change.  Do you want to do that in the report, or are you just trying to get the %'s on the report, and then you'll copy those and use them somewhere else to produce a graph?

 James
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question