skip to Main Content

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(June 9, 2010) as Year,
                               Month(June 9, 2010) as Month,
                               DAY([DATE]) as Day,
                               June 9, 2010 as Date,
                               CASE Month(June 9, 2010)
                                                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, June 9, 2010)  as Weeknumber
from DateSequence
group by YEAR(June 9, 2010),Month(June 9, 2010),DATENAME(MONTH,[Date]), June 9, 2010
option (MaxRecursion 10000)

end
GO

Importing this

will give me:

Great tip got from Dave Wickert 🙂

This Post Has 15 Comments
  1. Great idea. I’m sure it can be ported – in part – through VBA for those rare PowerPivot standalone users who don’t have Sharepoint… sigh… Except that Powerpivot doesn’t interact with VBA, but that’s what linked tables are for!

    Question for all the powers that be. I created my own date table, using the same start date and format as my data source. Can I put future dates in, ie beyond 2010 and if so – how far?

    I just know that in some measures, PowerPivot likes consecutive timeline and ocmparing future trends against dates that don’t exist in my sales data may prove problematic.

    Then next part of this question is that with Kasper, Mr.PowerpivotPro himself, and others, I’ve seen examples with Time Intelligent functions that are great for comparing sales trends but I haven’t seen any examples of future trend analysis/forecasting. Anyone want to step up to the plate on this?

    1. Hi Josh,

      I agree MonthShort would do in english but i created this for my own language, then this unfortunately doesn’t work.

      What is the reason you want to use the other generator?

      Kasper

      1. One of the first objects I create on a database is a generator function, and this one is the best that I have come across. There are many situations that a procedure will require some generated data and it is easy to forget to include max recursion, or inadvertently introduce byzantine errors, due to recursion issues, that are otherwise unrelated to the original generator.

  2. salam i’ve a stored procedure that i use temp tables and cursor in it when i try to use it in poerpivot for validation it give me error
    (The SQL statement is not valid. There are no columns detected in the statement.)
    and when i test it using design it runs successfully any idea how to user stored procedure with temp tables and cursors

  3. It seems to me that “SET NOCOUNT ON” should be used.

    When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

  4. Hi,

    I have a stored procedure that works fine when sending parameters.

    But. When creating another SP that calls the first one and sends parameters to it, I’m getting the “The SQL statement is not valid. There are no columns detected in the statement.”

    This is the SP:

    CREATE PROCEDURE [dbo].[GetFKData_TblParam]
    AS
    SET NOCOUNT ON;
    declare @DBName nvarchar(250)
    declare @TableName nvarchar(250)

    set @DBName = (Select top 1 DB from dbo.SP_Params)
    set @TableName = (Select top 1 Tbl from dbo.SP_Params)

    EXECUTE dbo.GetFKData @DBName, @TableName;
    GO

    needles to say that the above works fine in the design window, but it won’t validate…

    Any ideas?

    (I’m using TRM version of PowerPivot)

    Thanks!
    Yinon

Leave a Comment or Question