Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to show an account multiple times in a crystal report

Posted on 2013-02-01
14
Medium Priority
?
244 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

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 101

Assisted Solution

by:mlmcc
mlmcc earned 1000 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 101

Expert Comment

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

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1000 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 101

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 35

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 35

Expert Comment

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

 James
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 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