Archive

Archive for the ‘SSRS Expert’ Category

Consider removing the NON EMPTY key word from the COLUMNS axis

March 8, 2016 Leave a comment

You might have seen error message like this from a SQL job that pulls data from an OLAP cube using OPENQUERY:

The OLE DB provider “MSOLAP” for linked server “CUBE_LINK” indicates that either the object has no columns or the current user does not have permissions on that object.

When using linked server or writing MDX queries in SSRS reports to pull data from OLAP cube, consider removing the NON EMPTY key word on COLUMNS.

When there is no data in the result set, the NON EMPTY key word on COLUMNS will not return any columns. The linked server will send “object has no columns” error and the OPEMQUERY will fail.

Unless you truly want the OPEMQUERY to fail, consider removing the NON EMPTY key word from the COLUMNS axis.

Removing the NON EMPTY key word from the COLUMNS axis will allow the MDX to return the columns even when the query contains no data.

SELECT  --NON EMPTY
{[Measures].[The Minutes]} ON COLUMNS
,NON EMPTY {
[SomeDimension].[EMPID].CHILDREN *
[Date].[Dates].[Month].&[201603] } ON ROWS
FROM TheCube
WHERE  …

There are many tips in the book MDX with SSAS2012 Cookbook about how the NON EMPTY key word behaves. Since the book is organized not by MDX functions, but rather by subject areas, the best way to find these tips is to use the Index at the back of the book to find examples and tips in the book.

5th Blogging Year

December 31, 2014 Leave a comment

It’s hard to believe that it’s been 5 years since my first ever post, in which I simply spelled Business Intelligence. Thanks to everyone for being with me since then!

I blogged around my daily experience as a Data Warehouse and Business Intelligence developer. Most of my posts are about how to use the tools, i.e. the Microsoft SQL Server Business Intelligence stacks with more productivity and efficiency, of cause, also about the two major data query languages, SQL and MDX. I enjoy working as a DWH and BI developer for the creativity I found in all I do. I mostly praise the tools and the query languages we use, but occasionally my posts complain about the the inefficiency and even bugs in the tools.

In 2013, I co-authored the book MDX with SSAS 2012 Cookbook. As much as I’d love to write again and blog more, in 2014 I felt that I I needed more time for myself to relax and recharge, and to spend more time with my family.

Looking forward to next year, I would like to think that being a DWH and BI developer will continue to give me professional satisfactions; I do not need to find a new career. I also hope that 2015 is a year that will give me inspiration for a new book for DWH and BI developers.

My posts cover a lot of grounds in SSIS, SSRS, SSAS, SQL Jobs, SSMS, Visual Studio’s SSDT, and SQL and MDX. But it’s interesting that the top 5 posts that received the most views are all in SSIS, according to the WordPress’s annual report. More interestingly, these top SSIS posts are all about how to use variables in SSIS. I will consider writing about those topics again.

Once again, thanks for following and commenting on my posts in 2014. I look forward to seeing you all online again in 2015! Happy New Year!

 

Sherry Li

SSRS #75 – Use RunningValue() Function and VB Script to capture discrete dates

November 11, 2014 Leave a comment

Drill-down report is a common design

In SSRS, it’s a common design to allow users to drill down to more granular data in a same report.

In the following snapshot, users will see a summary line that shows the Call Handled 130. The two days, 11/4 and 11/5 will not be immediately visible. Only when the user clicks the + sign in the All Dates field, the two detail lines will become visible. All this is happening in the same report.

pic1

 

 

 

 

 

 

 

Result set can contain only discrete dates

In this particular report, I had two date filters, as shown below.

pic2

 

The date filters ask for data in a continuous date range, starting from 11/1 to 11/10. However, the report is only showing some discrete dates per employee. This is because other filters suppress certain dates.

The point here is that although the date filters ask for data in a continuous date range, the result set contains only discrete dates within the date range.

Now the users ask you to create a hyperlink to go to a separate report, to show data for that employee, and for those discrete dates only.

The challenge becomes how do we capture those discrete dates within a group (The report is grouped by employee).

Capture discrete dates in SSRS

You might attempt to create a stored procedure to run the same query for the current report again so you can capture those discrete dates in your code, and then pass them to your second query to get the new report.

This will not be my first choice though, because it requires a lot of coding, not mention that the query performance might suffer because I will need to jam too much and redundant logics into one query.

Instead, I want to accomplish it entirely in the SSRS report.

Before I create the hyperlink on the summary line, I created a new column “Running Values” to just test my idea of capturing those discrete dates per employee.

pic3

 

 

 

 

 

 

 

 

I named this column “Running Values”, because I know that the RunningValue() VB function is what I need to use. A while ago I blogged about this wonderful aggregate function.

SSRS #72 – Use RunningValue() Function to Concatenate Row Values

