Reporting Services Report links to SharePoint

Conditions:

• SQL Server 2008 R2 Reporting Services in SharePoint Integrated Mode
• Text Box Action set to “Go to URL”
• URL used is full path with query strings (e.g. ?ID=1) to a SharePoint destination

Symptoms:

• The URL has duplicated query strings

Solution:

• Update the URL to a path that is relative to the report’s site collection
  • Report location: http://sharepoint.com/reports/reportlibrary/myreport.rdl
    • http://sharepoint.com/reports is a site collection
  • Current Target URL: http://sharepoint.com/HR/lists/tasks/DispForm.aspx?ID=1
    • http://sharepoint.com/HR is a separate site collection
  • Updated Target URL: /../HR/lists/tasks/DispForm.aspx?ID=1

Further description of the problem can be found at the link below:

https://connect.microsoft.com/SQLServer/feedback/details/618500/reportviewer-in-sharepoint-action-url-parameters-are-duplicated

InfoPath Repeating Table Data

When Fields contained within Repeating tables are promoted to SharePoint, they only contain the first, last, or merged data. Unfortunately, this data is practically useless. The procedure below outlines how to delimit repeating data with semi-colons.

Prerequisites

• An hidden textbox used to contain the merged, delimited data.
• A repeating group with fields

Instructions

The hidden textbox will be populated with the below formula.

xdMath:Eval(xdMath:Eval(<Repeating Group>, 'concat(<Field1>, ";")'), "..")

Let’s break it down.

Repeating Group

xdMath:Eval(xdMath:Eval(<Repeating Group>, 'concat(<Field1>, ";")'), "..")

The Repeating Group is the group that contains the list of fields that are repeated. This parameter will return all the fields within the Repeating Group to the xdMath:Eval function for evaluation in the second parameter. In the “Insert Formula” window when creating a formula, you can use the “Insert Field or Group” button. Be sure to select the Repeating Group and not the field.

Field1

xdMath:Eval(xdMath:Eval(<Repeating Group>, 'concat(<Field1>, ";")'), "..")

Field1 is a field within the repeating group. But this value must contain the field name only, not the path to the field because the location was set by the first parameter of “Repeating Group”. For example, a field in InfoPath is usually referenced by “../my:Group1/my:RepeatField1”. But the Eval function would fail with this entry. Be sure to enter only “my:RepeatField1”. You may have to check the “Edit XPath (advanced)” box to make this change.

Concat Function

xdMath:Eval(xdMath:Eval(<Repeating Group>, 'concat(<Field1>, ";")'), "..")

The concat function above takes Field1 and concatenates it to the semicolon. This function is quoted in single quotes because it is a requirement in order to be evaluated by the xdMath:Eval function.

Inner xdMath:Eval function

xdMath:Eval(xdMath:Eval(<Repeating Group>, 'concat(<Field1>, ";")'), "..")

The inner xdMath:Eval function looks at the first repeating row of data in the Repeating Group, grabs the value of Field1 and concatenates it to a semicolon.

Outer xdMath:Eval function

xdMath:Eval(xdMath:Eval(<Repeating Group>, 'concat(<Field1>, ";")'), "..")

The two dots at the end of the formula is the second parameter for the outer  xdMath:Eval function. The two dots tells the xdMath:Eval function to go a level higher and return all instances of the Repeating Group. This is relevant because multiple instances of the Repeating Group will exist as new rows of data are inserted in the form. Then the outer xdMath:Eval function evaluates the inner xdMath:Eval function for all instances of the Repeating Group. This will then return all values in each row of data for Field1 concatenated with semi-colons.

Final output

xdMath:Eval(xdMath:Eval(../my:Group1, 'concat(my:Repeatedfield1, ";")'), "..")

The final output will be similar to this. Note that the “../my:Group1” value will change depending on how deeply the Repeating Group is nested.

Reporting Services links for Different Render Formats

Reporting Services links open in the same window as the report. To open the link in a new tab, the javascript window.open command can direct the link to a new window. But this doesn’t work in non-interactive views. Use the RenderFormat variable to determine when to use window.open.

NOTE: Refer to another post on this site about issues with duplicate query strings if these are used

