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 Analysis services MDX: How to write a query that works as LIKE operator in SQL?

Standard

Problem Statement:

In MDX, How to search for a sub-string using something similar to LIKE operator in SQL?

Solution:

here’s an example using Contoso Sales Cube, please use this to get started for your scenario:

Note the use of INSTR function.


select
[Measures].[Sales Amount]
on 0,
Filter(
[Geography].[Region Country Name].[Region Country Name]
,instr([Geography].[Region Country Name].CURRENTMEMBER.MEMBER_CAPTION,'United')>0)
on 1
from
[Sales]

SSAS MDX analysis services like SQL

Also,
if you are looking for NOT LIKE then replace >0 with =0 and you should get the desired results.

Conclusion:
In this post, you saw how to write a MDX query that does sub-string search for you similar to LIKE and NOT LIKE operators in SQL.

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.

PASS Business Analytics VC Meeting–May 29

Standard

RSVP URL: http://bit.ly/PASSBAVC052914

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

PASS Business Analytics Conference – Day #2 Keynote

Standard

what an inspiring keynote by David McCandless! Here are some the things that I noted:

1. Play with your data, get your hands dirty

“Data is the new Soil” – David McCandless

2. set the context while communicating what data is trying to tell.
Example: China has the largest army but compared it to their population they are 120ish! So measuring just the army size does not give you necessary context & sometimes it can meaningless

3. Data visualizations help you combine the language of the mind (numbers) with language of the eye (visuals)! Humans are better at processing visuals.

4. Make sure your data is true & keep your personal bias out of analysis.

5. Other than making sure that the data integrity is achieved in visuals, they should be interesting and functional.

6. Design is about removing unwanted things & distilling the visual to it’s functional essence.

You can check out David’s work at http://www.informationisbeautiful.net/
Information is beautiful