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

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:


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]

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?

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:

=Join(Parameters!ProductCategory.Label,",")

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?

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.

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

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?

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.

Quick note on evolution of Business Intelligence & Microsoft’s vision for BI space:

I attended “Enabling Familiar, Powerful Business Intelligence hosted by PASS BA VC last week & I got to listen to Microsoft where they shared their vision for the BI space, so I thought of posting this quick note about it:

“Corporate BI” has been around for may years. This space has established players like Microsoft, SAP, IBM, Oracle. But in recent times, “Self Service BI” space has been gaining momentum. Players like Qlikview & Tableau that lead the Self Service BI space have been ranked as leaders in the Gartner 2014 magic quadrant. Microsoft has also been making serious advancements in this space since last few years & with their latest offering called “Power BI” they have shown that they putting their bets on Self Service BI space. So, as Microsoft said in the event, they view themselves as the only player that offers a full suite of Corporate BI as well as Self Service BI:

Evolution of BI

you can watch the recorded session here: http://www.youtube.com/watch?v=0yKhxSPlykg

TSQL Script: How to get list of all tables or view in a database?

I was documenting the list of tables/views in a data mart & staging databases & I found the following scripts useful:

TSQL To get list of all tables:

SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE='BASE TABLE'

TSQL To get list of all views:

SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE='VIEW'

Alternatives (for SQL 2005 onwards):

SELECT * FROM SYS.TABLES

SSAS MDX: Alias for a Measure Name

I was working on a requirement that needed me to write MDX code that had an alias for a measure name. If it was SQL, you could just do something like select [column name] as [new column name] from [table name] but in MDX it’s not straight forward. What you could do though is declare members in your MDX statement. so to alias a measure name & your code would look something like:


with member [measures].[new name] as [measures].[old name]
select
{[measures].[new name]} ON COLUMNS,
{[Date].[Calendar Hierarchy].[ALL]} ON ROWS
FROM [Cube Name]

if you’re executing MDX code using OpenQuery, then you’ve one other option, something like this would work:


Select "[measures].[old name]" as "[measures].[new name]" from openquery(ConnectionName,'select {[measures].[old name]} ON COLUMNS from [cube name]')

Note: After taking inputs from business, Naming should be taken care in the cube. My requirement was to pull data from multiple cubes & so I was aliasing it so that I could standardize the names across different cubes.

I hope this gives you a good starting to alias names while writing MDX code.

SQL Spatial Data Types: A Tip to fix STIntersects Method returning NULL values.

I was working on some queries that used SQL Spatial data types and ran into a problem where STIntersects function was returning NULL. I was expecting it to say 1 or 0, but it kept on returning NULL. Here’s how I solved it:

1. On Technet article for STIntersects, it said “This method always returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.

here’s my pseudo code for you:
- – Function
- – Input Parameters @Area Geometry, @Point1 varchar(10), @Point2 varchar(10)
DECLARE @p point;
SET @p = geometry::STGeomFromText(‘POINT(@Point1 @Point2)’, 0);
- -Note that the 0 in the above line is SRID
return(SELECT @p.STIntersects(@area));

2. So next step was to make sure that the two geometry instances that I was using had same SRID’s.

3. Since I was passing the @area (geometry data type) to the function, I had to check the SRID for @area.
so wrote a single line of code that used the STSrid function.

Select @area.STSrid

That told me that the SRID of the geometric data type was 4xxx

4. So now I modified the original code to change the SRID of @p variable to same as that of @area variable.

So here’s the updated code:
DECLARE @p point;
SET @p = geometry::STGeomFromText(‘POINT(@Point1 @Point2)’, 4xxx);
return(SELECT @p.STIntersects(@area));

5. Now since the SRID of @area and @p matched, it started returning 1 or 0 values as expected.