Charting Print-Time Reports - monthly values not accumulating

[DESTINEEJSH]

Hello everyone in the Tek-Tips Forums.

I need help with the following scenario:

We need to calculate the amount of contacts that closed for all patients each month based on a formula. So January isn't necessarily Jan 1 - Jan 30. Also when we run February, we want January to hold the value it held when it was run in January. The same would be true for March, we would like Jan and Feb to hold the same values they had when they were run in those months. I've set up the following formulas, but when I check the count values, they are always zero.

In the ReportHeader, I set all of the count formulas = 0.
According to everything I’ve read about working with print-time reports, you are supposed to split Detail into Detaila and DetailB then put the Count Formulas in DetailB. The values are also zero. They don’t appear to be incrementing.
Any ideas on what I may be doing wrong?

Formulas and view of how the chart is supposed to look is below.

Examples Of Month Formulas:
Jan = “Jan”
Feb = “Feb”
Mar = “Mar”

Examples of Counts Formulas in DetailB:

M-JanCnt
Global NumberVar JanCnt;
if (isnull ({PAT_ENC.ENC_CLOSE_DATE}) and {PAT_ENC.CONTACT_DATE} <= date (2011,01,31) and currentdate > date (2011,01,31)) or
({PAT_ENC.CONTACT_DATE} <= date (2011,01,31) and {PAT_ENC.ENC_CLOSE_DATE} > date (2011,02,11))
then
JanCnt := JanCnt + 1

M-FebCnt
Global NumberVar FebCnt;
if (isnull ({PAT_ENC.ENC_CLOSE_DATE}) and {PAT_ENC.CONTACT_DATE} <= date (2011,02,28) and currentdate > date (2011,02,28))
or
({PAT_ENC.CONTACT_DATE} <= date (2011,02,28) and {PAT_ENC.ENC_CLOSE_DATE} > date (2011,03,11))
then
FebCnt := FebCnt + 1


The horizontal axis would be the months and the vertical axis would be the count.

Any guidance would be greatly helpful.

Thanks,

Debbie

 

[lbass]

Are you really wanting to graph this? Or are you attempting a crosstab?

It looks like your "month" formulas are simply labels that you could instead enter in text boxes.

Your formulas aren't incrementing because:
1) Your formulas must begin with "whileprintingrecords;"
2) The first half of your formulas will not be true until 2011, so only the second half can execute--if it is possible to have a close date in advance. You also did not check for nulls for the close date for the second half--not sure why.

-LB

 

[DESTINEEJSH]

I've included WhilePrintingRecords in all of my Count Formulas.

As our Fiscal year is July to June, I'm expecting January through June to be blank.

I do expect counts in my formulas for July through November. I'm still getting blank values. As I said before, I'm totaling my counts in the Detail section of the main report. I'm displaying that section so I can see if the numbers change and they don't.

I've looked at every version of the print-time formulas and charting and cannot figure out what I'm doing differently.

Thanks for any assistance you can give me.

Deb

 

[lbass]

You didn't answer my question about charting. So are you incrementing in a main report to display in a subreport?

You also have not clarified whether there really can be values for a close date in the future. You should be able to see results in the main report (unsuppress the formulas if necessary).

-LB

 

[DESTINEEJSH]

The goal of the whole report is to have a graph that would show each month on the horizontal axis, and the total count for each month on the vertical axis. I am accumulating in the main report and then using the subreport for display.

There shouldn't be any values in future close dates.

 

[lbass]

Please show your current formula for November 2010.

-LB

 

[DESTINEEJSH]

I'm back. Please don't give up on me.

I'm helping someone with this report so that they can do the graphing they want to do.


They were using the following formula to retrieve both the month and the amount.

NovMnt
she was told to always check if enc_close_date is null.in the first part. The formula is as follows for retrieving the month.

