SSRS Top N and Bottom N reporting with Duplicates

Previously I wrote a blog on Top N and Bottom N reporting. There were a couple of gotcha I did not cover in that blog so I thought I would cover those here to answer some of the questions I have received. The other blog can be found here.

On a report you are trying to group the top N number of rows together and all others should be in a separate group. Let’s say you want the top 5 sales people based on amount.

In my other blog I showed how to use the Top N and Bottom N filters on groups. The major problem comes in when you have duplicate amounts at the cut off. For example if you want the top 5 sales employees to be in one group based on their sales amount, and three employees tie for the number 5 slot. This would cause more than 5 people to show in the top N group and those same people would show in the bottom N using the technique I showed in the previous blog.

So how do you fix this? You will need a different method to do this. You need to find the cut off amount and spilt the top from the bottom based on that amount.

Here is the table we will use on the report. Notice that positions 4, 5, and 6 have the same amount. If you set the filter on the report to top 5 you will get 6 rows in the top. The Bottom N method I showed in the previous blog will fail because the bottom group will not know about the extra rows in the top group. This will cause row 6 to show in both groups.

clip_image001

A better way to do this Top N and Bottom N grouping would be to find the cut off amount and filter the groups based on this.

Here is the main query pulling the data to show on the table.

SELECT RowNum, Name, Amount

FROM TopN

Now you need to create a new data set and the query will look like below

With O as(

Select top (@N) t.RowNum, t.Amount, t.Name

From TopN t

Order by Amount desc

)

Select Top 1 Amount

From O

Order by Amount

I named this dataset MinTopAmount. This query is using a CTE to get the top N amounts. N is a parameter on the report, you can hard code this if desired. Then the outside query gets the lowest amount from that query. This gives you the lowest amount to show in the top N group. This query will only return single row and column with the cut off amount.

Now you need to set up the filters on the groups to filter based on the amount instead of Top or Bottom. These are two adjacent groups on a single table.

clip_image002

The Sum of the Amount value form the new dataset, which is named MinTopAmount in this example, should not sum anything because the query only returns one row due to the top 1 in the query with the CTE. The bottom group will have the exact same filter except it will use less than instead of greater than or equal to.

With the N set to 5 it will divide based on the amount at the fifth spot. If there is a tie, it won’t matter.

In this image you can see the bottom group in blue. Notice even though we selected Top 5 we get 6 rows in the top due to the tie, everyone else is in the bottom group.

clip_image003

If you have any issues let me know.

Posted in SSRS, Syndication | Tagged | 2 Comments

Standard Deviations with CTE in SQL

I was working with a client recently getting the number of standard deviations a student was off the average number of suspensions. I accomplished this with two Common Table Expressions, (CTE).

The first CTE gets the standard deviation and the average for the entire database. The Database holds all of the schools in the district and this CTE gives use the overall average.

The second CTE gets the number of suspensions summed up and group by student.

In the final query we bring the two CTE’s together by selected each column and performing the calculation to determine the number of standard deviations the student is off from the average (Mean).

That calculation is: (Student Count – Overall Average)/Standard Deviation

There are other ways to do this same thing. But by breaking it up into two CTE’s it make the maintenance and testing very easy. You can run the CTE’s separately to make sure each is returning the correct data before combining them.

Here is the query:

With

School as( — This CTE gets the Standard Deviation and Average of the Districts ISS

Select convert(numeric(18,3),stdev(f.InSchoolSuspension)) StdISS, AVG(convert(numeric(18,3),f.InSchoolSuspension)) AvgISS

from FactSchoolPerson f

),

Student as( — This CTE gets the ISS summed up by student

Select f.DimDWPersonKey, Sum(f.InSchoolSuspension) ISS

from FactSchoolPerson f

Group by f.DimDWPersonKey

)–Combine the two CTEs in this query and calculate the number of StdDevs Off the Mean

Select st.DimDWPersonKey, st.ISS, s.AvgISS , s.StdISS ,

((st.ISS – s.AvgISS)/ s.StdISS) StdOffISS

from student st Cross Join

School s

Order by st.DimDWPersonKey

Posted in Syndication, T-SQL | Tagged | Leave a comment

SSRS Report Models no Primary Keys

When creating report models you might run into bad database designs where the tables have no foreign keys and/or no primary keys. This can be resolved in the report model DSV. I have created two tables, one with first names, the second with last names. Each has an ID column which is the row that matches the row on the opposite table. There are no primary keys and no foreign keys on either table.

