Formula help and summing based on string true or false.

[davismar]

Using CRXI - I need some help with formulas along with the "summary" total add up values for the formulas based upon the result of either YES or NO.

MAIN REPORT
Here the basic data:

tblAccounts.AccountName
I'm summarizing this field based on "distinct count"
This provided me with 288 total and distinct accounts.

I have a subreport (data below) with linking by tblAccounts.AccountName.

SUBREPORT DATA

Account Name = Wealth Concepts

SUBREPORT

SO# Technician Time Log Reason SO Date PW Change
15269 John CSA - PM 02/18/13
15269 John Password Changed 02/18/13 YES

Account Name = Wellington Env

SO# Technician Time Log Reason SO Date PW Change
no data NO (should be displayed)

Account Name = Wheelchair Professionals

SO# Technician Time Log Reason SO Date PW Change
17188 BOB CSA - Phone Call 02/25/13 NO (should be displayed)



The main data is based upon a time log where the result = Password Changed

{tblSOLogs.TimeLogReason} = "Password Changed"

I created 2 formulas:

1. {@PasswordChangedSum}Formula:
If {@PasswordChangedYes} = TRUE then "Yes"

2. {@PasswordChangedYes} Formula:
{tblSOLogs.TimeLogReason} = "Password Changed"

What I need to finish the report is the following:

a. Out of the total # of accounts
1. HOw many had a Service Order.
2. Out of the total service orders, how many had password changed = yes?
3. Out of the total service orders, how many had password changed = no?
4. 2 different groups needed:
Password Changed = yes
Password Changed = no

The total number of SO's are to be unique. So if the account has any SO#, then the number 1 would be displayed. There are multiple time log reasons within 1 SO#.
There are some accounts that have no SO # and no time log.

Thanks for the help!
Need to sum the total number of SO's from the above count.

Need to define the total number of SO's by Password Changed = Yes and Password Changed = No and get a total of these also.

Thanks for the help!

 

[hilfy]

You're going to need to use Shared Variables to do this correctly. First, you'll have to initialize the variables to 0 in the AccountName group header. The formula will look something like this:

{@InitSharedVars}
Shared NumberVar hasSupport := 0;
Shared NumberVar hasPassword := 0;

You'll also need a formula to initialize the count variables for the report:

{@InitVars}
NumberVar supportCount := 0;
NumberVar passwordCount := 0;

Place this in the Report Header section. NOTE: If you're not using a report header, place it there anyways and suppress the section - Crystal will still evaluate the formula.

