Friday, May 15, 2009

Redirect User After Submission of New or EDIT List Form in SharePoint

I recently had the requirement to redirected uses back to a MOSS sites main page after creating a new list item or editing an existing one.  The default behavior in MOSS is to you are redirect back to the list.  Thankfully MOSS embeds the URL that will be used after clicking the OK button in the URL of the new or edit form.  This URL parameter is called Source.  Knowing this it was possible to write a JavaScript function that could be added to the ASPX page to either update or insert this value associated with the parameter.

Here is the function originally written by another RDA Consultant Viktor Dolezel and modified by myself to work in additional cases. 

<script type="text/javascript">

_spBodyOnLoadFunctionNames.push("redirectAfterSave");

function redirectAfterSave() {
var newUrl = "%2Fsite%2Fdefault%2Easpx";

//parse the form action query string into a map
var qs = document.forms[0].action.substring(1, document.forms[0].action.length);
var args = qs.split("&");
var vals = new Object();
for (var i=0; i < args.length; i++) {
var nameVal = args[i].split("=");
vals[nameVal[0].toLowerCase()] = nameVal[1].toLowerCase();
}

//if source doesn't exists in the original form action string, add it,
//otherwise replace it
if (!vals["source"]) {

if(args.length > 1) {
document.forms[0].action+= "&";
} else {
document.forms[0].action+= "?";
}
document.forms[0].action += "Source=" + newUrl;

} else {
document.forms[0].action = document.forms[0].action.toLowerCase().replace(
"source=" + vals["source"],
"Source=" + newUrl );
}
}
</script>




I manually replaced the characters / and . in my URL with %2F and %2E respectively.  Also I did not have to provide the full URL just the name of the site and MOSS filled in the remainder of the URL



On word of caution, it is against best practices to directly edit the ASPX pages for out of the box list.  So use this post at your own risk in those cases.  In my case we had created custom list definitions so this did not apply to us.

Thursday, May 14, 2009

SSAS and Dynamic Excel Reports

Excel can be a very powerful tool for allowing power users to analyze data in cubes.  However it is also possible to create dynamic reports using Excel.  This can be accomplished using Excel Cube functions.  Cube functions allow you to create the same type of functionality achieved in a pivot table but in a individual cell.  Then utilizing other Excel functions it is possible to parameterize the report making it dynamic.

For this example, I will use one of the Adventures Works Cubes.  The first step is to create a pivot table that contains the information we want.  For this example I created a comparison of Internet Gross Profit sales for current month, prior month and the same month a year ago.  This data is further divided by primary sales territory.

PivotTablePersonally I found that using the Cube Functions in the formula editor to be complicated.  Thankfully there is a menu option in Excel 2007 that can help.  It is called ‘Convert to Formulas’ and is found in the PivotTable Tools –> OLAP Tools menu.

ConvertToFormulas

Once this table has been converted we can analyze a few key cells to understand what is going on. The first is A3 where the measure is located. (All cell references relate to the image of the pivot table above.) This cell now contains the formula:

=CUBEMEMBER("AdventureWorksDW",
"[Measures].[Internet Gross Profit]")

The function CUBEMEMBER allows you to select the measure.  If you edit the text you can select other measures, just delete all the text inside quote after this ‘[Measures].[’ and Excel will offer other options.

The second cell we want to inspect is B8 (or any other value cell.)

=CUBEVALUE("AdventureWorksDW",$A$3,$A8,B$7)

You can see that this cell just contains the name of the data source and references to other cells.  One thing to note is that not all cells that were in the pivot table are used by the cube functions so they can be deleted.  Here is my cleaned up table.

ConvertedTable

The next step is to parameterize the dates reference in my converted table so that we can make the report dynamic.  Evaluating the cell D4 will help us do that.

=CUBEMEMBER("AdventureWorksDW","[Date].[Calendar].[Month].&[2004]&[5]")

You can see that the year and month number are used, however this will vary depending on how the time dimension in your cube is setup.  The first step is to place the date to drive the report into cell B1.  Then change the formulas in B4, C4, and D4 to use this date.

I replaced the formulas in cells B4 and D4 with the ones below.

B4: =CUBEMEMBER("AdventureWorksDW", CONCATENATE("[Date].[Calendar].[Month].&[",YEAR(B1)-1,"]&[",MONTH(B1),"]"))

D4: =CUBEMEMBER("AdventureWorksDW",
CONCATENATE("[Date].[Calendar].[Month].&[",YEAR(B1),"]&[",MONTH(B1),"]"))

The only difference is we subtract 1 year from cell B4.  Cell C4 is a little more difficult because when the current month is January the prior month is December of the prior year.  If it is January the we need to add an if statement to see if we need to subtract 1 form the the year and set the month to 12.  If not we can just subtract 1 from the month number.

C4: =CUBEMEMBER("AdventureWorksDW", CONCATENATE("[Date].[Calendar].[Month].&[",IF(MONTH(B1)=1,YEAR(B1)-1,YEAR(B1)),"]&[",IF(MONTH(B1)=1,12,MONTH(B1)-1),"]"))

The final spreadsheet now looks like the image below but now the data in the table, which is pulled from the cube can be updated by changing the date in cell B2.

PivotFinal

This is a straightforward example but could easily be expanded to handle more complex cases.  Graph data can also be updated using the same methodology.  So it is possible to see that this now gives you a complete report authoring environment that you can give to Excel power users and allow them to build their own reports from the cubes your organization has built.