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

x
?
Solved

Crystal Reports or SQL to Solve this problem.....?

Posted on 2013-10-23
4
Medium Priority
?
431 Views
Last Modified: 2013-10-23
I have the following two columns. I do not know if it is best to do this in the SP or in Crystal Reports. Currently the report uses an SP which Crystal REports points to. The MAC_CD is the Parameter used to pull records by users. They now want to switch and use the RAMS_CD....
One problem is when users pull the RAMS_CD 413000 it needs to return results for both MAC_CD 130 and 131.....How do I do that
Second problem....the MAC_CD has many extra codes not visible in the list that have Letters like S or P etc. If I need to get the following records matched up so that in the future the RAMS and MAC codes always pull the proper results back. How do I do that.
By the way the MAC codes are old and the RAMS codes are new. Your help is appreciated.

RAMS_CD	MAC_CD
413000	130
413000	131
413021	132
413031	133
415000	321
422000	220
450000	501
470000	700
471000	711
472000	721
473000	730
475000	751
485000	851
487000	870
489000	891
490000	901
491000	910
492000	921
494000	941
496000	960
498000	980
499000	990

Open in new window

0
Comment
Question by:ruavol2
[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
  • 2
  • 2
4 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 2000 total points
ID: 39594187
This is best done in SQL.  For the most part I always do as much of the row / grouping logic in sql and let crystal be the display formatter.

do you have a mapping table . . . if not you'll need to create one (see below).
The mac column can be varchar so that your records would always match.

At the end of the day it's just a matter of:

1)  change the parameter to be Rams_CD
2)  Create the mapping table if your don't have it.
3)  Change your table joins

eg:

select * from <tbl> where MAC_CD  = @mac_cd

becomes

select * from MappingTable m
join <tbl> t on m.Mac_Cd = t.Mac_Cd
where m.RAMS_CD = @rams_cd
0
 

Author Comment

by:ruavol2
ID: 39594381
Here is a question. If I have  the two columns in the table which do I make a primary key in the table. RAMS_CD or the MAC_CD or do I need something else.

I just created a table in SQL server.

I do not see where to give the table a name other than to save as Table 1
I have the records in an Excel Spreadsheet can I import them as they are above.
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 2000 total points
ID: 39594764
You would actually make both columns the primary key . . . you only want one combination of the RAMS_CD to MAC_CD.

(EG: you don't want
413000      130
413000      130
)


After you create the table, you could edit the top N rows and then copy and paste from excel.
0
 

Author Closing Comment

by:ruavol2
ID: 39595691
Very helpful thank you sir.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 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