Month: November 2013

SSRS in SharePoint mode: Data alert & Subscribe are grayed out or disabled.

Standard

Environment: SQL Server 2012 reporting services 2012 w/ SharePoint 2010 Enterprise.

Problem:

For SSRS reports hosted on SharePoint, the Data Alerts & Subscribe are grayed out or disabled.

data alert subscribe ssrs sharepoint 2012

What do you have to do?

You need to upload a SSRS and for the data sources, you’ll have to store the credentials or no credentials.

It’s not ideal for user-level security (e.g. via Windows Authentication) setup on the data source side but the requirement of data alerts and email subscription dictate that you need to stored the credentials or not have credential requirements.

If the New Data Alert option is grayed, the report data source is configured to use integrated security credentials or prompt for credentials. To make the New Data Alert option available, you must update the data source to use stored credentials or no credentials. – http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx

Real-world story:
what we ended up doing at a client’s was to create a SharePoint library for “report subscriptions” which is hidden from end-users. We added a service account to the data source & we store the credentials of the service in the report used for report subscription. And IT “controls” who receives the email. So after a user submits a request to get emails, IT goes in the security database & see’s if a user is fit to receive the email or not. So not all users may get approval to receive the email. That was a solution that we had to take to stay compliant.

How to embed or integrate Power View reports into SharePoint pages?

Standard

Why do you want to do that?

One of the common tactic that you can consider to drive adoption of a Business Intelligence system is to integrate/embed the BI reports to the APPS/SITE that the users are already using. Don’t make your users come to you, go to them! As a part of that, I figured out a way to integrate/embed Power View in a Site that was used by existing user base.

You can integrate/embed Power View reports in SharePoint web Parts. Here’s How:

Power View sharepoint integrate embedImage Credit & For step by step tutorial, please refer to: Integrate Power View with SharePoint using web parts

Note:

Environment: SQL Server 2012 in SharePoint Integrated mode w/ Power View Activated for the site. Also, SharePoint Enterprise 2010.

 

PASS BA VC EVENT: Integrating SSRS with SharePoint on Nov 25th, Monday:

Standard

Topic: Integrating SSRS with SharePoint

Register TODAY! URL: bit.ly/PASSBAVC

Speaker: Kevin Goff

Topic Abstract:

Have you ever seen a SharePoint site that integrates reports from SSRS, and wonder how all the pieces fit? If so, this session is for you. I’ll cover the necessary integration/configuration steps for integrating SSRS 2008R2/SSRS 2012 with SharePoint 2010/2013, as well as deploying reports to a SharePoint location. Because different versions of SSRS integrate differently with the available versions of SharePoint, I’ll provide a feature matrix for specific version integrations. I’ll also show how to integrate SSRS reports into SharePoint/PerformancePoint Services dashboard pages, as well as how to schedule reports for delivery to SharePoint document libraries. You’ll also see the improvements from Microsoft that make reporting against SharePoint lists much easier than before. At the end, I’ll show 2 new features in SSRS: Data Alerts in SharePoint, along with the new SSRS data visualization tool, Power View

I hope to see you there!
Paras | VP of Marketing | PASS BA VC

 

Power Pivot DAX: Difference between two DATE values

Standard

Requirement:

Take difference between two data values.

Example:

EndDate: 11/20/2013

StartDate: 11/14/2013

DateDifference: 6

Let’s start writing some DAX!

Ok, seems simple, right? Try creating a measure DateDifference:=[EndDate]-[StartDate]

Did that work? NO? Does it return date?

Don’t worry, Here’s the solution. Try creating following DAX Measure:

[code language=”sql”]
DateDifference:=1.0*([EndDate]-[StartDate])
[/code]

That’s about it for this post. Here are some related Posts:
Calculate the difference between two dates in DAX
Q: How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)
NETWORKDAYS() Equivalent in PowerPivot?

Power Pivot: Casting DateTime to Date in SQL Server source query

Standard

DateTime columns can be tricky for analysis purposes. They don’t work well with Pivot Tables because of the time part, each value seems unique to the Pivot Table & it also creates problems while creating relationships with Date Dimensions. And so, It’s a common need to convert them to just Date before analyzing data & also a common need to create a relationship between the Date (and not DateTime) with Date Dimension Table.

So if it’s possible, I try to do the data type conversion in the source system query. If your source system is SQL Server, you could use this piece of code:

[code language=”sql”]
select [your-fields],cast([DateTime_Col] as date) as Date_Col from TableName
[/code]

Doing the data type conversion upfront in the source system query is a good thing to do. And I hope this is helpful.

Related Posts:
Strange date relationships with #PowerPivot
Date and Date/Time – Sneaky Data Types!

 

A quick note on how select @@version helps me while I’m T-SQL’ing:

