Prompting For Date Range Parameters With Report Builder?

Nov 22, 2005


View 6 Replies


Report Builder And Optional Parameters

Nov 30, 2006


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 34 Replies View Related

Reporting Services :: Date Range Parameters Are Off By One Day?

Apr 21, 2015

I have a Start Date and End Date parameter in my SSRS report.  The results are off by 1 day.  For example if I enter 4/2/2015 and 4/20/2015 it will return a few results from 4/1/2015 to 4/19/2015.

View 4 Replies View Related

Report Builder Drop-down Parameters With SSAS

Apr 25, 2007


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

View 1 Replies View Related

Multivalue Optional Parameters : Report Builder

Mar 14, 2007

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 4 Replies View Related

Cascading Parameters In Report Builder Reports!

Mar 15, 2007

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 1 Replies View Related

Reporting Services :: Date Range Filter Based On Date Values Returned In Report?

Aug 27, 2015

I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:

What I want to accomplish:

I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.

For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.

View 3 Replies View Related

Report Builder: Date Filter, Relative Date

Dec 14, 2006

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 6 Replies View Related

Datetime Parameters In Report Builder Interchanges Days And Months :@

Mar 14, 2007


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 2 Replies View Related

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

Mar 12, 2007

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 2 Replies View Related

Writing A Date In Report Builder

Dec 20, 2007

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

View 8 Replies View Related

Reporting Services :: Adding Values To Parameters In Dropdown Prompts In Reports In BIDS Or Report Builder

Nov 20, 2015

I run these stored procedure to build the report and I am able successfully to build the report but I need some prompts to in the report to get the specific data .

PROCEDURE [dbo].[Sharepoint_Ticket_Report]

[Code] ....

And for prompts how to create created_by and message_type dropdowns as shown in picture.

View 7 Replies View Related

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

Apr 13, 2007

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 1 Replies View Related

Report Builder: How To Access Last Inactive Date

Apr 7, 2008

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 3 Replies View Related

SQL Server 2008 :: Query To Select Date Range From Two Tables With Same Date Range

Apr 6, 2015

I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

The table B stores the item process time. The structure is as below

Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000

I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.

Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.

The result expected is

Table A

Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000

Scenario 2 expected result

Table A

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000

View 8 Replies View Related

Problem With Date Filters In SQL 2005 Report Builder

Jul 6, 2007


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 3 Replies View Related

MDX Date Range Report - Didn't Get Correct Result

Aug 5, 2014

I am doing search through Date Range but didn't get correct result.

For Example if i created a Task on 5/8/2014 and when i have take Date Range from 1/8/2014 to 6/8/2014 then didn't get 5/8/2014 data in result but when i taken range from 1/8/2014 to 5/8/2014 then got the 5th Date data.

I am taking Range Inclusive Operator(:).


View 1 Replies View Related

Date Range For Report Built Against A Cube Using MDX Query

Aug 2, 2007

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 2 Replies View Related

Reporting Services :: Date Format In Report Builder When Using UTC Server

Oct 14, 2015

I am building reports based on Report Builder and SQL server 2014.

Our report data are hosted on azure sql database.

Date time on azure are stored as UTC.

When I run my report locally, how can I format correctly the date/time format on my report in order that if I am in French, I should have the date/time set to UTC + French offset. How can I do this ?

View 3 Replies View Related

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

Feb 6, 2008


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 5 Replies View Related

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

Mar 29, 2007

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 15 Replies View Related

Reporting Services :: In SSRS Report Need To Limit Date Range To One Month

Jul 22, 2015

In SSRS report I have 2 parameters start date and end date, so when somebody is selecting end date more than a month(if today date is 22 then they are selecting aug 23) then it should give error message to user like "Date out of range".

View 2 Replies View Related

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

Mar 31, 2008


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 3 Replies View Related

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

Apr 11, 2008

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 1 Replies View Related

Reporting Services :: SSRS Report Prompting For User ID And Password Of Admin

Nov 5, 2015

After publishing report on server and accessing it from URL its prompting for the USER ID and pAssword of Admin user of the server. Have tried to find the Virtual Directory of the Reporting server on IIS - which is not available. or unable to find the same. Apart from this have saved the Database credential in the report itself. 

View 5 Replies View Related

Reporting Services :: Scheduling MS Report With Date Parameters

Jun 2, 2015

I need to schedule a report with date ranges which change from month to month.  Is there a scheduler date function I can place in the parameter field(s) for that report?

View 4 Replies View Related

Incorrect Format Of Date Report Parameters Via Datepicker

May 22, 2007

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 4 Replies View Related

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

Nov 26, 2007

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 5 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

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 6 Replies View Related

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

Jul 4, 2007


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 5 Replies View Related

Reporting Services :: Start And End Date Parameters Report In SSRS 2008 Is Not Running On Runtime?

Aug 27, 2015

I made one report in SSRS 2008 in which getting data from one SharePoint List.

three parameters in report :


I am using query which I created with CAML.

query is running well and data is coming correctly if I run this on Query designer.and date format must be YYYY-MM-DD.but when I try to run through on run time then the date control is showing format dd/mm/yyyy.

I change the regional settings of windows and SharePoint site to English(United States). and when I select date control it is also putting date in format like "YYYY-MM-DD'. and in that format report is working well in Query designer view.But on run time still it is not working.

View 3 Replies View Related

Reporting Services :: Add Sub-report To Main Report Using Report Builder?

Sep 9, 2015

I just created a report builder. I have a main report and i wanted to create a sub report. why i cant or i cant view the path or the folder of my  .rdl file to be use as my sub report.

View 5 Replies View Related

Modifying A Report Created In Report Builder In The Report Designer.

Jun 30, 2006

After I use the report builder to create a generic report, how do I actually get that report into the report designer so that I can modify it more effectivly?

The issue that I have now is that the file on the report server is not a .rdl file and if I simply save it as one and then bring it into VS to modify it the code file is a html structure rater than a XML file type.

Any suggestions would be appreciated. Thanks

View 3 Replies View Related

Copyrights 2005-15, All rights reserved