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

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 in the character map in windows. You must use the 10 key number pad to type these in while holding down ALT.

Euro = Alt + 0128 €

Pound = Alt + 0163 £

Next you will need to put a back slash in the format string before the symbol for it to show up in excel.

Here are the images from SSAS:

clip_image001

\£ #,##0.00;(\£ #,##0.00)

clip_image002

#,##0.00 \€;(#,##0.00 \€)

Here is the image from Excel:

clip_image003

Posted in SSAS, Syndication | Tagged | 1 Comment

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 keep the goals up to do by simply updating the table.

The following example uses the adventure works data warehouse. You will need to create a new table. Here you can see one named KPI Goals and the data on it. This table contains the internet sales goal for each year.

image

Next you will need to add this table to the DSV in SSAS and create a relationship between it and the year column on DimDate. You can map the granularity to any attribute like Quarter or Month.

image

Then create a new measure group in the cube using the KPIGoals Table. You can delete the count measure it creates, you only need the internet sales goal.

image

Next you will create the relationship in the cube under the dimension usage tab, set it to a regular relationship on the year attribute.

image

Now you are ready to create a dynamic KPI. Name the KPI Yearly Internet Sales Goal. Use the Internet Sales Amount as the value expression.

image

You want the goal to only show when the user is at the year level when browsing in excel. A Case statement checking the level will accomplish this. We are also using a tuple to get the goal for each year.

Case When ([Due Date].[Date].CurrentMember.level is [Due Date].[Date].[Calendar Year]) then

([Due Date].[Date].CurrentMember,[Measures].[Internet Sales Goal])

Else Null

End

image

The Status will check the goal value, if the goal is empty then the status will be null. This will ensure the status only shows on the year level. Then you compare the value to goal and set the numbers to 1, 0, or -1 based on the comparison.

Case

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

When KPIValue(“Yearly Internet Sales”) > KPIGoal(“Yearly Internet Sales”) Then 1

When KPIValue(“Yearly Internet Sales”) = KPIGoal(“Yearly Internet Sales”) Then 0

Else -1

End

image

Now in Excel you will see the KPI status next to each of the years only. The goals are based on the values from the KPI Goals table.

image

Posted in SSAS, Syndication | Tagged | Leave a comment

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 groups. You want to be able to interactively sort the report on all of these groups.

image

There is an empty cell above the state and this can be the location for the sorting actions. First you need to create two more cells though, because you want to sort on all groups.

To do this right click on the black cell and select Split cells.

image

This gives you two cells now. You still need one more. Right click on the row at the top and select Insert Row, outside group above.

image

This will give you three cells as seen below.

image

In the blank text boxes you can now type in a descriptive text like “Sort by Year”.

image

To set up the sorting right click on the sort by year text box and select text box properties. Then select interactive sorting. Place a check next to Enable interactive sorting on this text box. Place the radio button next to Groups and select year in the drop down below then click ok.

image

Repeat these same steps for the other cells and your users will be able to sort the report using these cells.

image

Posted in SSRS, Syndication | Tagged | 1 Comment

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:

Abc.txt
Mno.txt
Rts.txt
Wln.txt
Xyz.txt

If I want to skip the file that starts with “W” then I will need an expression in my For Each Loop to detect this file.

Inside the For Each loop I am going to place a sequence container. This will give me a place to anchor my expression which I will place on the precedence constraint coming from the sequence container. There are no tasks in the sequence container.

clip_image002

On the precedence constraint line I am going to set it to constraint and expression. The expression will be:

substring(Upper(@strFileName),1,1) != “W”

clip_image004

This is looking at the first letter in the filename and comparing it to the letter “W”. I would place the “W” in a variable and use that instead, I am just showing this way for simplicity. Notice I convert the file name variable to upper case and compare it to an uppercase “W”. That way the case will not matter.

Posted in SSIS, Syndication | Tagged | 4 Comments

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 sites you will need to stop the sub sites from inheriting from the parent.

To do this click on the Site Action menu while viewing the sub site, then select Edit in SharePoint Designer.

clip_image001

On the right hand side of the screen you will see the permissions window. Click the button Stop Inheriting.

clip_image003

The button then changes to a new button.

clip_image005

Now click on the new button. This will bring up the Add Permissions window. Enter in the user name or group name in the top text box and click the check user icon on the right. Select the proper group or set up the custom permissions in the bottom section of this window.

clip_image007

Click ok and you have completed adding users to this sub sites. If you have other sub sites you want them to have access to you will need to repeat these steps for each sub site. Any sub sites that you do leave set to inherit from the parent will use the parent permission settings.

If you change your mind and want to set the site back to inherit you can do this with a button at the top of the SharePoint designer. You may need to close the designer and reopen it for it to update.

clip_image008

Posted in SharePoint, Syndication | Tagged | Leave a comment