Report's Parameter : From Date to Date

I want to add 2 parameters in my report to delimit a date range. The report is based on Analysis Services 2005 and I have created a Time Dimension. What is the best way to do this ?

I would prefer to use a Datetime parameter type because of the calendar component, but then I don't succeed to associate this parameter with the Time Dimension in the filter expression...

I really need an answer to that question, please help me |||You can't really use a datetime parameter (at least not using the built-in prompting) as AS dates are members of a dimension, not scalars. And you aren't really guaranteed that the range between two members will give you what you want. We could add a feature at some point to convert them but that doesn't exist now.|||

Thanks for the answer.

I just found a solution to my problem, even if the solution is not what I hoped for, it may help someone :

I created two Report Parameters "From Date" and "To Date" which use the calendar component to let the user choose a date. And in my Dataset, in the '...' button, in the 'Parameters' bar, I add a parameter whose value is :

="[Temps].[Temps - Date Key].[" & Year(Parameters!FromTempsTempsCalendrier.Value) & "-" & IIF(Month(Parameters!FromTempsTempsCalendrier.Value)<10,"0","") & Month(Parameters!FromTempsTempsCalendrier.Value) & "-" & IIF(Day(Parameters!FromTempsTempsCalendrier.Value)<10, "0","") & Day(Parameters!FromTempsTempsCalendrier.Value) & " 00:00:00]"

That way, I build the UNIQUENAME properties of my Time Dimension.

But an error occurs when the user select a date which does not exist in the Time Dimension.

|||

Thanks for the possible solution. However, I've been working on following your steps, but I am not getting it to work.

Could you explain in a little more detail how to accomplish this?

report when was last used the stored procedure/view- in a DB

hello,
is there a possibilitie to get the date when a stored procedure / view was
used
The problem is that in a database there are a lot of views - which where
created in a period of last 2 years from diffren user which had access to th
e
database. Now i want to have a kind of report for all views / stored
procedures from a database - to see if this where used in the last 3 month.
If no we can than make a backap and delete this.
best regardsYou could do the stored procedures by either using Profiler and auditing
whats executed against the SQL Server and then pull out the procs used, or
you could write into each stored procedure a write to an audit table
something like...
insert sp_run_log ( procname ) values ( 'myprocname' )
Then you can check through the log after a period of time - you would need
to run every piece of logic in yoru application though.
I can't think of anyway you could do a view, you could perhaps trap the SQL
again using profiler at the statement level and do string searches for them.
Hope that helps.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:07E773A8-9A95-4CB5-B839-72AA796BF2CE@.microsoft.com...
> hello,
> is there a possibilitie to get the date when a stored procedure / view
> was
> used
> The problem is that in a database there are a lot of views - which where
> created in a period of last 2 years from diffren user which had access to
> the
> database. Now i want to have a kind of report for all views / stored
> procedures from a database - to see if this where used in the last 3
> month.
> If no we can than make a backap and delete this.
> best regards|||hello Tony,
thanks for yur help, the idea to write in a audit table is great.
For the view ..... have you any link / information / step by step .. how
to realize the " trap the SQL using profiler at the statement level ..."
thanks
"Tony Rogerson" wrote:

> You could do the stored procedures by either using Profiler and auditing
> whats executed against the SQL Server and then pull out the procs used, or
> you could write into each stored procedure a write to an audit table
> something like...
> insert sp_run_log ( procname ) values ( 'myprocname' )
> Then you can check through the log after a period of time - you would need
> to run every piece of logic in yoru application though.
> I can't think of anyway you could do a view, you could perhaps trap the SQ
L
> again using profiler at the statement level and do string searches for the
m.
> Hope that helps.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:07E773A8-9A95-4CB5-B839-72AA796BF2CE@.microsoft.com...
>
>

Report Viewer Date Format..

Hi,

I have done a report using the reportviewer page, and placed it in my web application and the last thing i need to do is sort out the date formats, primarily to a UK time..

1. I want to display todays date on the page, but in the format "Wednesday, 4 April 2007". I seem to be able to get the various parts needed, but not put them together in the same textbox, could someone help me with the concatonation?

=WeekdayName(Weekday(DateValue(DateString)))

2. All the dates extracted from the database are in the format MM/dd/yyyy, and i need them to be in dd/MM/yyyy, i am completely lost on how to do this...

any help will be much appreciated, even if you could tell me where i can go look up the syntax for this markup

regards

Chris

check this for all posible date formats:http://msdn2.microsoft.com/en-us/library/8kb3ddd4.aspx

|||

cheers for the link, don't know why i was having such a hard time finding it..

anyway, all sorted now, in the end i didn't do any sort of concatenation, but instead just used =FormatDateTime(Now(), 1)

and the other days were as easy as putting dd/MM/yyyy HH:mm in the fomrat field, nice

cheers!

sql

Report Two Data Subsets in the same Grid