if (isnull ({PAT_ENC.ENC_CLOSE_DATE}) and {PAT_ENC.CONTACT_DATE} <= date (2010,11,30) and currentdate > date (2010,11,30))
or
({PAT_ENC.CONTACT_DATE} <= date (2010,11,30) and {PAT_ENC.ENC_CLOSE_DATE} > date (2010,12,11)) then "November"

NovCnt
she uses the same formula for november count except for the part after the "then". It is as follows:

if (isnull ({PAT_ENC.ENC_CLOSE_DATE}) and {PAT_ENC.CONTACT_DATE} <= date (2010,11,30) and currentdate > date (2010,11,30))
or

({PAT_ENC.CONTACT_DATE} <= date (2010,11,30) and {PAT_ENC.ENC_CLOSE_DATE} > date (2010,12,11)) then 1 else 0

She then sums each of the counts in the reportFooter.
=============================

I used the approach of WhilePrintingRecords in my counts as I was taught that it was better than summing fields like Nik did above.

NovMnt = "Nov"
because you will always have a month for every month of the year. The values only come into play in the count portion.

WhilePrintingRecords;
Global NumberVar NovCnt;
// November
if (isnull ({PAT_ENC.ENC_CLOSE_DATE}) and {PAT_ENC.CONTACT_DATE} <= date (2010,11,30) and currentdate > date (2010,11,30))
or
({PAT_ENC.CONTACT_DATE} <= date (2010,11,30) and {PAT_ENC.ENC_CLOSE_DATE} > date (2010,12,11))
then
NovCnt := NovCnt + 1

==========================
Nik is getting values in her formulas, but I am not.

Either way, we don't know how to use the OnChangeOf for the month as each are broken out into their own formula. That was why I was initially trying to do an array because I thought I could cycle through it to populate the months on the horzontal bar of the graph.

My array was simply using global variables and setting them as follows:

//Here we're creating an array to hold each month of the year.
Shared StringVar Array MonthArray := MakeArray("");

//To work with our arrays we need to have them dimensioned.
//We simply re-dimension them to have 1 array element.
ReDim MonthArray[12];

//This first part is to fill the month array with valid months
strMonthArray := ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];

//The result of a formula cannot be an array, so we
//give it something to display.
"Set Up Array"
====================================

So can you tell me how we get the month portion on the chart?

It would also be nice to know if I'm out of whack on the formula I want to use or if it is better to do it the way Nik is.

We both hope to hear from you with regards to what we could do.

Thanks in advance or any advice you can give.

Debbie

 

[lbass]

Try the following instead. Create a formula {@date}:

if (
isnull({PAT_ENC.ENC_CLOSE_DATE}) and
currentdate > dateserial(year({PAT_ENC.CONTACT_DATE}),month({PAT_ENC.CONTACT_DATE})+1,1)-1
) or
{PAT_ENC.ENC_CLOSE_DATE} > dateserial(year({PAT_ENC.CONTACT_DATE}), month({PAT_ENC.CONTACT_DATE})+1,11) then
{PAT_ENC.CONTACT_DATE}

Add this as your "on change of field"->group options->print on change of month.

Create a second formula {@cnt} to use as your summary field:

if (
isnull({PAT_ENC.ENC_CLOSE_DATE}) and
currentdate > dateserial(year({PAT_ENC.CONTACT_DATE}),month({PAT_ENC.CONTACT_DATE})+1,1)-1
) or
{PAT_ENC.ENC_CLOSE_DATE} > dateserial(year({PAT_ENC.CONTACT_DATE}), month({PAT_ENC.CONTACT_DATE})+1,11) then
1

Use a sum on the second formula in the chart expert.

Actually, you should only use variables when absolutely necessary, as inserted summaries are more efficient. No need for a subreport here, either.

Note, however, that you can't expect Crystal to store the values from previous runs of the report--you would have to manage this at the database level by creating a history table or something like that or by using data entry dates versus service dates in your formulas.

-LB