=IIF(NOT(Globals!RenderFormat.IsInteractive) OR Globals!RenderFormat.Name=”MHTML”, “http://site.com/HR”, “javascript:void(window.open(‘http://site.com/HR” & “‘,’_blank’))”)

Sources: https://connect.microsoft.com/SQLServer/feedback/details/618500/reportviewer-in-sharepoint-action-url-parameters-are-duplicated
http://stackoverflow.com/questions/17149244/how-to-open-url-in-ssrs-report-in-webpart-to-new-window

SharePoint List Format Number

1. Open the view in SharePoint designer
2. Click on the number value
3. Near the bottom is a breadcrumb trail of XSL code
4. Click on the dropdown of the “<xsl:value-of>” tag and select “Tag Properties”
5. Add the format-number function in the format below:

   1. format-number($thisNode/@*[name()=current()/@Name], '$#,##0.00;-$#,##0.00')

6. Click OK and save the view.

Source: http://www.balestra.be/2011/11/how-to-format-numbers-with-commas-in-sharepoint-dataview-using-decimal-format-functionality.html

Concatenating a Field in Grouped Records

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT '; ' + [Name]
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable

The breakdown:

The STUFF simply gets rid of the beginning semi-colon and space by starting from the first character (second parameter), and replacing two characters (third parameter) from there with blank (fourth parameter).

The SELECT statement returns a dataset of Names

The FOR XML converts the dataset into XML

The PATH parameter sets the results to not return the wrapping element (<Table_Name>) around the resulting XML

The TYPE directive allows the result to be returned as xml data type

The .value method allows retrieval of the value of the XML result

The ‘(./text())[1]’,’VARCHAR(MAX)’ setting returns the complete dataset as text. [1] is specified to return the first and only record.

Code Source: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server

InfoPath and SSO with Secure Store

The purpose of this document is to restrict user access to data sources such as SQL. Instead of granting users direct access to SQL databases to execute queries initiated from InfoPath, the SQL data source can be setup to use Secure Store as an intermediary authentication authority. Here’s a brief walkthrough on the authentication process:

1. InfoPath connects to a data source via a Target Application ID specified in a UDCX file
2. Secure Store verifies whether the user has access to the Secure Store Target Application ID
3. Once verified, Secure Store hands over the credentials stored in the Target Application ID to InfoPath
4. InfoPath then queries the Data Source and returns the data to the form

Here are instructions on how to setup an InfoPath form to use Secure Store to connect to a Data Source.

Note: These procedures assume the Secure Store Service has already been setup, including generating a new key.

Create a new Secure Store Service target application, and then add the account information. To do this, follow these steps:

1. Open SharePoint 2010 Central Administrator.
2. Click Application Management, and then click Manage service applications.
3. Click Secure Store Service.
4. Click New. Follow the onscreen instructions to create a New Secure Store Target Application
5. Point to the name of target application that you just created, and then select Set Credentials from the drop-down list.
6. Enter the account information for the Secure Store Service target application, and then click OK.
7. Point to the name of target application that you just created, and then select Set Permissions from the drop-down list.
8. Type in “All Authenticated Users” and click Add
9. Grant Execute and Selectable in Clients for this group
   1. Most convenient way of controlling access to SQL backend is to grant all users access to the Target Application and restrict user access to the InfoPath form or other app that uses the Target Application.

Use the Secure Store Service target application in data connections. To do this, follow these steps:

1. Download a copy of the UDCX file that is specified for the data connection.
2. Use Notepad to open the UDCX file.
3. Locate the <udc:Authentication> section, and then replace it with the following:

<udc:Authentication>
 <udc:SSO AppId='TestSSOGroup' CredentialType='NTLM'/>
 </udc:Authentication>

1. Change the value of the AppId attribute to the Target Application ID string that you noted previously.
2. Save the file in Notepad.
3. Upload the updated UDCX file to the SharePoint data connections library.
4. Then, approve the file.

Import SharePoint 2007 list template into SharePoint 2010

When trying to import a list template form SharePoint 2007 into SharePoint 2010 you will receive an error like the following:

Error
Microsoft SharePoint Foundation version 3 templates are not supported in this version of the product
Correlation ID: {random guid}
Date and Time: ….

In order to get around this error you must update the ProductVersion element in the manifest.xml file by performing the following steps.

1. Export the list you want from SharePoint 2007 as a .STP file.
2. Rename the original .STP file to .CAB
3. Extract its manifest.xml to a local folder (let’s call it {workingfolder})
4. Edit the manifest.xml and search for the ProductVersion element. This should have a value of 3.
5. Change the value to 4.
6. Repackage the manifest.xml into a .CAB. You can do this by using makecab.exe in the c:\Windows\System32 folder.
   1. Syntax: makecab.exe {workingfolder}\manifest.xml {workingfolder}\{template-name}.cab
7. Change the generated cabinet’s extension from .CAB back to .STP and upload it to SP2010.

Hide Fields in Item View

To hide fields in the item view window, paste the below code into a Content Editor Web Part.

<script>
var int=setInterval(
function(){
var elem = document.getElementsByTagName("td");
for (var i=0;i<elem.length;i++)
{
var fieldval =elem[i].innerHTML;
var fieldid = elem[i].id

if((fieldval.indexOf("Title")>0 || fieldval.indexOf("Modified")>0) && fieldid=="SPFieldLookup")
{
if(elem[i].parentNode.style.display=="none")
{
clearinterval(int);
}
elem[i].parentNode.style.display="none";
};

};
},1000)
</script>​​​​

Print Floating Window view of Item

The floating item view in SharePoint 2010 causes issue when printing the page via the Browser’s Print command. The text behind the floating wndow is printed too. Place the below code in a Content Editor Web Part to display a Print button that will only print the floating window content.

<style>
@media Print
{
BODY {
FONT-SIZE: 14pt; LINE-HEIGHT: 24px; color: black;
}
TD {
FONT-SIZE: 14pt; LINE-HEIGHT: 24px; color: black;
}
DIV.printbutton {
DISPLAY: none
}
}
</style>
<div class=”printbutton”>
<input onclick=”window.print();return false;” type=”button” value=”Print page”/>
 
</div>

Unable to Browse Document Library with Windows Explorer

When a user is only granted access to a library or list, the user account will not be able to view the contents of the list or library through Windows Explorer. Follow the below instructions to remedy this.

Create a new Permission Level called “Browse Site” with the below permissions:

1. 1. Under Site Permissions, add
      1. View Pages
      2. Use Remote Interfaces
      3. Use Client Integration Features
      4. Open

2. Grant your desired group permission to a list or library.
3. Go to the Site Level permission page. The newly added group usually appears with a permission level called “Limited Access” at the Site Level.
4. Select the desired group(s) and add the new permission level Browse Site.

After extensive testing, this new permission level was found to not grant access to any other site lists or libraries, EXCEPT ASPX web pages per the “View Pages” permission. If there are ASPX web pages that need to be secure, they need to be individually modified to remove inheritance of the Browse Site permission.