SQL Server Reporting Services: How to add Interactive Sorting to a Table with no groups?

Sorting is one of the common requirements in a SSRS report that business users ask for, even if they don’t ask – it’s great to have interactive sorting enabled at places where it makes sense. If you’ve a Table with no groups, here’s how you can enable interactive sorting on the table:

1. Right Click on the Column Header:

ssrs interactive sorting column reporting

2. Click on “Text Properties” and navigate to “Interactive sorting”.

3. Check the box “Enable interactive sorting on this text box“. Also since there are no groups, we want to sort “detail rows“. Also, you’ll need to specify the sort by column. For demo, I picked Sales_Amount but you could also have an expression here if you wanted:

Interactive Sorting Text Properties Detail rows reporting4. Click on OK. And run the report – you should see the interactive sorting buttons on the Sales Amount column. Users should be able to sort the data in ascending/descending order using this feature:

Interactive SSRS sorting enabledThat’s about it for this post. I hope this helps!

SQL Server reporting services: How to customize the Legend Text on a chart?

Problem Statement:

How do you customize the Legend Text on a chart? by default, it’s going to show you the name that you have in the Data Set, but sometimes that’s not what you want to display so how do you change the name?

It’s really useful when you don’t want to change the name of field in the dataset or when you are using an expression as an item on the chart and you need to display a business user friendly name on the report:

SSRS Custom Legend TextSolution:

1. Go to Chart Data Properties:

2. To change the Legend Text of “Sales Amount CY” – Navigate to series properties of the “Sales Amount CY” series:

SSRS Chart Series Legend Properties

3. Go to “Legend” and enter the Text in the “Custom Legend Text“:

SSRS Custom Legend Text Series Properties legend custom4. You can customize text for all legends on your report by navigating to the series properties.

SSRS Custom Legend Text Change Name

Conclusion:

In this post, I walked you through how you can customize the legend name/text in SQL Server Reporting Services.

SQL Server Reporting Services: What are Drill Down & Drill Through reports?

Analyzing data at summary level is great! it’s gives business users information that they need at summary level to make informed business decisions. But often, they also need to look at details. To satisfy this business requirement, there are two types of reports that you can create in SSRS to show detail level information

1. Drill Down Report.

2. Drill Through report.

So what is the difference in their layout?

Drill Down report:

Note the presence of Toggle Items. + for expand. and – for collapse. As you can see, this makes it easy to go a level below and see the details.

SQL Server Reporting services Drill Down reportDrill Through report:

Note the presence of a formatted text. It’s shown like a hyper linked text. For this report, To see Product level detail of Bikes Category, click on Sales Amount for Bikes category & it would take you a drill through report that shows the details:

SQL Server Drill Through Reporting ServicesSo when to choose Drill Down report vs Drill Through report?

They have different feature & serves different purposes. But there are two things that help me decide in majority of the cases:

1. Is the performance of Drill Down report acceptable (especially if data is surfaced through stored-procedures/queries hitting the data source without a semantic layer in between)?

Yes? Great. If not, then try drill through reports.

2. Are there too many (30-40+) data items show if a user drill downs? Yes? Try drill through.

Related resources:
Drillthrough, Drilldown, Subreports, and Nested Data Regions (Report Builder and SSRS)

Author
Paras Doshi

Questions Power Users Ask about Excel: #3 of N

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #3 of N:

a. How to remove Grand Totals & Sub Totals?

1. Select the Pivot Table.

2. From the toolbar, go to Pivot Table Tools > Design > Sub Totals > Do not Show Sub Totals.

you can also choose to remove it just for rows or columns. it depends on your requirement on how to layout data.

Pivot Tables Sub Totals How to remove

Similarly, you can remove Grand Totals:

Pivot Tables Turn off Grand TotalsAlternatively, you can remove grant totals from Pivot Table options:

Grant Totals Pivot Table Options

There’s also an alternate method for removing subtotals. Put your mouse over the hierarchy level for which you do not need subtotals. Right click > remove subtotals “field name”. But you’ll have to do that for all hierarchy levels if you need to remove all subtotals.

b. How to configure Automatic Data Refresh?

A common question asked by power user is how to make sure that the excel file is pulling the latest data from the cube? Good news for them is that Excel files that are configured to connect to a data source like SSAS cube can be configured to automatically refresh. Here’s how:

1. From the toolbar, Go to Data > Connections

Excel Data Connections Cube Properties2. Select the connection that interested for automatic data refresh > properties

Excel SSAS Cube Property connection3. From here, you can configure the file to do an automatic data refresh every xyz minute and/or configure the file to refresh data every time you open it.

Excel Data Refresh Cubeclick ok when you’re done and close the workbook connections after you’ve configured the data sources that you needed for automatic data refresh.

Conclusion:

In this post, we saw how to remove grand totals & subtotals and, how to configure the automatic data refresh.

In previous articles we saw:

