Grouping question

[boboleft]

I am building a report and having difficulties get the data format I want. Below is what I currently have. The report is currently grouped by MasterAcct# and sorted by masteracct# and postDate. I need to know what is the credit limit at beginning of the year and what is the current credit limit and what is the variance between old credit limit and new credit limit at the masteracct# level. I am not sure what is the best way to do this because each acct# is unique but they all shared the same masteracct# and credit limit. Does anybody know what is the best way to do it?? Any help will be greatly appreciated.

thanks

Detail section

MasterAcct# Acct# PostDate OldCredit_limit NewCred_Lim
6000 111 1/5/09 1000 1500
6000 121 3/5/09 1500 1200
6000 115 5/5/09 1200 2000
6111 221 2/5/09 500 1000
6111 321 8/5/09 1000 800

Desired output

MasterAcct# OldCredit_limit NewCredit_limit Variance
6000 1000 2000 1000
6111 500 800 (300)

 

[Turkbear]

Hi,
Do all the accounts increase in credit limit?
If so then you could try using a Command object with a SQL query to get the MIN and MAX, by MasterAcct# - it could then be linked to the table data table by the MasterAcct#


Otherwise you may have to revise the query to check for the MIN PostDate's limit and also the MAX PostDate's limit and use those.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could use formulas like this:

whileprintingrecords;
numbervar oldcr;
numbervar newcr;
if {table.postdate} = minimum({table.postdate},{table.masteracct}) then
oldcr := {table.oldcreditlimit} else
oldcr := oldcr;
if {table.postdate} = maximum({table.postdate},{table.masteracct}) then
newcr:= {table.oldcreditlimit} else
newcr:= newcr;

Place this in the detail section and suppress it. Then move the groupname into the group footer and add these three formulas next to it:

whileprintingrecords;
numbervar oldcr;

whileprintingrecords;
numbervar newcr;

whileprintingrecords;
numbervar oldcr;
numbervar newcr;
newcr - oldcr;

-LB

 

[boboleft]

The #oldcredit_limit appeared as 1500. I need the oldcredit_limit to be 1000 which was the beginning Credit limit on 1/5/09. Besides this the formula is wonderful and it worked great.

Current output with the formula

MasterAcct# OldCredit_limit NewCredit_limit Variance
6000 1500 2000 500

Desired output
MasterAcct# OldCredit_limit NewCredit_limit Variance
6000 1000 2000 1000

 

[lbass]

Oops. The formula should have been:

whileprintingrecords;
numbervar oldcr;
numbervar newcr;
if {table.postdate} = minimum({table.postdate},{table.masteracct}) then
oldcr := {table.oldcreditlimit} else
oldcr := oldcr;
if {table.postdate} = maximum({table.postdate},{table.masteracct}) then
newcr:= {table.[red]new[/red]creditlimit} else
newcr:= newcr;

It looks like you used the newcreditlimit field for both variables.

-LB

 

[boboleft]

Thanks so much. The formula worked great.

 

[boboleft]

Hi,

The table structure has changed to the following, all the credit limits are stored in the same field now, how should I modify the formula in order to get the the variance between credit limit (1/5/09) and credit limit (5/5/09) at the acct level.

Detail section:

Acct# PostDate credit limit
111 1/5/09 1000
111 3/5/09 1500
111 5/5/09 1200
112 1/5/09

Desired output

Desired output

acct# Credit_limit(1/5/09) Credit_limit(5/5/09) Variance
111 1000 1200 200



whileprintingrecords;
numbervar oldcr;
numbervar newcr;
if {table.postdate} = minimum({table.postdate},{table.masteracct}) then
oldcr := {table.oldcreditlimit} else
oldcr := oldcr;
if {table.postdate} = maximum({table.postdate},{table.masteracct}) then
newcr:= {table.newcreditlimit} else
newcr:= newcr;

 

[lbass]

You can just substitute the new field {table.creditlimit} for both {table.oldcreditlimit} and {table.newcreditlimit} in the formula.

-LB

 

[boboleft]

thanks. for some reasons, when I placed formula at the detail section, it is not displaying the correct data. It is taking the min of credit limit from the previous acct# and and max of the credit limit from the current acct#.

 

[lbass]

Sorry, you need a reset formula in the group header:

whileprintingrecords;
numbervar oldcr;
numbervar newcr;
if not inrepeatedgroupheader then (
oldcr := 0;
newcr := 0
);

-LB

 

[boboleft]

Hello,

Thanks for your help..I need additional help on the following, I need to show the credit limit increased/decreased at the master acct# level. Not really sure how to modify the formula.

MasterAcct# Acct# PostDate OldCredit_limit NewCred_Lim
6000 111 1/5/09 1000 1500
6000 121 3/5/09 1500 1200
6000 115 5/5/09 1200 2000

