Need return all sales departments including ones with zero dollars.

[fabianor]

I basically need to list all 51 departments in our store.
Right now only the departments with sales greater then $0.00 are being list in report.

I need the report to list all 51 departments including departments that have $0.00 in sales. Items that are not linked to a Departments need totals to be unassigned.

I have tried at in Formula editor @Department

IF ISNULL({Department.Name}) THEN
"<Unassigned>*"
ELSE
{Department.Name}

Also, tried

IF IsNull ({Department.Name}) OR Length({Department.Name}) = 0 THEN
"Unassigned"
ELSE
{Department.Name}

Not sure what I to this formula to achieve the result.

 

[lbass]

What tables are you working with, and what is the report supposed to show? I'll guess here...You should have a left join FROM the Department table to the Sales table, with no selection criteria on the Sales table. To include items not associated with any department, you could insert a subreport in the report footer where you use a record selection criteria of:

isnull({Department.Name})

-LB

 

[fabianor]

I am working with the following tables

Batch
[Transactions]
TransactionEntry
Item
Department

I want the report to list all the departments in the database. I need the reported departments list to show the departments with $0.00 in sales as wells the departments with actual values greater then $0.00.

Report is working fine but I would like all my departments to appear and not just the ones that had sales in them at the time off closing the day.

The xml report that is printed shows all the departments on the z receipt when we close.

Sample section of department xml

- <!-- ===========================
Section: Department Totals
===========================

-->
<ROW>"Department Sales:"</ROW>
- <FOR each="ReportDepartment">
<ROW>Report.Department.Name "|" Report.Department.Sales "|" Report.Department.PercentageSales</ROW>
</FOR>

 

[lbass]

Please try my earlier suggestion. Should work.

-LB

 

[fabianor]

I tried using

IsNull ({Department.Name}) in place of the original

IF IsNull ({Department.Name}) OR Length({Department.Name}) = 0 THEN
"<Unassigned>*"
ELSE
{Department.Name}

But when I run the report it now replace the DEPARTMENT NAMES with the Word "FALSE" for all the DEPARTMENTS.

 

[lbass]

I made two suggestions. The first one was about table linking. Once you have done that and grouped on the department field from the departments table, you will get a report that shows all department names. However, it will not include items that have no department, which was why I suggested a subreport in the report footer to capture those items. In that subreport, you would go to report->selection formula and enter:

isnull({table.departmentname}) or
len({table.departmentname}) = 0

Then create a formula in the formula expert to use as the field in the detail or group section of your subreport:

if isnull({table.departmentname}) or
len({table.departmentname}) = 0 then "Unassigned" else {talbe.departmentname}

This will only return "Unassigned" as all records in the subreport will meet that criterion.

-LB