SQL Server Reporting Services

SQL Server Reporting services: How to display “There are NO rows” message?

Standard

Problem:

You have a SQL Server reporting services (SSRS) report that has a table which displays some records — but sometimes it can have NO rows; In that case, how to display “There are No rows” message so that it doesn’t confuse the consumer.

Solution:

  1. Open the report in SQL Server Data Tools and go to the “design” tab of your SSRS report
  2. Select your table (do NOT select a cell inside a table. Make sure that the table is selected) SQL Server reporting services NO data rows message
  3. While the “table” is selected, Go the Properties section OR you can use F4
  4. Inside the Properties section, find “No Rows” section and you should see a NoRowsMessage property:SQL Server reporting services NO data rows message v2
  5. Go to the preview tab to make sure it’s working and you should be ready to deploy the change!

That’s it! Hope that helps.

Official reference:  https://msdn.microsoft.com/en-us/library/dd220407.aspx

Author: Paras Doshi

SQL Server Reporting Services Tip: How to capitalize just the first letter of text?

Standard

Attention to detail is a key in creating SSRS reports/dashboards that look like a work of a professional; To that end, here’s a tip: How to capitalize the first letter in your string? In other words, how to Camel Case the Text?

Here’s the function that you can use in your SSRS Expressions:

[code language=”SQL”]

StrConv("hello world",3)

[/code]

OR

[code language=”SQL”]
StrConv("hello world",vbProperCase)
[/code]

InputFunctionOutput
hello worldStrConv(“hello world”,3)Hello World

I hope that helps!

SQL Server Reporting Services: How to Solve Divide by Zero Errors?

Standard

Problem:

you thought you wrote an if condition to handle “divide by zero” errors in SSRS but still get the run time error?

Example. my expression is:

[code language=”sql”]
=IIF(Fields!denominator.Value=0,0,
CINT(Fields!numerator.Value/Fields!denominator.Value))
[/code]

And I still get the error:
Divide by Zero Error SSRS

Note: if you do not have integers to divide and you allow data conversion to double, it would show on SSRS as “infinity” instead of “#Error”.

Solution:

Let’s understand why does it do it?

So even though you are using IIF condition, the vb IIF condition evaluates every function in every expression before it renders and that’s why the “False” condition that I have in my condition gets run which results in #Error.

Ok, armed with that knowledge, let’s solve the problem.

So here’s a modified version of the expression, have a look:

See what we did there! We added one more IIF condition in the “false” condition of the parent IIF.

[code language=”sql”]
=IIF(Fields!denominator.Value=0,0,
CINT(Fields!numerator.Value
/IIF(Fields!denominator.Value<>0,Fields!denominator.Value,1)))
[/code]

That should solve the problem:
Divide by zero fix customer code ssrs

There’s also an alternative to this especially if you have a lot of expressions that does this. You can write your custom code and call it SSRSDIVIDE or you can come up with a better name! Here’s a post that talks about how to do that: http://salvoz.com/blog/2011/11/25/ssrs-checking-for-divide-by-zero-using-custom-code/

Conclusion:
In this post, we saw how to solve the divide by zero errors in SSRS.

#sqlpass #msbi online event: “The Accidental Report Designer: Data Visualization Best Practices in #SSRS”

Standard

PASS Business Analytics VC presents “The Accidental Report Designer: Data Visualization Best Practices in SSRS” by Meagan Longoria. Here are the meeting details:

Date & Time: Thu, June 19 2014 12:00 Eastern Daylight Time

RSVP URL:

http://bit.ly/PASSBAVC061914

Session Abstract:
Whether you are a DBA, a developer, or an analyst, there is a good chance that you will have to create reports for coworkers, company executives, or clients. As with any UI design, careful consideration should be given to your data visualization design to ensure you are effectively communicating the intended message and providing a good user experience. While the principles are applicable across reporting platforms and tools, this session will contain demos and examples implemented in Reporting Services using SQL Server Data Tools. Learn how to make information (not just data) the focus of your report and provide your audience with something better than just shiny!

