Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

How to show an account multiple times in a crystal report

I have a finacial statement that I have written in Crystal 2008. In this report, the first group is going to be the account unit. My question is there a way I can show an account unit multiple times. There are some account units that are group and then need to shown indivdiaully. For example:

Acct 20510,20800, 21200,21300,21800= Combined Pediatrics
20510 = Pediatrics TX
21800 = Pediatircs NY

Thanks for all the help

Chris
0
metalteck
Asked:
metalteck
  • 5
  • 3
  • 3
  • +1
2 Solutions
 
peter57rCommented:
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..
0
 
metalteckAuthor Commented:
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.
0
 
peter57rCommented:
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?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
metalteckAuthor Commented:
There are only 5. So you think I will need to have 5 subreports?
0
 
peter57rCommented:
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.
0
 
metalteckAuthor Commented:
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
0
 
mlmccCommented:
You could use a view/stored procedure in the database or a command in Crystal as the data source

Basic idea

Select yourfields, "ER HCA Contracts" as rptgroup
FROM yourDatabase
Where  yourFilter  and  {GLNAMES_1.ACCT_UNIT} in ['70600','70700','70900','71100']
UNION ALL
Select yourfields, "Test" as rptgroup
FROM yourDatabase
Where  yourFilter  and  {GLNAMES.ACCT_UNIT} in ['71100']
Etc for each group you have

mlmcc
0
 
metalteckAuthor Commented:
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
0
 
mlmccCommented:
If it produces the records correctly it should.

mlmcc
0
 
James0628Commented:
I suspect that you want a WHERE on one or both of those SELECTs.  They're both going to produce a row for every row in GLNAMES, so you're going to get two of everything.

 If the second SELECT is there specifically to add 72600 and 72610 under the 'Combined JFK 72600/72610' group, then it should probably only include those two units.

 For the record, I think you could also eliminate duplicate rows by changing UNION ALL to just UNION, but it should be a lot more efficient to change the SELECT.


 FWIW, your CASE statements could be simpler.  For example:

      (CASE
        WHEN glm.ACCT_UNIT IN ('70600', '70700', '70900', '71100', '71200', '71500', '71600',
         '71800', '71900', '72000', '72500', '72100', '72600', '20800', '21800', '21200') THEN
          'ER HCA CONTRACTS'
        ELSE
          NULL
      END) "Report Group"


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

mlmcc
0
 
James0628Commented:
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
0
 
metalteckAuthor Commented:
Thanks guys for the help.
This is exactly what I needed.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now