date queries

I have written a report that contains multiple queries that displays the
number of applications we submit month by month to various entities. I wrote
the report last year and have been asked to create it again this year.
I know that this is going to be require every year, and I don't want to
write it again.
My problem is that in order to get the month by month data, I had to write
"WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
tDocHistory.ChangeDate)), CONVERT(CHAR(4),
DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
I would like to be able to allow my users to select the year they wish to
view.
I wanted to replace the 2008 with % in the statement:
(tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
and then filter the datepart for the year by writing:
HAVING (tDocHistory.NewStatus = 'wv' OR
tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
tDocHistory.ChangeDate))
LIKE @.Year)
Do you know any way I could make this work?
--
SamyraWhy dont you use report parameter to select the date.
On Jan 12, 8:28=A0pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> I have written a report that contains multiple queries that displays the
> number of applications we submit month by month to various entities. =A0I =wrote
> the report last year and have been asked to create it again this year.
> I know that this is going to be require every year, and I don't want to
> write it again.
> My problem is that in order to get the month by month data, I had to =A0wr=ite
> "WHERE =A0 =A0 (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.ChangeDate)), CONV=ERT(CHAR(4),
> DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> I would like to be able to allow my users to select the year they wish to
> view.
> I wanted to replace the 2008 with % in the statement:
> =A0 (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> and then filter the datepart for the year by writing:
> HAVING =A0 =A0 =A0(tDocHistory.NewStatus =3D 'wv' OR
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.NewStatus =3D 'dcs=') AND (tCompany.CoName
> LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> tDocHistory.ChangeDate))
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 LIKE @.Year)
> Do you know any way I could make this work?
> --
> Samyra|||I would love to, but the report is built with text boxes with a separate
query in each box.
I am looking at a table that contains historical data for each record in the
database, not current data, so I wasn't able to build a table or a matrix
report that would display the data they wanted to see.
--
Samyra
"Sridar K" wrote:
> Why dont you use report parameter to select the date.
> On Jan 12, 8:28 pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> > I have written a report that contains multiple queries that displays the
> > number of applications we submit month by month to various entities. I wrote
> > the report last year and have been asked to create it again this year.
> >
> > I know that this is going to be require every year, and I don't want to
> > write it again.
> >
> > My problem is that in order to get the month by month data, I had to write
> >
> > "WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> > GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> > tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> > tDocHistory.ChangeDate)), CONVERT(CHAR(4),
> > DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> >
> > I would like to be able to allow my users to select the year they wish to
> > view.
> >
> > I wanted to replace the 2008 with % in the statement:
> > (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> >
> > and then filter the datepart for the year by writing:
> >
> > HAVING (tDocHistory.NewStatus = 'wv' OR
> > tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
> > LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> > tDocHistory.ChangeDate))
> > LIKE @.Year)
> >
> > Do you know any way I could make this work?
> >
> > --
> > Samyra
>

Date Formats

