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_Rev enue.Optio nCode}) and {tbOptionSelectionRole.Sys temName}=" DesignCent er" then "Design Center" else
if isnull({vw_SHEA_Option_Rev enue.Optio nCode}) and {tbOptionSelectionRole.Sys temName}=" SalesAgent " then "Sales Center" else
if isnull({vw_SHEA_Option_Rev enue.Optio nCode}) and {tbOptionSelectionRole.Sys temName}=" Both" then "Both" else
//Section 2
if ({vw_AMA_Option_Type.DESCR IPTION})=" Finance (Design)" then "Design Center" else
if ({vw_AMA_Option_Type.DESCR IPTION})=" Finance (Sales)" then "Sales Center" else
// Section 3
if not isnull({tbOptionSelectionR ole.System Name}) and {tbOptionSelectionRole.Sys temName}=" DesignCent er" then "Design Center" else
if not isnull({tbOptionSelectionR ole.System Name}) and {tbOptionSelectionRole.Sys temName}=" SalesAgent " then "Sales Center" else
if not isnull({tbOptionSelectionR ole.System Name}) and {tbOptionSelectionRole.Sys temName}=" Both" then "Both"
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_Rev
if isnull({vw_SHEA_Option_Rev
if isnull({vw_SHEA_Option_Rev
//Section 2
if ({vw_AMA_Option_Type.DESCR
if ({vw_AMA_Option_Type.DESCR
// Section 3
if not isnull({tbOptionSelectionR
if not isnull({tbOptionSelectionR
if not isnull({tbOptionSelectionR
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
NULL values when evaluated in a formula other than IsNull will error out and the formula returns nothing.
mlmcc
ASKER
Sorry for the delay in responding. I was out on vacation last week. mlmcc can you explain a little further?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_Rev enue.Optio nCode}) would be replaced by {vw_SHEA_Option_Revenue.Op tionCode} = "" (assuming that OptionCode is a string field). Or you could check for both. For example:
if (IsNull({vw_SHEA_Option_Re venue.Opti onCode}) or {vw_SHEA_Option_Revenue.Op tionCode} = "") and
{tbOptionSelectionRole.Sys temName}=" DesignCent er" then "Design Center"
James
if (IsNull({vw_SHEA_Option_Re
{tbOptionSelectionRole.Sys
James
mlmcc