Solved

How to show an account multiple times in a crystal report

Posted on 2013-02-01
14
230 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

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

Expert Comment

by:peter57r
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:metalteck
Comment Utility
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
Comment Utility
If it produces the records correctly it should.

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks guys for the help.
This is exactly what I needed.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.  Glad I could help.

 James
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now