Hi all,
In our whole system (Sql server, forms applications etc) I have configured
our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
problem is that Reporting services only respond to the format mm/dd/yyyy
... How can I configure reporting services to only use the format
dd/mm/yyyy?
Thanks alot in advance
RThere is a language property for the report and a textbox. You can for
example select English (United Kingdom), and the date will display as
dd/mm/yy. There is also an International Settings option in Tools,Options of
the development enviromnent.
The report property setting overrides the global setting, and the textbox
property overrides the report property.
The language property can also be left as Default on the report or textbox.
If left as Default, the more global settings takes effect.
Finally, you can use custom expressions such as
=Format(Fields!MyDateField.Value, "dd/MMM/yy")
HTH
Charles Kangai, MCT, MCDBA
"Rudi Groenewald" wrote:
> Hi all,
> In our whole system (Sql server, forms applications etc) I have configured
> our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> problem is that Reporting services only respond to the format mm/dd/yyyy
> .... How can I configure reporting services to only use the format
> dd/mm/yyyy?
> Thanks alot in advance
> R
>
>|||Hi
I'm using the product in the UK so the international settings are important.
On the Tools, Options route I only get the choice of 'English (United States)
or 'Same as Microsoft Office (Language Neutral)'. I'm not sure if this is
because I'm an MSDN subscriber or not.
I'm not keen on having to manually change the properties in every field on a
report so I can get dates, currencies etc in the right format. Is there a way
of using the machine regional settings as is done by most other products?
If this is just a problem because I'm using an MSDN version then I'll have
to bite the bullet and get rid of my MSDN developer subscription.
Cheers
Dave
"Charles Kangai" wrote:
> There is a language property for the report and a textbox. You can for
> example select English (United Kingdom), and the date will display as
> dd/mm/yy. There is also an International Settings option in Tools,Options of
> the development enviromnent.
> The report property setting overrides the global setting, and the textbox
> property overrides the report property.
> The language property can also be left as Default on the report or textbox.
> If left as Default, the more global settings takes effect.
> Finally, you can use custom expressions such as
> =Format(Fields!MyDateField.Value, "dd/MMM/yy")
> HTH
> Charles Kangai, MCT, MCDBA
> "Rudi Groenewald" wrote:
> > Hi all,
> >
> > In our whole system (Sql server, forms applications etc) I have configured
> > our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> > problem is that Reporting services only respond to the format mm/dd/yyyy
> > .... How can I configure reporting services to only use the format
> > dd/mm/yyyy?
> >
> > Thanks alot in advance
> >
> > R
> >
> >
> >|||There is no need to change the Language property of every field. Just change
it for the report. The Language property of the report takes effect unless
you change it at textbox level. Textbox setting overrides Report setting.
You are obviously aware of the Microsoft Office language setting in Tools,
Options. Have you tried using it and seeing whether new reports automatically
take the language that is set in Microsoft office? I am also in the UK, but
unfortunately have the US language installed as I am working for a US company.
Failing that, use the Report language property and not have to change it for
each textbox.
Cheers
Charles Kangai, MCT, MCDBA
"Dave Phillimore" wrote:
> Hi
> I'm using the product in the UK so the international settings are important.
> On the Tools, Options route I only get the choice of 'English (United States)
> or 'Same as Microsoft Office (Language Neutral)'. I'm not sure if this is
> because I'm an MSDN subscriber or not.
> I'm not keen on having to manually change the properties in every field on a
> report so I can get dates, currencies etc in the right format. Is there a way
> of using the machine regional settings as is done by most other products?
> If this is just a problem because I'm using an MSDN version then I'll have
> to bite the bullet and get rid of my MSDN developer subscription.
> Cheers
> Dave
> "Charles Kangai" wrote:
> > There is a language property for the report and a textbox. You can for
> > example select English (United Kingdom), and the date will display as
> > dd/mm/yy. There is also an International Settings option in Tools,Options of
> > the development enviromnent.
> >
> > The report property setting overrides the global setting, and the textbox
> > property overrides the report property.
> >
> > The language property can also be left as Default on the report or textbox.
> > If left as Default, the more global settings takes effect.
> >
> > Finally, you can use custom expressions such as
> > =Format(Fields!MyDateField.Value, "dd/MMM/yy")
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Rudi Groenewald" wrote:
> >
> > > Hi all,
> > >
> > > In our whole system (Sql server, forms applications etc) I have configured
> > > our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> > > problem is that Reporting services only respond to the format mm/dd/yyyy
> > > .... How can I configure reporting services to only use the format
> > > dd/mm/yyyy?
> > >
> > > Thanks alot in advance
> > >
> > > R
> > >
> > >
> > >|||Hello All,
This message appears to be related to a problem I have with date formats,
however the solution given only makes the report display in one format or
another. My problem is that I have users all over the world that want to
display the same report. I would like to have the same report display for
them all without having to specify one language over another and have it
automatically handled by Reporting Services. Does anyone know of a way to do
this? or am I stuck creating multiple reports, one for standard date format
and the other for each different format?
Thanks
"Charles Kangai" wrote:
> There is no need to change the Language property of every field. Just change
> it for the report. The Language property of the report takes effect unless
> you change it at textbox level. Textbox setting overrides Report setting.
> You are obviously aware of the Microsoft Office language setting in Tools,
> Options. Have you tried using it and seeing whether new reports automatically
> take the language that is set in Microsoft office? I am also in the UK, but
> unfortunately have the US language installed as I am working for a US company.
> Failing that, use the Report language property and not have to change it for
> each textbox.
> Cheers
> Charles Kangai, MCT, MCDBA
> "Dave Phillimore" wrote:
> > Hi
> >
> > I'm using the product in the UK so the international settings are important.
> > On the Tools, Options route I only get the choice of 'English (United States)
> > or 'Same as Microsoft Office (Language Neutral)'. I'm not sure if this is
> > because I'm an MSDN subscriber or not.
> >
> > I'm not keen on having to manually change the properties in every field on a
> > report so I can get dates, currencies etc in the right format. Is there a way
> > of using the machine regional settings as is done by most other products?
> >
> > If this is just a problem because I'm using an MSDN version then I'll have
> > to bite the bullet and get rid of my MSDN developer subscription.
> >
> > Cheers
> > Dave
> >
> > "Charles Kangai" wrote:
> >
> > > There is a language property for the report and a textbox. You can for
> > > example select English (United Kingdom), and the date will display as
> > > dd/mm/yy. There is also an International Settings option in Tools,Options of
> > > the development enviromnent.
> > >
> > > The report property setting overrides the global setting, and the textbox
> > > property overrides the report property.
> > >
> > > The language property can also be left as Default on the report or textbox.
> > > If left as Default, the more global settings takes effect.
> > >
> > > Finally, you can use custom expressions such as
> > > =Format(Fields!MyDateField.Value, "dd/MMM/yy")
> > >
> > > HTH
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > > "Rudi Groenewald" wrote:
> > >
> > > > Hi all,
> > > >
> > > > In our whole system (Sql server, forms applications etc) I have configured
> > > > our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> > > > problem is that Reporting services only respond to the format mm/dd/yyyy
> > > > .... How can I configure reporting services to only use the format
> > > > dd/mm/yyyy?
> > > >
> > > > Thanks alot in advance
> > > >
> > > > R
> > > >
> > > >
> > > >|||Apologies in advance if I have not properly understood. If you want the users
to see exactly the same thing, then you hard-code the date format in the
Format property of the field, e.g. dd-MMM-yyyy. If you want their Regional
Settings to be used, you can just enter a single letter "d" in the Format
property or "D". "d" stands for short date format as indicated in Regional
Settings; "D" stands for long date.
As far as I can see you can do anything you want: have the date display
according to the user's locale, or have a fixed display irrespective of
user's locale.
Charles Kangai, MCT, MCDBA
"Support Mechanic" wrote:
> Hello All,
> This message appears to be related to a problem I have with date formats,
> however the solution given only makes the report display in one format or
> another. My problem is that I have users all over the world that want to
> display the same report. I would like to have the same report display for
> them all without having to specify one language over another and have it
> automatically handled by Reporting Services. Does anyone know of a way to do
> this? or am I stuck creating multiple reports, one for standard date format
> and the other for each different format?
> Thanks
>
> "Charles Kangai" wrote:
> > There is no need to change the Language property of every field. Just change
> > it for the report. The Language property of the report takes effect unless
> > you change it at textbox level. Textbox setting overrides Report setting.
> >
> > You are obviously aware of the Microsoft Office language setting in Tools,
> > Options. Have you tried using it and seeing whether new reports automatically
> > take the language that is set in Microsoft office? I am also in the UK, but
> > unfortunately have the US language installed as I am working for a US company.
> >
> > Failing that, use the Report language property and not have to change it for
> > each textbox.
> >
> > Cheers
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Dave Phillimore" wrote:
> >
> > > Hi
> > >
> > > I'm using the product in the UK so the international settings are important.
> > > On the Tools, Options route I only get the choice of 'English (United States)
> > > or 'Same as Microsoft Office (Language Neutral)'. I'm not sure if this is
> > > because I'm an MSDN subscriber or not.
> > >
> > > I'm not keen on having to manually change the properties in every field on a
> > > report so I can get dates, currencies etc in the right format. Is there a way
> > > of using the machine regional settings as is done by most other products?
> > >
> > > If this is just a problem because I'm using an MSDN version then I'll have
> > > to bite the bullet and get rid of my MSDN developer subscription.
> > >
> > > Cheers
> > > Dave
> > >
> > > "Charles Kangai" wrote:
> > >
> > > > There is a language property for the report and a textbox. You can for
> > > > example select English (United Kingdom), and the date will display as
> > > > dd/mm/yy. There is also an International Settings option in Tools,Options of
> > > > the development enviromnent.
> > > >
> > > > The report property setting overrides the global setting, and the textbox
> > > > property overrides the report property.
> > > >
> > > > The language property can also be left as Default on the report or textbox.
> > > > If left as Default, the more global settings takes effect.
> > > >
> > > > Finally, you can use custom expressions such as
> > > > =Format(Fields!MyDateField.Value, "dd/MMM/yy")
> > > >
> > > > HTH
> > > >
> > > > Charles Kangai, MCT, MCDBA
> > > >
> > > > "Rudi Groenewald" wrote:
> > > >
> > > > > Hi all,
> > > > >
> > > > > In our whole system (Sql server, forms applications etc) I have configured
> > > > > our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> > > > > problem is that Reporting services only respond to the format mm/dd/yyyy
> > > > > .... How can I configure reporting services to only use the format
> > > > > dd/mm/yyyy?
> > > > >
> > > > > Thanks alot in advance
> > > > >
> > > > > R
> > > > >
> > > > >
> > > > >|||question sort of related, how would i format the date on a parameter so that
when it is entered "1/1/2005" or "01/01/2005" that it will print January 1,
2005.
thanks in advance
"Rudi Groenewald" wrote:
> Hi all,
> In our whole system (Sql server, forms applications etc) I have configured
> our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> problem is that Reporting services only respond to the format mm/dd/yyyy
> .... How can I configure reporting services to only use the format
> dd/mm/yyyy?
> Thanks alot in advance
> R
>
>|||Hi,
=Format(DateValue,"MMMM d, yyyy")
HTH,
Eric