Standard

As a part of developing ETL packages, sometimes, I’ve to write T-SQL queries to pull data from SQL server source systems. But before I start doing that, it’s always good to know the version/edition of the source system. Why? because it can determine whether a TSQL operators are available for me to use or not. Case in point, I had a requirements where I could have written a query that uses Pivot & UnPivot operators. So I write a query & it doesn’t work! I spent about 5 minutes trying to debug the code. The code seems OK to me. So I thought of checking the “version”. And there you go, client’s source system was running SQL Server 2000. So that meant, I couldn’t use the Pivot & UnPivot operators.

Select Version SQL serverThis was my quick note on how select @@version helps me while I’m TSQL’ing. Next time, I’ll probably check this first, before writing the code. That could save me few minutes 🙂

Example of an DAX Substring Equivalent:

Standard

DAX (Microsoft’ Data Analysis Expressions Language) does not have a Substring function but I needed something like that for the following problem:

I had domain/username as input and I needed to extract just the username part of the string.

Input format: domain/username

output format needed: username

Input column name: UserID

so here’s the DAX formula I used: RIGHT([UserID],LEN([UserID])-SEARCH(“”,[UserID]))

Note: The Formula is shown for demo purpose only, It may not work directly before making appropriate changes to the formula like making sure the column name is right & If the double quotes show problems, try deleting it & typing them back again.

Note that I combined some of the available DAX text functions to achieve what I was looking for. There might be other way to do this and I would be happy to learn about it too. Meanwhile, I hope if you reading this, this might give a good starting point while researching your DAX problem.

Quick Note about the Filters in the Power View Report:

Standard

You can put the fields as filters in two different areas:

1. View

2. Chart/Table

An end-user asked me the difference between the them today:

Filter area Power View Chart TableIt’s helpful to have more than two chart/table in a view (think of view as one excel single sheet) to understand the difference. So let’s say you have two charts in a single VIEW:

How do you filter data in both charts?

By putting the field in the VIEW area.

They can be called View-Level Filters

How do you filter data in just one of the chart/table?

By putting the field in the Table/Chart area.

They can be called Visualization-Level filters.

Official Resource:

Filtering, Highlighting, and Slicers in Power View

Nice series on WordPress Blog Statistics:

Standard

WordPress recently did a good series on how to analyze the data that’s available to you via WordPress Blog Stats tool. This series is great if you’ve a WordPress.com blog PLUS it’s a good read for any one in the data analytics role to learn how to write-up content like this.

1. Stats Wrangling I: Digging into Your Data
2. Stats Wrangling II: Days, Weeks, and Months
3. Stats Wrangling III: Top Posts and Pages
4. Stats Wrangling IV: Referrers and Clicks
5. Stats Wrangling V: The Words that Bring You Traffic

Along with WordPress Stats, I also use data from the Google Webmaster Tools. It’s a great way to see Keywords, Top posts & pages from a search engine point of view. It’s always good to have a healthy number of people searching for your content on search engines like Google.

I hope you take a look at how Data Analytics can help your Blog Grow. The series that WordPress ran focused on their platform but if you run your blog on other platform, this should give you a good sense of how to analyze the blog statistics.

PASS Business Analytics VC’s Online Event: “Power BI Info Management and Data Stewardship”

Standard

Power BI is an exciting new technology in the business analytics space from Microsoft. I’ve played with its current preview version & attended couple of sessions on Power BI at PASS Summit 2013. Based on my first impression, I noted down Problems that Power BI solves. Note that as of today, it’s in preview & so information around cost is not availale yet but I try to learn and understand as much as I can Today about how Power BI is going to help business users & power users in the future. As a part of that, I’m attending Business Analytics VC’s session on “Power BI Info Management and Data Stewardship” by Matthew Roche & Ofer Ashkenazi on Nov 7th 12 PM EST.

Topic: Power BI Info Management and Data Stewardship

Date & Time: Nov 7th 12 PM EST

Here’s the Link to register: http://bit.ly/PASSBAVC

Topic Abstract:

“Business intelligence tools continue to improve, letting users shorten their time to insight and take that insight to more devices in more places. But this evolution of BI doesn’t change one fundamental fact of information management: You can’t gain insight from data you can’t access.

In this session, Matthew Roche and Ofer Ashkenazi will introduce the role of the data steward and the self-service information management capabilities included in Power Query and Power BI for Office 365, focusing on how Power BI empowers business users to add value to the organization.”

Closing note:

I recently volunteered at Business Analytics VC as VP of Marketing, so it’s in my interest to spread word about the event but I would not spread word about something unless it gets me personally excited about it! 🙂

I hope to see you at the session and for some reason if you can not make it, we usually record sessions & so you can check out the meeting archives section of the PASS BA VC site after the event.