clip_image002

In visual studio I created a report model project and selected the database with these two tables. Then I created a DSV with these two tables.

clip_image004

After you create the DSV (Data Source View), right click on the DSV and select open.You will see the two tables in the DSV view.

clip_image006

Then I right clicked on the row to set a primary key. Remember this row must be unique.

clip_image008

Then I dragged the first ID to the second ID to create the foreign key. Remember, these must be matching data types.

clip_image010

Then I create the model and it has the relationships needed to create reports.

clip_image012

I created a report in report builder 2.0. Here you can see how the names match up correctly.

clip_image014

Posted in SSRS | Tagged | Leave a comment

SSRS – Creating Drill Through reports with MDX

If you are reporting from a Cube in SSRS and you want to drill through to another report you can set up actions just like any other drill through except one small change. The parameter you pass to the drill through report must be changed to Unique Name instead of value.

clip_image001

Go to the detailed report that you are drilling down to and right click on the datasets folder then select show hidden data sets so you can see the parameter dataset. In this query you will see the key under the parameter value. This is the unique value that the report needs to receive. Changing the parameter to unique name takes care of this issue.

clip_image002

Posted in mdx, SSAS, Syndication | Tagged , | 2 Comments

SSIS – Using Kill with SP_Who to Break locks

clip_image001

The dreaded table lock can occur and cause your SSIS packages to fail. A popular request I receive asks “How can I get rid of these table locks?” This blog will show you how to build a package that will kill any SPID’s that are running on your system that could be locking a table.

Note: Be careful using this technique, you could kill a critical process.

In this package you will have five variables.

clip_image002

objSpids = Holds the data from sp_Who2

strDatabase = Name of the database to look in Spids

strSpid = Current Spid in the for each loop

strSQLKill = Expression: “Kill ” + @[User::strSpid]

strSQLSPWho = Expression

“CREATE TABLE #sp_who2

(SPID INT,

Status VARCHAR(1000) NULL,

Login SYSNAME NULL,

HostName SYSNAME NULL,

BlkBy SYSNAME NULL,

DBName SYSNAME NULL,

Command VARCHAR(1000) NULL,

CPUTime INT NULL,

DiskIO INT NULL,

LastBatch VARCHAR(1000) NULL,

ProgramName VARCHAR(1000) NULL,

SPID2 INT,

REQUESTID int

)

INSERT INTO #sp_who2

EXEC sp_who2

SELECT cast(spid as varchar(10)) as spid

FROM #sp_who2

WHERE DBName = ‘”+ @[User::strDataBase] +”‘

and HostName is not null

and Status <> ‘BACKGROUND’

group by spid

DROP TABLE #sp_who2″

Notice the strSQLSPWho variable holds the query to create the table and put all of the SP_Who data into it. The database name comes from the strDatabase variable.

The first thing you will need to do is get the information from SP_who. This is done with an Execute SQL task. Set the SQL source type to variable and choose the strSQLSPWho variable as the source variable. Set the Results set to Full Results Set. In the Results set pane add a result set and set the name to 0 and the variable to objSpids.

clip_image003

clip_image004

Now you will need to loop through each row in the object variable with the Spids. The For Each Loop will do this. The Enumerator needs to be set to For Each ADO. Select the objSpids variable. Under variable mappings set the variable to strSpid and the index to 0.

clip_image005

clip_image006

Now drop an Execute SQL task in the For Each Loop. Set the SQL source type to variable and choose the strSQLKill variable as the source variable. Leave the Results set to None.

clip_image007

That is it for building the package. The next step is to test the package. Place a breakpoint on the For Each loop. Set this breakpoint to “Break at the beginning of every iteration of the loop”.

clip_image008

Start debugging the package and check the watch window or the locals window to for the value of the variables. To get these windows click on debug >Windows> Locals or Watch1.

clip_image010

Here is the watch window:

clip_image011

If the package is picking up Spids you don’t want you will need to adjust the where clause in strSPWho variable.

Posted in SSIS, Syndication | Tagged | 2 Comments

SSAS Trending KPI

KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is trending in right direction based on previous levels. If you want to show a measure has increased or decreased since last month, this can be accomplished by using the MDX function PrevMember. You want the trend to show at the month level only in excel. This way the users are not confused by extra trend graphics. This example will be using the adventure works cube.

Create a KPI in the cube and select the measure you want to compare.

clip_image001

