Calling a Stored procedure in PowerPivot to populate your date table

While building a new PowerPivot workbook i wanted to use Time Intelligent functions. One of the golden rules of PowerPivot time intelligent functions is to create a seperate related time table. I have created a stored procedure which creates a new table in PowerPivot. The important thing to do is when you want to call a stored procedure withing PowerPivot is set the “SET NOCOUNT OFF” at the beginning of your sproc. This will make sure your stored procedure will return only one dataset. 

You can use this code to create the range:

/*
Get a range of dates in the range @fromdate until @todate
*/
create procedure getDateRange
@fromdate datetime, @todate datetime
as
begin
SET NOCOUNT OFF
/*Declare @todate datetime, @fromdate datetime
select @fromdate = '2005-01-01'
select @todate = '2008-12-31'*/

;With DateSequence( [Date] ) as
(
                Select @fromdate as [Date]
                               union all
                Select dateadd(day, 1, [Date])
                               from DateSequence
                               where Date < @todate
)

select
                               YEAR([date]) as Year,
                               Month([date]) as Month,
                               DAY([DATE]) as Day,
                               [date] as Date,
                               CASE Month([date])
                                                WHEN 1 THEN 'Jan'
                                               WHEN 2 THEN 'Feb'
                                               WHEN 3 THEN 'Mar'
                                               WHEN 4 THEN 'Apr'
                                               WHEN 5 THEN 'May'
                                               WHEN 6 THEN 'Jun'
                                               WHEN 7 THEN 'Jul'
                                               WHEN 8 THEN 'Aug'
                                               WHEN 9 THEN 'Sep'
                                               WHEN 10 THEN 'Okt'
                                               WHEN 11 THEN 'Nov'
                                               WHEN 12 THEN 'Dec'
                               END as [MonthShort],
                               DATENAME(MONTH,[Date]) as [MonthName],
                               max(DATEPART(DAYOFYEAR,[Date])) as DayInYear,
                               DATEPART( wk, [date])  as Weeknumber
from DateSequence
group by YEAR([date]),Month([date]),DATENAME(MONTH,[Date]), [date]
option (MaxRecursion 10000)

end
GO

Importing this

will give me:

  • Hi Kasper, I also thought about this topic. The idea was to create a simple table in Excel sheet, where you put a start date in A2 cell and drag it downwards using the bottom-right corner (or Fill -> Series …) until you reach the end date (which is visible during dragging). It should be 31th December of one year. The start date should be 1st of January of a year before. Time span is completely up to the end user.

    Then you would go to cells B2, C2, etc and use the TEXT() function to get all the columns you have from stored procedure. For example, =TEXT(A2; “yyyy”) returns the year. Yes, there’s a shortcut in a form =YEAR(A2), but not all columns have shortcut version. There’s a nice help for it explaining various formats.

    PowerPivot is ment for Excel users, so I believe creating a table like that is easier for them instead using stored procedure. Which can be copy-pasted from Excel to Excel later. Btw, we can fix column A in the formula using $A and then drag the B2 cell right (with a TEXT formula in it), then change the format in other cells. Might be a quicker option.

    We’ll need headers too (that’s why I started from A2, not A1). When one makes such a table (and quickly formats it), that table can be used in PowerPivot using “Create Linked Table” button in PowerPivot tab of Excel 2010.

    Alternatively, the table in Excel can consists of only the date column (the first one) while the rest of the columns might be built using DAX calculations.

    Later I performed a search and found out Vidas has already cover this here:
    http://powerpivot-info.com/post/208-q-how-can-i-create-calendar-datetime-table-for-powerpivot-if-i-do-not-have-any-source-for-that
    and here:
    http://powerpivot-info.com/post/213-q-how-can-i-create-fiscal-datetime-table-for-powerpivot-if-i-do-not-have-any-source-for-that

  • Kasper de Jonge

    Hi @Tomislav Piasevoli ,
    Great method as well, thanks for sharing. Your method is indeed much better for the average excel user. I think PowerPivot will be used by BI pro’s a lot as well.

    This post was also to be used to show how you can use a stored procedure in PowerPivot

  • Kasper de Jonge :
    This post was also to be used to show how you can use a stored procedure in PowerPivot

    Yes, I noticed, it is valuable indeed from that perspective because it reminds us that we can use something else besides tables and views.