HI y'all.
PROBLEM. Would like to change row color based on a calculated field.
I.E. need the row to highlight when an expiration date is within 14 days of
expiring.
I can use the row expression panel to alternate row color, but cant quite
find the magic to make it happen with data as the driver. I can also use the
report code panel which can be accessed with an =Code.bgcolor in the row
background settings.
(sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
But yet the report code requires objects references and I can find no
information how this is done in reporting services.
While I am here: What is the process sequence at run time? Does the
report, Table and row formatting have a view of the fields in a defined
dataset?
Thanks in advance.From Properties/BackgroundColor, select Expression.
If you are using a field as the driver, cope and paste the following code
and make sure to change YourFieldName with the actual name.
=IIf(Fields!YourFieldName.Value < 14, "Red", "White")
If you are using a parameter field as the driver, use this instead.
=IIf(Parameters!YourParameterName.Value < 14, "Red", "White")
"MKTapps" wrote:
> HI y'all.
> PROBLEM. Would like to change row color based on a calculated field.
> I.E. need the row to highlight when an expiration date is within 14 days of
> expiring.
> I can use the row expression panel to alternate row color, but cant quite
> find the magic to make it happen with data as the driver. I can also use the
> report code panel which can be accessed with an =Code.bgcolor in the row
> background settings.
> (sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
> But yet the report code requires objects references and I can find no
> information how this is done in reporting services.
> While I am here: What is the process sequence at run time? Does the
> report, Table and row formatting have a view of the fields in a defined
> dataset?
> Thanks in advance.
Showing posts with label date. Show all posts
Showing posts with label date. Show all posts
Thursday, March 29, 2012
Monday, March 26, 2012
Dynamic Period Over Period Growth Without Hierarchy in Time/Date Dimension?
The time dimension in our AS2005 cube is not hierarchical. Therefore, how can I dynamically do a period over period growth calculated member? I'd like it to determine the growth whether the user is viewing it yearly, quarterly or monthly.
If our time dimension was indeed hierarchical, I would define the metric as follows:
([Time].[Currentmember], [Measures].[Sales] - ([Time].[CurrentMember].[PrevMember], [Measures].[Sales])
Right now, I have only been able to do it for a specific level whether it's year, quarter or month. For example, for a year over year growth, I've defined it as follows:
([Date].[Year].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Year].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])
If I wanted to do the same for months, I'd replace "Year" with "Month" as follows:
([Date].[Month].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Month].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])
Is it possible to do a dynamic period over period growth in 1 calculated member based on how our Date dimension is setup?
Could you explain what attribute relations exist in your time dimension - are quarters related to months, and years to quarters? Or is the [Month] like a "month-of-year" and quarter like a "quarter-of-year"? In that case, you could try a scoped assignment like:
Create [Measures].[SalesGrowth];
Scope([Measures].[SalesGrowth]);
Scope([Date].[Year].[Year]);
this = [Measures].[Sales USD] - ([Date].[Year].PREVMEMBER, [Measures].[Sales USD]);
Scope([Date].[Quarter].[Quarter]);
this = [Measures].[Sales USD] - ([Date].[Quarter].PREVMEMBER, [Measures].[Sales USD]);
Scope([Date].[Month].[Month]);
this = [Measures].[Sales USD] - ([Date].[Month].PREVMEMBER, [Measures].[Sales USD]);
End Scope;
End Scope;
End Scope;
End Scope;
|||Deepak,I've never used SCOPE before, but this looks like it could work. Where do I use SCOPE? In the definition of the [SalesGrowth] calculated member? Or do I have to somehow make use of a new SCRIPT command? I've never done this either. The only Script Command I have is at the default "CALCULATE". that goes before all of my calculated members.
Thanks!
|||The code above already includes a statement to create [SalesGrowth], at the beginning - you could append this snippet to your existing script, after the other calculated members.|||Deepak,
Thanks for exposing me to SCOPE! I was able to acheive what I wanted with just a few tweaks.
Thursday, March 22, 2012
Dynamic Parameters
I need to be able to schedule a report with a dynamic parameter.
For example,
The user may select "Start of Last Month" as the date parameter value. I
then need to turn this into something like :
CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
Date)))
When scheduling this report, this parameter should then always be "the Start
of Last Month"
I don't know how to make the parameter look at the code as an expression and
not as a String.
Any help would be appreciated.Try,
=CDATE(CStr(Month(DateAdd("m", -1, Date))) & "/01/" &
CStr(Year(DateAdd("m", -1,
Date))))
If it doesn't work, you may also try to create a default value using the
expression above at report manager.
Regards,
Cem Demircioglu
"Tarik Peterson" <tarikp@.investigo.net> wrote in message
news:utVz7JbHFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to be able to schedule a report with a dynamic parameter.
> For example,
> The user may select "Start of Last Month" as the date parameter value. I
> then need to turn this into something like :
> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
> Date)))
> When scheduling this report, this parameter should then always be "the
> Start of Last Month"
> I don't know how to make the parameter look at the code as an expression
> and not as a String.
> Any help would be appreciated.
>|||Another option if you're using stored procedures is to set the start/end date
within the procedure based on a parameter sent through.
"Tarik Peterson" wrote:
> I need to be able to schedule a report with a dynamic parameter.
> For example,
> The user may select "Start of Last Month" as the date parameter value. I
> then need to turn this into something like :
> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
> Date)))
> When scheduling this report, this parameter should then always be "the Start
> of Last Month"
> I don't know how to make the parameter look at the code as an expression and
> not as a String.
> Any help would be appreciated.
>
>|||Thanks!
I had actually decided to do it this way prior to reading your post, so it
was good to get some kind of confirmation that this was a good way to do it.
Thanks again.
"Dave Klug" <Dave Klug@.discussions.microsoft.com> wrote in message
news:CB4A4573-EB5B-498C-AF2C-11C6FE5CDE00@.microsoft.com...
> Another option if you're using stored procedures is to set the start/end
> date
> within the procedure based on a parameter sent through.
> "Tarik Peterson" wrote:
>> I need to be able to schedule a report with a dynamic parameter.
>> For example,
>> The user may select "Start of Last Month" as the date parameter value. I
>> then need to turn this into something like :
>> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
>> Date)))
>> When scheduling this report, this parameter should then always be "the
>> Start
>> of Last Month"
>> I don't know how to make the parameter look at the code as an expression
>> and
>> not as a String.
>> Any help would be appreciated.
>>
For example,
The user may select "Start of Last Month" as the date parameter value. I
then need to turn this into something like :
CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
Date)))
When scheduling this report, this parameter should then always be "the Start
of Last Month"
I don't know how to make the parameter look at the code as an expression and
not as a String.
Any help would be appreciated.Try,
=CDATE(CStr(Month(DateAdd("m", -1, Date))) & "/01/" &
CStr(Year(DateAdd("m", -1,
Date))))
If it doesn't work, you may also try to create a default value using the
expression above at report manager.
Regards,
Cem Demircioglu
"Tarik Peterson" <tarikp@.investigo.net> wrote in message
news:utVz7JbHFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to be able to schedule a report with a dynamic parameter.
> For example,
> The user may select "Start of Last Month" as the date parameter value. I
> then need to turn this into something like :
> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
> Date)))
> When scheduling this report, this parameter should then always be "the
> Start of Last Month"
> I don't know how to make the parameter look at the code as an expression
> and not as a String.
> Any help would be appreciated.
>|||Another option if you're using stored procedures is to set the start/end date
within the procedure based on a parameter sent through.
"Tarik Peterson" wrote:
> I need to be able to schedule a report with a dynamic parameter.
> For example,
> The user may select "Start of Last Month" as the date parameter value. I
> then need to turn this into something like :
> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
> Date)))
> When scheduling this report, this parameter should then always be "the Start
> of Last Month"
> I don't know how to make the parameter look at the code as an expression and
> not as a String.
> Any help would be appreciated.
>
>|||Thanks!
I had actually decided to do it this way prior to reading your post, so it
was good to get some kind of confirmation that this was a good way to do it.
Thanks again.
"Dave Klug" <Dave Klug@.discussions.microsoft.com> wrote in message
news:CB4A4573-EB5B-498C-AF2C-11C6FE5CDE00@.microsoft.com...
> Another option if you're using stored procedures is to set the start/end
> date
> within the procedure based on a parameter sent through.
> "Tarik Peterson" wrote:
>> I need to be able to schedule a report with a dynamic parameter.
>> For example,
>> The user may select "Start of Last Month" as the date parameter value. I
>> then need to turn this into something like :
>> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
>> Date)))
>> When scheduling this report, this parameter should then always be "the
>> Start
>> of Last Month"
>> I don't know how to make the parameter look at the code as an expression
>> and
>> not as a String.
>> Any help would be appreciated.
>>
Dynamic Parameter Date Ranges
I require three parameter fields:
1.)Daterange
2.)EndDate
3.)Start Date
Selecting an option from the DateRange Parameter (eg. option = today)
automatically populates the EndDate and StartDate Fields. I also want to give
the user the option to extend the date range to one which is not defined as a
daterange option of desired by editing the EndDate and StartDate Fields.
Any help is appreciated.Hi,
I have exactly the same problem, did you found a solution?
Thanks,
Elisabeth
"SAcanuck" wrote:
> I require three parameter fields:
> 1.)Daterange
> 2.)EndDate
> 3.)Start Date
> Selecting an option from the DateRange Parameter (eg. option = today)
> automatically populates the EndDate and StartDate Fields. I also want to give
> the user the option to extend the date range to one which is not defined as a
> daterange option of desired by editing the EndDate and StartDate Fields.
> Any help is appreciated.|||No I havent found a solution that works like I want it to.
"Elisabeth" wrote:
> Hi,
> I have exactly the same problem, did you found a solution?
> Thanks,
> Elisabeth
> "SAcanuck" wrote:
> > I require three parameter fields:
> >
> > 1.)Daterange
> > 2.)EndDate
> > 3.)Start Date
> >
> > Selecting an option from the DateRange Parameter (eg. option = today)
> > automatically populates the EndDate and StartDate Fields. I also want to give
> > the user the option to extend the date range to one which is not defined as a
> > daterange option of desired by editing the EndDate and StartDate Fields.
> >
> > Any help is appreciated.|||I would have to play with this but it seems like if you have three
parameters and the second and third parameters have an expression as the
default with the expression referencing the first parameter. I can't try
this right now but it should work.
Bruce L-C
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> No I havent found a solution that works like I want it to.
>
> "Elisabeth" wrote:
>> Hi,
>> I have exactly the same problem, did you found a solution?
>> Thanks,
>> Elisabeth
>> "SAcanuck" wrote:
>> > I require three parameter fields:
>> >
>> > 1.)Daterange
>> > 2.)EndDate
>> > 3.)Start Date
>> >
>> > Selecting an option from the DateRange Parameter (eg. option = today)
>> > automatically populates the EndDate and StartDate Fields. I also want
>> > to give
>> > the user the option to extend the date range to one which is not
>> > defined as a
>> > daterange option of desired by editing the EndDate and StartDate
>> > Fields.
>> >
>> > Any help is appreciated.|||Bruce:
I have tried this before but it doesnt perform as expected...
When you run your report the first time and select the first parameter the
other two dates are populated correctly, but when you change your first
parameter (date range) the other dates are not automatically changed.
"Bruce Loehle-Conger" wrote:
> I would have to play with this but it seems like if you have three
> parameters and the second and third parameters have an expression as the
> default with the expression referencing the first parameter. I can't try
> this right now but it should work.
> Bruce L-C
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > No I havent found a solution that works like I want it to.
> >
> >
> > "Elisabeth" wrote:
> >
> >> Hi,
> >>
> >> I have exactly the same problem, did you found a solution?
> >> Thanks,
> >> Elisabeth
> >>
> >> "SAcanuck" wrote:
> >>
> >> > I require three parameter fields:
> >> >
> >> > 1.)Daterange
> >> > 2.)EndDate
> >> > 3.)Start Date
> >> >
> >> > Selecting an option from the DateRange Parameter (eg. option = today)
> >> > automatically populates the EndDate and StartDate Fields. I also want
> >> > to give
> >> > the user the option to extend the date range to one which is not
> >> > defined as a
> >> > daterange option of desired by editing the EndDate and StartDate
> >> > Fields.
> >> >
> >> > Any help is appreciated.
>
>|||Bruce,
I have tried it also and it didn't work.
I hope you will find time to try it...
waiting,
Elisabeth
"SAcanuck" wrote:
> Bruce:
> I have tried this before but it doesnt perform as expected...
> When you run your report the first time and select the first parameter the
> other two dates are populated correctly, but when you change your first
> parameter (date range) the other dates are not automatically changed.
> "Bruce Loehle-Conger" wrote:
> > I would have to play with this but it seems like if you have three
> > parameters and the second and third parameters have an expression as the
> > default with the expression referencing the first parameter. I can't try
> > this right now but it should work.
> >
> > Bruce L-C
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > No I havent found a solution that works like I want it to.
> > >
> > >
> > > "Elisabeth" wrote:
> > >
> > >> Hi,
> > >>
> > >> I have exactly the same problem, did you found a solution?
> > >> Thanks,
> > >> Elisabeth
> > >>
> > >> "SAcanuck" wrote:
> > >>
> > >> > I require three parameter fields:
> > >> >
> > >> > 1.)Daterange
> > >> > 2.)EndDate
> > >> > 3.)Start Date
> > >> >
> > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > >> > automatically populates the EndDate and StartDate Fields. I also want
> > >> > to give
> > >> > the user the option to extend the date range to one which is not
> > >> > defined as a
> > >> > daterange option of desired by editing the EndDate and StartDate
> > >> > Fields.
> > >> >
> > >> > Any help is appreciated.
> >
> >
> >|||OK, I just tried this out and in the development environment it does as you
say. However, when I deploy it and try it from the server then it works as
you would expect. Can you try it after deploying it?
Bruce L-C [MVP SQL Server Reporting Services]
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:450111A7-157F-4000-B331-F36758529639@.microsoft.com...
> Bruce:
> I have tried this before but it doesnt perform as expected...
> When you run your report the first time and select the first parameter the
> other two dates are populated correctly, but when you change your first
> parameter (date range) the other dates are not automatically changed.
> "Bruce Loehle-Conger" wrote:
> > I would have to play with this but it seems like if you have three
> > parameters and the second and third parameters have an expression as the
> > default with the expression referencing the first parameter. I can't try
> > this right now but it should work.
> >
> > Bruce L-C
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > No I havent found a solution that works like I want it to.
> > >
> > >
> > > "Elisabeth" wrote:
> > >
> > >> Hi,
> > >>
> > >> I have exactly the same problem, did you found a solution?
> > >> Thanks,
> > >> Elisabeth
> > >>
> > >> "SAcanuck" wrote:
> > >>
> > >> > I require three parameter fields:
> > >> >
> > >> > 1.)Daterange
> > >> > 2.)EndDate
> > >> > 3.)Start Date
> > >> >
> > >> > Selecting an option from the DateRange Parameter (eg. option =today)
> > >> > automatically populates the EndDate and StartDate Fields. I also
want
> > >> > to give
> > >> > the user the option to extend the date range to one which is not
> > >> > defined as a
> > >> > daterange option of desired by editing the EndDate and StartDate
> > >> > Fields.
> > >> >
> > >> > Any help is appreciated.
> >
> >
> >|||Elisabeth:
Maybe the following link helps you...Im not a SQL Guru (far from it...) so
it doesnt help me much.
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?pg=4&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
"Elisabeth" wrote:
> Bruce,
> I have tried it also and it didn't work.
> I hope you will find time to try it...
> waiting,
> Elisabeth
> "SAcanuck" wrote:
> > Bruce:
> >
> > I have tried this before but it doesnt perform as expected...
> >
> > When you run your report the first time and select the first parameter the
> > other two dates are populated correctly, but when you change your first
> > parameter (date range) the other dates are not automatically changed.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > I would have to play with this but it seems like if you have three
> > > parameters and the second and third parameters have an expression as the
> > > default with the expression referencing the first parameter. I can't try
> > > this right now but it should work.
> > >
> > > Bruce L-C
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > No I havent found a solution that works like I want it to.
> > > >
> > > >
> > > > "Elisabeth" wrote:
> > > >
> > > >> Hi,
> > > >>
> > > >> I have exactly the same problem, did you found a solution?
> > > >> Thanks,
> > > >> Elisabeth
> > > >>
> > > >> "SAcanuck" wrote:
> > > >>
> > > >> > I require three parameter fields:
> > > >> >
> > > >> > 1.)Daterange
> > > >> > 2.)EndDate
> > > >> > 3.)Start Date
> > > >> >
> > > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > > >> > automatically populates the EndDate and StartDate Fields. I also want
> > > >> > to give
> > > >> > the user the option to extend the date range to one which is not
> > > >> > defined as a
> > > >> > daterange option of desired by editing the EndDate and StartDate
> > > >> > Fields.
> > > >> >
> > > >> > Any help is appreciated.
> > >
> > >
> > >|||Hi Bruce:
It does work after deploying... slightly annoying though. Thanks for the
effort.
"Bruce Loehle-Conger [MVP]" wrote:
> OK, I just tried this out and in the development environment it does as you
> say. However, when I deploy it and try it from the server then it works as
> you would expect. Can you try it after deploying it?
> Bruce L-C [MVP SQL Server Reporting Services]
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:450111A7-157F-4000-B331-F36758529639@.microsoft.com...
> > Bruce:
> >
> > I have tried this before but it doesnt perform as expected...
> >
> > When you run your report the first time and select the first parameter the
> > other two dates are populated correctly, but when you change your first
> > parameter (date range) the other dates are not automatically changed.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > I would have to play with this but it seems like if you have three
> > > parameters and the second and third parameters have an expression as the
> > > default with the expression referencing the first parameter. I can't try
> > > this right now but it should work.
> > >
> > > Bruce L-C
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > No I havent found a solution that works like I want it to.
> > > >
> > > >
> > > > "Elisabeth" wrote:
> > > >
> > > >> Hi,
> > > >>
> > > >> I have exactly the same problem, did you found a solution?
> > > >> Thanks,
> > > >> Elisabeth
> > > >>
> > > >> "SAcanuck" wrote:
> > > >>
> > > >> > I require three parameter fields:
> > > >> >
> > > >> > 1.)Daterange
> > > >> > 2.)EndDate
> > > >> > 3.)Start Date
> > > >> >
> > > >> > Selecting an option from the DateRange Parameter (eg. option => today)
> > > >> > automatically populates the EndDate and StartDate Fields. I also
> want
> > > >> > to give
> > > >> > the user the option to extend the date range to one which is not
> > > >> > defined as a
> > > >> > daterange option of desired by editing the EndDate and StartDate
> > > >> > Fields.
> > > >> >
> > > >> > Any help is appreciated.
> > >
> > >
> > >
>
>|||I think something is missing in the link you send to me...
"SAcanuck" wrote:
> Elisabeth:
> Maybe the following link helps you...Im not a SQL Guru (far from it...) so
> it doesnt help me much.
> http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?pg=4&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
> "Elisabeth" wrote:
> > Bruce,
> >
> > I have tried it also and it didn't work.
> > I hope you will find time to try it...
> > waiting,
> > Elisabeth
> >
> > "SAcanuck" wrote:
> >
> > > Bruce:
> > >
> > > I have tried this before but it doesnt perform as expected...
> > >
> > > When you run your report the first time and select the first parameter the
> > > other two dates are populated correctly, but when you change your first
> > > parameter (date range) the other dates are not automatically changed.
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > I would have to play with this but it seems like if you have three
> > > > parameters and the second and third parameters have an expression as the
> > > > default with the expression referencing the first parameter. I can't try
> > > > this right now but it should work.
> > > >
> > > > Bruce L-C
> > > >
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > > No I havent found a solution that works like I want it to.
> > > > >
> > > > >
> > > > > "Elisabeth" wrote:
> > > > >
> > > > >> Hi,
> > > > >>
> > > > >> I have exactly the same problem, did you found a solution?
> > > > >> Thanks,
> > > > >> Elisabeth
> > > > >>
> > > > >> "SAcanuck" wrote:
> > > > >>
> > > > >> > I require three parameter fields:
> > > > >> >
> > > > >> > 1.)Daterange
> > > > >> > 2.)EndDate
> > > > >> > 3.)Start Date
> > > > >> >
> > > > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > > > >> > automatically populates the EndDate and StartDate Fields. I also want
> > > > >> > to give
> > > > >> > the user the option to extend the date range to one which is not
> > > > >> > defined as a
> > > > >> > daterange option of desired by editing the EndDate and StartDate
> > > > >> > Fields.
> > > > >> >
> > > > >> > Any help is appreciated.
> > > >
> > > >
> > > >
1.)Daterange
2.)EndDate
3.)Start Date
Selecting an option from the DateRange Parameter (eg. option = today)
automatically populates the EndDate and StartDate Fields. I also want to give
the user the option to extend the date range to one which is not defined as a
daterange option of desired by editing the EndDate and StartDate Fields.
Any help is appreciated.Hi,
I have exactly the same problem, did you found a solution?
Thanks,
Elisabeth
"SAcanuck" wrote:
> I require three parameter fields:
> 1.)Daterange
> 2.)EndDate
> 3.)Start Date
> Selecting an option from the DateRange Parameter (eg. option = today)
> automatically populates the EndDate and StartDate Fields. I also want to give
> the user the option to extend the date range to one which is not defined as a
> daterange option of desired by editing the EndDate and StartDate Fields.
> Any help is appreciated.|||No I havent found a solution that works like I want it to.
"Elisabeth" wrote:
> Hi,
> I have exactly the same problem, did you found a solution?
> Thanks,
> Elisabeth
> "SAcanuck" wrote:
> > I require three parameter fields:
> >
> > 1.)Daterange
> > 2.)EndDate
> > 3.)Start Date
> >
> > Selecting an option from the DateRange Parameter (eg. option = today)
> > automatically populates the EndDate and StartDate Fields. I also want to give
> > the user the option to extend the date range to one which is not defined as a
> > daterange option of desired by editing the EndDate and StartDate Fields.
> >
> > Any help is appreciated.|||I would have to play with this but it seems like if you have three
parameters and the second and third parameters have an expression as the
default with the expression referencing the first parameter. I can't try
this right now but it should work.
Bruce L-C
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> No I havent found a solution that works like I want it to.
>
> "Elisabeth" wrote:
>> Hi,
>> I have exactly the same problem, did you found a solution?
>> Thanks,
>> Elisabeth
>> "SAcanuck" wrote:
>> > I require three parameter fields:
>> >
>> > 1.)Daterange
>> > 2.)EndDate
>> > 3.)Start Date
>> >
>> > Selecting an option from the DateRange Parameter (eg. option = today)
>> > automatically populates the EndDate and StartDate Fields. I also want
>> > to give
>> > the user the option to extend the date range to one which is not
>> > defined as a
>> > daterange option of desired by editing the EndDate and StartDate
>> > Fields.
>> >
>> > Any help is appreciated.|||Bruce:
I have tried this before but it doesnt perform as expected...
When you run your report the first time and select the first parameter the
other two dates are populated correctly, but when you change your first
parameter (date range) the other dates are not automatically changed.
"Bruce Loehle-Conger" wrote:
> I would have to play with this but it seems like if you have three
> parameters and the second and third parameters have an expression as the
> default with the expression referencing the first parameter. I can't try
> this right now but it should work.
> Bruce L-C
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > No I havent found a solution that works like I want it to.
> >
> >
> > "Elisabeth" wrote:
> >
> >> Hi,
> >>
> >> I have exactly the same problem, did you found a solution?
> >> Thanks,
> >> Elisabeth
> >>
> >> "SAcanuck" wrote:
> >>
> >> > I require three parameter fields:
> >> >
> >> > 1.)Daterange
> >> > 2.)EndDate
> >> > 3.)Start Date
> >> >
> >> > Selecting an option from the DateRange Parameter (eg. option = today)
> >> > automatically populates the EndDate and StartDate Fields. I also want
> >> > to give
> >> > the user the option to extend the date range to one which is not
> >> > defined as a
> >> > daterange option of desired by editing the EndDate and StartDate
> >> > Fields.
> >> >
> >> > Any help is appreciated.
>
>|||Bruce,
I have tried it also and it didn't work.
I hope you will find time to try it...
waiting,
Elisabeth
"SAcanuck" wrote:
> Bruce:
> I have tried this before but it doesnt perform as expected...
> When you run your report the first time and select the first parameter the
> other two dates are populated correctly, but when you change your first
> parameter (date range) the other dates are not automatically changed.
> "Bruce Loehle-Conger" wrote:
> > I would have to play with this but it seems like if you have three
> > parameters and the second and third parameters have an expression as the
> > default with the expression referencing the first parameter. I can't try
> > this right now but it should work.
> >
> > Bruce L-C
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > No I havent found a solution that works like I want it to.
> > >
> > >
> > > "Elisabeth" wrote:
> > >
> > >> Hi,
> > >>
> > >> I have exactly the same problem, did you found a solution?
> > >> Thanks,
> > >> Elisabeth
> > >>
> > >> "SAcanuck" wrote:
> > >>
> > >> > I require three parameter fields:
> > >> >
> > >> > 1.)Daterange
> > >> > 2.)EndDate
> > >> > 3.)Start Date
> > >> >
> > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > >> > automatically populates the EndDate and StartDate Fields. I also want
> > >> > to give
> > >> > the user the option to extend the date range to one which is not
> > >> > defined as a
> > >> > daterange option of desired by editing the EndDate and StartDate
> > >> > Fields.
> > >> >
> > >> > Any help is appreciated.
> >
> >
> >|||OK, I just tried this out and in the development environment it does as you
say. However, when I deploy it and try it from the server then it works as
you would expect. Can you try it after deploying it?
Bruce L-C [MVP SQL Server Reporting Services]
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:450111A7-157F-4000-B331-F36758529639@.microsoft.com...
> Bruce:
> I have tried this before but it doesnt perform as expected...
> When you run your report the first time and select the first parameter the
> other two dates are populated correctly, but when you change your first
> parameter (date range) the other dates are not automatically changed.
> "Bruce Loehle-Conger" wrote:
> > I would have to play with this but it seems like if you have three
> > parameters and the second and third parameters have an expression as the
> > default with the expression referencing the first parameter. I can't try
> > this right now but it should work.
> >
> > Bruce L-C
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > No I havent found a solution that works like I want it to.
> > >
> > >
> > > "Elisabeth" wrote:
> > >
> > >> Hi,
> > >>
> > >> I have exactly the same problem, did you found a solution?
> > >> Thanks,
> > >> Elisabeth
> > >>
> > >> "SAcanuck" wrote:
> > >>
> > >> > I require three parameter fields:
> > >> >
> > >> > 1.)Daterange
> > >> > 2.)EndDate
> > >> > 3.)Start Date
> > >> >
> > >> > Selecting an option from the DateRange Parameter (eg. option =today)
> > >> > automatically populates the EndDate and StartDate Fields. I also
want
> > >> > to give
> > >> > the user the option to extend the date range to one which is not
> > >> > defined as a
> > >> > daterange option of desired by editing the EndDate and StartDate
> > >> > Fields.
> > >> >
> > >> > Any help is appreciated.
> >
> >
> >|||Elisabeth:
Maybe the following link helps you...Im not a SQL Guru (far from it...) so
it doesnt help me much.
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?pg=4&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
"Elisabeth" wrote:
> Bruce,
> I have tried it also and it didn't work.
> I hope you will find time to try it...
> waiting,
> Elisabeth
> "SAcanuck" wrote:
> > Bruce:
> >
> > I have tried this before but it doesnt perform as expected...
> >
> > When you run your report the first time and select the first parameter the
> > other two dates are populated correctly, but when you change your first
> > parameter (date range) the other dates are not automatically changed.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > I would have to play with this but it seems like if you have three
> > > parameters and the second and third parameters have an expression as the
> > > default with the expression referencing the first parameter. I can't try
> > > this right now but it should work.
> > >
> > > Bruce L-C
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > No I havent found a solution that works like I want it to.
> > > >
> > > >
> > > > "Elisabeth" wrote:
> > > >
> > > >> Hi,
> > > >>
> > > >> I have exactly the same problem, did you found a solution?
> > > >> Thanks,
> > > >> Elisabeth
> > > >>
> > > >> "SAcanuck" wrote:
> > > >>
> > > >> > I require three parameter fields:
> > > >> >
> > > >> > 1.)Daterange
> > > >> > 2.)EndDate
> > > >> > 3.)Start Date
> > > >> >
> > > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > > >> > automatically populates the EndDate and StartDate Fields. I also want
> > > >> > to give
> > > >> > the user the option to extend the date range to one which is not
> > > >> > defined as a
> > > >> > daterange option of desired by editing the EndDate and StartDate
> > > >> > Fields.
> > > >> >
> > > >> > Any help is appreciated.
> > >
> > >
> > >|||Hi Bruce:
It does work after deploying... slightly annoying though. Thanks for the
effort.
"Bruce Loehle-Conger [MVP]" wrote:
> OK, I just tried this out and in the development environment it does as you
> say. However, when I deploy it and try it from the server then it works as
> you would expect. Can you try it after deploying it?
> Bruce L-C [MVP SQL Server Reporting Services]
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:450111A7-157F-4000-B331-F36758529639@.microsoft.com...
> > Bruce:
> >
> > I have tried this before but it doesnt perform as expected...
> >
> > When you run your report the first time and select the first parameter the
> > other two dates are populated correctly, but when you change your first
> > parameter (date range) the other dates are not automatically changed.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > I would have to play with this but it seems like if you have three
> > > parameters and the second and third parameters have an expression as the
> > > default with the expression referencing the first parameter. I can't try
> > > this right now but it should work.
> > >
> > > Bruce L-C
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > No I havent found a solution that works like I want it to.
> > > >
> > > >
> > > > "Elisabeth" wrote:
> > > >
> > > >> Hi,
> > > >>
> > > >> I have exactly the same problem, did you found a solution?
> > > >> Thanks,
> > > >> Elisabeth
> > > >>
> > > >> "SAcanuck" wrote:
> > > >>
> > > >> > I require three parameter fields:
> > > >> >
> > > >> > 1.)Daterange
> > > >> > 2.)EndDate
> > > >> > 3.)Start Date
> > > >> >
> > > >> > Selecting an option from the DateRange Parameter (eg. option => today)
> > > >> > automatically populates the EndDate and StartDate Fields. I also
> want
> > > >> > to give
> > > >> > the user the option to extend the date range to one which is not
> > > >> > defined as a
> > > >> > daterange option of desired by editing the EndDate and StartDate
> > > >> > Fields.
> > > >> >
> > > >> > Any help is appreciated.
> > >
> > >
> > >
>
>|||I think something is missing in the link you send to me...
"SAcanuck" wrote:
> Elisabeth:
> Maybe the following link helps you...Im not a SQL Guru (far from it...) so
> it doesnt help me much.
> http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?pg=4&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
> "Elisabeth" wrote:
> > Bruce,
> >
> > I have tried it also and it didn't work.
> > I hope you will find time to try it...
> > waiting,
> > Elisabeth
> >
> > "SAcanuck" wrote:
> >
> > > Bruce:
> > >
> > > I have tried this before but it doesnt perform as expected...
> > >
> > > When you run your report the first time and select the first parameter the
> > > other two dates are populated correctly, but when you change your first
> > > parameter (date range) the other dates are not automatically changed.
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > I would have to play with this but it seems like if you have three
> > > > parameters and the second and third parameters have an expression as the
> > > > default with the expression referencing the first parameter. I can't try
> > > > this right now but it should work.
> > > >
> > > > Bruce L-C
> > > >
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > > No I havent found a solution that works like I want it to.
> > > > >
> > > > >
> > > > > "Elisabeth" wrote:
> > > > >
> > > > >> Hi,
> > > > >>
> > > > >> I have exactly the same problem, did you found a solution?
> > > > >> Thanks,
> > > > >> Elisabeth
> > > > >>
> > > > >> "SAcanuck" wrote:
> > > > >>
> > > > >> > I require three parameter fields:
> > > > >> >
> > > > >> > 1.)Daterange
> > > > >> > 2.)EndDate
> > > > >> > 3.)Start Date
> > > > >> >
> > > > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > > > >> > automatically populates the EndDate and StartDate Fields. I also want
> > > > >> > to give
> > > > >> > the user the option to extend the date range to one which is not
> > > > >> > defined as a
> > > > >> > daterange option of desired by editing the EndDate and StartDate
> > > > >> > Fields.
> > > > >> >
> > > > >> > Any help is appreciated.
> > > >
> > > >
> > > >
Dynamic ParallelPeriode
Hi,
with this MDX expression as a calculated member in my cube
100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Month], 12), [Measures].[Sales Volume KG])
I get the difference of the Sales Volume from this month and this month last year. But how can I make it dynamic for drilldown in the Time Dimension, so that also year, semester, Quarters and so on are supported? I will compare this year - last year, this semester - same semester last year, this quarter - same quarter last year and so on.
Thanks
Hans
Hi Hans,
Why doesn't ParallelPeriod(Year ..) meet your needs - could you explain in the context of this Adventure Works query?
>>
select
{[Measures].[Sales Amount]} on 0,
Generate(Ascendants([Date].[Calendar].[Date].&[800]),
{[Date].[Calendar].CurrentMember,
ParallelPeriod([Date].[Calendar].[Calendar Year],
1, [Date].[Calendar].CurrentMember)}) on 1
from [Adventure Works]
-
Sales Amount
September 8, 2003 $36,027.71
September 8, 2002 $18,755.92
September 2003 $5,057,832.17
September 2002 $3,235,826.19
Q3 CY 2003 $13,670,536.57
Q3 CY 2002 $10,277,073.06
H2 CY 2003 $26,955,981.04
H2 CY 2002 $18,646,056.13
CY 2003 $41,993,729.72
CY 2002 $30,674,773.18
All Periods $109,809,274.20
>>
|||Hi Deepak,
Thanks for your suggestions. Your code gave me the idea to specify the Dimension in full, so I name it [Date].[Periode - Year].[Year] and not only [Date].[Year] and now it works. But it works only with the [Periode - Year] Hierarchy. How can I change it, to work with all my Time hierarchies ([Date].[Periode - Year], [Date].[Periode - Week], [Date].[Periode - Reporting])? I tried
IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Periode - Year].[Year], 1) * parallelperiod([Date].[Periode - Week].[Year], 1) * parallelperiod([Date].[Periode - Reporting].[Year], 1), [Measures].[Sales Volume KG])) )
but that doesn't work. You gave me this approach just a year ago for builing YTD sums just like
IIF( sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]) = 0, 0, sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Commission To Market]) / sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]))
which work fine, but this doesn't work here. How can I do it here?
Thanks
Hans
|||Hi Hans,
With ParallelPeriod() a different approach is needed, which would depend on the attributes in the 3 hierarchies and the attribute relations (an entry in Mosha's blog shows some examples). If all the hierarchies align with the beginning of the time dimension (which isn't true for Adventure Works Calendar hierarchy), then a cube MDX script assignment like this might work - otherwise, you could describe the attributes and hierarchies in more detail:
([Measure].[SalesGrowth], [Date].[Date].Members) =
IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG]
* (parallelperiod([Date].[Periode - Year].[Year]), [Measures].[Sales Volume KG])) );
Friday, March 9, 2012
Dynamic Email Attachment Name
I have a report subscription set up to email a report via a Data
Driven Subscription. I have been asked to make the file name unique
with a date stamp in the email message. I do not see the FileName
field as one of the options in the email delivery. I know that I can
do this via the FileShare extension, but this report is going to an
outside company that does not have access to our file share.
Is there any way to dynamically set the file name in the email
attachment?On Dec 14, 5:06 pm, dachrist <dachris...@.gmail.com> wrote:
> I have a report subscription set up to email a report via a Data
> Driven Subscription. I have been asked to make the file name unique
> with a date stamp in the email message. I do not see the FileName
> field as one of the options in the email delivery. I know that I can
> do this via the FileShare extension, but this report is going to an
> outside company that does not have access to our file share.
> Is there any way to dynamically set the file name in the email
> attachment?
Probably the best option you have for emailing a report with a dynamic
naming convention is to use the Reporting Services Web Service (http://
msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to call, export and dynamically name the report. Then use the built in
functionality of the .NET framework via a custom ASP.NET/Exe
application to email the reports (using System.Net.Mail). Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Driven Subscription. I have been asked to make the file name unique
with a date stamp in the email message. I do not see the FileName
field as one of the options in the email delivery. I know that I can
do this via the FileShare extension, but this report is going to an
outside company that does not have access to our file share.
Is there any way to dynamically set the file name in the email
attachment?On Dec 14, 5:06 pm, dachrist <dachris...@.gmail.com> wrote:
> I have a report subscription set up to email a report via a Data
> Driven Subscription. I have been asked to make the file name unique
> with a date stamp in the email message. I do not see the FileName
> field as one of the options in the email delivery. I know that I can
> do this via the FileShare extension, but this report is going to an
> outside company that does not have access to our file share.
> Is there any way to dynamically set the file name in the email
> attachment?
Probably the best option you have for emailing a report with a dynamic
naming convention is to use the Reporting Services Web Service (http://
msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to call, export and dynamically name the report. Then use the built in
functionality of the .NET framework via a custom ASP.NET/Exe
application to email the reports (using System.Net.Mail). Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Dynamic default value for parameter in OLAP report
I have a basic SSRS report against an SSAS database with a "start date" parameter. I want to set the default value of that date parameter to Today's date. What is the easiest way to do this? I have no problem doing it against a relational source, just haven't done it against an OLAP source.
Thanks for any ideas.
-Josh R.
At parameter properties page in section "Default values" check radio button "Non-queried" and then at Expression box write:
=Today
Wednesday, March 7, 2012
Dynamic Date/Calendar
Hey,
I have a financial query that has to retrieve sales totals for each day of
the month for the current year and prior year. Some days though (current an
d
prior) don't have any sales on those days so I still have to return zero. I
have this working just fine...but to do it I created a month_day_year table
that has all the month/day/year columns in it. I join to this and thus am
always assured to have rows/columns returned regardless if there are sales
for those days. The problem is the maintenance of this month_day table. If I
forget to add new months...things go bad. Surely there has to be a better wa
y
to do this...like some way to dynamically generate the month/day/year idea a
t
query run time...and not use this table to join to to ensure I get something
whether data is there or not.
Thanks in advance,
Paul
--
---
Jackson-Reed, Inc. www.jacksonreed.comActually, a calendar table is a great way to handle financial data like
this. As you know fiscal periods don't always line up to calendar
periods; a calendar table let yous manage this and other problems.
Managing this table shouldn't be a big deal; 10 years of days is only
slightly more than 3600 rows; 20 years is still less than 10,000 rows
of data, which should be nothing to manage. I would just generate the
calendar entries for 10 to 20 years, and leave it alone.
Stu|||> for those days. The problem is the maintenance of this month_day table. If
> I
> forget to add new months...things go bad.
Why do you need to add months and days in an ongoing fashion? Just put the
next 30 years in the table, and you might have to think about it again once
before you retire. 10,000 rows is less stressful than a walk in the park
for SQL Server.
http://www.aspfaq.com/2519
I have a financial query that has to retrieve sales totals for each day of
the month for the current year and prior year. Some days though (current an
d
prior) don't have any sales on those days so I still have to return zero. I
have this working just fine...but to do it I created a month_day_year table
that has all the month/day/year columns in it. I join to this and thus am
always assured to have rows/columns returned regardless if there are sales
for those days. The problem is the maintenance of this month_day table. If I
forget to add new months...things go bad. Surely there has to be a better wa
y
to do this...like some way to dynamically generate the month/day/year idea a
t
query run time...and not use this table to join to to ensure I get something
whether data is there or not.
Thanks in advance,
Paul
--
---
Jackson-Reed, Inc. www.jacksonreed.comActually, a calendar table is a great way to handle financial data like
this. As you know fiscal periods don't always line up to calendar
periods; a calendar table let yous manage this and other problems.
Managing this table shouldn't be a big deal; 10 years of days is only
slightly more than 3600 rows; 20 years is still less than 10,000 rows
of data, which should be nothing to manage. I would just generate the
calendar entries for 10 to 20 years, and leave it alone.
Stu|||> for those days. The problem is the maintenance of this month_day table. If
> I
> forget to add new months...things go bad.
Why do you need to add months and days in an ongoing fashion? Just put the
next 30 years in the table, and you might have to think about it again once
before you retire. 10,000 rows is less stressful than a walk in the park
for SQL Server.
http://www.aspfaq.com/2519
Dynamic date value required for function / view
Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
Jamie
Hi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie
|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
Jamie
Hi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie
|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>
Dynamic date value required for function / view
Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pas
s
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pas
s
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>
Dynamic date value required for function / view
Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
--
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> > Is there a method to send getdate() to a function without the function
> > returning an error: (functions is called from a view)
> > Incorrect syntax near '('
> >
> > example:
> > Select * from myfunction(getdate())
> >
> > create function myfunction (@.Today datetime)
> > returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> > Begin
> > --do stuff
> > return
> > End
> >
> >
> >
> > --
> > Regards,
> > Jamie
>
>
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
--
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> > Is there a method to send getdate() to a function without the function
> > returning an error: (functions is called from a view)
> > Incorrect syntax near '('
> >
> > example:
> > Select * from myfunction(getdate())
> >
> > create function myfunction (@.Today datetime)
> > returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> > Begin
> > --do stuff
> > return
> > End
> >
> >
> >
> > --
> > Regards,
> > Jamie
>
>
Dynamic date setting in loops
Hello everybody,
Now everything works!
I've been trying to write a script to populate a table.
One filed is of 'date' type and I would like to insert dates different from record to record.
I thought about creating a loop and then try to increment the day (or the hour, I don'care) by using the loop index.
There comes of course a problem of casting from integers to strings (or date).
I tried to do something like:
DECLARE @.K INT
WHILE(@.K<10)
BEGIN
INSERT INTO TABLE1 (DATETIME) VALUES ('20071026 11:'||CAST(@.K AS VARCHAR)||'00')
SET @.K=@.K+1
END
but it didn't work ...
Would you please suggest a method of performing this action?
Thanks in advance,
Stefano.What is the data that you are trying to insert. I dont think the initial condition of the loop satisfies at all.|||The data type is 'datetime' and I was trying to build the string by a cat operation.
For instance, to build '20071025 12:28:40' I coded:
'20071025 12:'+cast(@.j+28, varchar)+':40'
where @.j is the loop variable.
The aim is to obtain strings with dates like:
..............................
'20071025 12:29:40'
'20071025 12:30:40'
'20071025 12:31:40'
'20071025 12:32:40'
'20071025 12:33:40'
and so on ...|||If you have the table like below:
TABLE1 ([DATETIME] DATETIME)
The modification your script to correct one as follows:
DECLARE @.K INT
SET @.K = 0
WHILE(@.K<10)
BEGIN
INSERT INTO TABLE1 (DATETIME) VALUES (convert(datetime,'2007-10-26 11:'+CAST(@.K AS VARCHAR)+':00'))
SET @.K=@.K+1
END|||I implemented the suggested modification, the parser says ok, but the run.time execution got the following error:
Server: Msg 242, Level 16, State 3, Line 12
The conversion of a char data to a datetime data type resulted in
an out-of-range datetime value.
The statement has been terminated
The troubles keep going ...
Quote:
Originally Posted by sayedul
If you have the table like below:
TABLE1 ([DATETIME] DATETIME)
The modification your script to correct one as follows:
DECLARE @.K INT
SET @.K = 0
WHILE(@.K<10)
BEGIN
INSERT INTO TABLE1 (DATETIME) VALUES (convert(datetime,'2007-10-26 11:'+CAST(@.K AS VARCHAR)+':00'))
SET @.K=@.K+1
END
Now everything works!
Sorry, I am stupid ...
Thanks a lot anyway for the helpful suggetsions!!!
Stefano.
Dynamic Date Range
For some parameters i have in a report, i need it to use the first day of the month and the last day of the month.
The problem is that i need the variable that returns these values, i need them to be dynamic so that i can have them sent out as a subscription
For example here are my parameters and what i need them to equal
The problem is that i need the variable that returns these values, i need them to be dynamic so that i can have them sent out as a subscription
For example here are my parameters and what i need them to equal
@.StartDate=@.FirstDayOfThisMonth<--Does a variable like this exist?
@.EndDate=@.LastDayOfThisMonth<-Same here i need this sort of variable
Or if there is a way to somehow retrieve this value, I know how to do it in vb.net but i need it for the subscription
Thanks For any help you can giveI havent used subscriptions so I dont know how they work but you can write custom code in vb.net and use the value in the report..doesnt that work ?|||it may, how would you go about doing that. Where do you create the code, and where do you place it?|||
write a vb.net function that will return what you want (the first day of the month) and put it in the code tab under Report -> Properties > code tab. from your report you can call the function as = Code.functionname( Fields!Parameter1.Value, Fields!Parameter2.Value )
You can also loook up books online for some samples..
|||I hope this can be helpful fro you:
SELECT DATEADD(d, -1*DAY(GETDATE())+1, GETDATE()) FirstDayMonth,DATEADD(d, -1*DAY(GETDATE())-1, DATEADD(m, 1, GETDATE())) LastDayMonth
Dynamic Date Parameter in Report Manager
Hi all,
I was wondering if there's a way to override the default date parameter of a report to a dynamic value such as Today(), Now(), DateAdd(),... through Report Manager.
I know this can be done in Report Designer, but never been successful when I try to change the date parameter value in Report Manager.
I've tried to change the date parameter value by clicking on the Override Default button in the report's properties page from Report Manager, error like the one below will be shown after I entered Now() in the Default Value field and clicked Apply:
The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)
Thanks in advance.
You can set defaults by editing the parameter properties from the Layout tab. Just right click anywhere on the "blank" non used area of your report. Click the "Report Parameters" option. Select the parameter you want to default, and use the Non-Queried option. I used the following code to set Start and End date defaults for the current month. Hope that this helps.
For the Start Date parameter I have this in the box:
=DateAdd("d",-Day(Today())+1,Today())
End Date:
=DateAdd("h",23,DateAdd("d",-1,DateAdd("m",1,DateAdd("d",-Day(Today())+1,Today()))) )
For October of 2006, this should set the Start and End date parameters to 10/01/2006 12:00:00 AM and 10/31/2006 11:00:00 PM
Dynamic Date Parameter in Report Manager
Hi all,
I was wondering if there's a way to override the default date parameter of a report to a dynamic value such as Today(), Now(), DateAdd(),... through Report Manager.
I know this can be done in Report Designer, but never been successful when I try to change the date parameter value in Report Manager.
I've tried to change the date parameter value by clicking on the Override Default button in the report's properties page from Report Manager, error like the one below will be shown after I entered Now() in the Default Value field and clicked Apply:
The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)
Thanks in advance.
You can set defaults by editing the parameter properties from the Layout tab. Just right click anywhere on the "blank" non used area of your report. Click the "Report Parameters" option. Select the parameter you want to default, and use the Non-Queried option. I used the following code to set Start and End date defaults for the current month. Hope that this helps.
For the Start Date parameter I have this in the box:
=DateAdd("d",-Day(Today())+1,Today())
End Date:
=DateAdd("h",23,DateAdd("d",-1,DateAdd("m",1,DateAdd("d",-Day(Today())+1,Today()))) )
For October of 2006, this should set the Start and End date parameters to 10/01/2006 12:00:00 AM and 10/31/2006 11:00:00 PM
Dynamic Date Parameter in Report Manager
Hi all,
I was wondering if there's a way to override the default date parameter of a report to a dynamic value such as Today(), Now(), DateAdd(),... through Report Manager.
I know this can be done in Report Designer, but never been successful when I try to change the date parameter value in Report Manager.
I've tried to change the date parameter value by clicking on the Override Default button in the report's properties page from Report Manager, error like the one below will be shown after I entered Now() in the Default Value field and clicked Apply:
The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)
Thanks in advance.
You can set defaults by editing the parameter properties from the Layout tab. Just right click anywhere on the "blank" non used area of your report. Click the "Report Parameters" option. Select the parameter you want to default, and use the Non-Queried option. I used the following code to set Start and End date defaults for the current month. Hope that this helps.
For the Start Date parameter I have this in the box:
=DateAdd("d",-Day(Today())+1,Today())
End Date:
=DateAdd("h",23,DateAdd("d",-1,DateAdd("m",1,DateAdd("d",-Day(Today())+1,Today()))) )
For October of 2006, this should set the Start and End date parameters to 10/01/2006 12:00:00 AM and 10/31/2006 11:00:00 PM
|||Does anyone have a solution to the original question? Because if you override the default and put in an expression, you get an error like stated in the first post. It works before you override the default, but if you override the default and then enter in the modified expression, I get 'Syntax error converting datetime from character string.'
Thanks,
Iris
Dynamic Date Function for @date
Greetings...
I have a report that is being deployed w/ the parameters @.sdate (Start Date)
and @.edate (End Date). My goal is to setup a subscription to a few folks w/
the default dates to be the previous day. I tried using the =datetime.today
-1 but recieved errors during compile. If someone has a solution to this
please reply, it will be most helpful.
Thanks,
--
Ben Sullins
Our Vacation Store
http://ovstravelfolio.comFor others in the same boat I have found the solution to this...
To set the default value for report parameters using an expression you must
first be in 'layout' view in report designer. From there you will need to
select 'Report' from the properties window. Then click on the elipse (...)
next to 'Report Parameters'. From there you will see the Report Parameters
Dialogue box, this is where you set the default value for the parameters. You
can use other query results or functions like this,
=datetime.today.adddays(-1).tostring("mm/dd/yy").
Hope this helps!
"Ben Sullins" wrote:
> Greetings...
> I have a report that is being deployed w/ the parameters @.sdate (Start Date)
> and @.edate (End Date). My goal is to setup a subscription to a few folks w/
> the default dates to be the previous day. I tried using the =datetime.today
> -1 but recieved errors during compile. If someone has a solution to this
> please reply, it will be most helpful.
> Thanks,
> --
> Ben Sullins
> Our Vacation Store
> http://ovstravelfolio.com
I have a report that is being deployed w/ the parameters @.sdate (Start Date)
and @.edate (End Date). My goal is to setup a subscription to a few folks w/
the default dates to be the previous day. I tried using the =datetime.today
-1 but recieved errors during compile. If someone has a solution to this
please reply, it will be most helpful.
Thanks,
--
Ben Sullins
Our Vacation Store
http://ovstravelfolio.comFor others in the same boat I have found the solution to this...
To set the default value for report parameters using an expression you must
first be in 'layout' view in report designer. From there you will need to
select 'Report' from the properties window. Then click on the elipse (...)
next to 'Report Parameters'. From there you will see the Report Parameters
Dialogue box, this is where you set the default value for the parameters. You
can use other query results or functions like this,
=datetime.today.adddays(-1).tostring("mm/dd/yy").
Hope this helps!
"Ben Sullins" wrote:
> Greetings...
> I have a report that is being deployed w/ the parameters @.sdate (Start Date)
> and @.edate (End Date). My goal is to setup a subscription to a few folks w/
> the default dates to be the previous day. I tried using the =datetime.today
> -1 but recieved errors during compile. If someone has a solution to this
> please reply, it will be most helpful.
> Thanks,
> --
> Ben Sullins
> Our Vacation Store
> http://ovstravelfolio.com
Sunday, February 19, 2012
Dynamic columns
Hi,
Can a column be created dynamically in a table...
For example the user selects start date and enddate and i want to create
columns from startdate to enddate...
Thanks,
Chandra.Chandra,
The simple answer for tables is "No".
What you should be using is a matrix, the columns are dynamic on this.
Chris
CCP wrote:
> Hi,
> Can a column be created dynamically in a table...
> For example the user selects start date and enddate and i want to
> create columns from startdate to enddate...
> Thanks,
> Chandra.
Can a column be created dynamically in a table...
For example the user selects start date and enddate and i want to create
columns from startdate to enddate...
Thanks,
Chandra.Chandra,
The simple answer for tables is "No".
What you should be using is a matrix, the columns are dynamic on this.
Chris
CCP wrote:
> Hi,
> Can a column be created dynamically in a table...
> For example the user selects start date and enddate and i want to
> create columns from startdate to enddate...
> Thanks,
> Chandra.
Friday, February 17, 2012
Dynamic column in the query using SQL 2005
Hi All,
I am using Micosoft Visual Studio Report Desinger. with MS SQL 2005.
I have a table transac table fields are likely,
location,date,amount values,
USA,01/07/2006,3000
SG,01/07/2006,2500
USA,02/07/2006,6000
SG,02/07/2006,3500
USA,03/07/2006,1000
SG,03/07/2006,6700
USA,04/07/2006,500
SG,04/07/2006,200
Am writing query for date = 04/07/2006
select location,date,amount from transac where date = 04/07/2006
I wanted to add two more column in the query which is
a.two days before what is the amount
b. From 01/07/2006 to 04/07/2006 what is the amount
The result I want to be
Location,date,amount,2daysbefore,uptodate
USA,04/07/2006,500,6000,10500
SG,04/07/2006,200,3500,12900
How to write a query ?.
I am writing this query from DataSet for Report Desinger.
Is there any way to include this two column.
Please Advise,
Regrads Saleem
Here is the query in bold, the rest if for creating a tmp table with approx values like the ones you use. NB date format is MM/DD/YYYY.create table #x
(
country varchar(10),
Date datetime,
PRICE1 decimal(9,2),
)
insert #x
select 'USA', '1/1/2006', 3000 union all
select 'SG', '1/1/2006', 2500 union all
select 'USA', '1/2/2006', 2500 union all
select 'SG', '1/2/2006', 1500 union all
select 'USA', '1/3/2006', 1000 union all
select 'SG', '1/3/2006', 7550 union all
select 'USA', '1/4/2006', 500 union all
select 'SG', '1/4/2006', 300 union all
select 'USA', '1/5/2006', 350 union all
select 'SG', '1/5/2006', 400
select
country,
date,
price1 as dayAmount,
(select price1 from #x as b where datediff(dd,b.date,a.date) = 2 and a.country = b.country) as prevDayAmount,
(select sum(price1) from #x as b where datediff(mm,b.date,a.date) < 1 and a.country = b.country) as sumMonthAmount
from #x as a
where date = '01/03/2006'
drop table #x
Dynamic CASE statement based on list of dates
I have the following table of data. I need to take a date from a large table and do the following case:
CASE
When date < date(0)
Then '0'
When date between date(0) and date(1)
Then '1'
When date between date(1) and date(2)
Then '2'
When date >= date(3)
Then '3'
What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)
I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.
thanks
Milton
Dates Table
Fee Table
If you're using SQL Server 2005, you could write something like this. It combines two of the new features of SQL Server 2005: ROW_NUMBER T-SQL Function, and COMMON TABLE EXPRESSIONS (CTE).
--------------------------------------
USE NORTHWIND
GO
WITH CTE_ALL_DATES (ID, DATE)
AS (
SELECT ROW_NUMBER()OVER (ORDER BY DATEASC)AS ID
, DATE
FROM (
SELECTCAST('1995-01-01'AS DATETIME) DATE
UNION
SELECTCAST('1996-08-01'AS DATETIME)
UNION
SELECTCAST('1997-04-01'AS DATETIME)
UNION
SELECTCAST('1998-03-01'AS DATETIME)
UNION
SELECTCAST('2000-01-01'AS DATETIME)
) T1
)
SELECT D1.ID
, D1.DATE
, O.*
FROM ORDERS O
JOIN CTE_ALL_DATES D1
ON D1.DATE <= O.[ORDERDATE]
--------------------------------------
You would just have to replacee all the UNION, UNION, UNION with your Dates table. You can test out this code against Northwind database.
Hope this helps,
|||Ok, thanks. I am using sql server 2000.
|||SQL Server 2000 Version, it is not as simple as the 2005 version but it works, you should probably encapsulate it in a stored procedure.
--
USE NORTHWINDGO-- CREATE TEMP, FOR DATES NUMBERINGCREATE TABLE #INDEX_ALL_DATES(IDINT IDENTITY(1,1) ,DATESMALLDATETIME )CREATE CLUSTERED INDEX IX_INDEX_ALL_DATES_DATE_IDON #INDEX_ALL_DATES(DATE)INSERT #INDEX_ALL_DATES(DATE)SELECT DATEFROM (-- REPLACE THIS WITH YOUR SELECT * FROM DATES TABLESELECTDATEADD(M, -3,MIN([ORDERDATE])) DATEFROM ORDERSUNIONSELECTDATEADD(M, -3,DATEADD(DAY, (DATEDIFF(DAY,MAX([ORDERDATE]),MIN([ORDERDATE])))/2,MIN([ORDERDATE])))FROM ORDERSUNIONSELECTDATEADD(M, -3,MAX([ORDERDATE]))FROM ORDERS-- REPLACE THIS WITH YOUR SELECT * FROM DATES TABLE)AS T1ORDER BY DATEASC SELECT D1.ID, D1.DATE, O.*FROM ORDERS OJOIN #INDEX_ALL_DATES D1ON D1.DATE <= O.[ORDERDATE]-- CLEAN UPDROP TABLE #INDEX_ALL_DATES--
Hope this helps,
Subscribe to:
Posts (Atom)