#1: How to sort data? How to add slicers? How to change Pivot table Layout

#2: How to add calculated measures?

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

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.

 

Quick Note about the Filters in the Power View Report:

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

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

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.

A Note about “Edit Report” button seen on Power View reports in SharePoint:

Power View reports that are hosted on SharePoint has “Edit report” which you cannot hide:

Power View Edit report buttonWe can’t hide this button but how do you give someone “read-only” access to Power View?

To do so, you’ll have to host the Power View report in a SharePoint library where a user has read-only access. When a user opens the report, he/she could click on edit report & make changes to Power View report but they won’t be able to save the report instead they’ll see an error:

Power View Access Denied

Official resource: http://office.microsoft.com/en-us/excel-help/power-view-in-sharepoint-server-create-save-and-print-reports-HA102834736.aspx

[update]:

Also, check out this MSDN forum thread, it describes a solution to hide the “toolbar”: http://social.technet.microsoft.com/Forums/sharepoint/en-US/cec0feff-1055-4b53-a5ae-00373abdabdd/how-to-disable-edit-report-option-in-powerview-report-in-sharepoint-2013?forum=sharepointdevelopment

[Notes] Atlanta Business Intelligence User Group – Mapping Data in Power View & Tableau

Here are my notes from the Atlanta BI user group that I attended today:

Topic: Head-to-Head on Maps: Mapping Twitter in Tableau and Power View

Power View

Tableau

Setup/Installation? Office 2013 [Professional Plus editions], SharePoint 2010 onwards [Enterprise Editions] Public, Online, Desktop, Server
Licensing Get applicable office/SharePoint license Tableau Public is Free.Tableau Online is $500 per user/year.

Tableau Server has Per-User or Per-Server-Core model

*Note that if you’re using Tableau server’s per-user model the cost is $1000 per named user/consumer (min. 10 users) and $2000 seat for 1 developer.

**Contact Tableau Sales. Please don’t evaluate the product based on the information provided here as the information might change in future

Data Sources [SharePoint] Power Pivot Model, Analysis Service Cubes[Excel 2013] You can connect to data sources that excel supports and then create power view report on top of it. Tableau Public Supports Excel, Access & Text filesOther version of Tableau can connect to a variety of data sources.
Product Category Ad-hoc reporting a.k.a Self Service Business Intelligence Ad-hoc reporting
Ease of use easy easy
Visually Compelling? Yes Yes
Custom Shape Files No Limited
Motion effect No Yes
Product maturity Power View is new to the game Tableau is a leader in data visualization space
Additional Notes Power Map is an add-in by Microsoft that focuses on “Mapping”. It’s part of the newly announced Power BI suite. -

What Problems does Power BI solve?

I’m at PASS Summit 13 this week and I’m seeing nice amount of excitement among Business Intelligence Pros about “Power BI” so I thought I would post a brief post about problems that Power BI addresses:

#1: Mobile BI:

  • The Visualizations that you’ll publish to Power BI sites would use the HTML 5 rendering & hence the support for Mobile BI.
  • There’s also a native Microsoft Power BI app for Windows 8 so you can use surface tablets for Mobile BI. IOs (apple) or Android native apps have NOT been announced yet.

#2: An end-to-end self-service suite of tools for Power Users:

  • Users will be enabled to search, analyze and visualize data using Power Query, Power Pivot & Power View. Plus it allows them a way to collaborate with each other.

#3: Easier way to search for data that’s available inside & outside for organization:

  • One of the key themes of “Power BI” has been easier discovery of data that’s available to you to analyze.
  • This is really important from an adoption standpoint because with the technologies that we have today, we can’t enable power users to search for “data-sets”. Power BI enables IT to publish Data Catalogs which I imagine would make it easier for power users to search & connect to data sets & start analyzing!

#4: cool tools that people *want* to use it.

  • Power BI has rich user experience.
  • Users can build cool visualizations & create some business value
  • Since this is a “self-service” suite, it seems to be designed as a user-friendly set of tools. This is important because if a user is “confused” or “over whelmed” then they are not going to use the tool & find something else.

#5: Gateway to the future:

  • The Human-computer interaction is evolving. Over the past couple of years, we’ve seen tools like Siri (apple audio powered personal assistant) which allows users to use “Natural Language” to interact with computers
  • Power BI has a tools called “Q&A” that allows users to do business analysis using “Natural Language”. I don’t know the maturity of the current offering but I’m excited about the possibilities that this could offer in future!
  • Imagine a computer (in some amazing futuristic form) and you say to it “sales trend in north america region during past 12 months” and it gives a you nice trend chart that you can use to start analysis.

Cost-benefit analysis of this cloud-powered suite of tools*

  • (Book Mark for future editing: The cost of the tool & its general availability is not announced, so I didn’t talk about the cost-benefits that we might see so I’ll defer this analysis until after the details are announced)

What do you think? What are the problems and pain-points that Power BI is trying to solve?