When passing multiple values for a parameter in SQL Server Reporting Services to a stored procedure, I used to JOIN them all together into one long pipe-delimited string (JOIN(Parameters!Paramname.Value, "|")) which I'd then loop through with a cursor in the stored procedure, break all the values into a table, and join that to my dataset.
That worked, but it did involve a bit of setup, since I had to add that loop to every stored procedure I used. So I was pleased this week to stumble onto an even simpler way to handle parameters.
This method relies upon SSRS's ability to dynamically string together the values itself. SSRS can create dynamic SQL that will handle these multi-selects if you enter your dataset as a text query, but it can't get into your stored procedure to modify it to take the new string. All you have to do, however, is shape your WHERE clause like the following:
WHERE (@Param + ',' LIKE '%'+ table.value + ',%' OR @Param = '' OR @Param IS NULL)
Now your SP can handle the multi-select string just the way SSRS passes it. Nice and quick! =)
Thursday, October 14, 2010
Using Sharepoint to Get the Current User ID
This was something I'd meant to post about a while back, but didn't get around to doing so until a co-worker asked me for more infomation. Here's what I shared!
As Sharepoint is used more and more as a BI platform, a lot of different solutions get hosted on SP pages. But how can you plug into SP's user identification and use it for your own code?
Here's a quick and dirty approach I unearthed while squeezing some Silverlight onto a SharePoint page.
The basic idea:
*In SharePoint Designer, open up the page you’ll be using
*Drop a list (any list) web part onto the page; convert the list to an XSL data view
*Add a system variable for the current user (I used LOGON_USER)
To do this, find the section of the page where “ParameterBinding”s are set. Add a new row that looks like this:
Next, look for the area where parameters are defined by searching for the string “XSL:Param name”, and add this line:
*Drop that variable in a cell within the data view which is named something you can recognize later, and hide it
Now you can reference the cell “MagicalUserPerson” in your code elsewhere.
Here's a bit more of a walkthrough, including an explanation of what other user variables you might choose to work with:
Getting the Current User Name or ID in SharePoint using SharePoint Designer (no code)
As Sharepoint is used more and more as a BI platform, a lot of different solutions get hosted on SP pages. But how can you plug into SP's user identification and use it for your own code?
Here's a quick and dirty approach I unearthed while squeezing some Silverlight onto a SharePoint page.
The basic idea:
*In SharePoint Designer, open up the page you’ll be using
*Drop a list (any list) web part onto the page; convert the list to an XSL data view
*Add a system variable for the current user (I used LOGON_USER)
To do this, find the section of the page where “ParameterBinding”s are set. Add a new row that looks like this:
<ParameterBinding> Name = “LogonUser” Location = “ServerVariable(LOGON_USER)” />
Next, look for the area where parameters are defined by searching for the string “XSL:Param name”, and add this line:
<xsl:param name="LogonUser"/>
*Drop that variable in a cell within the data view which is named something you can recognize later, and hide it
<table border="0" cellspacing="0" width="100%">
<tr style="display:none">
<td id="MagicalUserPerson" width="400px" valign="top" class="ms-formbody">
<xsl:value-of select="$LogonUser"/>
</td>
</tr>
</table>
Now you can reference the cell “MagicalUserPerson” in your code elsewhere.
Here's a bit more of a walkthrough, including an explanation of what other user variables you might choose to work with:
Getting the Current User Name or ID in SharePoint using SharePoint Designer (no code)
Thursday, August 12, 2010
Re-Direct With ID - REVAMP
Ended up re-working the process from my previous post!
RECAP: The goal is to allow users to enter a list item and associate a number of items in ANOTHER list with that original list item. In this case, we have a list of projects and we want to associate multiple notes with a single project.
My original approach was to use URL parameters wherever possible. That still works in a few cases, but I found that it works better to skip the URL hassle. Instead, I hid a data view web part on the child (the notes list) newform page.
The hidden data view web part is filtered to show only the ID of the project most recently modified by the current user. That ID is displayed in a cell with the ID "ProjectID".
On the same page, my add-a-new-note form has a hidden field that accepts that ID. That field is populated using the following Javascript:
NOTE: In this case, the ProjectID field in my child list is a lookup field. If you have a different type of field, you'll need to alter the javascript to some degree. The holy grail of info on setting values in SP forms with Javascript is here.
Once the data is ready, I wanted to send the user back to the display form for the project they were working on (I would have preferred to send them to the edit form, actually, but doing so can cause save conflict errors). To do that, I had to go back to the URL parameter, so I can specify what project I want to display.
It took a smidge of Javascript magic again to properly insert the project ID into the URL. Here's the code:
This is called in a custom submit button:
And there you have it!
RECAP: The goal is to allow users to enter a list item and associate a number of items in ANOTHER list with that original list item. In this case, we have a list of projects and we want to associate multiple notes with a single project.
My original approach was to use URL parameters wherever possible. That still works in a few cases, but I found that it works better to skip the URL hassle. Instead, I hid a data view web part on the child (the notes list) newform page.
The hidden data view web part is filtered to show only the ID of the project most recently modified by the current user. That ID is displayed in a cell with the ID "ProjectID".
On the same page, my add-a-new-note form has a hidden field that accepts that ID. That field is populated using the following Javascript:
NOTE: In this case, the ProjectID field in my child list is a lookup field. If you have a different type of field, you'll need to alter the javascript to some degree. The holy grail of info on setting values in SP forms with Javascript is here.
Once the data is ready, I wanted to send the user back to the display form for the project they were working on (I would have preferred to send them to the edit form, actually, but doing so can cause save conflict errors). To do that, I had to go back to the URL parameter, so I can specify what project I want to display.
It took a smidge of Javascript magic again to properly insert the project ID into the URL. Here's the code:
This is called in a custom submit button:
And there you have it!
Thursday, July 1, 2010
Sharepoint, Meet Database. Database, Sharepoint.
...And, Sharepoint? We'd like to ask you to be a bit more like Database.
I needed to associate multiple records from one list with a single item in another list. And boy, is that harder than you think!
Say, for example, that you have a list of projects. You want to be able to enter notes on each project as the weeks go by. If multiple users are working on a single project, then each should be able to add as many notes as they want. Versioning wouldn't be an option in this case, since the users want to be able to quickly and easily view all notes for a project at once.
So, that means you gotta do two lists: ProjectList and NotesList. Each Note will have some kind of ID tying it to a Project. Simple enough - databases do this all the time, right?
Well, the challenge is in NewForm.aspx!
We had started working on a custom New Item Form page for the Projects list, assuming that we could add a simple button that, when clicked, would let a user enter a new note. Specifically, we figured that the button would redirect the user to the New Item page for the Notes list, add an ID, and life would be good.
The problem? You don't HAVE an ID. The ID is only created when the form is submitted.
I started surfing for options. I tried a couple JQuery solutions, but I honestly couldn't get them to work right. The Redirect-with-ID option was very promising, but I could only get it to perform the redirect on 'submit' - and that doesn't help much when I want my users to click a special button for this.
Tried combining Jquery with a custom submit button, but that didn't work either. Only the built-in button seemed to work. I'm sure I was doing something dumb, but I was frustrated and moved on.
I considered writing up an event handler, but the pain of writing it (code isn't my strongest suit) combined with the pain of deploying it convinced me to discard that option as well.
Found a few examples with SPAPI, but wasn't successful in implementing them.
Finally, inspired by the SharePoint Hillbilly's post on passing parameters, I took a look at it from the SharePoint perspective.
Let's see - I know I can use those custom submit buttons to get a user onto a page. And once I actually get there, there WILL be an ID, 'cause the form was just submitted. I can't pass it via the URL parameter since it's just not ready when the user clicks the button....But what if I can pull the ID back as soon as they hit the page?
I tried the following:
--Re-added that custom submit button to my New Project form, and pointed the redirect paramete rto my New Note form
--Added a stripped-down display item form to the New Note page. This form just shows one item: the ID of the most recently modified Project for the current user. To make life easier, I identified the cell that contains the Project ID as "ProjectID", so I could nab the value in my javascript.
--Added javascript to pull that ID and write it to the form, so that the Note is associated with the right Project ID (code below)
--Added more javascript to append that ID onto another custom submit button - this one points the user at the Project list's EditItem form
Now a user can click to enter a new project, type stuff in, click to enter a note, go right back to their project page, and finish typing whatever they want. Magic!
---THE CODE----
*Custom button on New Project form:
<input type="button" value="Add a Note" name="btnSave"onclick="{ddwrt:GenFireServerEvent('__commit;__redirect={http://server/site/pages/newnote.aspx}')}; " />
*Javascript to add the ID from the New Note page onto my redirect URL:
<script type="text/javascript">
function GoToPage(url)
{
var TheCell = document.getElementById("ProjectID")
var TheID = TheCell.innerHTML;
location.href = url + "?ID=" + TheID;
return false;
}
</script>
*Button code that will send user from New Note page to Project Edit Form with correct ID appended:
<input type="button" value="Click me!" onClick="GoToPage('http://server/site/Lists/Projects/Item/editform.aspx');"></input>
*Javascript to pull the value from my ProjectID field and write it to the Notes form:
<script type="text/javascript">
_spBodyOnLoadFunctionNames.push("fillDefaultIDValues");
function fillDefaultIDValues() {
var temp = document.getElementById("ProjectID")
var RealId = temp.innerHTML;
setLookupFromFieldName("Project ID", RealId);
alert(temp);
}
function setLookupFromFieldName(fieldName, value) {
if (value == undefined) return;
var theSelect = getTagFromIdentifierAndTitle("select","Lookup",fieldName);
// if theSelect is null, it means that the target list has more than
// 20 items, and the Lookup is being rendered with an input element
if (theSelect == null) {
var theInput = getTagFromIdentifierAndTitle("input","",fieldName);
ShowDropdown(theInput.id); //this function is provided by SharePoint
var opt=document.getElementById(theInput.opt);
setSelectedOption(opt, value);
OptLoseFocus(opt); //this function is provided by SharePoint
} else {
setSelectedOption(theSelect, value);
}
}
function setSelectedOption(select, value) {
var opts = select.options;
var l = opts.length;
if (select == null) return;
for (var i=0; i < l; i++) {
if (opts[i].value == value) {
select.selectedIndex = i;
return true;
}
}
return false;
}
function getTagFromIdentifierAndTitle(tagName, identifier, title) {
var len = identifier.length;
var tags = document.getElementsByTagName(tagName);
for (var i=0; i < tags.length; i++) {
var tempString = tags[i].id;
if (tags[i].title == title && (identifier == "" || tempString.indexOf(identifier) == tempString.length - len)) {
return tags[i];
}
}
return null;
}
</script>
I needed to associate multiple records from one list with a single item in another list. And boy, is that harder than you think!
Say, for example, that you have a list of projects. You want to be able to enter notes on each project as the weeks go by. If multiple users are working on a single project, then each should be able to add as many notes as they want. Versioning wouldn't be an option in this case, since the users want to be able to quickly and easily view all notes for a project at once.
So, that means you gotta do two lists: ProjectList and NotesList. Each Note will have some kind of ID tying it to a Project. Simple enough - databases do this all the time, right?
Well, the challenge is in NewForm.aspx!
We had started working on a custom New Item Form page for the Projects list, assuming that we could add a simple button that, when clicked, would let a user enter a new note. Specifically, we figured that the button would redirect the user to the New Item page for the Notes list, add an ID, and life would be good.
The problem? You don't HAVE an ID. The ID is only created when the form is submitted.
I started surfing for options. I tried a couple JQuery solutions, but I honestly couldn't get them to work right. The Redirect-with-ID option was very promising, but I could only get it to perform the redirect on 'submit' - and that doesn't help much when I want my users to click a special button for this.
Tried combining Jquery with a custom submit button, but that didn't work either. Only the built-in button seemed to work. I'm sure I was doing something dumb, but I was frustrated and moved on.
I considered writing up an event handler, but the pain of writing it (code isn't my strongest suit) combined with the pain of deploying it convinced me to discard that option as well.
Found a few examples with SPAPI, but wasn't successful in implementing them.
Finally, inspired by the SharePoint Hillbilly's post on passing parameters, I took a look at it from the SharePoint perspective.
Let's see - I know I can use those custom submit buttons to get a user onto a page. And once I actually get there, there WILL be an ID, 'cause the form was just submitted. I can't pass it via the URL parameter since it's just not ready when the user clicks the button....But what if I can pull the ID back as soon as they hit the page?
I tried the following:
--Re-added that custom submit button to my New Project form, and pointed the redirect paramete rto my New Note form
--Added a stripped-down display item form to the New Note page. This form just shows one item: the ID of the most recently modified Project for the current user. To make life easier, I identified the cell that contains the Project ID as "ProjectID", so I could nab the value in my javascript.
--Added javascript to pull that ID and write it to the form, so that the Note is associated with the right Project ID (code below)
--Added more javascript to append that ID onto another custom submit button - this one points the user at the Project list's EditItem form
Now a user can click to enter a new project, type stuff in, click to enter a note, go right back to their project page, and finish typing whatever they want. Magic!
---THE CODE----
*Custom button on New Project form:
<input type="button" value="Add a Note" name="btnSave"onclick="{ddwrt:GenFireServerEvent('__commit;__redirect={http://server/site/pages/newnote.aspx}')}; " />
*Javascript to add the ID from the New Note page onto my redirect URL:
<script type="text/javascript">
function GoToPage(url)
{
var TheCell = document.getElementById("ProjectID")
var TheID = TheCell.innerHTML;
location.href = url + "?ID=" + TheID;
return false;
}
</script>
*Button code that will send user from New Note page to Project Edit Form with correct ID appended:
<input type="button" value="Click me!" onClick="GoToPage('http://server/site/Lists/Projects/Item/editform.aspx');"></input>
*Javascript to pull the value from my ProjectID field and write it to the Notes form:
<script type="text/javascript">
_spBodyOnLoadFunctionNames.push("fillDefaultIDValues");
function fillDefaultIDValues() {
var temp = document.getElementById("ProjectID")
var RealId = temp.innerHTML;
setLookupFromFieldName("Project ID", RealId);
alert(temp);
}
function setLookupFromFieldName(fieldName, value) {
if (value == undefined) return;
var theSelect = getTagFromIdentifierAndTitle("select","Lookup",fieldName);
// if theSelect is null, it means that the target list has more than
// 20 items, and the Lookup is being rendered with an input element
if (theSelect == null) {
var theInput = getTagFromIdentifierAndTitle("input","",fieldName);
ShowDropdown(theInput.id); //this function is provided by SharePoint
var opt=document.getElementById(theInput.opt);
setSelectedOption(opt, value);
OptLoseFocus(opt); //this function is provided by SharePoint
} else {
setSelectedOption(theSelect, value);
}
}
function setSelectedOption(select, value) {
var opts = select.options;
var l = opts.length;
if (select == null) return;
for (var i=0; i < l; i++) {
if (opts[i].value == value) {
select.selectedIndex = i;
return true;
}
}
return false;
}
function getTagFromIdentifierAndTitle(tagName, identifier, title) {
var len = identifier.length;
var tags = document.getElementsByTagName(tagName);
for (var i=0; i < tags.length; i++) {
var tempString = tags[i].id;
if (tags[i].title == title && (identifier == "" || tempString.indexOf(identifier) == tempString.length - len)) {
return tags[i];
}
}
return null;
}
</script>
Monday, April 12, 2010
Lookup Fields and XSL
Ahh, XSL...Cause of and solution to all of SharePoint's formatting woes.
I'm displaying a document library in an XSLT data view on my SharePoint page, and one of the fields I want to show is a lookup type.
Now, lookup type field values are stored with both the string value and the ID, like so:
...Which is fine, right up until you try to include that field in your data view, and get pages of 17#;Value, 18#;AnotherValue, etc.
Thankfully, XSL provides its own solution. Use the substring-after command to strip out those pesky IDs....Or, conversely, use substring-before if you just want the IDs!
Code sample:
I'm displaying a document library in an XSLT data view on my SharePoint page, and one of the fields I want to show is a lookup type.
Now, lookup type field values are stored with both the string value and the ID, like so:
17;#Value
...Which is fine, right up until you try to include that field in your data view, and get pages of 17#;Value, 18#;AnotherValue, etc.
Thankfully, XSL provides its own solution. Use the substring-after command to strip out those pesky IDs....Or, conversely, use substring-before if you just want the IDs!
Code sample:
<xsl:value-of select="substring-after(@Fieldname, ';#')" />
Wednesday, April 7, 2010
Sharepoint Navigation: It Hurts So Good
Just when you think you've mastered navigation in SharePoint, something new gets tossed at you.
My own ego was deflated when a client requested a 3rd-level flyout on our top navigation, something like this:
Sure, I confidently asserted, I can definitely do that! No sweat.
Smugly, I set up a heading and a bunch of links beneath that on SubsiteA. Still brimming with confidence, I clicked 'OK', and moused over the navigation bar to admire my work.
But...it wasn't there. Just...nothing. Did I save it properly? I doublechecked, and yes, I saw the heading and all the subnav items still there in the admin page. Mocking me. I could almost hear their laughter.
I mucked around with different combinations of relative and absolute URLs, and replicated the issue on other sub-sites. Sure enough, headings just refused to show up on anything except the root site.
Annoyed, I turned to the web for help, and finally dug up a post by Millerw that addressed the problem. Millerw suggested changing the data source for the navigation to "CurrentNavSiteMapProvider". I checked, and found that my source was "CombinedNavSiteMapProvider". Well, OK - worth a try!
I swapped sources, and checked out my site. Um...Not quite what I was looking for. Yes, I could now see the flyout on that subsite, but navigation to all other subsites was messed up. Everything was oriented vertically, I wasn't seeing all the subsites I wanted to, etc., etc.
Beginning to despair, I searched around and ended up on the Microsoft Enterprise Content Management Team blog (there's a mouthful), where they have a VERY good deep-dive on MOSS navigation. They actually had a lot of useful information on CombinedNavSiteMapProvider...And in their sample code, they had the following line:
TrimNonCurrentTypes="Heading"
AH-HAH, they're turning off 'heading' in the navigation - that MUST mean that CombinedNavSiteMapProvider can show headings!
Sure enough, by tweaking settings I was able to get the code below to work. Now I see all my horizontal top nav items just as before, with the addition of headers on my sub-sites.
I'll tell ya, if SharePoint is good for anything, it's good for keeping your ego in check.
The code:
My own ego was deflated when a client requested a 3rd-level flyout on our top navigation, something like this:
HOME SUBSITEA SUBSITEB SUBSITEC
Nav 1
Nav 2
Heading->
Subnav 1
Subnav 2
Subnav 3
Sure, I confidently asserted, I can definitely do that! No sweat.
Smugly, I set up a heading and a bunch of links beneath that on SubsiteA. Still brimming with confidence, I clicked 'OK', and moused over the navigation bar to admire my work.
But...it wasn't there. Just...nothing. Did I save it properly? I doublechecked, and yes, I saw the heading and all the subnav items still there in the admin page. Mocking me. I could almost hear their laughter.
I mucked around with different combinations of relative and absolute URLs, and replicated the issue on other sub-sites. Sure enough, headings just refused to show up on anything except the root site.
Annoyed, I turned to the web for help, and finally dug up a post by Millerw that addressed the problem. Millerw suggested changing the data source for the navigation to "CurrentNavSiteMapProvider". I checked, and found that my source was "CombinedNavSiteMapProvider". Well, OK - worth a try!
I swapped sources, and checked out my site. Um...Not quite what I was looking for. Yes, I could now see the flyout on that subsite, but navigation to all other subsites was messed up. Everything was oriented vertically, I wasn't seeing all the subsites I wanted to, etc., etc.
Beginning to despair, I searched around and ended up on the Microsoft Enterprise Content Management Team blog (there's a mouthful), where they have a VERY good deep-dive on MOSS navigation. They actually had a lot of useful information on CombinedNavSiteMapProvider...And in their sample code, they had the following line:
TrimNonCurrentTypes="Heading"
AH-HAH, they're turning off 'heading' in the navigation - that MUST mean that CombinedNavSiteMapProvider can show headings!
Sure enough, by tweaking settings I was able to get the code below to work. Now I see all my horizontal top nav items just as before, with the addition of headers on my sub-sites.
I'll tell ya, if SharePoint is good for anything, it's good for keeping your ego in check.
The code:
<publishingnavigation><?xml:namespace prefix = publishingnavigation /><publishingnavigation:portalsitemapdatasource id="GlobalNavDataSource" treatstartingnodeascurrent="true" startingnodeoffset="0" startfromcurrentnode="true" showstartingnode="false" sitemapprovider="CombinedNavSiteMapProvider" runat="server"></publishingnavigation:portalsitemapdatasource>
Wednesday, March 3, 2010
With a Little Javascript From My Friends
Had an interesting problem with an XSLT data view today.
THE BACKGROUND: I needed to display a document library from another sub-site. I set up an XSLT data view to pull data from that document library. One of the fields I brought over was a multi-select lookup list. The values all came back in a semicolon-delimited format:
This;Is;What;The;Values;Looked;Like
Problem was, a number of rows had a very long list of tags in this column, and it wouldn't wrap on semi-colons...Although it WOULD wrap on hyphens. So sometimes you'd get:
A;Long;List;Of;Hypen-
Delimited-
Entries;That;Linebreak;Like;This
Massively ugly. I tried restricting the width of the column to try and force the data view to add line breaks, but that didn't work - the cells stayed very wide and wouldn't wrap where I wanted them to.
It took a bit of beating my head to my desk before I finally settled on the simplest solution. Convince CSS to wrap on a particular character? Count the number of chars, look for the next semicolon, and insert a page break after that? Rawrgh, too complicated!
Finally, I realized this could be tackled very simply. Word wrapping WAS working - just not on semicolons. What if we replaced them with a semicolon AND a space? This would keep things completely dynamic in case I wanted to change the column width in the future, too. But...how to make that happen?
In the end, I found two solutions. Some smart friends who know their way around Javascript helped put together the following:
Placed at the end of the page in the appropriate tag, this did take care of the problem.
In conjunction, I poked around for a solution on the XSL side. I finally found a suggestion from David Carlisle here on replacing characters in XSL. I pasted in his code, changed the from and to parameters, and pointed it at my data field. Voila!
I think it's neater to do this on the XSL side, but it was great to get another round of education on Javascript at the same time.
Back to the grind!
THE BACKGROUND: I needed to display a document library from another sub-site. I set up an XSLT data view to pull data from that document library. One of the fields I brought over was a multi-select lookup list. The values all came back in a semicolon-delimited format:
This;Is;What;The;Values;Looked;Like
Problem was, a number of rows had a very long list of tags in this column, and it wouldn't wrap on semi-colons...Although it WOULD wrap on hyphens. So sometimes you'd get:
A;Long;List;Of;Hypen-
Delimited-
Entries;That;Linebreak;Like;This
Massively ugly. I tried restricting the width of the column to try and force the data view to add line breaks, but that didn't work - the cells stayed very wide and wouldn't wrap where I wanted them to.
It took a bit of beating my head to my desk before I finally settled on the simplest solution. Convince CSS to wrap on a particular character? Count the number of chars, look for the next semicolon, and insert a page break after that? Rawrgh, too complicated!
Finally, I realized this could be tackled very simply. Word wrapping WAS working - just not on semicolons. What if we replaced them with a semicolon AND a space? This would keep things completely dynamic in case I wanted to change the column width in the future, too. But...how to make that happen?
In the end, I found two solutions. Some smart friends who know their way around Javascript helped put together the following:
function SeparateSemicolons( inStr )
{
var strArray = inStr.split(";");
return strArray.join("; ");
}
var clevervarname = document.getElementsByTagName("td");
var i = 0;
for (i=0; i < clevervarname.length; i++)
{
var place = clevervarname[i];
if( place.id == "TheTDWithSemicolons" )
{
place.innerHTML = SeparateSemicolons(place.innerHTML);
}
}
Placed at the end of the page in the appropriate tag, this did take care of the problem.
In conjunction, I poked around for a solution on the XSL side. I finally found a suggestion from David Carlisle here on replacing characters in XSL. I pasted in his code, changed the from and to parameters, and pointed it at my data field. Voila!
I think it's neater to do this on the XSL side, but it was great to get another round of education on Javascript at the same time.
Back to the grind!
Subscribe to:
Posts (Atom)