I almost feel embarrassed…, I’ve been writing this blog for over 9 months now, and I have yet to mention Mosha, although in my defence, there is a link to his blog in the links section to the right.

As many/most of you may know, Mosha Pasumansky is one the key brains behind designing the MDX language and Analysis Services – nuff said?

Over the last year he has been working on a pet project, MDX Studio. It’s an MDX query tool which any self respecting OLAP developer should now be using on a regular basis. He has just released v0.4.6, which adds some really nifty features such as the dependency view.

If you’re just starting out with MDX, then the intellisense will be of massive benefit to you; even if you’re a seasoned pro, the performance monitoring is an essential tool on its own.

If you haven’t already tried it, have a look at Mosha’s blog, and get a copy – you won’t regret it.

And thanks for all your hard work Mosha – It’s much appreciated.

Alex

Next week we’re lucky enough to have a double-whammy of SQL events in the UK, has Christmas come early?…

On Wednesday 10th September, Microsoft are running a live Technet event, SQL Server 2008 Unleashed, at their Reading campus. It looks like it should be a day well spent, with Prem Mehra delivering a keynote speech on large scale SQL Server systems, and other sessions including data warehouse improvements, upgrade paths, and a session focusing on BI. The event is now full, so I hope you’ve already booked your place…

Then, on Saturday 13th September, we’ve got the long awaited SQLBits III event in Hatfield. There are far too many sessions to list here, but with dedicated tracks for BI, Admin, Development and Katmai, there is certainly something for everyone. You can safely assume that I’ll be on the BI track all day!

I’ll be going to both events; I know a few of you regular frog-blogees are also going, so I look forward to meeting you at one or both.

Alex

It’s commonly known that there are a few frustrations when using stored procedures as the source for SSRS 2005 datasets, the inability to cope with temporary tables is always a key one, another is not being able to get at output parameters or return values.

This post explains the easiest way to get at the output parameters and return value.

Lets assume you are using the following stored proc:

IF OBJECT_ID('myProc') IS NOT NULL
  DROP PROCEDURE myProc
GO
CREATE PROCEDURE dbo.myProc
  @OutTest int OUTPUT
AS
  SET @OutTest = 42

  --Random test query
  SELECT 1 as x, 'rec1' as y, 'rec1' as z
  UNION
  SELECT 2, 'rec2', 'rec2'

  RETURN 1
GO

When we set the dataset to use myProc, we will get the three fields (x, y and z) returned, but not the @OutTest parameter, or the return value of 1.

To fix this all you need to do is create a wrapper procedure that converts the values into extra fields.

IF OBJECT_ID('myProcWrapper') IS NOT NULL
  DROP PROCEDURE myProcWrapper
GO
CREATE PROCEDURE dbo.myProcWrapper
AS

  CREATE TABLE #myTmp(
    x int,
    y varchar(20),
    z varchar(20)
  )

  DECLARE @ReturnValue int, @OutTest int

  INSERT INTO #myTmp
    EXEC @ReturnValue=dbo.myProc @OutTest OUTPUT

  SELECT x, y, z,
      @ReturnValue as RetVal,
      @OutTest as [Output]
    FROM #myTmp

  Drop Table #myTmp

  RETURN 1
GO

This runs the original procedure, and stores the values in a temporary table (yes, I know I know…!). It then appends the return value and output parameters as extra fields and then returns the whole lot. These values are repeated for every record returned, but all you have to do is take the value from the first record =First(Fields!Output.Value).

If you wanted to get rid of the temporary table, you could always replace it with a permenant table, but you’d have to be careful to keep each client instance seperate so results for different users didn’t interfere with each other. You could do this by adding a working column to the myTmp table contaning the username and/or workstation ID. Something like this…

IF OBJECT_ID('myTmp') IS NOT NULL
  DROP TABLE myTmp
GO

CREATE TABLE myTmp(
  x int,
  y varchar(20),
  z varchar(20),
  UserID varchar(50),
  Workstation varchar(50)
)
GO
ALTER TABLE dbo.myTmp ADD CONSTRAINT
  DF_myTmp_UserID DEFAULT SYSTEM_USER FOR UserID
