Calling a Stored procedure in PowerPivot

By Kasper de Jonge, crosspost from PowerPivotblog.nl

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:

Great tip got from Dave Wickert 🙂

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Exciting Improvements to Power BI’s Export to Excel

It is a standing joke in the analytics industry that “Export to

Read the Blog

The case of the disappearing Field Parameters: SOLVED

The Case: The new Field Parameters feature from Microsoft had been added

Read the Blog

Completing the Set Up: Field Parameters Using Tabular Editor

May 2022’s release of Power BI Desktop is the best releases we’ve

Read the Blog

Calculation Groups to the Rescue!

o set the stage, I need you to travel back in time

Read the Blog