As a matter of fact, all the aggregate functions in the following screenshot allow you to do aggregation within a group in SSRS.

pic4

 

 

 

 

 

 

 

RunningValue() function needs an expression, an aggregate function and a group name

To use the RunningValue() function, I’ll need three parameters.

  • An expression, which normally is a field name, such as Yearly Income.
  • An aggregate function, such as MAX, SUM.
  • An group name, which is called GROUP_EMP in my report.

It is quite obvious that using the RunningValue() function out-of-box will not work for me, because I am not doing any direct aggregation on dates per employee.

It’s time to go back to my old blog, SSRS #72 – Use RunningValue() Function to Concatenate Row Values.

A VB script that simply concatenate the dates per GROUP_EMP

What I need is a VB script that allows me to simply concatenate the dates per GROUP_EMP. Every time the script is called, it keeps the prior dates, and adds the new date to the concatenated string.

This function will do exactly that. In Report –> Report Properties window, write the following code, making sure that the two strings aStr and tempStr are defined outside the Public Function/End Function block. This will make sure that both aStr and tempStr are saved per GROUP_EMP.

 

pic5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here is the code in text.

Dim aStr as String = “”
Dim tempStr as String = “”

Public Function ConcatenateString_Dates (ByVal inEmployee_ID As String, ByVal inDays As String) As String

If inEmployee_ID = aStr Then
tempStr = tempStr + “,” + inDays
Else
aStr = inEmployee_ID
tempStr = inDays
End If

Return tempStr

End Function

 

Calling the RunningValue() function from the test column

Now it’s time to use the RunningValue() function.

In this test column RunningValues, I need to call the RunningValue() function.

 

pic6

 

 

 

 

 

 

 

 

 

Here is the actual code I used.

=RunningValue(Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value), Max, “Group_Banker”)

The trick is in the first parameter, the expression.

Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value)

This expression is not a simple field, it is the VB script I just created to concatenate the dates per employee.

The aggregation function I used is MAX. This allows me to get the last concatenated string, which is all the dates within one GROUP_EMP.

URL encoding allows more robust drill-through design

Now I am ready to just pass the following as a dates parameter to my next report.

RunningValue(Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value), Max, “Group_Banker”)

Calling another SSRS report, or creating a drill-through report is normally pretty straightforward. SSRS has an out-of-box feature called Action for textboxes that can do that perfectly. But to make your reports more robust, and lasting long after the developer has moved on, there is something called URL encoding that VB scripts are good at. That can be another blog topic.

SSRS #74 – 2008 Cascading bug when you have a middle parameter

May 9, 2014 2 comments

Some SSRS reports have a large number of parameters

I recently had a chance to help a co-worker to modify an existing SSRS+Cube report. The first thing that caught my eye is the large number of parameters in the report.

Although I never really developed any reports that have more than, let’s say, 6 parameters, I can understand why developers sometimes need to use, let’s say more than 20 parameters. This happened most likely because, the report developer is lack of authority to re-design the cube, or because the report has unnecessarily complex design. Another possibility is that the developer had made conscious decision to avoid writing MDX queries and shifting the coding to the Reporting Services.

A bug in SSRS 2008

Here is a scenario where developers sometimes might need to resolve to using a hidden parameter, that sits in the middle of two parameters. In the diagram below, the middle parameter will take the user input from parameter 1, and have some IIF statement to transform the input, and then the dynamically set value will be the input for the dataset 3 which feeds parameters 3.

clip_image002

I’ve tested this type of "cascading" parameters (with a middle parameter that has a dynamically set default value). The short conclusion is this: in SSRS 2008, the parameter 3 failed to reflect user’s section in parameters 1; in SSRS 2012, all is good.

So I had to say that this is a bug in SSRS 2008.

Remove the middle parameter to work around the bug

To work around the bug, fortunately, we have a pretty simple solution. Simply remove the middle parameters, AND then code the IIF logic in the query parameter for dataset 3 (which feeds parameter 3).

clip_image004

The only drawback I can see in this workaround is that if the transformation logic needs to be used for another dataset, then you have no choice by repeating it.

Longer version of the solution

The following is the longer version of the solution.

My Original Suggestion
  • Use a middle parameter to do the transformation, with nested IIF statement in both Available Values and Default Values.
  • Then pass the middle parameter to the cascading dataset; the middle parameter is passed directly to the dataset without the IIF statement.
  • The solution failed in SSRS 2008: the cascading dataset DataSet_Employees would never get refreshed even when the middle parameter is refreshed correctly when ever a new Department is selected by the user.
  • Reason for the failure: it might have to do with how the value for the middle parameter is set programmatically with the nested IIF statement, rather than by user selection.
  • Good news: this solution worked in SSRS 2012 in my test.