The Goal will be a case statement to determine if the user is looking at the month level. If the user is not at the month level the goal will be null. If the user is at the month level then the goal will be a tuple of the previous month.

Case When ([Due Date].[Date].CurrentMember.level is [Due Date].[Date].[Month Name]) then

([Due Date].[Date].prevmember,[Measures].[Sales Amount Internet])

Else Null

End

clip_image003

The trend will check for the goal value, if the goal is null then the trend will be null. This ensures the trend only shows on the month level. If the goal is not null then the tuple for the previous month is compared to the KPI Value. Based on this comparison you will return a 1,0, or -1.

Case

When Isempty(KPIGoal(“Monthly Internet Sales”)) Then Null

When ([Due Date].[Date].prevmember, [Measures].[Sales Amount Internet]) <

KPIValue(“Monthly Internet Sales”) Then 1

When ([Due Date].[Date].prevmember, [Measures].[Sales Amount Internet]) =

KPIValue(“Monthly Internet Sales”) Then 0

Else -1

End

clip_image005

This is what it looks like in excel.

clip_image006

Posted in SSAS, Syndication | Tagged | Leave a comment

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 comes when you need to pass something different to the report. If the report has a date range on it you need to pass the report a start date and end date. If a user clicks on a reporting action in excel at the year, quarter, or month level, you will need to get the start date and end date dynamically. Let’s see how to do that.

Here you can see three reporting actions in a cube in SSAS. Each one handles a different date level. The user will see only one action when clicking in excel.

clip_image001

To ensure the user only sees the proper action at the proper date level you need a conditional statement in the optional condition box. So for the month level the code is:

[Due Date].[Date].CurrentMember.Level is [Due Date].[Date].[Month Name]

clip_image003

The server name is the name of the server where reporting services is running. The report path is the URL where the report is located. This is not the report manager URL. This is the report server URL.

The parameters will be different at each level.

Year Level Start Date Parameter:

URLEscapeFragment(

“01/01/” +

[Due Date].[Date].CurrentMember.Properties(“Key”)

)

The key for the year is the year, so I am able to just concatenate the date together here for the first day of the year. The last of the year works very similar.

Year Level End Date Parameter:

URLEscapeFragment(

“12/31/” +

[Due Date].[Date].CurrentMember.Properties(“Key”)

)

Month Level Start Date Parameter:

URLEscapeFragment(

[Due Date].[Date].CurrentMember.Properties(“Key1”) + “/01/” +

[Due Date].[Date].CurrentMember.Properties(“Key0”)

)

Month Level End Date Parameter:

URLEscapeFragment(

Cstr(

DateAdd(“d”,-1,

DateAdd(“M”,1,

DateValue(

[Due Date].[Date].CurrentMember.Properties(“Key1”) + “/01/” +

[Due Date].[Date].CurrentMember.Properties(“Key0”)

)))))

The keys for month are year and month. So Key1 is the month and Key0 is the year. This is typical to avoid duplicate key issues in a date hierarchy.

For the Month end date you need to use the dateadd function to go to the first day of the next month, then dateadd again to step back one day. This will give you the last day of the month.

Now for the hard one, Quarter level. The key for quarter is year and quarter. In this you are multiplying the quarter number by three and then subtracting 2 to get the first month in the quarter. The end date for the quarter is the same as the start date with two DateAdd functions to get to the end of the quarter. First you add 3 months then subtract one day.

Quarter Level Start Date Parameter:

URLEscapeFragment(

Cstr(

(Cint(

[Due Date].[Date].CurrentMember.Properties(“Key1”)

) * 3)-2)

+”/01/”+

[Due Date].[Date].CurrentMember.Properties(“Key0”)

)

Quarter Level End Date Parameter:

URLEscapeFragment(

Cstr(

DateAdd(“d”,-1,

DateAdd(“m”,3,

DateValue(

Cstr(

(Cint(

[Due Date].[Date].CurrentMember.Properties(“Key1”)

) * 3)-2

)+”/01/”+

[Due Date].[Date].CurrentMember.Properties(“Key0”)

)))))

Now in excel, when the users right click on a field, they will see the report action for the clicked level. Here are some images of this in action.

Year Level in Excel

clip_image004

Year date ranged passed to report

clip_image005

Quarter Level in Excel

clip_image006

Quarter date ranged passed to report

clip_image007

Month Level in Excel

clip_image008

Month date ranged passed to report

clip_image009

Posted in SSAS, SSRS, Syndication | Tagged , | Leave a comment