0845 643 64 63

Monthly Archives: January 2008

Extract Datasource and Query from Excel Pivot

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

Report Parameter Selection Memory

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…
https://www.purplefrogsystems.com/Download/blog/ParameterMemory.zip

Hope this is of some use!

Alex

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon