Surpressing current month and future months from chart 1

[jeffm777]

I have a chart (see attached) showing monthly totals for the last 3 full years and the current year thru the last full month completed. Is there a way to surpress April - December of the current year so there isn't an abrupt drop between March and April?

 

[lbass]

Can you please explain the chart set up? Are you using formulas for the individual summaries? What is the content of one of the formulas (as an example)?

-LB

 

[jeffm777]

I'm pulling data from two classes via subreports to do the calculation for my table and chart. I'll give you the current year setup...

Top level report:
{@currentYear} - if {table.DateEntered} >= date(year(currentdate),01,01) then
1
else
0

{@CYA) - shared stringvar currentYA := currentYA & Sum ({@currentYear}, {@month}) & "^"


1st subreport:
{@sub1_currentYear) - if {@dateYear} >= date(year(currentdate),01,01) then
{table2.Raw} + {table2.Over}
else
0

{@sub2_CYA} - shared stringvar sub1_YrC := sub1_YrC & Sum ({@sub1_currentYear}, {table2.Month}) & "^"


2nd subreport (shows table):
{@CALC_currentYear} - whileprintingrecords;
shared stringvar currentYA;
local stringvar temp1 := Left(currentYA, len(currentYA)-1);
toNumber(split(temp1,"^")[groupnumber]);

{@CALC_currentYear_sub1} - whileprintingrecords;
shared stringvar sub1_YrC;
local stringvar temp1 := Left(sub1_YrC, len(sub1_YrC)-1);
toNumber(split(temp1,"^")[groupnumber]);

{@CALC_currentYear_calculation} - if {@CALC_currentYear_sub1} = 0 or {@CALC_currentYear} = 0 then 0 else
{@CALC_currentYear_sub1} / {@CALC_currentYear}

{@CALC_currentYear_export} - shared stringvar Per_Unit_CYA := Per_Unit_CYA & {@CALC_currentYear_calculation} & "^"


3rd subreport (shows chart):
{@CHART_CYA_Per_Unit} - whileprintingrecords;
shared stringvar Per_Unit_CYA;
local stringvar temp1 := Left(Per_Unit_CYA, len(Per_Unit_CYA)-1);
tonumber(split(temp1,"^")[groupnumber]);

To surpress the incomplete month from the table and chart subreports, I'm using the following in my record selection - local datevar endDate;
IF month(currentdate) in [1,2,3] then
endDate := date(year(currentdate)-1,12,31)
else
if month(currentdate) in [4,5,6] then
endDate := date(year(currentdate),3,31)
else
if month(currentdate) in [7,8,9] then
endDate := date(year(currentdate),6,30)
else
if month(currentdate) in [10,11,12] then
endDate := date(year(currentdate),9,30);

{table2.DateEntered} >= Date (Year(currentdate)-3, 01, 01) and {table2.DateEntered} <= endDate

 

[lbass]

I think all you need to do is the following, although I'm not sure how this will play out with all of your variables:

Change the current year formula to:

if {table.DateEntered} in date(year(currentdate),01,01) to
maximum(lastfullmonth) then
1 else
tonumber({@null})

...where {@null} is a formula that you create by opening a new formula and saving it without entering anything.

-LB

 

[jeffm777]

I modified the current year formula on the top level report and I'm now getting "The string is non-numeric.". I tried removing the tonumber () from the formula, and I get "A number is required here.

 

[lbass]

What formula is generating the non-numeric error message?

-LB

 

[jeffm777]

{@currentYear} on the top level report

 

[lbass]

I can't see how you would get that error. When you created {@null} you didn't enter anything, right? Not even a space.

Please show the content of the formula as it now stands.

-LB

 

[jeffm777]

{@null} contains no data...

{@currentyear} - if {InventoryAUT.DateEntered} in date(year(currentdate),01,01) to
maximum(lastfullmonth) then
1 else
tonumber({@null})

 

[lbass]

You don't literally have "{@currentyear}" in the formula, do you? You should comment out the formula name and just show the actual content like this:

//{@currentyear}

if {InventoryAUT.DateEntered} in date(year(currentdate),01,01) to
maximum(lastfullmonth) then
1 else
tonumber({@null})

I'm guessing you have set your values to be converted to null. Go into file->report options and uncheck "convert nulls to default values."

-LB

 

[jeffm777]

You are correct...I don't have "{@currentyear}" included in my formula. I do have the "convert nulls to default values" on.

 

[jeffm777]

Ok I got rid of the error - had to change the {@null} formula to basic syntax, save it, edit it, remove the formula= from the formula, change it back to Crystal Syntax, and save it. Now the error is gone but I'm still seeing 0's on the table and chart instead of nulls.

 

[jeffm777]

I'm not going to worry about this as it's a minor inconvienence. Tks lbass for your help.

 

[lbass]

Did you removee the check from "convert nulls to defaults"? This is necessary for it to work.

-LB

 

[jeffm777]

I did make sure the "convert nulls to defaults" is unchecked. I also tried the formula on a different, but similar report and it gave the same "string is non-numeric" error.

 

[lbass]

I would try unchecking the conversion and replacing the null formula with a new one --opening and saving without entering anything--Crystal syntax.

I have used this method in the past.

-LB

 

[jeffm777]

I went back in and double checked the {@currentyear} and {@null} formulas and both were set to "default values for nulls" so I changed them to "exception for nulls" and the error is gone and my 0 values are surpressed...but only on the top level report but I think I know why.

The {@currentyear} formula is being summarized in the footer of the top level report and then the shared variable is based on the summary field, not the formula itself so therefore, the variable is being shared with 0 values, not null values.

The formula I'm using to create the shared variable is named {@currentYA}, is in the group footer, and contains the following:
shared stringvar YrC := YrC & Sum ({@currentyear}, {@month}) & "^

 

[jeffm777]

I tried changing the {@currentYA} formula to exception for nulls and I get "A subscript must be between 1 and the size of the array" on the calculation sub report formula {@CALC_currentYear}...

whileprintingrecords;
shared stringvar CYA;
local stringvar temp1 := Left(CYA, len(CYA)-1);
toNumber(split(temp1,"^")[groupnumber]);

I'm showing GroupNumber is 4 but only 3 values.

 

[jeffm777]

Correction. My {@CALC_currentYear} formula is...

whileprintingrecords;
shared stringvar YrC;
local stringvar temp1 := Left(YrC, len(YrC)-1);
toNumber(split(temp1,"^")[groupnumber]);

 

[lbass]

It sounds like you are somehow setting the exceptions for nulls at the formula level. You should be doing this at the report level in file->report options.

-LB