6111 221 2/5/09 500 1000
6111 321 8/5/09 1000 800

Desired output

MasterAcct# OldCredit_limit NewCredit_limit Variance
6000 3700 4700 1000
6111 1800 1500 (300)


whileprintingrecords;
numbervar oldcr;
numbervar newcr;
if {table.postdate} = minimum({table.postdate},{table.masteracct}) then
oldcr := {table.oldcreditlimit} else
oldcr := oldcr;
if {table.postdate} = maximum({table.postdate},{table.masteracct}) then
newcr:= {table.oldcreditlimit} else
newcr:= newcr;

 

[lbass]

I think we/I have overcomplicated this. It appears that you could just use the following for the difference:

sum({table.newcreditlimit},{table.masteracct})-
sum({table.oldcreditlimit},{table.masteracct})

Place this in a masteracct section.

-LB

 

[boboleft]

Hi,

Thanks so much for your help, I think I have placed the wrong table..Today is just not my day at all.

The issue I am having is multiple accts are linked to the same master acct# and the credit limit are only changed at the acct# level. Some of the Credit limit would remain the same month over month for each acct#, can’t really total all the credit limit at the master acct# level, it is adding up all the credit limit. Not really sure how to do this. Can you please help me one more time??


MasterAcct# Acct# PostDate Cred_Lim
6000 111 1/5/09 3000
6000 111 2/5/09 3000
6000 111 3/5/09 1850
6000 121 1/5/09 5500
6000 121 2/5/09 5500
6000 121 3/5/09 5500
6000 121 4/5/09 4650

6111 222 1/5/09 1500


Desired output
MasterAcct# OldCredit_limit NewCredit_limit Variance
6000 8500 6500 (2000)
6111 1500 1500 0

thanks

 

[lbass]

Can't tell how you are getting the desired results based on your sample data.

-LB

 

[boboleft]

This is how I calculated it.

Master acct# 6000, had 2 accts# (111, 121) linked to it with beginning credit limit of 3000,5500 on 1/5/09. I added these two to come up with 8500, then I added 1850 (3/5/09 for acct# 111) + 4650 (4/5/09 credit limit for acct# 121) to get the total of new ending Credit limit of 6500. Everything needs to be displayed at the master acct# level but changes occurred sometimes monthly at the acct# level. I am not sure if it is possible to get to the desired output I want. Hope this make sense. Can you please help??

 

[lbass]

Change this detail level formula to:

whileprintingrecords;
numbervar oldcr;
numbervar newcr;
if {table.postdate} = minimum({table.postdate},{table.acct#}) then
oldcr := {table.oldcreditlimit} else
oldcr := oldcr;
if {table.postdate} = maximum({table.postdate},{table.acct#}) then
newcr:= {table.newcreditlimit} else //make sure you have this
newcr:= newcr;

In the group footer for {table.acct#}, use a formula like this:

//{@accumacct}:
whileprintingrecords;
numbervar oldcr;
numbervar newcr;
numbervar sumoldcr := sumoldcr + oldcr;
numbervar sumnewcr := sumnewcr + newcr;

//{@displ} to be placed in the masteracct group footer:
whileprintingrecords;
numbervar sumoldcr;

Add a similar formula for sumnewcr, and then create a third formula:

whileprintingrecords;
numbervar sumoldcr;
numbervar sumnewcr;
sumnewcr-sumoldcr

Add a reset formula to the master acct group header:

whileprintingrecords;
numbervar sumoldcr;
numbervar sumnewcr;
if not inrepeatedgroupheader then (
sumoldcr := 0;
sumnewcr := 0
);

-LB

 

[boboleft]

Hi,

Thanks for the quick response. should I group this report by acct# or master acct#?? Also, the table I have only have one credit limit field. Do I replace both "{table.oldcreditlimit}" and "{table.newcreditlimit}" with "cred_lim" in the formula??

whileprintingrecords;
numbervar oldcr;
numbervar newcr;
if {table.postdate} = minimum({table.postdate},{table.acct#}) then
oldcr := {table.oldcreditlimit} else
oldcr := oldcr;
if {table.postdate} = maximum({table.postdate},{table.acct#}) then
newcr:= {table.newcreditlimit} else //make sure you have this
newcr:= newcr;

MasterAcct# Acct# PostDate Cred_Lim
6000 111 1/5/09 3000
6000 111 2/5/09 3000
6000 111 3/5/09 1850
6000 121 1/5/09 5500
6000 121 2/5/09 5500
6000 121 3/5/09 5500
6000 121 4/5/09 4650

 

[lbass]

I'm assuming you have an outer group on masteracct and an inner group on acct#. Yes, change the formula as you mentioned.

-LB

 

[boboleft]

Wonderful, it worked, thanks so much for your help.