SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSAS Reporting Action with Date Parameters

Reporting Actions in Analysis Services allows you to open a report in Reporting Services. Most of the time users want to pass in some parameters to the report so it shows the relevant data. If the parameter name matches the item in excel then this is a breeze. The problem…

Read more

0 comments, 2,010 reads

Posted in Mike Davis SQL on 20 January 2014

SSAS – Using the Euro or Pound currency Symbol in a measure Format String

If you have a set of measures and some of them are US dollars, some are Euros, and some are UK Pounds, you want to format them correctly in SSAS.

First you will need to know the ASCII command to type a pound and euro symbol. You can find this…

Read more

1 comments, 1,715 reads

Posted in Mike Davis SQL on 13 January 2014

SSAS Dynamic KPI from a table

KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is above or below a goal. Managing the goal numbers can be a headache because they made need to be updated often. By placing the goal numbers on a table you can dynamically…

Read more

0 comments, 2,082 reads

Posted in Mike Davis SQL on 6 January 2014

SSRS Interactive Sorting a Matrix

Sorting a matrix report can be done interactively. To perform interactive sorting, your report you will need a cell to click on to perform the action. In the image below you can see a basic matrix report with State as the row group, with year and quarter as the column…

Read more

1 comments, 1,349 reads

Posted in Mike Davis SQL on 30 December 2013

SSIS For Loop Skip Files

When running a For Each Loop through a set of files, sometimes you will have specific files that you do not want to load.

For example, I have a set of files named:


If I want to skip the file that starts with “W” then…

Read more

4 comments, 2,427 reads

Posted in Mike Davis SQL on 23 December 2013

Setting up Security on sub sites in SharePoint 2010

A lot of companies have several departments and want to limit the sub sites users can view. For example, the IT department should only see the IT sub site, but the HR department may need to be able to see all sub sites. To set up individual security for sub…

Read more

0 comments, 1,268 reads

Posted in Mike Davis SQL on 16 December 2013

SSAS – Setting Attributes as Properties for Excel

When a user is browsing your cube with excel they may not understand how to slice the data and end up using things like “first name” as a hierarchy to slice the data. This does not make for good analysis, since grouping all of the people with the same name…

Read more

1 comments, 960 reads

Posted in Mike Davis SQL on 9 December 2013

SSRS – Self Referencing Reports or Drill through to same report

When looking at a report and wanting to drill through to get more detail, we often create two reports for this. This can be accomplished by just one report by creating a self-referencing action that links to the same report. This can be done even if the report has parameters…

Read more

4 comments, 2,688 reads

Posted in Mike Davis SQL on 2 December 2013

Reverse or Mirrored Bar Chart in SSRS

There are some pretty charts out there and most of them can be done in Reporting Services. There is one type of chart I had someone ask for that is not natively built in to SSRS. But with a little manipulation you can get the look you want. Here is…

Read more

0 comments, 709 reads

Posted in Mike Davis SQL on 25 November 2013

SSIS Execute SQL error – No disconnected record set is available

If you get the error in SSIS that says:

…failed with the following error: “No disconnected record set is available for the specified SQL statement.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

This is can be…

Read more

0 comments, 1,434 reads

Posted in Mike Davis SQL on 18 November 2013

SSRS Map Layers Parameter

With the new Map control in SSRS 2008 R2 there are three different map types, Road, Aerial, and Hybrid. If you want to allow your users to change the map type you would think you could map a parameter to the property on the tile layer with these three types.…

Read more

1 comments, 1,307 reads

Posted in Mike Davis SQL on 11 November 2013

SSIS Pivot on bad data

The pivot transform in SSIS is already a pain to use. When you have bad data it makes it even worse. In this blog I am going to show how to handle bad data when using the pivot transform. You should understand how to use the pivot transform already and…

Read more

0 comments, 1,629 reads

Posted in Mike Davis SQL on 28 October 2013

SSIS Records on the Same Row – kind of like pivot

If you have two or more records on the same row, and need to write each record on its own row in a destination, you have two choices. You can do this in series or parallel in a single data flow in SSIS. Here is the input table I am…

Read more

1 comments, 1,932 reads

Posted in Mike Davis SQL on 7 October 2013

MDX YTD Calculation Issus

If you are using the templates for MDX calculation built into SSAS 2008 you might run into some strange issues. In the image below you can see the calculation is working for all years except for 2008.

My first thought was data issue. After querying the table and finding no…

Read more

0 comments, 862 reads

Posted in Mike Davis SQL on 30 September 2013

MDX Scope with an If Then statement

The scope statement in MDX is great for applying calculations to a certain area of a cube, also called a subcube. Dustin Ryan wrote a great article on the scope statement here. The issue I am going to cover will be using the scope statement when you only want to…

Read more

2 comments, 877 reads

Posted in Mike Davis SQL on 23 September 2013

Loop Through Excel Files in SSIS

You can loop through excel files using SSIS. This will use the For Each Loop container and a data flow task.

First Create a variable named strExcelfile as a string variable; you can leave the value blank.

Next, drag in a For Each Loop. Set it to For Each File,…

Read more

10 comments, 615 reads

Posted in Mike Davis SQL on 16 September 2013

Junk Dimensions with no Loading Needed

When you are working with dimensional modeling there are some situations where several dimensional attributes don’t make since to be in any other dimension. When this occurs you can combine them into one dimension call a Junk Dimension.

In this example you will see a person type dimension that only…

Read more

0 comments, 1,341 reads

Posted in Mike Davis SQL on 9 September 2013

Handling Large Many to Many bridge tables

In some scenarios you will need to create a many to many relationship in your cube in SSAS. One of the problems that arise from many to many bridge tables are the size of these tables. In this example we have a fact table that contains the history of a…

Read more

1 comments, 2,434 reads

Posted in Mike Davis SQL on 2 September 2013

Execute SSIS Packages with a Macro in BIDS

When running SSIS packages in BIDS it is common to click on the green arrow at the top of BIDS to run a package.

One problem that can occur when using this button is the deployment of project and the creation of the deployment manifest file. If you have turned…

Read more

1 comments, 1,739 reads

Posted in Mike Davis SQL on 19 August 2013

Environment Variables in SSIS Packages and Configuration Tables

Configuration tables are a best practice in just about any SSIS environment. They make it easy to update multiple packages from a single change. But one of the issues with configuration tables is the location of the server is different on each server. You may have a Server name of…

Read more

4 comments, 1,547 reads

Posted in Mike Davis SQL on 12 August 2013

Newer posts

Older posts