Prompting For Date Range Parameters With Report Builder?


View Replies


Report Builder And Optional Parameters


Is it possible to create optional parameters in Report Builder?

An *ordinary* parameter is created by marking a filter expression as "prompted". At run-time, the user will be asked to provide a value for this parameter (or accept a default value if it is available). This works fine. What I want is to let the user choose whether to provide a value, or leave the parameter empty. In latter case, the reports should not take the parameter into account and display all available data.

Example: I have a list of products grouped by the name of the supplier. I want users to be able to see the entire list, or narrow it down to one supplier only. So I create a free text parameter that will contain a part of the supplier's name. It works ok, but if parameter is left blank, or set to NULL, the report will display no data (all products always have their suppliers).

I tried to create a filter formula to check for empty value and bypass the filter if necessary, but the formula only allows me to use the parameter expression once. So I cannot check for empty value and apply the filter in the same expression.

As a last resort, I used the following workaround: created a new formula named "Enter 'ALL' to see all suppliers" that would return text constant "ALL". I then added another prompted filter expression for this field and grouped it with my supplier prompt using "Any of" group. User is now able to choose a supplier, or enter "ALL" to the second prompt field. This clumsy approach actually works, but then another problem emerges  - although I mark both filter fields as Prompted, the Report Builder will forget this flag for one of the fields when the report is reopened. I think it could be a bug (we use SQL2005 SP1)

Anyway, it'd be nice if I could create an optional parameter as one expression, not two. Is there any way to do that?



View Replies View Related

Report Builder: Date Filter, Relative Date

I have a date filter, and I default it to first day this month and last day this month under relative date, when I run it it givis me error:

The Value expression for the report parameter €˜FromDate€™ contains an error: [BC30456] 'Date' is not a member of 'Integer'. (rsCompilerErrorInExpression)

Can anyone fix this problem?

But it works for Today or (n)months ago.


View Replies View Related

Report Builder Drop-down Parameters With SSAS



is there a way, how to build drop-down parameters in report builder when creating a report based on SSAS report model ?

View Replies View Related

Multivalue Optional Parameters : Report Builder

Hi All,

Is there any way to make Multivalue parameter ( list parameter) in Report builder as optional ?

I was able to achieve optional functionalitiy for parameter is single value but not able to with respect to multivalue parameters

An early response is highly appreciated




View Replies View Related

Cascading Parameters In Report Builder Reports!

Is it possible to create cascading parameters with in Report Builder? I have done this with Report Designer but i need a solution with Report Builder.

(I need the structure: First the user selects the state, afer the postpack the cirties of the state are listed in another dropdownlist for selection).

Thanks in advance

View Replies View Related

Datetime Parameters In Report Builder Interchanges Days And Months :@


This is my scenario: a Cube in SSAS, dimension time with an attribute "cdate" defined as datetime, model deployed using "Generate Model" option of Report Server.

Querying in Report Builder: only _date attribute of time dimension is selected. Filter use _date fields , from option is selected, from the calendar I select from 1 january 2007 to 10 january 2007, results a list of dates between 1 january 2007 to 1 October 2007!!!

When I select from 1 january 2007 to 15 january 2007 the list is correct!!!

Clearly it interchange days and months in first case, and do it right in second case.

What is it about? it's about Report Server settings? SQL Server settings? .Net Settings? Windows Settings?

What do I have to do ?


Julio Díaz C.

View Replies View Related

Writing A Date In Report Builder

 what kind of expressions do you need to put around a date when defining a formula?

View Replies View Related

How To Use A Stored Procedure With Parameters To Create The Datasource View For Report Model Builder

I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?

View Replies View Related

Date Range For Report Built Against A Cube Using MDX Query

I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.
My Issue:
I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.
Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.
My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.
Many Thanks

View Replies View Related

NUL L Date Values In Report Builder Display As...

Null date values within a table in SQL Server are displays as (12/30/1899) by Report Builder. Does anyone know how to display the null value as blank?

Thanks in advance

View Replies View Related

