CurrentDate within a report?

Hi all, im having difficulty using dates within my report. Any advice
welcome.
Basically I want to restrict my query by date, however, I want my start and
enddate parameters to have universal generic values.
I.e.
startdate = CurrentDate - 31
enddate = CurrentDate
I cannot figure out how todo this within RS 2005, I have attempted to use
some of the .NET datetime functions but I cannot get them to work (i.e.
"Today() - 31" etc)
What should I be doing?
Kind regards
TazTry the following expressions:
=Today() -- for StartDate
=DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Tarun Mistry" wrote:
> Hi all, im having difficulty using dates within my report. Any advice
> welcome.
> Basically I want to restrict my query by date, however, I want my start and
> enddate parameters to have universal generic values.
> I.e.
> startdate = CurrentDate - 31
> enddate = CurrentDate
> I cannot figure out how todo this within RS 2005, I have attempted to use
> some of the .NET datetime functions but I cannot get them to work (i.e.
> "Today() - 31" etc)
> What should I be doing?
> Kind regards
> Taz
>
>|||Many thanks for the reply, this has helped alot.
However, a new problem has surfaced. There is an inconsistency between the
date format in my data fields and the format within the database. I need to
force an American format, how can I do this?
Alternatively, is there a way to convert UK dates into American dates?
Currently, each time I run my report, within the parameter window, the
values are being alternated (which is VERY strange).
I.e.
12/01/2006 -> Press "View Report"
01/12/2006 -> Press "View Report"
12/01/2006 -> Press "View Report"
etc etc
Thanks for your help
Taz
"magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
> Try the following expressions:
> =Today() -- for StartDate
> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
> HTH
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland
>
> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>|||did you put that date formula into an expression or did you put it in the
data tab.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
> Many thanks for the reply, this has helped alot.
> However, a new problem has surfaced. There is an inconsistency between the
> date format in my data fields and the format within the database. I need
> to force an American format, how can I do this?
> Alternatively, is there a way to convert UK dates into American dates?
> Currently, each time I run my report, within the parameter window, the
> values are being alternated (which is VERY strange).
> I.e.
> 12/01/2006 -> Press "View Report"
> 01/12/2006 -> Press "View Report"
> 12/01/2006 -> Press "View Report"
> etc etc
> Thanks for your help
> Taz
> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>|||I have put those formulas into the Default values within the "Report
Parameters" setup.
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
> did you put that date formula into an expression or did you put it in the
> data tab.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>|||try this: =switch(Fields!YOURFIELD.Value = "US","M/d/yy")
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
> did you put that date formula into an expression or did you put it in the
> data tab.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>|||I'm not sure where you need to force an American date format.
If you are referencing columns in your SQL Server database which have a
datatype of DATETIME then you shouldn't have a problem. If you are running
RS2000 and haven't applied Service Pack two you can get inconsistencies in
presentation of dates when entering report parameters - apply the service
pack.
Also, you can force a UK format in your reports by using the format property
of a given textbox, making it something like dd-MM-yy.
If none of these pointers helps you then please clarify what the exact
problem is with date formats.
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Tarun Mistry" wrote:
> Many thanks for the reply, this has helped alot.
> However, a new problem has surfaced. There is an inconsistency between the
> date format in my data fields and the format within the database. I need to
> force an American format, how can I do this?
> Alternatively, is there a way to convert UK dates into American dates?
> Currently, each time I run my report, within the parameter window, the
> values are being alternated (which is VERY strange).
> I.e.
> 12/01/2006 -> Press "View Report"
> 01/12/2006 -> Press "View Report"
> 12/01/2006 -> Press "View Report"
> etc etc
> Thanks for your help
> Taz
> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
> > Try the following expressions:
> >
> > =Today() -- for StartDate
> >
> > =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
> >
> > HTH
> >
> > --
> > Magendo_man
> >
> > Freelance SQL Reporting Services developer
> > Stirling, Scotland
> >
> >
> > "Tarun Mistry" wrote:
> >
> >> Hi all, im having difficulty using dates within my report. Any advice
> >> welcome.
> >>
> >> Basically I want to restrict my query by date, however, I want my start
> >> and
> >> enddate parameters to have universal generic values.
> >>
> >> I.e.
> >>
> >> startdate = CurrentDate - 31
> >> enddate = CurrentDate
> >>
> >> I cannot figure out how todo this within RS 2005, I have attempted to use
> >> some of the .NET datetime functions but I cannot get them to work (i.e.
> >> "Today() - 31" etc)
> >>
> >> What should I be doing?
> >>
> >> Kind regards
> >> Taz
> >>
> >>
> >>
>
>|||and if my last post doesnt help then try changing up your formula some.
=dateadd("d",-31,today). leave off the () and use "d" instead of
dateinterval.day. I have never had my dates go crazy like that.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:eE1DXHo0GHA.4972@.TK2MSFTNGP03.phx.gbl...
>I have put those formulas into the Default values within the "Report
>Parameters" setup.
> Taz
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
>> did you put that date formula into an expression or did you put it in the
>> data tab.
>> "Tarun Mistry" <nospam@.nospam.com> wrote in message
>> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my
>> start and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work
>> (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>>
>|||Guys,
I really don't understand what was going on. The "flipping" date issue was
only happening within RS and not in the webservice. It seems to be fine now.
Thanks for all the help and the magic formulaes!!
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:OdnS4Ko0GHA.3752@.TK2MSFTNGP02.phx.gbl...
> and if my last post doesnt help then try changing up your formula some.
> =dateadd("d",-31,today). leave off the () and use "d" instead of
> dateinterval.day. I have never had my dates go crazy like that.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:eE1DXHo0GHA.4972@.TK2MSFTNGP03.phx.gbl...
>>I have put those formulas into the Default values within the "Report
>>Parameters" setup.
>> Taz
>> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
>> news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
>> did you put that date formula into an expression or did you put it in
>> the data tab.
>> "Tarun Mistry" <nospam@.nospam.com> wrote in message
>> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my
>> start and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work
>> (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>>
>>
>|||It seems i posted in success a little too early,
My default parameter is correctly appearing in UK format, however when I
pass it to another report, it is being sent in american format, or being
interpretted in american format. The chart that sends the data is set to UK,
as is the main report.
what could be afoot here? furter, my reports may be deployed on a number of
different systems, will this behaviour happen differently on different
servers depending on its language setting'
Thanks
Taz|||Tarun Mistry wrote:
> It seems i posted in success a little too early,
> My default parameter is correctly appearing in UK format, however when I
> pass it to another report, it is being sent in american format, or being
> interpretted in american format. The chart that sends the data is set to UK,
> as is the main report.
> what could be afoot here? furter, my reports may be deployed on a number of
> different systems, will this behaviour happen differently on different
> servers depending on its language setting'
> Thanks
> Taz
If you are running SQL 2005 make sure you have installed servicepack 1.
The thing you are describing looks a bit like this post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1&PageID=1|||> If you are running SQL 2005 make sure you have installed servicepack 1.
> The thing you are describing looks a bit like this post.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1&PageID=1
>
Many thanks Peter, it does indeed look like this is the problem.
I googled the issue but couldn't find anything, i really appreciate your
help.
Downloading SP1 now, fingers crossed.
Kind regards
Taz

Cube Roles - How to restrict access to certain members

I have a dimension called Account that has five levels. I'm trying to define a cube role to deny access to accounts in the lowest level that starts with the letters GL. I tried applying a filter in the dimension data tab in the role designer, however, this only seems to create a static rule. The account structure gets updated all the time and I want to restrict access to all accounts that start with the letters GL now and in the future.

When I created a rule in another dimension using a top-level static member, a denied member set was created in the advanced tab. I think I need to create something similar for the Account dimension, but I just can't get the syntax of MDX. I tried the BOL samples but can't find anything since I want to filter only part of the member name.

Try typing something like the following expression for DeniedSet:

Filter(Account.Levels(4), Account.Name >= 'GL' AND Account.Name < 'GM')

|||

Thanks, but that didn't work. In addition, I tried Filter([Account].Members, Left([Account].CurrentMember.Name, 2) = "GL") but that didn't work either. I'm wondering if there are too many members to filter at the bottom level.

Oddly, I was able to solve part of the problem by Enabling Visual Total in my other dimensions. I can filter GL type accounts in my other dimensions because there is only one fixed GL member in those dimensions. Since GL accounts only match up to the GL members in the other dimensions and since those GL members have been filtered, GL Accounts are not displayed in the cube result.

I will still try to find a solution, although I can restrict GL data from being displayed in the cube, the GL accounts still show up in the drop down. It's a good thing that when the user selects GL accounts, nothing is displayed.