Here are two screen shots from our failed solution.

clip_image006

clip_image008

New Solution That Worked
  • Remove the middle parameter entirely.
  • Pass the parameter Departments to the cascading dataset, DataSet_Employees, instead of the middle parameter.
  • The Departments parameter needs to be modified to perform the transformation; use the same nested IIF statement we used in the middle parameter.
  • Voila. It works.
  • Reason for the success: by removing the middle parameter and coding the nested IIF in the parameter that is passed to the cascading dataset, Reporting Services made no mistake, but had to refresh the cascading dataset based on the user selection.

Here are a few screen shots from our successful solution.

clip_image010

The nested IIF statement is now coded in the parameter expression.

clip_image012

SSRS #73 – Marlon Ribunal and Mickey Stuewe’s New Book: Reporting Services 2012 Blueprints

January 24, 2014 Leave a comment

The book’s link is here, SQL Server 2012 Reporting Services Blueprints.

This book is a step-by-step, task-driven tutorial that goes straight to the practical development of reporting skills, explaining actions as they are taken. If you perform the role of report development using SSRS in your job and you already have a basic knowledge of how data source and datasets in SSRS work, this book will advance your reporting skill to the next level.

In order to accomplish all the steps discussed in this book, including the steps on how to integrate SharePoint 2010/2013, PowerPivot, and Power View with SSRS, it is recommended that you use SQL Server 2012 Enterprise or Business Intelligence Edition. You can still use the Standard Edition to accomplish most of the steps in the book but it doesn’t support the advanced BI features, such as PowerPivot and Power View.

To create SSRS 2012 reports, you have two options:

  • You can use the SQL Server Data Tools (SSDT). SSDT is Visual Studio shell which is an integrated environment used to build SQL Server database objects. You can install SSDT
    from the SQL Server installation media.
  • You can also use Visual Studio 2012. If you use Visual Studio, you must install the SSDTBI templates. SSDTBI (SQL Server Data Tools-Business Intelligence) is a component that contains templates for SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) for Visual Studio 2012.

To try the examples in this book, you will need to have 2012 AdventureWorks Sample database.

In Chapter one you will learn right away different tricks on how to implement parameters for  
different scenarios, ability to exclude one or more parameters at runtime, drop-down parameter, multivalued parameter, cascading parameter and cascading multivalued parameter.

I really appreciate Chapter 2 in which a section is devoted to create custom report template. Report templates not only save time but also creates visual consistency that suit your organization branding. Number crunching is really all about data summaries, aggregates, and groupings. Through practical examples in Chapter 2, you will be able to pick up the skills very quickly.

Another essential skills in creating SSRS reports is to be able to add interactive elements, such as Actions, Tool tips, Document Map, Sorting and Bookmark to a report. In SSRS, one report can also interact with another report through Subreport, Drillthrough report or linked report. You will absolutely love Chapter 3 which focuses on adding actions to SSRS reports. 

Have you ever wondered if SSRS is also a good tool for data visualization? In SSRS 2012, in the report designer toolbox, you will find a few report items that are new, they are, Data Bar, Sparkline, Indicator and Map. Chart and Gauge exist in the prior versions.

Chapter 4 will show you how to create reports in SSRS 2012 with data bars, sparklines, gauges, and indicators. Next time if you need to meet the common data visualization requirements, you will find that this book can make your data visualization work seem effortless.

I particularly like Chapter 5 which goes right into map visualization. It not only shows how to use the built-in maps for the United States, but also how to import and use commercial shapefiles (map files) to create world map report.

If you are tasked to develop reports based on the cubes in the OLAP, Chapter 6 will show you how to survive simple implementation just with the query designer in SSRS.

The last chapter in this book goes beyond just report development. It will show you how to get your SSRS reports ready for production. You will find practical information on deployment configurations, user security, shared schedules, cached reports, and snapshots. It also dives into report subscriptions with e-mail delivery / scheduled delivery and data-driven subscription.

Overall, this SSRS 2012 blueprints book is a well written book and worth buying if you just start out developing SSRS reports in 2012. If you started with the prior versions and already are an experienced report developer, then this book might not be right for you. However, if you are like me and new to the data/spatial visualization features in SSRS 2012, you will find very useful information in this book on how to use the data bars, sparklines, gauges, and indicators and map objects.

Congratulations to Marlon Ribunal and Mickey Stuewe on their first SSRS book. And to reviewers Basit A. Masood-Al-Farooq, Varinder Sandhu, SIVAKUMAR VELLINGIRI and Valentino Vranken. Their combined experience in SQL Server Reporting Services report development makes this book a valuable addition to every SSRS developer’s library.

MDX+SSRS #31– Query Designer in SSRS only allows the Measures dimension in the first axis

September 28, 2013 Leave a comment

