Can this kind of report be done?

Hi,
I'm using SQL 2005 and I'm currently trying to create a report using VS
2005. I'm pulling the data from a CRM 3 database.
So far it is going pretty well. I got a good base but I'm stuck.
I want to create a report similar to the Sales Pipeline report. The problem
with that report is that you can only generate revenue based only on 1 date
(the close date). I would like to generate the same kind of report but based
on weekly revenue.
So I added 3 fields to the database for that table: 2 date fields
(contractstart and contractend) and a number field (contractlenght).
I know that I can use a simple formula to divide the revenue by the lenght
of the contract.
Say I have a contract that start on 3/11 and end on 4/7. It will last 4
weeks.
Is there a way to make it generate a column for each week? It that case it
would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for all the
dates mentioned in the start/end fields.
Also, for some reason my date only fields include a time stamp. How can I
remove that?
Thanks!
SimonYou can use matrix, but will be difficult to manipulate for your weekly
columns, instead I would suggest, you do all these things using sql query
itself and bring just the results to the report layout.
Amarnath, MCTS
"Simon Renshaw" wrote:
> Hi,
> I'm using SQL 2005 and I'm currently trying to create a report using VS
> 2005. I'm pulling the data from a CRM 3 database.
> So far it is going pretty well. I got a good base but I'm stuck.
> I want to create a report similar to the Sales Pipeline report. The problem
> with that report is that you can only generate revenue based only on 1 date
> (the close date). I would like to generate the same kind of report but based
> on weekly revenue.
> So I added 3 fields to the database for that table: 2 date fields
> (contractstart and contractend) and a number field (contractlenght).
> I know that I can use a simple formula to divide the revenue by the lenght
> of the contract.
> Say I have a contract that start on 3/11 and end on 4/7. It will last 4
> weeks.
> Is there a way to make it generate a column for each week? It that case it
> would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for all the
> dates mentioned in the start/end fields.
> Also, for some reason my date only fields include a time stamp. How can I
> remove that?
> Thanks!
> Simon
>
>
>|||On Mar 8, 11:47 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> You can use matrix, but will be difficult to manipulate for your weekly
> columns, instead I would suggest, you do all these things using sql query
> itself and bring just the results to the report layout.
> Amarnath, MCTS
> "Simon Renshaw" wrote:
> > Hi,
> > I'm using SQL 2005 and I'm currently trying to create a report using VS
> > 2005. I'm pulling the data from a CRM 3 database.
> > So far it is going pretty well. I got a good base but I'm stuck.
> > I want to create a report similar to the Sales Pipeline report. The problem
> > with that report is that you can only generate revenue based only on 1 date
> > (the close date). I would like to generate the same kind of report but based
> > on weekly revenue.
> > So I added 3 fields to the database for that table: 2 date fields
> > (contractstart and contractend) and a number field (contractlenght).
> > I know that I can use a simple formula to divide the revenue by the lenght
> > of the contract.
> > Say I have a contract that start on 3/11 and end on 4/7. It will last 4
> > weeks.
> > Is there a way to make it generate a column for each week? It that case it
> > would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for all the
> > dates mentioned in the start/end fields.
> > Also, for some reason my date only fields include a time stamp. How can I
> > remove that?
> > Thanks!
> > Simon
I believe you will have to use a combination of a SQL stored procedure
(preferrably for the improved performance) and a matrix. As long as
you are only pivoting one column (in your case the week column), I
wouldn't foresee there being a problem. One thing I would suggest
though is to require the user to obtain a set period type (i.e., data
listed by week all the time, or by day all the time; but not both/
either one, as this will get extremely complicated query-wise (and
most likely performance-wise as well)). When you refer to your date-
only fields, are you referring to it in a table or the report? If in a
table, I believe that you will have to stick w/the defaults of the
smalldatetime format (which includes standard midnight time) unless
you want to cast dateparts as characters and then recast them (once
concatenated) to datetime again. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Well, we got it using a function.
Only thing left is to fix the date/time problem.
Thanks for the pointers!
Simon
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1173443216.891594.153580@.h3g2000cwc.googlegroups.com...
> On Mar 8, 11:47 pm, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
>> You can use matrix, but will be difficult to manipulate for your weekly
>> columns, instead I would suggest, you do all these things using sql query
>> itself and bring just the results to the report layout.
>> Amarnath, MCTS
>> "Simon Renshaw" wrote:
>> > Hi,
>> > I'm using SQL 2005 and I'm currently trying to create a report using VS
>> > 2005. I'm pulling the data from a CRM 3 database.
>> > So far it is going pretty well. I got a good base but I'm stuck.
>> > I want to create a report similar to the Sales Pipeline report. The
>> > problem
>> > with that report is that you can only generate revenue based only on 1
>> > date
>> > (the close date). I would like to generate the same kind of report but
>> > based
>> > on weekly revenue.
>> > So I added 3 fields to the database for that table: 2 date fields
>> > (contractstart and contractend) and a number field (contractlenght).
>> > I know that I can use a simple formula to divide the revenue by the
>> > lenght
>> > of the contract.
>> > Say I have a contract that start on 3/11 and end on 4/7. It will last 4
>> > weeks.
>> > Is there a way to make it generate a column for each week? It that case
>> > it
>> > would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for
>> > all the
>> > dates mentioned in the start/end fields.
>> > Also, for some reason my date only fields include a time stamp. How can
>> > I
>> > remove that?
>> > Thanks!
>> > Simon
>
> I believe you will have to use a combination of a SQL stored procedure
> (preferrably for the improved performance) and a matrix. As long as
> you are only pivoting one column (in your case the week column), I
> wouldn't foresee there being a problem. One thing I would suggest
> though is to require the user to obtain a set period type (i.e., data
> listed by week all the time, or by day all the time; but not both/
> either one, as this will get extremely complicated query-wise (and
> most likely performance-wise as well)). When you refer to your date-
> only fields, are you referring to it in a table or the report? If in a
> table, I believe that you will have to stick w/the defaults of the
> smalldatetime format (which includes standard midnight time) unless
> you want to cast dateparts as characters and then recast them (once
> concatenated) to datetime again. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Well, also fixed the date/time problem within the query.
"Simon Renshaw" <simon@.benchmarkconsulting.com> wrote in message
news:eFFTfVNZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Well, we got it using a function.
> Only thing left is to fix the date/time problem.
> Thanks for the pointers!
> Simon
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1173443216.891594.153580@.h3g2000cwc.googlegroups.com...
>> On Mar 8, 11:47 pm, Amarnath <Amarn...@.discussions.microsoft.com>
>> wrote:
>> You can use matrix, but will be difficult to manipulate for your weekly
>> columns, instead I would suggest, you do all these things using sql
>> query
>> itself and bring just the results to the report layout.
>> Amarnath, MCTS
>> "Simon Renshaw" wrote:
>> > Hi,
>> > I'm using SQL 2005 and I'm currently trying to create a report using
>> > VS
>> > 2005. I'm pulling the data from a CRM 3 database.
>> > So far it is going pretty well. I got a good base but I'm stuck.
>> > I want to create a report similar to the Sales Pipeline report. The
>> > problem
>> > with that report is that you can only generate revenue based only on 1
>> > date
>> > (the close date). I would like to generate the same kind of report but
>> > based
>> > on weekly revenue.
>> > So I added 3 fields to the database for that table: 2 date fields
>> > (contractstart and contractend) and a number field (contractlenght).
>> > I know that I can use a simple formula to divide the revenue by the
>> > lenght
>> > of the contract.
>> > Say I have a contract that start on 3/11 and end on 4/7. It will last
>> > 4
>> > weeks.
>> > Is there a way to make it generate a column for each week? It that
>> > case it
>> > would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for
>> > all the
>> > dates mentioned in the start/end fields.
>> > Also, for some reason my date only fields include a time stamp. How
>> > can I
>> > remove that?
>> > Thanks!
>> > Simon
>>
>> I believe you will have to use a combination of a SQL stored procedure
>> (preferrably for the improved performance) and a matrix. As long as
>> you are only pivoting one column (in your case the week column), I
>> wouldn't foresee there being a problem. One thing I would suggest
>> though is to require the user to obtain a set period type (i.e., data
>> listed by week all the time, or by day all the time; but not both/
>> either one, as this will get extremely complicated query-wise (and
>> most likely performance-wise as well)). When you refer to your date-
>> only fields, are you referring to it in a table or the report? If in a
>> table, I believe that you will have to stick w/the defaults of the
>> smalldatetime format (which includes standard midnight time) unless
>> you want to cast dateparts as characters and then recast them (once
>> concatenated) to datetime again. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>