This is likely very easy, but I'm not sure which direction is the best
way to go...
Dimension:
Time: Date and Hour the record occurred
Cube:
Has a field that counts the number of times an event occurred in a
given hour
I want to show a grid that has for each day of the month how many times
an event occured between 8:00 and 16:00 and from 16:00 to 20:00 hours.
What is the best way to role up the records in the report?
I've seen two possible ways to do it...
1) Use calculated fields with IIF statements to try to role up the
records.
Problem: IIF statement doesn't seem to work correctly.
2) Use a Matrix control to Filter the records correctly.
Problem: I can't seem to have two Filters on the Hour Group in the same
Matrix. One for 08:00 to 16:00 and one for 16:00 to 20:00.
Clearly I'm missing some key concept, because what I'm asking has to be
commonplace. Can someone point me in the right direction?
Thanks,
Paul WI might have missed something, but this is my suggestion:
Create a dataset that returns the number of events in two section, one for
8-16 and the other for 16-20. You should be able to do this with a
calculated member and filter in your MDX query. (No good examples spring to
mind, but you can always ask in the microsoft.public.sqlserver.olap news
group.)
Then create a matrix, and have two coloums (one for each section of the day)
and group on date.
Kaisa M. Lindahl Lervik
"Paul W" <pbwedz@.yahoo.com> wrote in message
news:1155143250.949345.213080@.n13g2000cwa.googlegroups.com...
> This is likely very easy, but I'm not sure which direction is the best
> way to go...
> Dimension:
> Time: Date and Hour the record occurred
> Cube:
> Has a field that counts the number of times an event occurred in a
> given hour
> I want to show a grid that has for each day of the month how many times
> an event occured between 8:00 and 16:00 and from 16:00 to 20:00 hours.
> What is the best way to role up the records in the report?
> I've seen two possible ways to do it...
> 1) Use calculated fields with IIF statements to try to role up the
> records.
> Problem: IIF statement doesn't seem to work correctly.
> 2) Use a Matrix control to Filter the records correctly.
> Problem: I can't seem to have two Filters on the Hour Group in the same
> Matrix. One for 08:00 to 16:00 and one for 16:00 to 20:00.
> Clearly I'm missing some key concept, because what I'm asking has to be
> commonplace. Can someone point me in the right direction?
> Thanks,
> Paul W
>

Report Service 2005 parameter at Date

I set Report Parameters ,

Properties

Name : StartDate

Data Type : DateTime

Prompt : StartDate

Default values = Format(Today(),"yyyy/MM/dd")

But have error (An error occurred during local report rpocessing. The properly "DefaultValue" of report parameter 'EndDate' doesn't have the expected type.)

The Result show : like "2007/05/01"

Thanks help

Moving to SQL Server Reporting Services forum. This is not an SSIS issue.|||

Hi,

I think the Format function converts your value into String datatype & that is why you're getting the error.

Just use CDate of your expression, that is:

CDate(Format(Today(),"yyyy/MM/dd")

That should get rid of the error.

-Aayush.

|||

=CDate(Format(Today(),"yyyy/MM/dd")

had error

[rsCompilerErrorInExpression] The Value expression for the report parameter ‘endDate’ contains an error: [BC30198] ')' expected.

Thanks help

|||

Oops.. I missed out a bracket Sorry for that.

Try =CDate(Format(Today(),"yyyy/MM/dd"))

-Aayush

|||Thanks You very much|||

Thanks all

The command is right, but I look in client pc, i see the date is "06/11/2007"

The =Cdate(format(today(),"yyyy/MM/dd")) could not work.

|||You're right I overlooked the fact that CDate converts it back to the default date format. So this fix does not work. Actually the expression =Format(Today(),"yyyy/MM/dd") would work but it would require you to change the Data Type of the parameter from 'DateTime' to 'String'. So I don't think it is very feasible.|||

Thanks

But not work

Report Response Times

Have a report written in SQL2005 that has 12 parameters, 3 of which are date
pickers. The client is complaining that once clicking on the link for the
report that it takes nearly 10 seconds to open the parameter page up. Does
anybody else have any probs with this sort of thing or does anybody have an
answer as to why it would take so long for the report to open.If a report has not been run in awhile then IIS unloads reporting services.
If this is what is happening then the second time any report is opened it
will open fast.
You can set the timeout period in IIS (this is an IIS thing, not Reporting
Services per se). Reporting Services is an asp.net application.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:4B42CBAA-6BD5-4EAA-AE13-489E95EB6EAA@.microsoft.com...
> Have a report written in SQL2005 that has 12 parameters, 3 of which are
> date
> pickers. The client is complaining that once clicking on the link for the
> report that it takes nearly 10 seconds to open the parameter page up.
> Does
> anybody else have any probs with this sort of thing or does anybody have
> an
> answer as to why it would take so long for the report to open.