Report Builder Timeline Date Filter

in the conditions of the report I have to insert a date in the American format of 04/10/2008 (month, day, year). It returns yesterdays cases. However if I select Relative Dates > Last (n) > Days (including today) = 3 it returns nothing. I'm thinking this may be because it's using the system date minus three, except the system date is in UK format 11/04/2008. So it's trying to take 3 days away from what it thinks is the 4th November 2008.

Does any have any ideas how I can get around this and generate a report for the last 3 days. (changing the system clock to US format isn't really an option :-)

Any help would be gratefully received.

View Replies View Related

Problem With Date Filters In SQL 2005 Report Builder



I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.


1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database. 


2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?


3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?


Any help is greately appreciated. I know we'll be getting customer calls about these issues.





View Replies View Related

How To Use The Custom Code (getting Start And End Dates Of Every Month In Date Range) In The Report ?

Hi all,

I am trying to use the custom code in the report but I don't think I am understanding how this is being used.

I have a function to get starting months for a report parameter.
The function is below:-

Shared Function GetStartingMonths() as String
dim strDefault as string
dim CurrentMonth as String
Dim SqlString as String

'strDefault = Month(Now) & "/1/" & Year(Now)
CurrentMonth = "5/1/2002"
Do While CDate(CurrentMonth) <= Now
SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear"
CurrentMonth = dateadd("m",1,CDate(CurrentMonth))

if Cdate(CurrentMonth) = Now then
 Exit Do
 sqlString = SqlString & " Union " 
end if


return SqlString

End Function
what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.

In the dataset, I had put the following expression

However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.

Am I doing something wrong here or is there a better way to doing this ?

Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?

thanks !

Bernard Ong

View Replies View Related

Relative Dates In Report Builder (non-us) - How To Change Autogenerated Date

Are there any way to change how Report Builder builds relative dates ?

I've got a Report Model (autogenerated from an OLAP cube) and using relative dates in a filter generates the following MDX
{  } on 0,
{ EXISTS( { [Ulykke].[Ulykkenummer].Levels(1).Members * [Ulykke].[Ulykkedato].Levels(1).Members }, , "Ulykkeshendelser" ) }
{ ( Extract( filter( { [Kalender].[Dato].Levels(1).Members * { [Kalender].[Dag].[All] } },
( ( [Kalender].[Dato].CurrentMember.membervalue >= ( VBA!DateAdd( "d", ( 1 - 14 ), CDate("03/06/2007 00:00:00") ) ) )
AND ( [Kalender].[Dato].CurrentMember.membervalue < ( VBA!DateAdd( "d", 1, CDate("03/06/2007 00:00:00") ) ) ) ) ), [Kalender].[Dato] ) , * ) } on 0
This should have been
{  } on 0,
{ EXISTS( { [Ulykke].[Ulykkenummer].Levels(1).Members * [Ulykke].[Ulykkedato].Levels(1).Members }, , "Ulykkeshendelser" ) }
{ ( Extract( filter( { [Kalender].[Dato].Levels(1).Members * { [Kalender].[Dag].[All] } },
( ( [Kalender].[Dato].CurrentMember.membervalue >= ( VBA!DateAdd( "d", ( 1 - 14 ), CDate("06/03/2007 00:00:00") ) ) )
AND ( [Kalender].[Dato].CurrentMember.membervalue < ( VBA!DateAdd( "d", 1, CDate("06/03/2007 00:00:00") ) ) ) ) ), [Kalender].[Dato] ) , * ) } on 0

It seems like the current date is 'hardcoded' to us-english format in the CDate function. Changing this to Norwegian format generates a MDX statement that works.

The client is running Norwegian settings, the server is English SQL Server on a Norwegian Regional Settings configuration.

Any pointers to how to get relative dates to work would be great !

What are the mechanisms that control these date settings ? AS Language ? Server Regional Settings ? Client Regional Settings ? anything else ?




View Replies View Related

Remove 'All' From 'From Date' And 'To Date' Report Parameters


I'm using the following code (created by Report Designer) to populate the From and To data controls in my report;

WITH MEMBER [Measures].[ParameterCaption] AS '[Check Date].[Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Check Date].[Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Check Date].[Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Check Date].[Date].ALLMEMBERS ON ROWS FROM [MyCube]

The report parameters for From and To contain a first item of 'All' which I don't want. How can I remove this please?


View Replies View Related

Report Builder: How To Provide Parameter In The Report Generated Report Builder

Hi All,

I have used Report Builder in one of the project for client. Is it possible to provide parameter in the report that is generated by Report Builder? It's quite the same with the parameter in usual Report/RDL (by Visual Studio).


And is it possible to pass the UserID that is running the report to the Data Source View or directly to the query? Currently, I need to get the UserID of the user that runs the report to do some filtering of the data (in SQL Query). I can do this easily in the RDL file developed by Visual Studio, which I can use global parameter User!UserID and pass to query. I find difficulty to use SQL Execution Context (select USER_NAME(),SUSER_NAME()), because report needs to be run as certain account, so that the report can be subscribed by user (e.g. email subscription). How to pass this UserID to the query/data without end-user intervention in Report Builder?



View Replies View Related

Report Builder: How Can I Append Two Table Fields To Report In Report Builder


When i select datasource in Report Builder, i am able to see all the available DataSources.

Eg: I have selected one datasource from the list and which has 3 tables(table1, table2, table3) associated to that datasource.

when i drag and drop table1 fields to report, i am not able to see the other 2 tables(table2 & table3)

Is there any property or relationship do i need to maintain?


View Replies View Related

Query Info Between Time Range & Date Range

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View Replies View Related

Preview Report Tab Is Prompting For An Input Parameter That Does Not Exist For The Dataset


I created a dataset based on a stored procedure. There are nine input parameters. When I click on the preview tab, it is asking me for ten input parameters.
I double checked my dataset on the data tab, and it only shows the expected nine parameters when I look at the parameters tab. I can also run the query without issue from the data tab.
I have no idea why it is asking for an extra parameter on the preview tab. It is a column name that does exist in my database, but is in no way used in the stored procedure in question.
Has anyone else run into a similar situation?

View Replies View Related

Incorrect Format Of Date Report Parameters Via Datepicker

OK - truck loads of folk have reported this problem - but I cannot solve it - so apologies for repeating the question...


My SQL Server 2005 Report is developed on the same server as it is run. 

I have SQL Server 2005 SP2 installed.  The report language is set as en-GB.

The browser language setting is en-NZ.


The report previews fine - I pick May 22 from the picker and it appears as 22/05/2007 in the report parameter text box - which since I am in NZ is exactly as I want it.


I deploy the report - If I run it from the report manager virtual directory the date picker performs as above i.e correctly.


I run the deployed report from the report server virtual directory and here is where the problem occurs - selecting May 22 from the date picker produces 05/22/2007 in the report parameter text box and produces the error


"The value provided for the report parameter 'StartScheduledDate' is not valid for its type. (rsReportParameterTypeMismatch)"


thanks for any help here - this problem has made this a ridiculously expensive report!

View Replies View Related

2nd Question - Date Parameters - To Run A Monthly Report Automatically


hi there
I am using SQL Server 2005 with Reporting Services (Using the Visual side - not direct code)
I am having problems understanding the dates. eg where to put them,
I want a report that runs on the 1st day of the month for the previous month.  I know you can set up something in subscriptions but then how do I get my report header to say from
I have been through the AW reports but can't see what I need.
Happy if someone wants to direct me to somewhere that has date examples.

View Replies View Related

Drill Through Report -- Passing Date Parameters -- Resulting In RsReportParameterTypeMismatch



I have two reports. One is the main/summary report and other one is drill through. When I pass the Start and End Date parameters from main to the drill, the original format of DateTime changes. For example, in main report the data is displayed for following date range:


4/7/2007 - 5/9/2007 (i.e 4 July 2007 to 5 Sept 2007)

which displays correct data.


However, when I click on the drill through link, it jumps to the drill through report but displays data for the following period:


7/4/2007 - 9/5/2007 (i.e. 7 Apr 2007 to 9 May 2007)


The reporting services is converting the value from one format to another of the report parameters when passing them from parent report to the drill through. When run individually, these two reports display data for correct date range. And you can imagine, the child report crashes with rsReportParameterTypeMismatch error if the start or end date had a day part greater than 12 (e.g 25/4/2007).


I can't understand what could be going wrong. All the parameters in both reports are datetime, so intrisically, it shouldn't matter even if the reporting services is converting or using different date formats as long as the data type remains the same. Is there a way to fix this and force the parameters to stay in the format they are provided in the main report?


Your help is much appreciated.



View Replies View Related

Report Builder: How To Access &&"Last Inactive Date&&"

A Contractor has a list of Contracting Companies that he has worked for in his history.

For each row in the "Contracting Company" table, there is an Active Date and an Inactive Date.  Each Contracting Company cannot have overlapping dates.

The last row in the table for that Contractor may have a null Inactive Date.  That just means that he doesn't know when his contract will end.

In Report Builder, I can access the "Last Active Date", because there is a pre-built formula for "Last Active Date".  However, whenever I access the prebuilt formula "Last Inactive Date" - I get the inactive date for the second-last row, because the last row has a null value for the Inactive Date!  I want to get the Inactive Date for the last row - not the second last row!

How to do this easily?  Thanks!

View Replies View Related

Report Wizard Doesn't Take A Simple Query In The Query String - Query Works In Query Builder When I Supply The Parameters

The following query in the query string:


execute p_rpt_cli_v_index_reg_adj_exp_by_bkt2 @as_of_date='06/06/2007', @client_type=3, @index_as_of_date='05/31/2007'


produces following error:


There is an error in the query, invalid object name '#CLI_1', Invalid object name '#index'.


When I open up the query Builder, and provide the same query and run, it asks for those 3 parameters values and after I provide those parameters, the query runs, but, clicking on Ok, produces same error as above in the 'Microsoft Report Designer' information window.


The above query works in the Query Analyzer fine. What's so different in the Reporting Services env?

View Replies View Related

Query Help - Giving A Date Range Given The Start Date, Thanks!

Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)

