Link to home
Start Free TrialLog in
Avatar of srilee
srilee

asked on

Crystal Report Grouping

I have a report that will group my Options in specific "buckets" so I can track revenue. The report was working quite well up until today. Let me mention that the report uses multiple data sources but again has been working well. All that being said let me explain further....

I have an a particular option that was deleted/ no longer sold (Option ID= '209133820') . This option exists in the view referenced in Section 1 so this section would be ignored I assume and we'd continue on to section 2. Section 2 looks to my live option database and since option is no longer available (no option "Description") I'd also assume this section would be ignored and we'd proceed to section 3.  This section looks to the option "SystemName" and groups its accordingly but for some reason its not and the option is orphaned out on its own.


//Section 1
if isnull({vw_SHEA_Option_Revenue.OptionCode})  and {tbOptionSelectionRole.SystemName}="DesignCenter" then "Design Center" else
if isnull({vw_SHEA_Option_Revenue.OptionCode}) and {tbOptionSelectionRole.SystemName}="SalesAgent" then "Sales Center" else
if isnull({vw_SHEA_Option_Revenue.OptionCode}) and {tbOptionSelectionRole.SystemName}="Both" then "Both" else

//Section 2
if ({vw_AMA_Option_Type.DESCRIPTION})="Finance (Design)" then "Design Center" else
if ({vw_AMA_Option_Type.DESCRIPTION})="Finance (Sales)" then "Sales Center"  else

// Section 3
if not isnull({tbOptionSelectionRole.SystemName}) and {tbOptionSelectionRole.SystemName}="DesignCenter" then "Design Center" else
if not isnull({tbOptionSelectionRole.SystemName}) and {tbOptionSelectionRole.SystemName}="SalesAgent" then "Sales Center" else
if not isnull({tbOptionSelectionRole.SystemName}) and {tbOptionSelectionRole.SystemName}="Both" then "Both"
Avatar of Mike McCracken
Mike McCracken

WHat results are you seeing?

mlmcc
Avatar of srilee

ASKER

Most of the options are grouping as desired but a few like the one I mentioned are in their own separate group with no heading at the top of the group (section).
YOu have some fields with NULL you aren't testing for,

NULL values when evaluated in a formula other than IsNull will error out and the formula returns nothing.

mlmcc
Avatar of srilee

ASKER

Sorry for the delay in responding. I was out on vacation last week. mlmcc can you explain a little further?
SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of srilee

ASKER

Thank you. I added a little more code to check for Null values and also enabled the "Convert Database NULL values to Defalut", as well as "Convert Other NULL values to Default" under the Options\Reporting menu.
If you turned on "Convert Database NULL values to Default", then you shouldn't need to use IsNull in a regular formula.  In fact, it probably won't work (because the field should never be null).  You'd need to test for the default value instead.  That would be an empty string -- "" -- if it's a string field.  So, for example, isnull({vw_SHEA_Option_Revenue.OptionCode}) would be replaced by {vw_SHEA_Option_Revenue.OptionCode} = "" (assuming that OptionCode is a string field).  Or you could check for both.  For example:

if (IsNull({vw_SHEA_Option_Revenue.OptionCode}) or {vw_SHEA_Option_Revenue.OptionCode} = "")  and
 {tbOptionSelectionRole.SystemName}="DesignCenter" then "Design Center"


 James