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.

SSAS MDX: How to hide null values for months in current fiscal year while comparing the value against last years numbers?

Let me explain the purpose of the blog post via a diagram:

ssas mdx nonempty month sales amount non empty

The current year selected was 2014 and since it’s 30 MAR 2014 today, it’s not showing data for April and onwards. But for 2013, it’s showing all months. How to hide null values for months in current fiscal year and at the same time show only applicable months for Previous Year? Here’s the pseudo code to get you started:


WITH MEMBER [Measures].[Prior Year Sales Amount] AS 
([Measures].[Sales Amount],[Fiscal Year].CurrentMember.PrevMember)

SELECT
{[Measures].[Sales Amount],[Measures].[Prior Year Sales Amount]} on columns,

NONEMPTY ([Month].[Month],
[Measures].[Sales Amount]) on rows

FROM [CUBENAME]

WHERE 
[Fiscal Year].&[2014]

Note:
1) Do you see the use of NONEMPTY function? It hides the null values in the [measures].[sales amount].
Resource: Non Empty vs NONEMPTY function.

2) do not forget the { } around the measures that you’re putting on column axis. Otherwise you may see an error like “Parser: The statement dialect could not be resolved due to ambiguity.”

I hope this gives you a good starting point.

PASS BA VC: Analyzing Road Traffic Accident Data in #PowerBI by Chris Webb #sqlpass

Join PASS Business Analytics VC on Thu, Mar 27 2014 12:00 (GMT-05:00) Eastern Time (US & Canada) for “Analyzing Road Traffic Accident Data in Power BI” by Chris Webb. In this one hour session, Chris will spend an hour going over the details on how he developed his amazing demo for the Power BI contest (see here & here).

here’s the link to Register: http://bit.ly/SQLPASSBAVC

Demo:

We hope to see you there!

Paras Doshi
VP – Marketing, PASS BA VC

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

#sqlpass business analytics VC event tommorow 16th Jan! “Analytics at Cisco”

Featured Presentation:

Analytics at Cisco

Date:  16th Jan. Time: 12 Noon EST.

Arun Saksena, Director, Data Science and Analytics Cisco Consulting Services

Big Data and Analytics is an important component of Cisco’s strategy to become the #1 IT company in the world. Cisco is taking a unique, network-differentiated approach to Big Data and Analytics. I will briefly discuss Cisco’s strategy and approach to Data Analytics, and describe the Analytics Services that we offer to customers. I will then discuss our approach to Data Science, and the skills we are developing within Cisco in the area of Data Science and Analytics. I’ll also discuss the role of a Data Scientist on cross-functional teams, and close off by sharing a perspective on how this role is expected to evolve in the next few years.

URL: http://bit.ly/PASSBAVC

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.

 

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

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

 

Example of an DAX Substring Equivalent:

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.