GO
ALTER TABLE dbo.myTmp ADD CONSTRAINT
  DF_myTmp_Workstation DEFAULT Host_Name() FOR Workstation
GO

IF OBJECT_ID('myProcWrapper') IS NOT NULL
  DROP PROCEDURE myProcWrapper
GO
CREATE PROCEDURE dbo.myProcWrapper
AS

  DECLARE @ReturnValue int, @OutTest int

  DELETE FROM myTmp
    WHERE UserID = SYSTEM_USER
    AND Workstation = Host_Name()

  INSERT INTO myTmp (x,y,z)
    EXEC @ReturnValue=dbo.myProc @OutTest OUTPUT

  SELECT x, y, z,
      @ReturnValue as RetVal,
      @OutTest as [Output]
    FROM myTmp
    WHERE UserID = SYSTEM_USER
    AND Workstation = Host_Name()

  DELETE FROM myTmp
    WHERE UserID = SYSTEM_USER
    AND Workstation = Host_Name()

  RETURN 1
GO

This still has the benefit of not having to modify the original stored procedure, but now allows you to see the available fields in Reporting Services.

This post explains how you can create a ranking of data from an OLAP MDX query. This will take the results from the query, and assign a ranking to each row. i.e. 1st, 2nd, 3rd best rows etc.

The first thing to do is to decide two things.
1) What measure do you want to rank by
2) What data set are you returning

Let’s assume we want to rank all stores by sales value. The basic non-ranked MDX query would be something like this

  SELECT
    {[Measures].[Sales Value]} ON 0,
    {[Store].[Store Name].members} ON 1
  FROM
    [SalesCube]

So our measure is Sales Value, and our data set (granularity) is Store Name. We now want to create an ordered set of this data, ordered by Sales Value. We do this with the ORDER() function, which takes a set, a measure and either ascending or descending as its parameters. Note that by specifying the attribute twice we remove the [All] member from the set.

  WITH SET [OrderedSet] AS
    ORDER([Store].[Store Name].[Store Name].MEMBERS,
	  [Measures].[Sales Value],
	  BDESC)
  SELECT
    {[Measures].[Sales Value]} ON 0,
    {[OrderedSet]} ON 1
  FROM
    [SalesCube]

The next stage is to apply a ranking to this ordered set. Helpfully, MDX provides us with a Rank() function, which takes a member and a set as its parameters. All it does is locate the member within the set and return its position. Because we have ordered the set, it will give us the ranking.

  WITH SET [OrderedSet] AS
    ORDER([Store].[Store Name].[Store Name].MEMBERS,
	  [Measures].[Sales Value],
	  BDESC)
  MEMBER [Measures].[Rank] AS
    RANK([Store].[Store Name].CurrentMember,
	 [OrderedSet])
  SELECT
    {[Measures].[Rank], [Measures].[Sales Value]} ON 0,
    {[OrderedSet]} ON 1
  FROM
    [SalesCube]

You can now easily expand this to only show you the top x records, by using the Head() function on the ordered set. In this example we’re only showing the top 10. You could also use Tail() to find the bottom x records. Other functions you can use include TopPercent(), TopSum(), BottomPercent() and BottomSum().

  WITH SET [OrderedSet] AS
    ORDER([Store].[Store Name].[Store Name].MEMBERS,
	  [Measures].[Sales Value],
	  BDESC)
  MEMBER [Measures].[Rank] AS
    RANK([Store].[Store Name].CurrentMember,
	 [OrderedSet])
  SELECT
    {[Measures].[Rank], [Measures].[Sales Value]} ON 0,
    {HEAD([OrderedSet], 10)} ON 1
  FROM
    [SalesCube]

Today’s Frog-Blog top-tips are quite simple ones, but ones that I always find very useful. SSRS Report Performance monitoring.

