We help IT Professionals succeed at work.

SharePoint Dataview Where Lists Act Like Joined Tables

aristotle9
aristotle9 asked
on
1,375 Views
Last Modified: 2012-05-08
There are many articles out there on how to link two lists in a dataview to act like joined tables.  However all the articles I have seen just link two tables.  What if you want a linking table that hold the foreign keys of several tales to, and you want the dataview to give you a grid that looks somewhat like a pivot table? It does not have to work as a pivot table in terms of being able to drop fields, etc, but the output should look like one.  
So imagine a 4 table ERD as shownin the attached image. How do you achieve a data view that looks like the attached grid image using SharePoint lists and building the databiew in SharePoint designer?
Note the rank table carries the foreign keys of the other tables as links, plus the numeric data that is show in the center area of the data view grid.  Also notice that a numeric average is calculated both vertically and horizonatlly in the grid. This is nice to have, but not requried.
view.PNG
ERD.PNG
Comment
Watch Question

You can use the designer to join many tables - simply select all 4 tables when creating joined view.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Trying to use Sharepoint for managing relational data will not succeed unless the data is very lightweight.  You can use lists to simulate tables then combine the data using the DVWP but it's slow on large datasets and there is a learning curve.

Don't use Sharepoint lists as a replacement for a RDMS.  You are better off keeping the data in RDMS, creating joins in views then displaying the views in the DVWP.

Author

Commented:
Thanks for pointing out the limitations in terms of data set size. Unfortunately with the IT limitations my company places on us, building a simple web site based on a RDB is not really an option - sharepoint is pretty much it. If there is a way in SharePoint to build an RDB that I dont know of perhaps that would work. Luckily, however this dataset will be relatively small.  The intention is a flexible scorecard, not a transactional DB.  I could cluge together simulated version of this using a signle view, but it would require me manually changing the dataview every time we add or remove a Customer.  I dont want to do this.  That dataview should grow or shrink depending on what the tables contain without the need to manually alter that dataview with every data change.
So if it is possible I would still like to understand how to do this. Some of the simple items I can figure out, but others I am having trouble with. For example, I understand how to get "Industry" to make rows, but I cannot figure out how to get "Customer" to make columns.
Could you provide the detailed steps for building the data view?  Perhaps there is even somthing outside of a dataview that would suffice such as a recursive Java function. Not sure.  I am open to any solution that meets the following criterea:
1) Must be built in SharePoint without the use of Excel or BI services (IT has not installed them)
2) The data display should appear as shown above
3) The data display should grow or shrink without manual design intervention when the contents of the tables grows or shrinks.

Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
So IT won't let you add a database to the SQL instance hosting Sharepoint?  That is a safer low risk than complex lists with complex joins in the lists that can actually place load that slows the server.

Sharepoint lists cannot create rows using joins like a RDMS.  With Sharepoint you can create lists of data manually, then use the Data View Web Part to display the results of joins but those data sets are for display only which I think might suit your needs.

Your main list will represent the Rank table.  You have three other lookup datasets represented.  You have two choices to store those lookups.  You can use a column that is a lookup choice (basically a set of choices for a dropdown list).  If your list of Industries and Locations is short (30 or less) then simply create a custom column that is a list of choices.

If your customer list is longer then create a list with all your customers and in the Rank list setup a lookup column that uses the Customers list as a source.

With that solution a user can create ranks by adding a new row then selecting the choices using the lookups.

As you likely know I'm a volunteer who doesn't get paid for answering questions on this site.  I'm sorry with my own job and family I don't have a few hours to spend giving you the full details on how to implement a solution.

Author

Commented:
Understood and thanks for your time.  I still think however that does not solve the issue of listing the "customers" accross the top of the dataview.  Is there a simple way to take rows of data and cause them to create columns in the dataview.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
No not to create the columns.  The Data View is what it says it is, a 'view' just like in SQL.  All the data has to already exist in the lists then you can display it.

It's pretty common in Sharepoint to separate the data from the actual view.  So you store the data in the lists then pull it all together on another page using the Data View Web Part.

I'd recommend you download the Sharepoint and WSS 3.0 templates Microsoft provides.  They actually have working examples to demonstrate what I've recommended.

http://www.microsoft.com/downloads/details.aspx?FamilyID=516b95c5-9133-46c4-a01f-d7598780dc17&displaylang=en#top

http://www.microsoft.com/downloads/details.aspx?familyid=5807B5EF-57A1-47CB-8666-78C1363F127D&displaylang=en

Author

Commented:
I understand that that views are separate from where the data is stored. I'm sorry if I was not clear.  When creating a data view (not a view of a list from within SharePoint page, but a custom data view within SharePoint designer), a row of data is displayed as a row.
In order to achieve the above display, one set of data must display in rows (Industry) and one must display in columns (Customers), and the Rank represents the relationship between the two. None of the templates that I can find shows how to do this (uncluding those in your links).  This is the essence of the problem.
The view needs customer accross the top, industry down the side, and rank in the middle.
 
Senior Software Developer
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ah thanks much.  That makes sense. I unfortunately dont know XSLT. I'll need to spend some time figuring this out.  Combined with what you wrote I think I have the starting point.  Here is what I found.  If I remove the <tr> tags from the correct place leaving the <td> tags I can get the data rows to list across the page as opposed to down:
Old Code:
  <tr>
   <xsl:if test="position() mod 2 = 1">
    <xsl:attribute name="class">ms-alternating</xsl:attribute>
   </xsl:if>
   <xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
    <td class="ms-vb" width="1%" nowrap="nowrap">
     <span ddwrt:amkeyfield="ID" ddwrt:amkeyvalue="ddwrt:EscapeDelims(string(@ID))" ddwrt:ammode="view"></span>
    </td>
   </xsl:if>
   <td class="ms-vb">
    <xsl:value-of select="@Title"/>
   </td>
 </tr>
New Code:
   <xsl:if test="position() mod 2 = 1">
    <xsl:attribute name="class">ms-alternating</xsl:attribute>
   </xsl:if>
   <xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
    <td class="ms-vb" width="1%" nowrap="nowrap">
     <span ddwrt:amkeyfield="ID" ddwrt:amkeyvalue="ddwrt:EscapeDelims(string(@ID))" ddwrt:ammode="view"></span>
    </td>
   </xsl:if>
   <td class="ms-vb">
    <xsl:value-of select="@Title"/>
   </td>

This gives me a good start and I can redirect my further questions to the XSLT forums.
Thanks much.

Author

Commented:
Ted gave me the stuff I needed to get down the path but not the complete answer.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.