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.

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 Analysis Services MDX: Sum of Last N Years, Quarters, Months, Periods.

Standard

Problem Statement:

create a calculated measure in MDX to compute last 3 years Sales Amount

Solution:

Here’s the MDX code to calculate sum of sales amount for last three calendar years:

[code language=”sql” gator=”false”]

with member [measures].[Sales Amount Last Three Years]
as
SUM({[Date].[Calendar].CurrentMember:[Date].[Calendar].CurrentMember.PrevMember.PrevMember},[Measures].[Sales Amount])
select
[measures].[Sales Amount Last Three Years] on 0
from
[Adventure Works]
where
[Date].[Calendar].[Calendar Year].&[2008]

[/code]

Results:

MDX Caculated Measure Sum of sales amout LAST N PERIODS

I hope that gives you a good starting point to calculate the Sum of Measure during Last N Periods.

SQL Server Reporting Services: How to display Multi Select parameter selected on the report?

Standard

Here’s the problem statement:

You’ve a multi select parameter on your SSRS report and you need a way to display that on that the SSRS report. (Note: It’s a good practice because when the report is printed out the parameter values that were selected gets displayed and the consumer know that’s right off the bat.) – So how do you do that?

You added an expression on your report and double clicked on the parameter. If you do so, something like this will show up on your expression values: =Parameters!ProductCategory.Value(0) and after you changed Value to Label “=Parameters!ProductCategory.Label(0)” – here’s what you get on the report:

Multi Select parameter SSRS display

Solution:

I learned this trick via this stackoverflow thread.

Go back to expression and set the value of the expression to:

[code language=”sql” gator=”false”]
=Join(Parameters!ProductCategory.Label,",")
[/code]

And after I did that, it fixed the problem!

Multi Select parameter SSRS display Join Value Label fixed

I hope this gives a good starting point!

Paras Doshi

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

Standard

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:
[code language=”sql” gator=”false”]

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]

[/code]

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.

SSRS: How do I know where a report item is on the report?

Standard

Have you ever got a SSRS message saying something like textbox107 refers to an invalid measure or measure used in gaugepanel7 is not valid, etc? The problem is how do you find that “Textbox107” report item on your SSRS report? After all, you might have lots of text boxes on your report.

Here’s how you can know where a report item is on the report:

Step 1: Go to view > Properties Window

Step 2: Open the report.

Step 3: if you select a report item then it’s going to show the properties of the report item in the properties pane.

SSRS Properties Window Report ItemStep 4: And you can select a report item by it’s name like textbox107 or gaugepanel11 for which you are seeing an error, that way you can find the location of the report item and troubleshoot it!

SSRS Properties Window Report Item list

 Conclusion:

I hope this tip helps you find the report item on your SSRS reports.

How to use TSQL checksum to compare data between two tables?

Standard

In any BI project, data validation plays an important part. You want to make sure that the data is right! usually business helps in this validation. As a developer, you might also want to do some preliminary data validation. One of the techniques that I’ve learned recently is to use TSQL checksum to compare data between two tables. In this post , I’ll describe the technique & post a pseudo code.

we’ll create a pseudo code to compare all columns but you should be able to use that to tweak that if you need it.

1) Run checksum(*) on Tables:

On Table1:

select checksum(*) as chktb1 from table1
go

On Table 2:

select checksum(*) as chktb2 from table2
go

At this point, you should get two result sets each populated by checksum values computer over all columns since you passed * in the checksum function.

2) Now let’s join these tables & look at rows w/ different checksum: (in other words, it is going to list all rows that are different between table1 & table2)

select * from
(
select checksum(*) as chktb1 from table1
) as tb1
left join
(
select checksum(*) as chktb2 from table2
) as tb2
on tb1.someid=tb2.someid /* you can have more ids */
where tb1.chktb1 != tb2.chktb2

3) You can add individual column now to see what changed:

select * from
(
select checksum(*) as chktb1, columnname1, columnname2 from table1
) as tb1
left join
(
select checksum(*) as chktb2, columnname1, columnname2 from table2
) as tb2
on tb1.someid=tb2.someid
where tb1.chktb1 != tb2.chktb2

Conclusion:
I hope this helps especially if you don’t have rights to install 3rd party tools on your dev machine.