Not jPoint specific but... DataView summaries with sorting on groupby calculations [Resolved]

Mar 3, 2010 at 7:52 AM
This isn't jPoint specific but I have some questions about pulling together dashboard type summaries. If anyone can provide pointers it would be most appreciated, including recommendations of good SharePoint dev forums.

I have seen some XSLT approaches that I will be looking into tomorrow. It isn't my preferred way since I'd much rather just do a SQL query but that isn't an option. :) The next popular way is to run a workflow that handles sums, etc., but I haven't been able to find an example of setting up a workflow that processes all items of a list. Even the ones that talk about looping didn't seem to get into the actual looping part. I must be missing something (and again need sleep... like the UBU CamlBuilder link...).

If all else fails I know I can pull the data and crank through it with jPoint but I'm not sure how well that will scale as it could conceivably pull a couple thousand rows from what I understand at the moment.

Any pointers are most appreciated!

Thanks in advance,

Michael

Coordinator
Mar 3, 2010 at 6:23 PM

Michael,

Are you trying to modify all rows in a list only initially or periodically?

If only once maybe you can All items view -> Actions -> "Edit in Datasheet" and use excel like editing to edit one row and drag result down across all other rows.

Funny coincidence we are also building dashboard for a client for not only SharePoint data, but other external data as well.

Mar 3, 2010 at 6:54 PM

Thanks but it has to update every time item data changes. But about the tip of dragging the result down, I once told my wife SharePoint is like Excel on steroids. :)

Essentially it breaks down to this:

I have a set of activities tied to regions and locations. The stats of these activities are displayed on a dashboard broken down by region, location, type, etc. Those would just be different SQL queries to the totals, sums, percentages, etc. But in SP I have to make a view and perhaps do some XSLT (or use jQuery) to pull data out of those views to do the math. So far the only way I have seen suggested to get around this is that, in my case, whenever the stats of an activity is modified, start a workflow that updates separate lists that maintain counts on those stats.

So to that I could have a list that has a lookup for all the activities and their corresponding regions and locations. This list would only record sums. The workflow would loop through and calculate the sums, and my dashboard would pull data from the sum list instead of the actual data itself. This seems (and apparently is) error prone. I don't have access to the server, and I'll have to confirm if I can use a SQL Datasource or not. If I can't (and I doubt I will be able to) I am now looking at seeing if I can build an ASPX page that does the calcs and stores that in a custom list for me. 

I could do the whole thing in jPoint but that would really bog down the client side as the dataset increases.

Mar 3, 2010 at 7:24 PM
Edited Mar 4, 2010 at 5:24 AM

*sigh* can't add code to the aspx page unless web.config is updated... makes sense from a security point of view. Searching... searching...

Thanks for your suggestions though!

[edit] OK this is making more sense. I just hope multiple dataviews with sorting and grouping all the data will perform OK. Anyway, using custom XSLT to calculate what I need and NOT calling the bodyrow template so those details are hidden is pulling it all together. I'll still need jQuery to handle sorting on that table to avoid roundtrips to the server.

[edit 2] I haven't done this much XPath in a while, and I've never done it before in the context of an XSL transform. But I have to say... I'm starting to like it. I would sort in jQuery if it's dynamic but fortunately the main dashboard bits are NOT dynamic and now I see how to do a GroupBy and sort by a calculation based on that GroupBy. I am starting to actually like SharePoint now that I am getting away from thinking in terms of an RDBMS.

All of that to say I am able to build the summaries now in the XSL of the DVWP which is pretty nifty.