Masi,
Create a SQL sample report of what you want your templete to be and then copy the rdl from to this local directory C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
all rdl files in this directory show up when you add new item in BIDS.
Hammer
|||Thanks
It works
|||Not a problem.
I am working on a new reporting system using reporting services, but I cannot figure out how to create a footer row which will only subtotal select group totals. If anyone has a method to do this please help!
Nathan
If you have a matrix report right click on the group and select the option subtotal.I've been able to do that for individual groups, but what I want to do is make footer subtotal of a select set of groups. So say I have data grouped by Credit card type. I have a group for MC, and another for Visa, and another for American express.
I want a footer total of just the MC and Visa groups, excluding the total for American express.
--Robert|||Alright!
Thank you Robert. you made my day 
|||
When running this selective sum, i get a scope error. I have tried giving it a group name and a dataset. What am i doing wrong? here is my code:
=Sum(iff(Fields!CardType.Value = "Visa/Mc" OR Fields!cardType.Value = "Diner" OR Fields!cardType.Value = "JCB", Fields!amount.Value, 0))
This is my error:
"The value expression for the textbox ‘textbox9’ refers to the field ‘CardType’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Whats wrong?
|||I have the same problem. I don't want to sum a select number of groups, but all groups within the report. The principle is the same as the above, and I get the same scope error.-- Robert|||If you want to get the grand total, you just need to specify either the data region name (i.e. table, list, or matrix report item name) or the data set name.
E.g.
=Sum(Fields!Amount.Value, "DataSet1")
-- Robert|||Thanks, Robert! I knew it had to be something simple. |||Hi, somehow related with the topic:
Is posible to have something like: the sum of the ValueField from all the rows of DataSet2 that have CompareFiled equal with the current value of ComparedWithField from DataSet1?
In other words: in the expression of the SUM function can be used more then one scope?
=Sum(iff( DS2!Fields!CardType.Value = Fields!CT.Value, DS2!Fields!amount.Value, 0))
Hello,
I am wondering if there is some sample code out there that shows how to create a subscription for a report on reporting services via a win app or if anyone has a better suggestion. We are wanting to have a report that resides on reporting services server be sent to a client via email subscription, but do not want the client to goto the actual website that host reporting services. Thanks in advance.
John
There are SOAP APIs that lets you create subscriptions programmatically. Is that what you are looking for? See the following link for details.
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx
Thanks,
Sharmila
|||This was it. Thank you!!Hi All,
Is there a way to create a report in SQL2005 reporting services to call a stored procedure that has parameters? I keep getting an error saying i am not supplying the params, I can't seem to find where to put my parameters for the report to use.
Thanks, Joe
You would be better posting this on the reporting services forum, as this is mainly for T-SQL issuesIs there a way to create a report in SQL2005 reporting services to call a stored procedure that has parameters? I keep getting an error saying i am not supplying the params, I can't seem to find where to put my parameters for the report to use.
Thanks,
Joe
How about something like
exec uspTest @.parm1, @.parm2
If I remember right it will provide a couple of boxes for you to type the parm values when you view the report in Report Manager
|||the parameters (Report Parameters) you have in RS should have the same name as the parameters you declare in stored procedure.sqlHi all,
I'm just getting started with Reporting Services and have a question.
I'm trying to set up a report model based on a UDB data source. However when I use the report model wizard, I get presented with:
[DB2/SUN] SQL0104N An unexpected token "SET TRANSACTION" was found following "BEGIN-OF-STATEMENT" ... SQLSTATE 42601.
I think that this is trying to set transaction isolation levels however this is not valid SQL for a UDB database.
Is there something that I'm missing with my configuration to make RS know that this is a non-SQLServer database?
Many thanks,
JK
Hello,
Did you ever receive an answer to this issue?
Thanks.
Hi all,
I'm just getting started with Reporting Services and have a question.
I'm trying to set up a report model based on a UDB data source. However when I use the report model wizard, I get presented with:
[DB2/SUN] SQL0104N An unexpected token "SET TRANSACTION" was found following "BEGIN-OF-STATEMENT" ... SQLSTATE 42601.
I think that this is trying to set transaction isolation levels however this is not valid SQL for a UDB database.
Is there something that I'm missing with my configuration to make RS know that this is a non-SQLServer database?
Many thanks,
JK
Hello,
Did you ever receive an answer to this issue?
Thanks.
We would like to create a corporate reporting portal. It must be reasonably quick and customisable with our corporate logos and look and feel.
We have a RS2005 server and a number of reports.
We also have a number of other reports that we would like to display that are in various formats (.pdf, .asp, .aspx, .xls etc).
I like the security model of RS2005.
Is it possible to create a good flexible portal driven by the content and security of the RS server, that will also display PDF, XLS, ASP etc?
Has anyone done this - code samples or commercial product?
I did try sharepoint at one stage buit found it a bit slow and could not modify the look and feel sufficiently. I am willing to try it again if people can vouch that a good result can be achieved that way.
The company I work for has built many portals using both Sharepoint and custom ASP.NET.
If you are looking for a document managment solution with many other features plus a reporting portal option and tight integration with Office I would suggest Sharepoint 2007 & Reporting Services.
If you would like to have a reporting portal with a logo, custom look-and-feel, a display of reports, and custom security based on Reporting Services, I would suggest using ASP.NET & C# to create a custom solution. I can't post the code for our solutions as they are custom for each client. There are quite a few examples out there on how to use the Reporting Services Web Service interface.
Here's one...
http://www.odetocode.com/Articles/95.aspx
if you would like to contact me ( email andrew(dot)sears(at)t4g(dot)com) with details regarding your implementation I can give you further information.
cheers,
Andrew
|||Thanks Andrew,
That was helpful. I looked at the code in http://www.odetocode.com/Articles/95.aspx and have written some code based on this but it still does not address all the issues I have.
We are also going to investigate MOSS, as others in the company are starting to use this. This will take some time I think.
I think there is scope for a book or at least a whitepaper in this area.
I already have the title: "Creating a Reporting Portal using SSRS and MOSS"
Perhaps Microsoft will pay you to write it and me for the idea.
Some of the areas that need addressing are:
. How to manage security with different groups of users who are allowed access to overlapping sets of reports.
. How to deal with linking reports from another folder when the main report uses subreports - I tried this and the subreports dont get loaded because SSRS tries to get them from the folder where the link is.
. How to best publish links to other web resources (e.g. .asp and .pdf) so these are dealt with in a consistent manner.
We would like to create a corporate reporting portal. It must be reasonably quick and customisable with our corporate logos and look and feel.
We have a RS2005 server and a number of reports.
We also have a number of other reports that we would like to display that are in various formats (.pdf, .asp, .aspx, .xls etc).
I like the security model of RS2005.
Is it possible to create a good flexible portal driven by the content and security of the RS server, that will also display PDF, XLS, ASP etc?
Has anyone done this - code samples or commercial product?
I did try sharepoint at one stage buit found it a bit slow and could not modify the look and feel sufficiently. I am willing to try it again if people can vouch that a good result can be achieved that way.
The company I work for has built many portals using both Sharepoint and custom ASP.NET.
If you are looking for a document managment solution with many other features plus a reporting portal option and tight integration with Office I would suggest Sharepoint 2007 & Reporting Services.
If you would like to have a reporting portal with a logo, custom look-and-feel, a display of reports, and custom security based on Reporting Services, I would suggest using ASP.NET & C# to create a custom solution. I can't post the code for our solutions as they are custom for each client. There are quite a few examples out there on how to use the Reporting Services Web Service interface.
Here's one...
http://www.odetocode.com/Articles/95.aspx
if you would like to contact me ( email andrew(dot)sears(at)t4g(dot)com) with details regarding your implementation I can give you further information.
cheers,
Andrew
|||Thanks Andrew,
That was helpful. I looked at the code in http://www.odetocode.com/Articles/95.aspx and have written some code based on this but it still does not address all the issues I have.
We are also going to investigate MOSS, as others in the company are starting to use this. This will take some time I think.
I think there is scope for a book or at least a whitepaper in this area.
I already have the title: "Creating a Reporting Portal using SSRS and MOSS"
Perhaps Microsoft will pay you to write it and me for the idea.
Some of the areas that need addressing are:
. How to manage security with different groups of users who are allowed access to overlapping sets of reports.
. How to deal with linking reports from another folder when the main report uses subreports - I tried this and the subreports dont get loaded because SSRS tries to get them from the folder where the link is.
. How to best publish links to other web resources (e.g. .asp and .pdf) so these are dealt with in a consistent manner.
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||'ad-hoc dist' property controls whether or not the use of OpenRowset/Datasource is allowed. If it's not enabled, you will not be able to execute the query, regardless of the data access provider you use.
Your only option is to create a linked server and use OpenQuery() or 4-part name query.
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
|||Thanks for the reply
I have managed to create a linked server using the following bit of code:
Code Snippet
EXEC sp_addlinkedserver
@.server = 'APPOLO/ACT7',
@.srvproduct = 'SQLServr OLEDB Provider',
@.provider = 'MSDASQL',
@.datasrc='ACT7'
GO
I then created and ran the following statement:
Code Snippet
SELECT *
FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')
I then get the following error message:
"Incorrect Syntac near '/'"
I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.
Any Ideas?
|||How about:
[APPOLO/ACT]
HTH!|||Great - that solved that problem -
I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?
Thanks
Tom
|||You use this to set the login.
Code Snippet
EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||'ad-hoc dist' property controls whether or not the use of OpenRowset/Datasource is allowed. If it's not enabled, you will not be able to execute the query, regardless of the data access provider you use.
Your only option is to create a linked server and use OpenQuery() or 4-part name query.
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
|||Thanks for the reply
I have managed to create a linked server using the following bit of code:
Code Snippet
EXEC sp_addlinkedserver
@.server = 'APPOLO/ACT7',
@.srvproduct = 'SQLServr OLEDB Provider',
@.provider = 'MSDASQL',
@.datasrc='ACT7'
GO
I then created and ran the following statement:
Code Snippet
SELECT *
FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')
I then get the following error message:
"Incorrect Syntac near '/'"
I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.
Any Ideas?
|||How about:
[APPOLO/ACT]
HTH!|||Great - that solved that problem -
I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?
Thanks
Tom
|||You use this to set the login.
Code Snippet
EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'
Hello:
Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:
Jan - 1 - 27
Feb - 28 - 24
Mar - 25 - 31
Currently, I have an SSIS package creating a record for each day in the Time Dimension.
Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?
Thank you!
Hi desibull,
this only way i though it use "dateadd" function for your issue.
check the sample code as below:
decalre @.dt_startdate datetime
set @.dt_startdate = '2007-01-01'
select dateadd(day,0,@.dt_startdate),dateadd(day,27,@.dt_startdate),
|||dateadd(day,28,@.dt_startdate),dateadd(day,55,@.dt_startdate),
dateadd(day,56,@.dt_startdate),dateadd(day,85,@.dt_startdate),
'next_startdate'=dateadd(day,86@.dt_startdate)
use this method for build date list.
take 'next_startdate' replace the @.dt_startdate.
hoping this can help you.
Best Regrads,
Hunt.
Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.
|||The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.
Here's the query i came up with. Nothing succedes like brute force!
You can set any date for @.dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.
Code Snippet declare @.dtFiscalYearStart smalldatetime , @.dtFiscalYearEnd smalldatetime , @.iDaysInFiscalYear smallint set @.dtFiscalYEarStart = 'January 1, 2007' set @.dtFiscalYearEnd = dateadd(yyyy, 1, @.dtFiscalYEarStart) set @.iDaysInFiscalYear = datediff(d, @.DtFiscalYearStart, @.dtFiscalYearEnd) declare @.Numbers table(Num int, dtTemp smalldatetime) insert into @.Numbers select 0, @.dtFiscalYEarStart declare @.i tinyint set @.i = 0 while @.i < 9 begin insert into @.Numbers select Num + power(2,@.i) , Dateadd(d, power(2,@.i), dtTemp) from @.Numbers set @.i = @.i + 1 end delete from @.Numbers where dtTemp >= @.dtFiscalYearEnd select dtTemp , Num + 1 as FiscalDay , Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek , case when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11 else 12 end as [FiscalMonth] , case when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*1) then 1 when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*2) then 2 when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*3) then 3 else 4 end as FiscalQuarter , datepart(dy,dtTemp) as CalendarDayOfYear , Datepart(wk,dtTemp) as CalendarWeekOfYear , Datepart(m,dtTemp) as CalendarMonth , Datepart(q,dtTemp) as CalendarQuarter from @.Numbers order by Dateadd(d, Num, @.dtFiscalYearStart)
Oh rusag2! I Bow To Thee!!!
I truly apologize for not looking at your post for this long. I have not figured out how to get alerts in my emal when a post is entered.
Your code is really amazing. I ran it and am now comparing it with a physical copy of a fiscal calendar I got from Finance. We seem to be off by a day. You see, at DBL we end our weeks on a Sat; the first month therefore ends on the 27th instead of the 28th, and although the 28th happens to be a Sunday it is a big deal for us as we process e-commerce orders on Sundays. Further, our year will end on the 29th and 2008 will being on the 30th.
As I would not know where to being modifying your code to accomplish the above I would sincerely appreciate some direction from you.
Thanks so much for taking the time to write the code!!! I would love to use it but need to make the changes I have indicated above.
Thanks again!
|||Near the very top, there is a "FiscalYearStart" variable. Currently, it's set to January 1, 2007. That's a Monday...which, following a 4, 4, 5 rule, (which is weeks) then if the fiscal year starts on January 1, 2007...well then the week ends on Sunday.
Try changing the value of that variable to "December 31, 2006" (that's a sunday). That way, the last day of the week will be saturday.
|||Thanks!
I almost get what I want when I start the date on December 31, 2006. I need to check with Finance if it is correct though.
The other issue is that December 2007 should end on the 29th, and fiscal 2008 should start on December 30th. How can I get your code to do this.
Early on you mentioned that the last quarter needed to be 93 days; can we not have that be the case because at DBL we actually end our fiscal year on the 29th.
|||Ok, the first answer was a bit...over the top.
Try this. Explicitly define the StartOfFiscalYear and EndOfFiscalYear dates:
Code Snippet
--A few variables:
declare @.dtFiscalYearStart smalldatetime
, @.dtFiscalYearEnd smalldatetime
, @.iTemp int
This is the table we'll populate and return at the end
declare @.tb table(DayOfFiscalYear int identity (1,1)
,CalendarDate smalldatetime
, FiscalWeek int
, FiscalMonth tinyint
, FiscalQuarter tinyint)
--Now, populate our variables:
--This can be any date you choose. We assume that the fiscal year
--begins on the first day of the "fiscal week"
--We explicity populated STart of Fiscal Year and End
set @.dtFiscalYearStart = 'December 31, 2006'
set @.dtFiscalYearEnd = 'December 29, 2007'
set @.iTemp = 0
--Here's the loop to populate our output table:
while not exists(select * from @.tb where CalendarDate >= @.dtFiscalYearEnd)
begin
insert into @.tb (CalendarDate, FiscalWeek)
select dateadd(dd, @.iTemp, @.dtFiscalYearStart), (@.iTemp / 7) + 1
set @.iTEmp = @.iTemp + 1
end
update @.tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 5
update @.tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 9
update @.tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 14
update @.tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 18
update @.tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 22
update @.tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 27
update @.tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 31
update @.tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 35
update @.tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 40
update @.tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 44
update @.tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 48
update @.tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek > 47
--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.
--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!
--uncomment this for a double-check of week counts
--select FiscalMonth,count(distinct fiscalWeek) from @.tb group by FiscalMonth
select * from @.tb
|||Well rusag2, life is just about the get more interesting.
I just had a conversation with Finance and they confirmed that the Fiscal Calendar is not going to have 365 days all the time. Further, the calendar method that has been in use for awhile is called the Retail Calendar, which is what I need to take a look at.
Basically, every so often the last quarter of the year becomes a 4-4-6 to "catch-up" for a wekk lost in previous years.
I have to get to the bottom of this and so am going to do some research on how the Retail Calendar can be programmed. Apparently all the retail stores have this programmed so I am hoping there is something out there.
I will keep you posted.
Thanks a bunch again for your efforts!!
desibull
|||rusag2:
Can you modify your code to accept the start dt, end dt, and the number of weeks for the last quarter as variables and then just cutoff the year when you reach that last day?
Let me know if I am pushing my luck! Your code is almost there and I really would like it to work.
|||Use my most recently posted code. You explicity specify the Fiscal Year Start Date and Fiscal Year End Date.
Then, I build the year, one week at a time, going 4-4-5 for each quarter until the last when I go 4-4-<Whatever Is Left>.
|||I did and it is almost working like a charm except that for some reason the script is creating an extra day at the end. Any clues why?
For example, I provided the following values: Start Dt: December 31, 2006 End Dt: December 29, 2007. I got a record for 12/30/07, which I should not. It should be part of 08.
|||This is a quesion of "Through" vs. "To"
Just adjust your end date.
Or you may change this:
CalendarDate >= @.dtFiscalYearEnd
to this:
CalendarDate > @.dtFiscalYearEnd
|||Actually, setting it to >= worked. Somehow it got changed to >.
Your code now is fully functional.
I cannot thank you enough, rusag2. When I have some spare time I would like to go through your code and understand what you have done. Many of the functions you have used are new to me. It is one slick code though!!
Thanks!
Spoke too soon! Your second code while it produced the correct end date does not set the Fiscal month correctly. Your first code is working correctly. I made a similar change to the first one and it works.
Regardless, you are a genius!!
Hello:
Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:
Jan - 1 - 27
Feb - 28 - 24
Mar - 25 - 31
Currently, I have an SSIS package creating a record for each day in the Time Dimension.
Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?
Thank you!
Hi desibull,
this only way i though it use "dateadd" function for your issue.
check the sample code as below:
decalre @.dt_startdate datetime
set @.dt_startdate = '2007-01-01'
select dateadd(day,0,@.dt_startdate),dateadd(day,27,@.dt_startdate),
|||dateadd(day,28,@.dt_startdate),dateadd(day,55,@.dt_startdate),
dateadd(day,56,@.dt_startdate),dateadd(day,85,@.dt_startdate),
'next_startdate'=dateadd(day,86@.dt_startdate)
use this method for build date list.
take 'next_startdate' replace the @.dt_startdate.
hoping this can help you.
Best Regrads,
Hunt.
Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.
|||The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.
Here's the query i came up with. Nothing succedes like brute force!
You can set any date for @.dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.
Code Snippet declare @.dtFiscalYearStart smalldatetime , @.dtFiscalYearEnd smalldatetime , @.iDaysInFiscalYear smallint set @.dtFiscalYEarStart = 'January 1, 2007' set @.dtFiscalYearEnd = dateadd(yyyy, 1, @.dtFiscalYEarStart) set @.iDaysInFiscalYear = datediff(d, @.DtFiscalYearStart, @.dtFiscalYearEnd) declare @.Numbers table(Num int, dtTemp smalldatetime) insert into @.Numbers select 0, @.dtFiscalYEarStart declare @.i tinyint set @.i = 0 while @.i < 9 begin insert into @.Numbers select Num + power(2,@.i) , Dateadd(d, power(2,@.i), dtTemp) from @.Numbers set @.i = @.i + 1 end delete from @.Numbers where dtTemp >= @.dtFiscalYearEnd select dtTemp , Num + 1 as FiscalDay , Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek , case when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11 else 12 end as [FiscalMonth] , case when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*1) then 1 when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*2) then 2 when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*3) then 3 else 4 end as FiscalQuarter , datepart(dy,dtTemp) as CalendarDayOfYear , Datepart(wk,dtTemp) as CalendarWeekOfYear , Datepart(m,dtTemp) as CalendarMonth , Datepart(q,dtTemp) as CalendarQuarter from @.Numbers order by Dateadd(d, Num, @.dtFiscalYearStart)
Oh rusag2! I Bow To Thee!!!
I truly apologize for not looking at your post for this long. I have not figured out how to get alerts in my emal when a post is entered.
Your code is really amazing. I ran it and am now comparing it with a physical copy of a fiscal calendar I got from Finance. We seem to be off by a day. You see, at DBL we end our weeks on a Sat; the first month therefore ends on the 27th instead of the 28th, and although the 28th happens to be a Sunday it is a big deal for us as we process e-commerce orders on Sundays. Further, our year will end on the 29th and 2008 will being on the 30th.
As I would not know where to being modifying your code to accomplish the above I would sincerely appreciate some direction from you.
Thanks so much for taking the time to write the code!!! I would love to use it but need to make the changes I have indicated above.
Thanks again!
|||Near the very top, there is a "FiscalYearStart" variable. Currently, it's set to January 1, 2007. That's a Monday...which, following a 4, 4, 5 rule, (which is weeks) then if the fiscal year starts on January 1, 2007...well then the week ends on Sunday.
Try changing the value of that variable to "December 31, 2006" (that's a sunday). That way, the last day of the week will be saturday.
|||Thanks!
I almost get what I want when I start the date on December 31, 2006. I need to check with Finance if it is correct though.
The other issue is that December 2007 should end on the 29th, and fiscal 2008 should start on December 30th. How can I get your code to do this.
Early on you mentioned that the last quarter needed to be 93 days; can we not have that be the case because at DBL we actually end our fiscal year on the 29th.
|||Ok, the first answer was a bit...over the top.
Try this. Explicitly define the StartOfFiscalYear and EndOfFiscalYear dates:
Code Snippet
--A few variables:
declare @.dtFiscalYearStart smalldatetime
, @.dtFiscalYearEnd smalldatetime
, @.iTemp int
This is the table we'll populate and return at the end
declare @.tb table(DayOfFiscalYear int identity (1,1)
,CalendarDate smalldatetime
, FiscalWeek int
, FiscalMonth tinyint
, FiscalQuarter tinyint)
--Now, populate our variables:
--This can be any date you choose. We assume that the fiscal year
--begins on the first day of the "fiscal week"
--We explicity populated STart of Fiscal Year and End
set @.dtFiscalYearStart = 'December 31, 2006'
set @.dtFiscalYearEnd = 'December 29, 2007'
set @.iTemp = 0
--Here's the loop to populate our output table:
while not exists(select * from @.tb where CalendarDate >= @.dtFiscalYearEnd)
begin
insert into @.tb (CalendarDate, FiscalWeek)
select dateadd(dd, @.iTemp, @.dtFiscalYearStart), (@.iTemp / 7) + 1
set @.iTEmp = @.iTemp + 1
end
update @.tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 5
update @.tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 9
update @.tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 14
update @.tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 18
update @.tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 22
update @.tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 27
update @.tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 31
update @.tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 35
update @.tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 40
update @.tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 44
update @.tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 48
update @.tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek > 47
--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.
--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!
--uncomment this for a double-check of week counts
--select FiscalMonth,count(distinct fiscalWeek) from @.tb group by FiscalMonth
select * from @.tb
|||Well rusag2, life is just about the get more interesting.
I just had a conversation with Finance and they confirmed that the Fiscal Calendar is not going to have 365 days all the time. Further, the calendar method that has been in use for awhile is called the Retail Calendar, which is what I need to take a look at.
Basically, every so often the last quarter of the year becomes a 4-4-6 to "catch-up" for a wekk lost in previous years.
I have to get to the bottom of this and so am going to do some research on how the Retail Calendar can be programmed. Apparently all the retail stores have this programmed so I am hoping there is something out there.
I will keep you posted.
Thanks a bunch again for your efforts!!
desibull
|||rusag2:
Can you modify your code to accept the start dt, end dt, and the number of weeks for the last quarter as variables and then just cutoff the year when you reach that last day?
Let me know if I am pushing my luck! Your code is almost there and I really would like it to work.
|||Use my most recently posted code. You explicity specify the Fiscal Year Start Date and Fiscal Year End Date.
Then, I build the year, one week at a time, going 4-4-5 for each quarter until the last when I go 4-4-<Whatever Is Left>.
|||I did and it is almost working like a charm except that for some reason the script is creating an extra day at the end. Any clues why?
For example, I provided the following values: Start Dt: December 31, 2006 End Dt: December 29, 2007. I got a record for 12/30/07, which I should not. It should be part of 08.
|||This is a quesion of "Through" vs. "To"
Just adjust your end date.
Or you may change this:
CalendarDate >= @.dtFiscalYearEnd
to this:
CalendarDate > @.dtFiscalYearEnd
|||Actually, setting it to >= worked. Somehow it got changed to >.
Your code now is fully functional.
I cannot thank you enough, rusag2. When I have some spare time I would like to go through your code and understand what you have done. Many of the functions you have used are new to me. It is one slick code though!!
Thanks!
Spoke too soon! Your second code while it produced the correct end date does not set the Fiscal month correctly. Your first code is working correctly. I made a similar change to the first one and it works.
Regardless, you are a genius!!
Hello:
Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:
Jan - 1 - 27
Feb - 28 - 24
Mar - 25 - 31
Currently, I have an SSIS package creating a record for each day in the Time Dimension.
Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?
Thank you!
Hi desibull,
this only way i though it use "dateadd" function for your issue.
check the sample code as below:
decalre @.dt_startdate datetime
set @.dt_startdate = '2007-01-01'
select dateadd(day,0,@.dt_startdate),dateadd(day,27,@.dt_startdate),
|||dateadd(day,28,@.dt_startdate),dateadd(day,55,@.dt_startdate),
dateadd(day,56,@.dt_startdate),dateadd(day,85,@.dt_startdate),
'next_startdate'=dateadd(day,86@.dt_startdate)
use this method for build date list.
take 'next_startdate' replace the @.dt_startdate.
hoping this can help you.
Best Regrads,
Hunt.
Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.
|||The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.
Here's the query i came up with. Nothing succedes like brute force!
You can set any date for @.dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.
Code Snippet declare @.dtFiscalYearStart smalldatetime , @.dtFiscalYearEnd smalldatetime , @.iDaysInFiscalYear smallint set @.dtFiscalYEarStart = 'January 1, 2007' set @.dtFiscalYearEnd = dateadd(yyyy, 1, @.dtFiscalYEarStart) set @.iDaysInFiscalYear = datediff(d, @.DtFiscalYearStart, @.dtFiscalYearEnd) declare @.Numbers table(Num int, dtTemp smalldatetime) insert into @.Numbers select 0, @.dtFiscalYEarStart declare @.i tinyint set @.i = 0 while @.i < 9 begin insert into @.Numbers select Num + power(2,@.i) , Dateadd(d, power(2,@.i), dtTemp) from @.Numbers set @.i = @.i + 1 end delete from @.Numbers where dtTemp >= @.dtFiscalYearEnd select dtTemp , Num + 1 as FiscalDay , Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek , case when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10 when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11 else 12 end as [FiscalMonth] , case when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*1) then 1 when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*2) then 2 when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*3) then 3 else 4 end as FiscalQuarter , datepart(dy,dtTemp) as CalendarDayOfYear , Datepart(wk,dtTemp) as CalendarWeekOfYear , Datepart(m,dtTemp) as CalendarMonth , Datepart(q,dtTemp) as CalendarQuarter from @.Numbers order by Dateadd(d, Num, @.dtFiscalYearStart)
Oh rusag2! I Bow To Thee!!!
I truly apologize for not looking at your post for this long. I have not figured out how to get alerts in my emal when a post is entered.
Your code is really amazing. I ran it and am now comparing it with a physical copy of a fiscal calendar I got from Finance. We seem to be off by a day. You see, at DBL we end our weeks on a Sat; the first month therefore ends on the 27th instead of the 28th, and although the 28th happens to be a Sunday it is a big deal for us as we process e-commerce orders on Sundays. Further, our year will end on the 29th and 2008 will being on the 30th.
As I would not know where to being modifying your code to accomplish the above I would sincerely appreciate some direction from you.
Thanks so much for taking the time to write the code!!! I would love to use it but need to make the changes I have indicated above.
Thanks again!
|||Near the very top, there is a "FiscalYearStart" variable. Currently, it's set to January 1, 2007. That's a Monday...which, following a 4, 4, 5 rule, (which is weeks) then if the fiscal year starts on January 1, 2007...well then the week ends on Sunday.
Try changing the value of that variable to "December 31, 2006" (that's a sunday). That way, the last day of the week will be saturday.
|||Thanks!
I almost get what I want when I start the date on December 31, 2006. I need to check with Finance if it is correct though.
The other issue is that December 2007 should end on the 29th, and fiscal 2008 should start on December 30th. How can I get your code to do this.
Early on you mentioned that the last quarter needed to be 93 days; can we not have that be the case because at DBL we actually end our fiscal year on the 29th.
|||Ok, the first answer was a bit...over the top.
Try this. Explicitly define the StartOfFiscalYear and EndOfFiscalYear dates:
Code Snippet
--A few variables:
declare @.dtFiscalYearStart smalldatetime
, @.dtFiscalYearEnd smalldatetime
, @.iTemp int
This is the table we'll populate and return at the end
declare @.tb table(DayOfFiscalYear int identity (1,1)
,CalendarDate smalldatetime
, FiscalWeek int
, FiscalMonth tinyint
, FiscalQuarter tinyint)
--Now, populate our variables:
--This can be any date you choose. We assume that the fiscal year
--begins on the first day of the "fiscal week"
--We explicity populated STart of Fiscal Year and End
set @.dtFiscalYearStart = 'December 31, 2006'
set @.dtFiscalYearEnd = 'December 29, 2007'
set @.iTemp = 0
--Here's the loop to populate our output table:
while not exists(select * from @.tb where CalendarDate >= @.dtFiscalYearEnd)
begin
insert into @.tb (CalendarDate, FiscalWeek)
select dateadd(dd, @.iTemp, @.dtFiscalYearStart), (@.iTemp / 7) + 1
set @.iTEmp = @.iTemp + 1
end
update @.tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 5
update @.tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 9
update @.tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 14
update @.tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 18
update @.tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 22
update @.tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 27
update @.tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 31
update @.tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 35
update @.tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 40
update @.tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 44
update @.tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 48
update @.tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek > 47
--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.
--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!
--uncomment this for a double-check of week counts
--select FiscalMonth,count(distinct fiscalWeek) from @.tb group by FiscalMonth
select * from @.tb
|||Well rusag2, life is just about the get more interesting.
I just had a conversation with Finance and they confirmed that the Fiscal Calendar is not going to have 365 days all the time. Further, the calendar method that has been in use for awhile is called the Retail Calendar, which is what I need to take a look at.
Basically, every so often the last quarter of the year becomes a 4-4-6 to "catch-up" for a wekk lost in previous years.
I have to get to the bottom of this and so am going to do some research on how the Retail Calendar can be programmed. Apparently all the retail stores have this programmed so I am hoping there is something out there.
I will keep you posted.
Thanks a bunch again for your efforts!!
desibull
|||rusag2:
Can you modify your code to accept the start dt, end dt, and the number of weeks for the last quarter as variables and then just cutoff the year when you reach that last day?
Let me know if I am pushing my luck! Your code is almost there and I really would like it to work.
|||Use my most recently posted code. You explicity specify the Fiscal Year Start Date and Fiscal Year End Date.
Then, I build the year, one week at a time, going 4-4-5 for each quarter until the last when I go 4-4-<Whatever Is Left>.
|||I did and it is almost working like a charm except that for some reason the script is creating an extra day at the end. Any clues why?
For example, I provided the following values: Start Dt: December 31, 2006 End Dt: December 29, 2007. I got a record for 12/30/07, which I should not. It should be part of 08.
|||This is a quesion of "Through" vs. "To"
Just adjust your end date.
Or you may change this:
CalendarDate >= @.dtFiscalYearEnd
to this:
CalendarDate > @.dtFiscalYearEnd
|||Actually, setting it to >= worked. Somehow it got changed to >.
Your code now is fully functional.
I cannot thank you enough, rusag2. When I have some spare time I would like to go through your code and understand what you have done. Many of the functions you have used are new to me. It is one slick code though!!
Thanks!
Spoke too soon! Your second code while it produced the correct end date does not set the Fiscal month correctly. Your first code is working correctly. I made a similar change to the first one and it works.
Regardless, you are a genius!!
Hello:
Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:
Jan - 1 - 27
Feb - 28 - 24
Mar - 25 - 31
Currently, I have an SSIS package creating a record for each day in the Time Dimension. Is there any script out there that will help me build a Fiscal calendar such as the one described above?
I realize that this is not a direct SSIS question but I figured that some of you might have encountered this situation and hence my post.
Thank you!
Phew. That doesn't sound easy - other than just looping over the weeks I don't see how it could be done. I suggest you post this on the T-SQL forum. I bet there's some people on there that would love to have a go at this.
-Jamie
|||I agree wit Jamie, the T-SQL forum is a better place for that. We have handle those cases in the past via stored procedure, but sorry I don't have a sample now. This a fairly common scenario and I am pretty sure there have to be some sample code out there.|||Thank you for your response! I will close this one out.