Once you start to build up a few Reporting Services reports, you need to find out how they’re performing, whether any particular reports are causing the server problems and even which users are running which reports. The following set of queries should point you in the right direction. They should all be run against the ReportServer database.

The 20 most recently run reports

  SELECT TOP 20
     C.Path, C.Name,
     EL.UserName,
     EL.Status,
     EL.TimeStart,
     EL.[RowCount],
     EL.ByteCount,
     (EL.TimeDataRetrieval
          + EL.TimeProcessing
          + EL.TimeRendering)/1000 AS TotalSeconds,
     EL.TimeDataRetrieval,
     EL.TimeProcessing,
     EL.TimeRendering
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  ORDER BY TimeStart DESC

The slowest reports (in the last 28 days)

  SELECT TOP 10
     C.Path, C.Name,
     Count(*) AS ReportsRun,
     AVG((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS AverageProcessingTime,
     Max((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MaximumProcessingTime,
     Min((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MinimumProcessingTime
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
  GROUP BY C.Path,C.Name
  ORDER BY
     AVG((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) DESC

The most active users

  SELECT TOP 10
     EL.UserName,
     Count(*) AS ReportsRun,
     Count(DISTINCT [C.Path]) AS DistinctReportsRun
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
  GROUP BY EL.UserName
  ORDER BY Count(*) DESC

The most popular reports

  SELECT TOP 10
     C.Path, C.Name,
     Count(*) AS ReportsRun,
     AVG((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS AverageProcessingTime,
     Max((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MaximumProcessingTime,
     Min((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MinimumProcessingTime
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
  GROUP BY C.Path, C.Name
  ORDER BY Count(*) DESC

Failed Reports

  SELECT TOP 20
     C.Path, C.Name,
     EL.UserName,
     EL.Status,
     EL.TimeStart,
     EL.[RowCount],
     EL.ByteCount,
     (EL.TimeDataRetrieval
          + EL.TimeProcessing
          + EL.TimeRendering)/1000 AS TotalSeconds,
     EL.TimeDataRetrieval,
     EL.TimeProcessing,
     EL.TimeRendering
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.Status <> 'rsSuccess'
  ORDER BY TimeStart DESC

There are countless other variations, but this should be enough to get you going.

One of the most frustrating limitations of SQL Server 2005 Standard edition is that it doesn’t support semi additive measures in SSAS Analysis Services cubes. This post explains a work around that provides similar functionality without having to shell out for the Enterprise Edition.

What Are Semi Additive Measures?

Semi Additive measures are values that you can summarise across any related dimension except time.

For example, Sales and costs are fully additive; if you sell 100 yesterday and 50 today then you’ve sold 150 in total. You can add them up over time.

Stock levels however are semi additive; if you had 100 in stock yesterday, and 50 in stock today, you’re total stock is 50, not 150. It doesn’t make sense to add up the measures over time, you need to find the most recent value.

Why are they important?

Whether they are important to you or not depends entirely on what you are trying to do with your cube. If all of your required measures are fully additive then you really don’t need to worry about anything. However as soon as you want to include measures such as stock levels, salarys, share prices or test results then they become pretty much essential.

Why are they not available in SQL Standard edition?

Microsoft has to have some way of pursuading us to pay for the Enterprise edition!

How can I get this functionality within SQL Standard?

Firstly we need to understand what semi additive measures do. By far the most common aggregation used is the LastNonEmpty function, so we’ll stick with that as an example. This basically says that whatever time frame you are looking at, find the most recent value for each tuple. This really is a fantastically powerful function, which only really becomes apparent whan you don’t have it!

Lets say that you perform a stock take of different products on different days of the week. You will have a stock entry for product A on a Thursday and product B on a Friday. The LastNonEmpty function takes care of this for you, if you look at the stock level on Saturday it will give you the correct values for both A and B, even though you didn’t perform a physical stock take on the Saturday.

If you then add the time dimension into the query, SSAS will perform this function for each and every time attribute shown, and then aggregate the results up to any other dimensions used. i.e. Each month will then display the sum of all LastNonEmpty values for all products within that month, essentially the closing stock level for each and every month.

To replicate this in Standard Edition, we need to split the work up into two stages.
1) Create daily values in the data warehouse
2) Use MDX to select a single value from the time dimension.

Think of this as splitting up the LastNonEmpty function into two, ‘Last’ and ‘Non Empty’. The ‘Non Empty’ bit essentially fills in the blanks for us. If a value doesn’t exist for that particular day, it looks at the previous day’s value. The ‘Last’ bit says that if we are looking at months in our query, find the value for the last day in that month. The same goes for years, or indeed any other time attribute.

To code up a full LastNonEmpty function ourselves in MDX would be too slow to query as soon as you get a cube of any reasonable size. One of the key benefits of a cube is speed of querying data and we don’t want to impact this too much, therefore we move some of the donkey work into the ETL process populating the datawarehouse. This leaves the cube to perform a simple enough calculation so as to not cause any problems.

1) The ‘Non Empty’ bit

Lets say that have a table called tblStock, containing the following data

We need to expand this into a new fact table that contains one record per day per product.

There are a number of ways of doing this, I’ll describe one here that should suit most situations, although you may need to customise it to your own situation, and limit it to only updating changed/new records rather than re-populating the entire table, but you get the idea. I should point out that you would be much better off populating this as part of your ETL process, but I’m showing this method as it’s more generic.

You need a list of all available dates relevant to your data warehouse or cube. If you already have a time dimension table then use this, otherwise create a SQL function that returns you a list of dates, such as this one:

   CREATE FUNCTION [dbo].[FN_ReturnAllDates](
         @DateFrom DateTime, @DateTo DateTime)
         RETURNS @List TABLE (Date DateTime)
     BEGIN
     DECLARE @tmpDate DateTime
     SET @tmpDate = @DateFrom
     WHILE @tmpDate<=@DateTo
       BEGIN
         INSERT INTO @List
           SELECT Convert(datetime,
                 Convert(Nvarchar,@tmpDate, 102), 102)
         SET @tmpDate = Dateadd(d,1,@tmpDate)
       END
     RETURN
   END

We need to perform a full outer join between the date dimension and any other relevant dimensions, in this case product. This will generate one record per product per date. We can then perform a sub query for each combination to find the stock level appropriate for that day. (Yes, this will be a slow query to run – I did say you should do it in your ETL process!)

     INSERT INTO FactStock
        (StockTakeDate, ProductID, StockLevel)
     SELECT D.Date, P.ProductID,
           ISNULL((SELECT TOP 1 StockLevel
              FROM tblStock
              WHERE ProductID = P.ProductID
                 AND StockTakeDate<=D.Date
              ORDER BY StockTakeDate DESC),0)
        FROM FN_ReturnAllDates((SELECT Min(StockTakeDate)
                      FROM tblStock),GetDate()) D
           FULL OUTER JOIN
                    (SELECT ProductID FROM tblProduct) P ON 1=1

2) The ‘Last’ bit

Now that we have a large fact table consisting of one record per product/date, we can load this into the cube.

If you just add the StockLevel field as a measure and browse the results, you’ll quickly see that if you view it by month, you will get each day’s stock level added together giving you a non-sensical value. To fix this we need to tell Analysis Services to only show one day’s value.

To do this we first need to find all descendents of the current time member at the day level, using something like this:

     DESCENDANTS([Time].[Year Month Day].CurrentMember,
       [Time].[Year Month Day].[Day])
       --Please modify to suit your own date hierarchy! 

We can then find the last member (giving us the closing stock level) by using TAIL():

     TAIL(DESCENDANTS([Time].[Year Month Day].CurrentMember,
          [Time].[Year Month Day].[Day]))

You could aso use HEAD() if you wanted to find the opening stock instead of closing.

You should hide the actual StockLevel measure to prevent users from selecting it, I usually alias these with an underscore, as well as making them invisible, just for clarity. You can then add a calculated member with the following MDX:

     CREATE MEMBER CURRENTCUBE.[MEASURES].[Stock Level Close]
      AS SUM(TAIL(DESCENDANTS([Time].[Year Month Day].currentmember,
                    [Time].[Year Month Day].[Day])),
                    [Measures].[_Stock Level]),
     FORMAT_STRING = "#,#",
     VISIBLE = 1  ;

Or you can calculate the average stock over the selected period

     CREATE MEMBER CURRENTCUBE.[MEASURES].[Stock Level Avg]
      AS AVG(DESCENDANTS([Time].[Year Month Day].currentmember,
                   [Time].[Year Month Day].[Day]),
                   [Measures].[_Stock Level]),
     FORMAT_STRING = "#,#",
     VISIBLE = 1  ;

Or the maximum value

     CREATE MEMBER CURRENTCUBE.[MEASURES].[Stock Level Max]
      AS MAX(DESCENDANTS([Time].[Year Month Day].currentmember,
                   [Time].[Year Month Day].[Day]),
                   [Measures].[_Stock Level]),
     FORMAT_STRING = "#,#",
     VISIBLE = 1  ;

Or the mimimum value

     CREATE MEMBER CURRENTCUBE.[MEASURES].[Stock Level Min]
      AS MIN(DESCENDANTS([Time].[Year Month Day].currentmember,
                  [Time].[Year Month Day].[Day]),
                  [Measures].[_Stock Level]),
     FORMAT_STRING = "#,#",
     VISIBLE = 1  ;

And there you have it, semi additive measures in SQL Server 2005 Standard Edition!

Even though this method does work well, it is still not as good as having the Enterprise edition. The built in functions of Enterprise will perform significantly better than this method, and it saves having to create the large (potentially huge) fact table. This process will also only work on a single date hierarchy. If you have multiple hierarchies (i.e. fiscal and calendar) you will need to enhance this somewhat.

I think it’s about time for a good old rant. Nothing to do with Business Intelligence, but…

Anyone out there who uses Sky Anytime (and I believe the BBC iPlayer) will have probably noticed that they use a nasty peer-to-peer file sharing application called Kontiki, which they install automatically without giving you any control over. My problem is that I have a good 8Mb download speed but my upload is still a rather paltry 256K (I know, it’s almost embarrasing). I use Sky Anytime to download a film probably no more than once a month, the rest of the time Kontiki sits there on my PC hogging my precious upload bandwith. They don’t give you any control over restrict it or turning it off – hense my rant.

I’m happy to leave it running for a bit when it’s not causing a problem, but I want to easily be able to turn it off. So, the solution? Create a Kontiki-Killer batch file that disables it. When you next launch Sky Anytime it will automatically load again, but when you’re finished, you can run this batch file to kill the little blighter.

It kills the KHost.exe process, stops the KService service, sets the KService to start manually not automatically (so it will only start when you launch Sky Anytime), and removes KHost.exe from the startup options in the registry (Sky will add them back in when it is next launched).

I use this on my XP Pro SP2 box, I’ve not tried it on any other OS, so I can not accept any responsibility for any problems is may cause – use entirely at your own risk.
Copy this into a blank text file, and save it as KillKontiki.bat

@Echo off
Echo.
Echo.
Echo    Kontiki-Killer...
Echo.    - In progress...
Echo.
Echo    Killing KHost.exe process...
taskkill /IM KHost.exe /T /f
Echo.
Echo    Stopping KService service...
NET Stop KService
Echo.
Echo.   Making the KService service on demand not on startup
sc config KService start= demand
Echo.
Echo    Removing from Registry startup....
REG DELETE HKLM\Software\Microsoft\Windows\CurrentVersion\Run /v kdx /f
REG DELETE HKCU\Software\Microsoft\Windows\CurrentVersion\Run /v kdx /f
Echo.
Echo.
Echo    Kontiki blitz complete.
Echo.
Echo.                                        (C) Purple Frog Systems
Echo.
pause

You can also download it here

Have you ever tried to reverse engineer an Excel pivot table? It’s not as easy as you would think! Whether you just want to find out the datasource details, or identify the query that was used, there is just no simple way of getting Excel to tell you.

The macro below will loop through every sheet in a workbook, and will document the datasources, SQL or MDX queries as well as the page, row, column and data fields.

To use it, add it into your macros, then select a starting cell where you want te report to be placed and run the macro. It’s pretty raw, and may need some tweaks to suit your requirements but it should give you a good starting point. I use it on Excel 2003 MDX pivots from SQL Server Analysis Services 2005, but I presume it will work on other versions of Excel as well.

Public Sub PivotDetails()
   Dim ws As Worksheet
   Dim qt As QueryTable
   Dim pt As PivotTable
   Dim pc As PivotCache
   Dim pf As PivotField 

   For Each ws In ActiveWorkbook.Sheets 

      For Each qt In ws.QueryTables
        ActiveCell.Value = "Sheet"
        ActiveCell.Offset(0, 1).Value = ws.Name 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Data Source"
        ActiveCell.Offset(0, 1).Value = qt.Connection 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Query"
        ActiveCell.Offset(0, 1).Value = qt.CommandText
      Next qt 

      ActiveCell.Offset(2, 0).Select 

      For Each pt In ws.PivotTables 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Pivot Table"
        ActiveCell.Offset(0, 1).Value = pt.Name 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Connection"
        ActiveCell.Offset(0, 1).Value = pt.PivotCache.Connection 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "SQL"
        ActiveCell.Offset(0, 1).Value = pt.PivotCache.CommandText 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "MDX"
        ActiveCell.Offset(0, 1).Value = pt.MDX 

        For Each pf In pt.PageFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Page"
            ActiveCell.Offset(0, 1).Value = pf.Name
            ActiveCell.Offset(0, 2).Value = pf.CurrentPageName
        Next pf 

        For Each pf In pt.ColumnFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Column"
            ActiveCell.Offset(0, 1).Value = pf.Name
        Next pf 

        For Each pf In pt.RowFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Row"
            ActiveCell.Offset(0, 1).Value = pf.Name
        Next pf 

        For Each pf In pt.DataFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Data"
        Next pf 

      Next pt
   Next ws
End Sub

This post explains a method of enhancing Reporting Services reports to make them more user friendly. I have been quizzed many times by end users who want reports to remember the parameter selections so that next time they run the report they haven’t got to re-select the same parameters. This is especially useful if there are a lot of long parameter lists, and each user tends to run the same selection frequently.

This process is made possible by the User!UserID global variable within SSRS. It contains the domain and username of the user running the report (DOMAIN\USER).

There are a number of stages required to get this to work :-
- New SQL table to store users’ selections
- New stored procedures to save and load selections
- Two new datasets within the report

Firstly create a database table to store the selections

   CREATE TABLE tblUserSetting(
      ID int IDENTITY(1,1) NOT NULL,
      UserID varchar(100) NULL,
      Report varchar(100) NULL,
      Setting varchar(100) NULL,
      Val varchar(max) NULL
   )

Then add a couple of stored procedures, one to save the default and one to retrieve it.

   CREATE PROC spu_SetUserSetting
      (
         @UserID varchar(100),
         @Report varchar(100),
         @Setting varchar(100),
         @Value varchar(max)
      )
   AS
   BEGIN
      --Firstly delete any old settings for this parameter
      DELETE FROM tblUserSetting
            WHERE UserID=@UserID
            AND Report=@Report
            AND Setting=@Setting

     --Now add the new setting
      INSERT INTO tblUserSetting
         (UserID, Report, Setting, Val)
      VALUES
         (@UserID, @Report, @Setting, @Value)

     --Return the new setting
      SELECT @Value AS Val
  END

  CREATE PROC spr_GetUserSetting
      (
         @UserID varchar(100),
         @Report varchar(100),
         @Setting varchar(100),
         @Default varchar(max)=NULL
      )
   AS
   BEGIN
      DECLARE @Val varchar(max)
      SET @Val = ISNULL((SELECT Val FROM tblUserSetting
            WHERE UserID=@UserID
            AND Report=@Report
            AND Setting=@Setting
        ),ISNULL(@Default,''))

     SELECT @Val AS Val
   END

Now on to the report.

Lets say that we have a parameter called ‘TestParam’. The first thing we need to do is create a new dataset that will be used to retrieve the default value if one exists.

Name the new dataset dsTestParamDefault, the CommandType should be StoredProcedure, the query string should be spr_GetUserSetting.

Edit the Dataset and select the parameters tab. SSRS has already created report parameters for each of the proc’s parameters. We need to replace these with our own values.

   @UserID    =User!UserID
   @Report    =Globals!ReportName
   @Setting   ="TestParam"
   @Default   =Nothing

(if you want to you can specify a default value here for when the user runs the report for the first time)

Set the default value of your TestParam parameter to be the Val field of the dsTestParamDefault dataset.
The parameter will now load the default from the appropriate database record for that user.

Now we need to add the ability to save the users selection for next time.

Create a new dataset named dsTestParamSave, the CommandType should be StoredProcedure, the query string should be spu_SetUserSetting.

Edit the Dataset and select the parameters tab. SSRS has already created report parameters for each of the proc’s parameters. Again, we need to replace these with our own values.

   @UserID    =User!UserID
   @Report    =Globals!ReportName
   @Setting   ="TestParam"
   @Value     =Parameters!TestParam.Value

You will then need to delete the four report parameters SSRS created for you (UserID, Report, Setting and Default).

And that’s it! When the user first executes the report, it will ask them for the parameter value (or use the default if you specified one). However the next time they run it it will automatically remember what value they last used. As it is stored by username, it doesn’t matter whether they use the same PC or not.

The example above is for a simple single-valued parameter, however this technique can also be used for more complex parameters. The parameter could have a list of available values, and can even be multi-valued. If you want to use a multi-valued parameter, then you only need to make a couple of minor changes…

The spr_GetUserSetting will need changing to

   BEGIN
      DECLARE @Val varchar(max)
      SET @Val = ISNULL((SELECT Val FROM tblUserSetting
            WHERE UserID=@UserID
            AND Report=@Report
            AND Setting=@Setting
         ),ISNULL(@Default,''))

     SELECT Val FROM dbo.Split(',', @Val)
   END

For this to work you will need to find yourself a suitable Split function. (Search Google for ‘SQL Server Split’ to find hundreds that are suitable, or I’ve put one in the linked sample file)Then change the @Value parameter of the dsTestParamSave dataset to

   =Join(Parameters!TestParam.Value, ",")

This will store a comma delimited value containing all values selected. If you use commas in your data then choose a suitable alternative as a delimiter.

I have put some working examples of all of this in the link here along with copies of all relevant SQL script…
http://www.PurpleFrogSystems.com/Download/blog/ParameterMemory.zip

Hope this is of some use!

Alex

There isn’t yet a built-in facility within Reporting Services to open a report link (i.e. a drillthrough report) in a new browser window or tab, however it isn’t too tricky to get this to work using a bit of javascript.

javascript:void(window.open(Destination,’_blank’))

My preferred method is to wrap this up into a custom code function such as

Function OpenURL(ByVal URL As String) As String
Return “javascript:void(window.open(‘” & URL & “‘,’_blank’))”
End Function

In your report, you can then just set the navigation property to jump to a URL of a website:

=Code.OpenURL(“http://www.purplefrogsystems.com“)

Or to the path of a the drillthrough report:

=Code.OpenURL(“http://[Your Server]/ReportServer?/Folder/ReportName&rs:Command=Render”)

If you have parameters that you need to pass in, you can add these into the URL either fixed or dynamic:

=Code.OpenURL(“http://[Your Server]/ReportServer?/Folder/ReportName&rs:Command=Render &MyFirstParam=Value1&MySecondParam=” & Fields!MyField.Value)

Please note that this will not work in the BIDS development environment, it will only work once you have deployed the report to the Report Server.