Solved

SQL OR CRYSTAL HELP

Posted on 2014-12-23
6
200 Views
Last Modified: 2014-12-24
I'm trying to create a report that has a table that lists part numbers and a table that lists an attribute name and a third table that lists the attribute value.  There are up to 7 different attribute names but each part number usually has between zero and four attributes.  When I create a CR that shows the part number and the attribute name and value, a part that has four different attributes will show up four different times, one for each attribute.  I want to somehow display all attributes 1's in one column, 2's in a second column and so forth, with the part number listed only once.  The attributes have a UID (1-7) but they also have a name. Ultimately, I would like to create a new view for this as I seem to always need this info.  I'm not too familiar with SQL so don't really know how to do that.  How can I get this info all on one line in a report?
0
Comment
Question by:ITworks
[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
6 Comments
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40515236
While I do not fully understand what you are trying to accomplish it would help the community if you share the sql you have created so far and maybe a screenshot of the report layout you have created.

My first thought was that you may benefit from a subreport to show the attributes for each part but then you mentioned to have all of the attributes on one line which I do not understand.
0
 
LVL 18

Expert Comment

by:vasto
ID: 40515288
You need to use left join between the parts, attribute name and attribute value. This will create a view with all parts, attributes name and corresponding values (if any). Then use a Cross tab to place the parts and rows, attribute names as columns and attributes values as values
0
 
LVL 4

Author Comment

by:ITworks
ID: 40515391
Untitled.png
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:ITworks
ID: 40515394
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 40516306
I can think of a few ways to do something like that in CR.  How many UID's can there be for a "part"?  Is there a set limit of 7, or could there be more?  If there could be more, and you're not sure how many, then you may need a more sophisticated solution (like a cross-tab, like vasto suggested).

 If there is a set limit, you could try something like this.

 Using your sample report, with 4 formulas for UID's 1 - 4.  Group the report on whatever identifies a "part" (lot and item_id?), and suppress the detail section.  Put lot, item_id and item_desc in the group header or footer (your choice).  Right-click on each of your formulas in the detail section (eg. @COO) and select Insert > Summary, and create a Maximum summary for the "part" group (not a grand total).  Put those summary fields in your group header or footer with the other fields.

 I think that should work.

 James
0
 
LVL 4

Author Closing Comment

by:ITworks
ID: 40516733
That did it!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …

697 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