Unable to use a variable in a Summary

[former_member997610]

Greetings,

My report includes a formula that generates either a 0 or1,  which I hoped would  help me summarize my count of distinct patient days.

Here is the formula:

if (previous({Command.PAT_ID}) <> {Command.PAT_ID}) OR ( previous({Command.PAT_ID}) = {Command.PAT_ID} AND previous ({@Date}) <> {@Date}) then 1 else 0

The variable works fine in the detail section in that it correctly displays either the 0 or 1 as expected.

The problem is that when I select the Insert Summary tool,  this variable is not available to use in a summary. I 'googled' the issue and found similar situations but don't fully grasp what I must do to resolve this (apparently Crystal sees this as a constant?). Any suggestions would be much appreciated.

[abhilash_kumar · Accepted solution]

Hi Dave,

You cannot summarize formulas that include the previous() function.

1) Create a formula with this code;

{Command.PAT_ID} & {@Date}

2) Go to the Insert Summary Option > Choose this formula field as the 'Field to Summarize' > Choose 'Distinct Count' as the Summary Function > Under Summary Location, choose 'Report Footer' if you wish to create a Grand Total or 'Group#' if you wish to place this at the Group Footer.

-Abhilash

[former_member997610 · Accepted solution]

Many thanks for your help. I found both responses to be very good and although I am using Abihilash' code in this situation, I am sure Prathamish' solution will also come in very handy and I am glad to know both techniques. Outstanding assistance! .

Now for the icing on the cake....  I placed the summary formula (DistinctCount of @DeptDate) in the Group1 header (lists Department Name). It works great thanks to your assistance. I would be interested in knowing if it is possible now to combine Group#1 Name (the Department) with the Summary total (DistinctCount of DeptDate) so that the group heading display of this information would appear in this format for each department grouping: Department name (5) .   …where 5 in this example is simply the total for that particular department. Anyway… thought it would be good to consistently display this in each section in one field using this format (if possible)

Thanks again!

[abhilash_kumar · Accepted solution]

Yes you can!

Create a formula with this code and place this on the GH:

{Group_field} & ' (' & ToText(DistinctCount({@Formula_you_created_above}, {Group_Field}), '#') & ')'

P.S: You can also Insert a text object > drag and drop the group field inside the text object > type in the round braces > drag and drop the summary field within the braces.

-Abhilash

[former_member997610 · Accepted solution]

Excellent.... Worked Perfectly.

Many Thanks!

[former_member477249]

Hi,

Summarizing happens in a previous pass than the execution of previous() function, hence this issue.

1.Modify the existing formula to include a numeric variable:

numbervar PtCnt;

if (previous({Command.PAT_ID}) <> {Command.PAT_ID}) OR ( previous({Command.PAT_ID}) = {Command.PAT_ID} AND previous ({@Date}) <> {@Date})

then PtCnt:= 1 else PtCnt:=0;

2.Create a formula to collect the summary with the following code and place it in Details section:

Whileprintingrecords;

numbervar PtCnt;

numbervar PatientCount:= PtCnt + 1;

Suppress this formula.

3. Create another formula to show the final summary.

Whileprintingrecords;

numbervar PatientCount;

Thanks

[Former Member]

This message was moderated.

[Former Member]

Many thanks for your help!!