Note that you need to use ":=" instead of "=" when assigning a value to a variable and a semi-colon ( at the end of the lines. When you include a semi-colon at the end of the last line, the values will process but not actually appear when the formula is placed on the report.

In the subreport, change the {@PasswordChangedSum} formula to something like this:

Shared NumberVar hasPassword;
If {@PasswordChangedYes} then hasPassword := 1;

Place this formula in the details section of the subreport.

Add another formula to identify whether the account has any support tickets:

{@SupportSum}
Shared NumberVar hasSupport;
if not IsNull({tblSOLogs.AccountName}) then hasSupport := 1;

Place this in a group header section of the subreport if you're using groups or in the details section if you're not grouping the subreport.

In the main report, create another formula which you'll place in the AccountName group footer in a section BELOW the section where the subreport is located:

{@AddVars}
Shared NumberVar hasSupport;
Shared NumberVar hasPassword;
NumberVar supportCount;
NumberVar passwordCount;

supportCount := supportCount + hasSupport;
passwordCount := passwordCount + hasPassword;

Then, at the end of the report, place two more formulas to output your values:

{@SupportCount}
NumberVar supportCount;
supportCount

{@PasswordCount}
NumberVar passwordCount;
passwordCount

Note that I did not put a semi-colon at the end of either of these - this will cause the value to display on the report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[davismar]

Wow - thank you very much for all of the formulas and the detail!
Here is some of the output based upon the changes made:

MAIN REPORT:

Group Header = Grouped on {tblAccounts.Account Name}

The @InitVars formula is in the Report Header and the Report Header has been suppressed.

The @InitSharedVars formula is now in the Group Header to the right of the AccountName.


SUBREPORT DATA EXAMPLE #1 Veterans Home Care
The @InitSharedVars returned a 0.00.
The @SupportSum returned a 1.00.
However, there were no time logs with password changed.

SO# 17007 Andy CSA-Remote 2/13/13 @PasswordChangedSum = 0.00
SO# 17063 John CSA-Phone Call 2/21/13 @PasswordChangedSum = 0.00

SUBREPORT DATA EXAMPLE #2 Wealth Concepts
The @InitSharedVars returned a 0.00.
The @SupportSum returned a 1.00.
However, this one has a time log with password changed.

SO# 15269 John CSA - PM 2/18/13 @PasswordChangedSum = 0.00
SO# 15269 John Password Changed 2/18/13 @PasswordChangedSum = 1.00

Shouldn't the @InitSharedVars return a 1.00 since it has password changed?

Also, at the end of the report, where do I place the 2 formulas to output the values? I currently have the @SupportCount and @PasswordCount formulas in the Report Footer of the main report. However, the result is showing 0.00 for both of these formulas.

Thanks again!

 

[hilfy]

@InitSharedVars will ALWAYS have zero for the value - it is a means of resetting the numbers back to 0 at the start of every Account. If you don't do this, the shared variables will have the numbers left over from the previous account - even if there is no subreport data! This is why it should ONLY be located in a group header section that runs BEFORE the subreport.

The "Sum" formulas should be put in the Account Group Footer section - even if it is suppressed. These are where the counts actually get added up.

The two "Count" formulas should be located at the end of the report in whatever section you want to use to show the total counts for the report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[davismar]

Thank you for clarifying. But for some reason, the count formulas that I placed in the Report Footer of the Main Report as return 0.00 for both.

Any ideas?

 

[hilfy]

Try putting the count formulas in the Account group footer and see whether they're accumulating.

Also, sometimes when working with variables you have to put the line:

WhilePrintingRecords:

At the top of every formula that uses the variables. So, you could try doing that as well.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[davismar]

Great! I added WhilePrintingRecords; to the count formulas and that worked.

Almost there!

So finally - the report is now 45 pages.
Can I suppress the data that does not contain a time log "Password Changed" but still allow the total SO counts to calculate properly?


Thanks!

 

[hilfy]

If there is a password change row in the data do you need to show all of the support rows or just the password change rows?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[davismar]

I just need to show the password change row but I need to make sure by doing so, that the count formulas still calculate properly.

 

[hilfy]

In the subreport, set the suppression formula on the section where you're displaying the data. The formula will look something like this:

Not {@PasswordChangedYes}

This will suppress the non-password data from appearing, but it will still be available for the counts.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[davismar]

Dell -

That worked perfectly. You are my new best friend!

Now for the final questions- I promise:

For the Subreport

1. Report Header B - How do I suppress the field headings for all results other than "Password changed?

2. Detail Section - How do I remove the space for suppressed fields due to the suppression formula?

Thanks!
MD

 

[hilfy]

I'll answer question 2 first - suppress the section, NOT the individual fields.

For question 1, it's going to take some fudging to do this....

1. In the subreport, create a group on {tblSOLogs.TimeLogReason}. You could also turn on "Repeat Group Header on Every Page" to cover situations where the subreport might span pages.
2. Put your column headers in the group header section, not in the report header.
3. Set the suppression formula for the group header section to: {tblSOLogs.TimeLogReason}<>"Password"
4. Suppress or delete the report header b section.
5. Suppress the group footer section.

So, at this point, ALL of the sections on the subreport should either be explicitly suppressed or have a suppression formula set.

In the main report, turn on "Suppress Blank Section" for the section where the subreport is located.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[davismar]

Yes - so I performed all of the above, but there are still blank records.

Also, the field names in Group Header 1 on the subreport are not displayed when "Password Changed" is populated.

Any other ideas?

 

[hilfy]

I mis-typed the suppression formula for that section, it should be:

{tblSOLogs.TimeLogReason}<>"Password Changed".

If that doesn't get you all that you need, run a copy of the report with "Save Data with Report" turned on and email it to me. You can go to my website -

http://www.dellstinnett.com

- to get my email address which is on the bottom-right.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[davismar]

Dell,

I emailed you a copy of the CR with saved data and a .PDF with 1 page of the report to show you what is being displayed.

Thanks!

 

[davismar]

Dell,

I emailed you a copy of the CR with saved data and a .PDF with 1 page of the report to show you what is being displayed.

Thanks!

 

[hilfy]

I've got it - I should have time to look at it this evening.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[davismar]

Thank you! No hurry - just wanted to make sure you received it.

I very much appreciate the help!

MD