In Chapter 1 of the book MDX with SSAS 2012 Cookbook, in the first recipe “Putting data on x and y axes”, I have given a simple example to show how easy it is to use the CROSSJOIN function to "combine" more than one hierarchy into the COLUMNS and ROWS axes.

SQL Server Reporting Services is a report design and information delivery tool, and has been adopted by many companies for their Business Intelligence reporting needs. However, building reports in SSRS accessing OLAP cubes in Analysis Services is not without frustration.

The graphical MDX Query Designer in Reporting Services allows you to retrieve data from any BI semantic model through the technique of drag and drop, without you actually needing to understand and write MDX queries.

If you have some experience building reports in SSRS with Analysis Services data, you have already discovered that the graphical MDX Query Designer serves its purpose very well to  graphically generate well-formed and efficient MDX queries. Very quickly, however, you also will find that the you will need to cross a bridge. This bridge will lead you to the generic MDX query editor where you can edit the MDX query that is built by the graphical designer.

The following is a screenshot of the Query Designer and the toggle button for switching between the graphical designer and the editor.

image

Don’t expect the MDX query editor in SSRS works the same way as the MDX query editor in SSMS.

This MDX query simply puts two measures on the COLUMNS, and the CROSSJOIN (all possible combinations) of the sales territory country and product category on ROWS.

SELECT   
    { [Measures].[Internet Sales Amount],
      [Measures].[Internet Gross Profit]
    } ON 0,
    { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 1
FROM   
    [Adventure Works]

In SSMS, the previous query will produce the following result.

image

If we copy the same query to the query editor in SSRS, we do get the same number of rows back, and the measures match perfectly for every combination of the sales territory country and product category. However, we see some noticeable differences, comparing the previous screenshot with the following screenshot.

image

1. The measures are not formatted in the query editor in SSRS.

2. We get four columns in the query editor in SSRS. In addition to the two measures that we put on the X axis, the two hierarchies from our CROSSJOIN function on the Y axis have also appeared as two separate columns.

Now, let’s change the previous MDX query slightly, by switching the measures to the ROWS, and the CROSSJOIN of the sales territory country and product category to COLUMNS.

SELECT   
    { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 0,
    { [Measures].[Internet Sales Amount],
      [Measures].[Internet Gross Profit]
    } ON 1
FROM   
    [Adventure Works]

 

In SSMS, we would expect to see the following results.

image

Let’s copy the same query to the query editor in SSRS. This time we would get an error.

image

The message is actually very clear. I’d translate the error message into the following two rules. The query editor (and the graphical Query Designer) in SSRS:

1. does not allow CROSSJOIN in the COLUMNS (or 0-axis)

2. only allows the Measures dimension in the COLUMNS (or 0-axis).

What we have put on the COLUMNS clearly violated both of the rules.

     { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 0

 

In my experience, knowing what to expect from the graphical MDX Query Designer and the MDX query editor in SSRS will put you half way through the learning curve.

SSRS #72 – Use RunningValue() Function to Concatenate Row Values

August 13, 2012 2 comments

I had a post quite a while ago on how to use FOR XML clause with PATH mode, which is available since SQL 2005, to concatenate row values into columns.

T-SQL – Concatenate row values in Transact-SQL

We can achieve the same in SSRS with a wonderful aggregate function, RunningValue() function.

image

But to make it work, it can take more than a few tries.

In this example, I am going to use the MDX query from my post MDX #10 – Top 5 Resellers in Each Country.

image

Once I have the MDX query ready, I want to concatenate the top 5 resellers into a column, and the result will look like this:

image

A great post on beyondrelational.com has inspired this post. I forgot the link, but still want to attribute to the original blogger.

First add some custom code to the report

The first we need to do is to add some custom code to the report by going to the Report Properties.

image

In the Code section, create a custom function ConcatenateString() to concatenate resellers for each country. It will take Country and Reseller as 2 parameters.

image 

Next create a row group

Next we need to create a row group, named “Group_Country”. In the group property, make sure that the group by column is [Sales_Territory_Country].

image

Use RunningValue in the expression

In the Top 5 Resellers expression, enter:

=RunningValue(Code.ConcatenateString(Fields!Sales_Territory_Country.Value, Fields!Reseller.Value), Max, "Group_Country")

  • The ConcatenateString() function take 2 parameters, Fields!Sales_Territory_Country.Value and Fields!Reseller.Value.
  • The aggregate function Max is the second parameter for the RunningValue() function. 
  • The last parameter is the name of the row group.
  • The RunningValue() function iterates through each [Sales_Territory_Country] in the row group, finds each reseller, concatenates them, until it finds all the resellers in each [Sales_Territory_Country].

Concatenating row values into column can be easily done in TSQL, and also in SSRS.

%d bloggers like this: