Unable to remove external user from personal SharePoint site (OneDrive for Business)

I needed to share some large files with a client and decided to use a shared folder on my OneDrive for Business. Using the Invite People option, I invited two users external to my company domain in Office 365 Enterprise. One of the users (Andrea) forwarded their invite to a colleague (Ray). Ray used his name and email address to accept the invite and that caused all kind of chaos in my OneDrive for Business site. Andrea couldn’t accept the invite to her email anymore, getting an error “We’re sorry, but email@address can’t be found in the my-personal.sharepoint.com directory.” Issue type: User not in directory.

After some time of searching online, I couldn’t find the resolution to my problem. I simply needed the location where the external users were located, but couldn’t find them anywhere.
– Site Settings > People and groups only shows the Viewers group that was empty.
– The O365 Admin centre > Users list only shows the internal users.
– Sites and document sharing in Admin > Service Settings doesn’t exist anymore. There was an option there to remove individual external users, but not anymore.
Sites and document sharing

So I put in a call with Microsoft and within 2 minutes got an answer.

Under OneDrive for Business > Site Settings
OneDrive for Business - Site Settings

Under People and groups
Site Settings - People and groups

All we see is the Viewers group, with no members. There is no way to arrive at the All People list using the navigation.
People and Groups - Viewers

We need to change the URL MembershipGroupID parameter to 0 in order to get the All People list. This is where we can modify/delete the external users by selecting the user and choosing the appropriate Action. We can then re-invite the user; with instructions not to forward their invite to other people. (IE might cache the old user, so make sure you clear all IE caching. Chrome is fine.)

People and Groups - All People

Quick way to enable PowerPivot tab

Sometimes PowerPivot/Excel crashes or hangs, and you need to kill the Excel process. When you open Excel again, you notice that the PowerPivot tab doesn’t show up.

Here’s a quick way to re-enable the PowerPivot tab.

1. First, we add the COM Add-Ins button to Quick Access Toolbar. This is done only once. Customize Quick Access Toolbar to add COM Add-Ins…

Picture1

2. From Developer Tab, choose COM Add-Ins… Add, OK.

Picture2

 

3. If the PowerPivot tab, for any reason, becomes unavailable; click on the COM Add-Ins button.

Picture3

4. Check the PowerPivot checkbox, click OK.

Picture4

 

PowerPivot KPI for Different Category Range

Suppose we have two simple related tables, ‘Table’ and ‘Service Code’. Main table contains transactions with accounts/dates/consumption amount by Service Code. The Service Code table contains the Min and Max ranges for service codes that will be used to analyze the average consumption. The Min/Max values differ for every service code and we want to create a KPI that will indicate if the average consumption falls within the range specified for the service code in question.

1. Import your data into the PowerPivot Model.

clip_image002

clip_image003

2. Create the relationships between the tables.

clip_image004

3. Create a calculated field for Average Consumption

clip_image005

4. Add two related columns for Min and Max values into the transaction table by using the PowerPivot RELATED() function. Note: this function will only work if you created the appropriate relationship in step 2. A good alternative for the RELATED() function is PowerPivot LOOKUPVALUE() if your tables are not related.

clip_image007

5. Add two calculated fields to find the minimum MIN value and maximum MAX value across the board using the MIN() and MAX() function on the respective columns.

clip_image009

6. Now for the fun part, create a calculated field that will provide us with a ‘Boolean’ value indicating if the Average Consumption (calculated field from step 3) falls between the Min and Max calculated fields from the previous step.

Consumption in range:=if([Average Consumption]=0,BLANK(),IF(AND([Average Consumption]>=[Service Code Min],[Average Consumption]<=[Service Code Max]),1,0))

clip_image011

7. Create KPI on the new calculated field.

clip_image013

8. Set the absolute value to 1 and move both min and max targets to 1 (that is 0.999 for the min value)

clip_image015

9. Cleanup the model by hiding the unnecessary columns/fields/table from client tools.

clip_image017

clip_image018

clip_image019

10. Create PivotTable.

clip_image020

11. The KPI Status column will provide the indicators showing if the average consumption falls in the ranges specified in the Service Code table for every service code.

clip_image022

SSIS – Create a dynamic XSD source file for XML Source task

Create a XML Schema Collection in your database.
USE [PortalETL]
GO

CREATE XML SCHEMA COLLECTION [dbo].[MyXmlFileSchemaCollection] AS
N'<xs:schema attributeFormDefault=”unqualified” elementFormDefault=”qualified” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
<xs:element name=”myName”>

