Solved

How to show an account multiple times in a crystal report

Posted on 2013-02-01
14
235 Views
Last Modified: 2013-02-21
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
Comment
Question by:metalteck
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38843890
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
 

Author Comment

by:metalteck
ID: 38844121
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
 
LVL 77

Expert Comment

by:peter57r
ID: 38844551
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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

Author Comment

by:metalteck
ID: 38844731
There are only 5. So you think I will need to have 5 subreports?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38845596
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
 

Author Comment

by:metalteck
ID: 38865996
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 38866795
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
 

Author Comment

by:metalteck
ID: 38868865
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 38870433
If it produces the records correctly it should.

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
ID: 38870760
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
 
LVL 100

Expert Comment

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

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 38872536
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
 

Author Closing Comment

by:metalteck
ID: 38910602
Thanks guys for the help.
This is exactly what I needed.
0
 
LVL 34

Expert Comment

by:James0628
ID: 38913087
You're welcome.  Glad I could help.

 James
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question