Session Level:
Intermediate

Speaker BIO:
Meagan Longoria is a BI consultant with Valorem Consulting in Kansas City, Missouri. She has over 6 years of experience with the SQL Server BI stack and enjoys sharing her knowledge and experiences at SQL Saturdays. She is also one of the coordinators for SQL Saturday in Kansas City.
Contact URL: http://datasavvy.wordpress.com

See you there!

Paras Doshi
Chapter Co-Leader, PASS BA VC

SQL Server reporting services: How to use the Split function in the SSRS expressions to get sub-string?

Standard

Problem Statement:
How do you use SQL Server Reporting Services (SSRS) expression to get sub-string from the inputted text?

Solution:
I am going to show you few SSRS expressions that you can use in your SSRS reports:

SSRS SQL Server Reporting Services Expression SPLIT

Here’s the same in a text:

Input:SSRS Expression used:Output:
[Date].[Fiscal Year].&[2008]Split(Parameters!DateFiscalYear.Value,”&”)(1)[2008]
[Date].[Fiscal Year].&[2008]Split(Parameters!DateFiscalYear.Value, “.”)(2)&[2008]
[Date].[Fiscal Year].&[2008]Split(Split(Split(Parameters!DateFiscalYear.Value, “&”)(1),”[“)(1),”]”)(0)2008
[Date].[Fiscal Year].&[2008]Parameters!DateFiscalYear.Value.Split(“&”)(1)[2008]

Conclusion:
In this post, you saw how to use the split function in SQL Server Reporting Services (SSRS) expressions to get a sub-string.

SQL Server reporting services: How to add a seconday axis on a chart?

Standard

Problem Statement:

Need a chart on a SQL Server Reporting Services chart with a secondary axes.

Need measure #2 on a secondary axes:

SQL Server Reporting Services Secondary AxesSolution:

1. In the Design Mode > Open the Chart Date Pane > For Measure #2, navigate to Series properties:

SQL Server Reporting Services Series Properties Chart Data2. From the Series Properties Dialog box, navigate to “Axes and Chart Area” and choose the option “Secondary” under vertical axes.

SSRS Secondary Axis Axes3. Click OK to go back to the design mode and preview the report to test it:

SSRS SQL SERVER SECONDARY AXIS LINE CHART BAR CHART

4. Make sure to rename the axis title of the secondary axis, format the number to make it consistent with the report layout.

Note:

SSRS Version needed: SQL Server 2008 and above.

Conclusion:

In this post, you saw how to add a secondary axis on a SSRS Chart.

SSAS Tabular Model: How to change the query used for tables?

Standard

Problem:

How do I change the query that’s being used to populate tables in SSAS Tabular Model?

Solution:

Here are the steps that you need to follow:

1. After you’ve the solution open in SQL Server Data Tools, go to menu > Table Properties

SSAS Tabular model change query

2. On the Edit Table Properties, you can change the query. you can also change the tables if that’s what you have used using the “switch to” box on the right side of the dialog box.

SSAS Tabular model change table query connection

3. If you need to change the server name or instance, used then you will need to modify the connection, for that go back to menu bar and click on Model > Existing Connections. you should be able to edit the connection from here:

SSAS Tabular model edit query table connectionConclusion:

In this post, you saw how to change the query used for the tables in SSAS Tabular models.

SQL Server reporting services: How to Add Trademark special symbol in Text?

Standard

Problem Statement:

The goal of this blog post is to share a quick tip on SQL Server Reporting Services (SSRS): How to add Trademark TM special symbol in Text?

Solution:

In SSRS, you can put following code in the Expressions:

[code language=”SQL”]

="Text"& ChrW(8482) &" Dashboard"

[/code]

Output:
SQL Server Reporting Services SSRS Trademark special symbol

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

Standard

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?

Standard

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.