Sum of Variable - rpt doesn't include a subrpt 1

[jenschmidt]

Hi All!

I have a variable based on a parameter. End-user enters the time period they want to see charges for, in "YYYYMM" format. Based on what they enter, I want to show, in twelve separate fields, charges for the last twelve months. I've implemented variables because if I subtract 1 from "200401", I'll get "200300", which is obviously not a valid period.

//@Period-1
WhilePrintingRecords;
Shared StringVar period1 := ToText(ToNumber({?Posting Period})-1,0,""

Shared StringVar mo1;
if totext(right(period1, 2)) = "00" then mo1 :="12";
if totext(right(period1, 2)) = "99" then mo1 :="11";
if totext(right(period1, 2)) = "98" then mo1 :="10";
if totext(right(period1, 2)) = "97" then mo1 :="09";
if totext(right(period1, 2)) = "96" then mo1 :="08";
if totext(right(period1, 2)) = "95" then mo1 :="07";
if totext(right(period1, 2)) = "94" then mo1 :="06";
if totext(right(period1, 2)) = "93" then mo1 :="05";
if totext(right(period1, 2)) = "92" then mo1 :="04";
if totext(right(period1, 2)) = "91" then mo1 :="03";
if totext(right(period1, 2)) = "90" then mo1 :="02";
if totext(right(period1, 2)) = "89" then mo1 :="01";
if totext(right(period1, 2)) in "01" to "12" then mo1 := totext(right(period1, 2));

Shared StringVar yr1;
if totext(right(period1, 2)) in "01" to "12" then yr1 :=ToText(Left({?Posting Period},4));
if not(totext(right(period1, 2)) in "01" to "12&quot then yr1 :=ToText(ToNumber(Left({?Posting Period},4))-1,0,""

yr1+""+mo1;

//@Period-2
WhilePrintingRecords;
Shared StringVar period2 := ToText(ToNumber({?Posting Period})-2,0,""

Shared StringVar mo2;
if totext(right(period2, 2)) = "00" then mo2 :="12";
if totext(right(period2, 2)) = "99" then mo2 :="11";
if totext(right(period2, 2)) = "98" then mo2 :="10";
if totext(right(period2, 2)) = "97" then mo2 :="09";
if totext(right(period2, 2)) = "96" then mo2 :="08";
if totext(right(period2, 2)) = "95" then mo2 :="07";
if totext(right(period2, 2)) = "94" then mo2 :="06";
if totext(right(period2, 2)) = "93" then mo2 :="05";
if totext(right(period2, 2)) = "92" then mo2 :="04";
if totext(right(period2, 2)) = "91" then mo2 :="03";
if totext(right(period2, 2)) = "90" then mo2 :="02";
if totext(right(period2, 2)) = "89" then mo2 :="01";
if totext(right(period2, 2)) in "01" to "12" then mo2 := totext(right(period2, 2));

Shared StringVar yr2;
if totext(right(period2, 2)) in "01" to "12" then yr2 :=ToText(Left({?Posting Period},4));
if not(totext(right(period2, 2)) in "01" to "12&quot then yr2 :=ToText(ToNumber(Left({?Posting Period},4))-1,0,""

yr2+""+mo2;


... and so on, though Period-12 ...

//@Charges - Period-1
if ToNumber({testing.Period})=ToNumber({@Period -1}) then {testing.Charges} else 0

//@Charges - Period-2
if {testing.Period}={@Period -2} then {testing.Charges} else 0

It is the @Charges formulas that I would like to summarize as a grand total for now (most likely I'll get asked for group level summaries later). I inserted the ToNumber function in @Charges - Period-1 because I initially thought it wasn't summarizing because it was returning a string, but I was wrong - both examples return a number.

Any ideas on how to create the summaries or how to rethink how I'm approaching the report altogether are welcome!!

Thanks!


jennifer.giemza@uwmf.wisc.edu

 

[jenschmidt]

Secondary Note: I would also like to use the @Period-# variables in the selection criteria:
{TABLE.PERIOD} in {?Posting Period} to (@Period -12}

Thanks!

jennifer.giemza@uwmf.wisc.edu

 

[lbass]

I would approach this by converting strings to dates and back again to define the range for the record selection, as in:

{testing.period} = totext(dateadd("m",-12, date(val(left(
{?PostingPeriod},4)), val(mid({?PostingPeriod},5, 2)), 01)),"yyyyMM&quot to
totext(date(val(left({?PostingPeriod},4)), val(mid({?PostingPeriod},5,2)), 01),"yyyyMM&quot

//where {testing.period} is a string of form "yyyyMM" and
{?PostingPeriod} is a discrete string parameter.

Then create a formula to convert {testing.period} to a date, as in {@testperiod}:

date(val(left({testing.period},4)), val(mid({testing.period},5,2)), 01)

Then you can insert a crosstab into the report header or footer, and use {@testperiod} as your column. Right click on it, choose "Group options", and choose print on change of month. Use whatever group fields you want for the row values, and use {testing.charges} as your summary field.

-LB