View Replies View Related

{RESOLVED} Date Logic - Calculating A Date Range

I have a report that I need to run on 2 different date ranges.

Both report's data is 2 days behind today's date.
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)

The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)

So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007

My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007

Any help would be greatly appriciated!

View Replies View Related

Date Picker Controls - Anyway To Limit Date Range

Have seen other questions here about modifying date pickers supplied by reports created in BIDS.  The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year.
Any ideas?

View Replies View Related

Finding Where My Date Falls In Date Range


We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.

I have a CommisionAudit table that tracks changes in commission rate with the following values.

ID | Commission Change | UpdatedOn
1 | Change from 20->25 | 03/07/2007 09:00:00
2 | Change from 25->35 | 10/09/2007 17:00:00
3 | Change from 35->20 | 01/10/2007 16:00:00
4 | Change from 20->26 | 11/10/2007 10:00:00

with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.

View Replies View Related

Date Format In A Date Range Parameter

I´ve made a report with a date range parameter as described at

The language setting is Dutch. The date in de parameter is dd-M-yyyy. Is it posible to change this to dd-MM-yyyy.

View Replies View Related

Multivalued Range Parameters


Hi all,

We are using Reporting Services 2005 with Analysis Services 2005 data source.

So far we only had multivalued parameters without any range.

now we are to add a report parameter income group which would have values

Below $75,000 , $75,000 - $120,000, $120,000 - $220000 .....,Above 220000.

Now the user can select multiple range values.. Eg( Below $75,000 , $85,000 -   $95,000 , Above $100,000 )

We have to filter the cube with this user selected multiple range values.

Please tell me how to do this in MDX..


Looking Forward your replies




View Replies View Related

Copyrights 2005-15, All rights reserved