</xs:element>
</xs:schema>’
GO
Define project parameters in your project for the source XML and XSD file location.

1

In your SSIS package, add a connection to the database where you created the schema collection from step 1.

Add a dataflow task to your package.

Choose an OLE DB source connection, pointing to your database, with Data access mode set to ‘SQL command’.

SELECT CAST(xml_schema_namespace(N’dbo’,N’MyXmlFileSchemaCollection’) AS nvarchar(max)) AS CatXsd

2

Your destination will be a Flat File with one column of type DT_NText.

3

Run the package in order to generate your XSD file in the location you specified in the project parameters.

Add the dataflow that will read your XML file.

4

Add a XML Source and point the XML and XSD location to the corresponding files. (we will make this dynamic later)

5

Click on the Columns section. This will generate all of your outputs from the XML file.

Add all the destinations to match your desired outputs.

6

Now we can set the flat file connections dynamically. Go back to the Control Flow, and right-click on the import task you just created to view the properties. Find the Expressions property and add two expressions to build a dynamic connection string for the following:
      [XML Source].[XMLData]
    [XML Source].[XMLSchemaDefinition]

7

Ensure to ‘evaluate expression’ to validate your paths are correct.

The second thing you need to do on the import task properties is to set the DelayValidation to true. Since you might be deploying this package to another server and running it for the first time, the xsd file being created in the first task might not be there.

Set the same expressions from above to the two flat file connections as well, on the ConnectionString property.

8

Now you can convert your connections to a ‘Project Connection’ (shared data source, in pre-SQL2012 world) by right-clicking on each and selecting ‘Convert to project connection’. This is helpful because you only need to manage one set of connection in your deployment. In SQL 2012, ‘they’ finally fixed the issue around using shared connections in a SQL Server deployment by introducing the Project Deployment Model, which is reeeally useful. Thanks Microsoft!

9

Run the package to ensure everything is copasetic. Now we’re ready to deploy to the Integration Services Catalogs on the server, which is also new in SQL 2012. There are a bunch of blog posts on this, so I’m not going to reinvent the wheel. 🙂 Here are some really good articles on that:

SSRS Custom Code to Get Textbox Name

Here’s a simple SSRS function that will get you the textbox name.

Public Function TextBoxName(ByVal meString As String) As String
Dim s As Integer = 19
Dim e As Integer = meString.IndexOf(“_TextBoxExprHost”) – 19
TextBoxName = meString.Substring(s,e)
End Function

Expression:
=Code.TextBoxName(Me.ToString)

‘Me’ is the object you are ‘in’… and Me.ToString returns the following:

“ReportExprHostImpl+[TextBoxName]_TextBoxExprHost+Paragraph00_ParagraphExprHost+TextRun00_TextRunExprHost”

Our custom code extracts the Textbox Name from the Me string.

————————————————————–



Report > Report Properties > Code

1

Add the expression where you need it… i.e. maybe you need to dynamically change the background color of all textboxes in a table, or set the hidden property.

2

Here’s the output:

3

SQL Date Range Function

Here is a function I came up with to return a range of dates. The function returns a table with FromDate and ToDate fields, while accepting a number of parameters.

@date
–date to be evaluated, usually getdate()
@period
–W=week, M=month, Q=quarter, Y=year => default: week
@ptd
–period to date, otherwise last full period => default: last full
@week_end_day
–specifies the end day of the week to report on
–MO=monday, TU=tuesday, WE=wednesday, TH=thursday, FR=friday, SA=saturday, SU=sunday

This can be useful when cross joining to a table in order to retrieve a dataset with a specific date range.

In this example we want to get all the process runs that were created last week (@ptd=0, last full period), with the week (@period=’W’) ending on Sunday (@week_end_day=’SU’). Since today is Saturday, the FromDate/ToDate will be the previous Monday April 2nd to Last Sunday April 8th.

SELECT *
FROM dbo.Run
CROSS JOIN dbo.fnGetDateRange(getdate(),’W’,0,’SU’)
WHERE Run.DateCreated BETWEEN FromDate AND ToDate

fn1

In this example, we are retrieving all process runs that were created this week (@ptd=1, period to date), with the week (@period=’W’) ending on Sunday (@week_end_day=’SU’). Since today is Saturday April 14th, the FromDate/ToDate will be the last Monday April 9th to today April 14th.

SELECT *
FROM dbo.cdmsRun
CROSS JOIN dbo.fnGetDateRange(getdate(),’W’,1,’SU’)
WHERE cdmsRun.DateCreated BETWEEN FromDate AND ToDate

fn2

You will notice a table variable @daysToSubtract; this is used instead of the DATEPART(weekday, @date) function… The return value of datepart, when using week or weekday, is set by using SET DATEFIRST. This cannot be done within the function itself, so I came up with a ‘dirty’ solution to get the appropriate number of days to subtract from the @date being passed in, in order to compute the FromDate.

———————————————————————————————

IF OBJECT_ID(‘dbo.fnGetDateRange’) IS NOT NULL
DROP FUNCTION dbo.fnGetDateRange
GO
CREATE FUNCTION dbo.fnGetDateRange
(@date DATE  — date to evaluate
,@period NCHAR(1) = ‘W’ — W=week, M=month, Q=quarter, Y=year => default: week
,@ptd BIT = 0  — period to date, otherwise last full period => default: last full
,@week_end_day NCHAR(2) = ‘SU’ — MO=monday, TU=tuesday, WE=wednesday, TH=thursday, FR=friday, SA=saturday, SU=sunday
)
RETURNS @t TABLE (FromDate DATE, ToDate DATE)
AS

BEGIN

DECLARE @FromDate DATE = NULL
DECLARE @ToDate DATE = NULL

IF (@period = ‘W’)
BEGIN

DECLARE @days INT
DECLARE @curr_day NVARCHAR(12) = DATENAME(WEEKDAY, @date)
DECLARE @daysToSubtract AS TABLE (EndDay NCHAR(2), MO INT, TU INT, WE INT, TH INT, FR INT, SA INT, SU INT)

INSERT INTO @daysToSubtract (EndDay,MO,TU,WE,TH,FR,SA,SU)
SELECT ‘MO’,0,-1,-2,-3,-4,-5,-6
UNION
SELECT ‘TU’,-6,0,-1,-2,-3,-4,-5
UNION
SELECT ‘WE’,-5,-6,0,-1,-2,-3,-4
UNION
SELECT ‘TH’,-4,-5,-6,0,-1,-2,-3
UNION
SELECT ‘FR’,-3,-4,-5,-6,0,-1,-2
UNION
SELECT ‘SA’,-2,-3,-4,-5,-6,0,-1
UNION
SELECT ‘SU’,-1,-2,-3,-4,-5,-6,0

SELECT @days =
CASE @curr_day
WHEN ‘Monday’ THEN MO
WHEN ‘Tuesday’ THEN TU
WHEN ‘Wednesday’ THEN WE
WHEN ‘Thursday’ THEN TH
WHEN ‘Friday’ THEN FR
WHEN ‘Saturday’ THEN SA
WHEN ‘Sunday’ THEN SU
END
FROM @daysToSubtract
WHERE EndDay = @week_end_day

SET @ToDate = DATEADD(DAY, @days, @date)
SET @FromDate = DATEADD(DAY, -6, @ToDate)

IF (@ptd = 1)
BEGIN
IF (@days = 0) SET @days = -6 ELSE SET @days = 1

SET @FromDate = DATEADD(DAY, @days, @ToDate)
SET @ToDate = @date
END
END

ELSE IF (@period = ‘M’)
BEGIN
IF (@ptd = 0)
BEGIN
SET @FromDate = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,@date)), 0)
SET @ToDate = DATEADD(ms,-2,DATEADD(mm, DATEDIFF(mm,0,@date), 0))
END
ELSE
BEGIN
SET @FromDate = DATEADD(mm, DATEDIFF(mm,0,@date), 0)
SET @ToDate = @date
END
END

ELSE IF (@period = ‘Q’)
BEGIN
IF (@ptd = 0)
BEGIN
SET @FromDate = DATEADD(qq, DATEDIFF(qq,0,DATEADD(qq,-1,@date)), 0)
SET @ToDate = DATEADD(ms,-2,DATEADD(qq, DATEDIFF(qq,0,@date), 0))
END
ELSE
BEGIN
SET @FromDate = DATEADD(qq, DATEDIFF(qq,0,@date), 0)
SET @ToDate = @date
END
END

ELSE IF (@period = ‘Y’)
BEGIN

IF (@ptd = 0)
BEGIN
SET @FromDate = DATEADD(yy, DATEDIFF(yy,0,DATEADD(yy,-1,@date)), 0)
SET @ToDate = DATEADD(ms,-2,DATEADD(yy, DATEDIFF(yy,0,@date), 0))
END
ELSE
BEGIN
SET @FromDate = DATEADD(yy, DATEDIFF(yy,0,@date), 0)
SET @ToDate = @date
END

END

INSERT INTO @t (FromDate, ToDate)
VALUES (@FromDate,@ToDate)

RETURN

END

———————————————————————————————

Here are the test results:

declare @date date = getdate()

select 1, *, ‘MTD’ as Period from dbo.fnGetDateRange(@date,’M’,1,default)
UNION
select 2, *, ‘PriorMonth’ as Period from dbo.fnGetDateRange(@date,’M’,0,default)
UNION
select 3, *, ‘QTD’ as Period from dbo.fnGetDateRange(@date,’Q’,1,default)
UNION
select 4, *, ‘PriorQuarter’ as Period from dbo.fnGetDateRange(@date,’Q’,0,default)
UNION
select 5, *, ‘YTD’ as Period from dbo.fnGetDateRange(@date,’Y’,1,default)
UNION
select 6, *, ‘PriorYear’ as Period from dbo.fnGetDateRange(@date,’Y’,0,default)
UNION
select 7, *, ‘WTD’ as Period from dbo.fnGetDateRange(@date,’W’,1,’SU’)
UNION
select 8, *, ‘PriorWeek’ as Period from dbo.fnGetDateRange(@date,’W’,0,’SU’)

fn3

SSRS Bullet List from an Array of Values

Thought I’d give this blogging thing a try; so here’s my first attempt at blogging a recent challenge I had in getting Reporting Services to display a bullet list from an array of values.

To get right down to it; say you wanted to convert the following list of skills into a bullet list:
Capture
||
||
/
Capture2
Navigate to Report Properties > Code and copy the following code into the Custom Code box. If you need to customize it even further; I have no objection.
Capture3

Public Shared Function SetBullet(ByVal Value As String, ByVal Delimiter As String) As String
””””””””””””””””””””””””””””””””’
‘ Declare and load array with Split FN using Delimiter parameter
””””””””””””””””””””””””””””””””’
Dim ListArray() As String
ListArray = Split(Value, Delimiter)
””””””””””””””””””””””””””””””””’
‘ Remove any ’empty’ values from array
””””””””””””””””””””””””””””””””’
Dim LastNonEmpty As Integer = -1
For i As Integer = 0 To ListArray.Length – 1
If ListArray(i) <> “” Then
LastNonEmpty += 1
ListArray(LastNonEmpty) = ListArray(i)
End If
Next
ReDim Preserve ListArray(LastNonEmpty)
””””””””””””””””””””””””””””””””’
‘ Apply bullet html markup to the list array
””””””””””””””””””””””””””””””””’
Dim RetValue As String = “”
Dim x As String = “”
RetValue = “<ul>”
For Each x In ListArray
RetValue = RetValue + “<li>” + x + “</li>”
Next
RetValue = RetValue + “</ul>”
Return RetValue
End Function

Back in the report, modify the expression for the field you want to apply bullets to and wrap the field with the following code:

=Code.SetBullet(Fields!Skills.Value, “~”)

Capture4

The first parameter in the SetBullet function is the field (or custom hard coded values) to which you want to apply the bullet style.The second parameter is the delimiter used in your list of values.

The code will handle any ’empty’ entries and remove them from the list in case you have one too many delimiters applied side-by-side or at the end of your list.

Once you’ve applied the function to your field, you need to change the markup type to convert the contents to HTML.

Click inside the item to highlight your new expression. Once highlighted, right-click > Placeholder Properties.

Capture5
Select the markup type to be ‘HTML – Interpret HTML tags as styles’. This is pretty much self explanatory.
Capture6
…and voila! You got yourself a bullet list from an array of values.
Capture2

Reporting Services doesn’t support a wide range of HTML code, but here are some documented features (per Microsoft):

The following is a complete list of tags that will render as HTML when defined as placeholder text:
  • Hyperlinks: <A href>
  • Fonts: <FONT>
  • Header, style and block elements: <H{n}>, <DIV>, <SPAN>,<P>, <DIV>, <LI>, <HN>
  • Text format: <B>, <I>, <U>, <S>
  • List handling: <OL>, <UL>, <LI>
Be careful with adding CSS attributes, as some may not work on all tags.
Here’s what Microsoft has to say about it.