How to Group DVWP Rows by a Joined Source Field?

Question

I have a joined data source of 2 sharepoint lists and am trying to group by category and setup conditional formatting, yadda yadda yadda, in a DVWP using various fields within these lists.

I'm able to do everything just fine on a single-list data source - but it's pretty tedious for the users to enter the data and I'm trying to streamline the process. Apparently what I want to do using a joined data source is a bit more complex / advanced compared to all of the HowTos out there?

Each quarter we will need the same "measures" to have new values entered and available for analysis (and possibly not all measures will have entries for every quarter?). We have a list of predefined measures, and each measure belongs to a predefined category. Each measure will have a target value & actual value entered each quarter (among other comments, notes & fields as needed).

Rather than have the users enter the measure name, category, and other static information, etc., repeatedly each quarter I've split this common info out into a separate list. It seemed simple enough at the time, but appears to have caused complications. Here's a simplified version of the details:

List 1: Common Info
Field: Measure Name
Field: Measure Category Choice
Field: Measure Objective
Field: Measure display-as choice ($, %, etc.)

List 2: Scorecard
Field: Measure Name Lookup
Field: Target Value
Field: Actual Value
Field: Year
Field: Quarter

I want to:
- Display only items for this year's quarter (e.g. "Q3 of 2010"). I can do this with a single-list source using a filter on query string parameters, but that doesn't appear to work using a joined source?
- Group this quarter's filtered items in List 2 by the categories corresponding to each measure in List 1. - Format the values of this quarter's filtered items in List 2, by the appropriate corresponding Measure field in List 1.

In a single-list source I can do all of this, but the user has to enter/select every value for each measure, each quarter. I was hoping to have them simply select a "measure" and enter just the new values & comments for that quarter, and then the DVWP will pull the details of the fields that don't change from a separate joined list. I've tried every combination that I can think of:

• Creating a DVWP by converting an existing list view web part. This gives me problems and behaves strangely when trying to later add another joined list to the data source.

• Starting off with adding the filtered items from List 2 of the joined source, then trying to add the grouping & conditional formatting from List 1. The DVWP just seems to ignore everything that I try to do that relies on List 1 and so grouping just doesn't work at all.

• Starting off with adding the data from List 1 to setup the initial grouping, then add the joined sub-view items from List 2. This seems to always display the items from List 1 even of there are no corresponding items in List 2 - but I'm thinking that I just don't have my filter setup properly?

Agh!

Answer 1

I was able to come to an acceptable compromise. I don't need to have the user re-enter ALL of the common info, but they do need to enter some of it (mainly, the field that's being used for grouping - I couldn't figure out how to group on a joined field).

Use the standard DVWP + joined data source method. From there, displaying the content from the "common info" list (e.g. "Objective") was pretty straightforward.

"Formatting" using the joined field "Display As" was a bit more tricky. I wasn't able to find a way to directly format it based on the value of that joined field. I had to add the field itself as a joined field, then instead of displaying the field's choice value, have it display my own text - and I had to do this multiple times.

Call the "dvt_dollar" template in the cell where the $ prefix should be displayed. Then in the dvt_dollar template itself, setup a conditional formatting rule to display my own text ("$") dependant on the value of that field, otherwise, hide. Then do the same for the "%" symbol - calling another "dvt_percent" template with similar setup.

Lather, rinse, repeat.

Not exactly fast & easy, but it got the job done. Thankfully I didn't have to ACTUALLY format the numbers (e.g. "add 2 decimal places") based on the value of that joined field. A little time consuming because it was used in a few places on my DVWP.

Also - don't forget to setup conditional formatting in a number of places in the event that some of the values are blank to display a "space" or blank filler image. otherwise if you have borders on your cells they'll be missing on any empty cells.