How to show an account multiple times in a crystal report

It depends what you mean.

Any given record can only appear once in a report.

You can create a group called Combined Pediatrics , based on those Account numbers(see below).

I'm not whether you are saying that
20510 = Pediatrics TX
is itself a group with lower level records or whether it is a record.

If it is a group then it can appear as a second level group within Combined Pediatrics.

Then the records in the 20510 group will be listed.
then you get the 20800 group and records in that group are listed and so on.
If the 20510 = Pediatrics TX is just a record then it will be listed in the combined pediatrics
group.
======================================================
How to create a Combined Pediatrics group.

Choose Insert>group and select the Account field as the grouping field.
Then change 'in ascending order' to 'in specified order'.

This gives you an extra tab (Specified Order)  on the dialog
In that tab click New.
This opends the Define Named Group dialog.
Specify the name (Combined Pediatrics)  and then in the dropdown choose 'Is one Of'
You can then add all the accounts that make up this group to the list.
When you have done that click OK and you will be returned to the Specified Order dialog with your new group listed.
If you want other high level groups then repeat the process.

For any 'other' go to the Others tab and decide what you want to do with them.

'OK' your way out until you are back at the report design..

20510 is the account unit and Pediatics TX is the name of the unit. All the accounting units are records, but they can be in multiple groups. Is there way I can get a record to show multiple times? I know that will be breaking the data integrity of the report.

Not in a single report.  A record can only belong to one group.

You can look at using subreports based maybe on account groups and that allows you to display a record once  in the main report and in each subreport.

The alternative would be to do some processing in your database to create as many copies of each record as groups it belongs to and tag each copy with one of the groups.

How many account units  (like Combined Pediatrics) are there?

There are only 5. So you think I will need to have 5 subreports?

If one record could appear in all 5 groups you would need 5 subreports.

Groups with NO overlap of records could appear in the same subreport.

I think with 5 groups it is probably simpler to use 5 subreports (or a main report for one group and 4 subreports) and define a different data source for each one.  

However, if there is anything resembling 'grand totals' in the report you are going to have a real problem with repeated records.

I was looking and for this particular report, there are 5 custom groups with account units that may overlap. In other reports, there are at least 10 custom groups. So I have to rule out the sub report option.

Is there a way I could use table alias to create a formula that will allow me to have the same account unit show up more than once.

for example I have a table called glname and glnames_1 and have a formula that works like:
if {GLNAMES_1.ACCT_UNIT} in ['70600','70700','70900','71100'] then 'ER HCA Contracts' else  
if {GLNAMES.ACCT_UNIT} in ['71100'] then 'test' else
{GLNAMES.ACCT_UNIT}

This doesn't work, but I don't know if there is a way I can get it to.

Thanks

Would this work?

SELECT glm.COMPANY,
       glm.ACCT_UNIT,
       glm.[DESCRIPTION],
       glm.OBJ_ID,
      (CASE
        WHEN glm.ACCT_UNIT = '70600' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '70700' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '70900' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '71100' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '71200' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '71500' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '71600' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '71800' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '71900' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '72000' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '72500' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '72100' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '72600' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '20800' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '21800' THEN 'ER HCA CONTRACTS'
        WHEN glm.ACCT_UNIT = '21200' THEN 'ER HCA CONTRACTS'
        ELSE NULL
      END) "Report Group"
FROM LSPROD.dbo.GLNAMES glm

UNION ALL

SELECT glm.COMPANY,
       glm.ACCT_UNIT,
       glm.[DESCRIPTION],
       glm.OBJ_ID,
      (CASE
        WHEN glm.ACCT_UNIT = '72600' THEN 'Combined JFK 72600/72610'
        WHEN glm.ACCT_UNIT = '72610' THEN 'Combined JFK 72600/72610'
        ELSE NULL
      END) "Report Group"
FROM LSPROD.dbo.GLNAMES glm

If it produces the records correctly it should.

mlmcc

Good catch James.  I looked at the change from my syntax to the CASE without thinking about the filter being needed.

mlmcc

Well, you sort of covered that when you said "If it produces the records correctly", but I decided to go ahead and point out what I thought might be a problem.

 James

Thanks guys for the help.
This is exactly what I needed.

You're welcome.  Glad I could help.

 James