.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

several date ranges in one report - first/last function?

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server


    I´m working on a report with SSRS 2008 which is meant to show a lot of figures aggregated for the whole calendar year. In all datasets I have the date as a parameter (date with hierarchy: year - month). So to get the sums for the whole calendar year I only have to select all the months of the wanted year and SSRS shows the total for that period. That works fine. The problem:

Some figures (for which separate datasets are used) are not meant to be aggregated, eg the number of open cases at the beginning of the period or the number of open cases at the end of the period.

I tried using the First/Last-functions on that figures but that doesn´t work because the first/last function doesn´t seem to refer to the date-parameter but to other dimensions (in my case: region because it shows the summed up values for the first region instead of the not-aggregated values for the first month).

How can I acchieve that my report shows the first/last value dependent on the choosen date-parameters while summing up the other values for the whole choosen date-parameters?


Your help would be much appreciated.



View Complete Post

More Related Resource Links

How to get "Current Date" on SharePoint Designer for Invoice Ageing Data View Report??

Hi Experts, I would like to know how can i get/show "Current Date" in SharePoint Designer ?? Any good tutorial ? FYI, I need this "Current Date" for my Ageing Data View Report, Purpose To offset with "Invoice Receive Date" to get total days of payment payable for the invoice. Please assists, Thanks

Report Parameter Values - Can I use getdate() or now() or any other function?

Hi all,      I have a report that utilizes a calendar function but I need to make it a subscription report. Is it possible to use getdate() or now() on the Report Parameter Values? I have a text box that says Enter a Date and that is where I want to have the default value to be run with the subscription. Please let  me know if possible. Thank you.

How to Date function convert (09/13/2010 10:18:24 to 13.Sep.10)

 How to Date function convert (09/13/2010 10:18:24  to     13.Sep.10)passing a simple query no template used input  09/13/2010 10:18:24output 13.Sep.10

Using "As Of" date in a report model

Hi Everyone. I have an organisation structure table that contains start and end dates so that I can use it to sequence financial reports with the structure "as of" a set date. Along with this, I also have some slow moving dimension tables related to chart of account structure that also have start and end dates. Now, I can build a model with this OK and when I create a report, I can add the selections ( "as of date between start orgstructure date and end orgstructure date and as of date between account structure start and end structure start) without any problem but its much to complicated for my ad-hoc report writers to use. Is there any way of setting up a parameter field in a model so that I can build the selections into the model itself so my users only need to feed it the date?  

Unble to pass date parameter to "Go to Report " ?


Hi ,

I have Report A need to go from Report A to Report B passing multivalued parameter and date parameter as well , I do not have any problem passing  Multivalued parameter but cant pass date parameter from Report A and Report B .

Report A : Date parameter properties

name : date

Data Type : Date/Time

Default value =Today()

Report B : Date parameter properties

name : date

Data Type : Date/Time

Default value =Today()

Go to Report action ,Expression used to pass date parameter  from Report A to Report B


Unable to pass appropriate date parameter if changed from default value from ReportA to ReportB .

Assistance would highly be appreciated !!

Thanks .

Rajkumar Yelugu

Date picker format -> Preview vs Report


I'm at my wits end!

When previewing a report on one of our machines, the date format for a parameter insists that en-GB dates are invalid (dd-mm-yyyy-). Instead, it requires en-US (mm-dd-yyyy). When deploying the report, it goes back to the correct format of en-GB.

Bear in mind, this works on all of the rest of our development machines exactly as required. Only this one machine doesn't work.

I've tried: -

  • Setting the report properties to United Kingdom - it already was
  • Changing ReportViewer.aspx to include --> <%@ Page Language="C#" AutoEventWireup="true" Inherits="Microsoft.ReportingServices.WebServer.ReportViewerPage" Culture="en-GB" %>
  • Ensuring that regional settings (control panel->regional and language options) is setup to English -United Kingdom. It all already was (regional, location, language and advanced tabs all setup to UK)
  • Ensuring the browser on the machine is setup to English -United Kingdom, it already was
  • Adding <globalization culture="en-GB" uiCulture="en-GB" /> to web.config
  • Updating BIOS (not my idea, but was getting to the point where I'd try anything! Basically, the BIOS has the date in the US format, so despite my reasoning that it's irrelevant due to regional settings, we upda

Status of Accessing Nested Objects in Data Source of Local Report Does Not Function


We recently upgraded out platform to .NET 4.  We are also using Microsoft Reporting dlls.  Unfortunately, we found that the upgrade broke all of our reports.  Researching the cause, we found this article:


It seems that this issue has been open for some time now and I have not seen a good workaround (except for the shim class which is not really feasible for us).  Can I please get a status update for this?  Is there some other type of resolution?

Thank you,

Benjamin Strum 

Ben Strum

Date Conversion in Function


I have a date conversion in a function that is failing to convert.

this fails when trying to convert

This uses a function to convert the date to a string



Error -

Conversion failed when converting datetime from character string.

below is the code for the functin




-- Replace Year


	SET @pos = CHARINDEX('yyyy', @format)

	WHILE @pos > 0


		SET @format = STUFF(@format, @pos, 4, DATENAME(yyyy, @date))

		--PRINT @format

		SET @pos = CHARINDEX('yyyy', @format)



	SET @pos = CHARINDEX('yy', @format)

	WHILE @pos > 0


		SET @format = STUFF(@format, @pos, 2, RIGHT(DATENAME(yyyy, @date) ,2))

		--PRINT @format

		SET @pos = CHARINDEX('yy', @format)



-- Replace Month


	SET @pos = CHARINDEX('mmmm', @format)

	WHILE @pos > 0


		SET @format = STUFF(@format, @pos, 4, DATENAME(month, @date))

		--PRINT @format

		SET @pos = CHARINDEX('mmmm', @format)



	SET @pos = CHARINDEX('mmm', @format)

	WHILE @pos > 0


		SET @format = STUFF(@format, @pos

Separate Date and Time report parameters in SSRS 2005



Is it possible to declare separate report parameters in SSRS 2005 as Date and Time?

I want the user to be able to enter a date and a time.

When I declare the parameters in my stored procedure as datetime, in SSRS the parameter just shows as a Date only.


Dynamics AX Report call a function at the begin of report execution


I need a way to call a function at begin of execution of the report, and to call another function when execution ends.


I need to connect to SQL to call a stored procedure.

Report Builder returning #Error value in empty fields and working with Date field


Hey guys,

I am trying to build a report using the date field from CRM and only wanting to return the date I am using this expression "=Fields!new_date.Value.ToShortDateString()" but when there is an empty fiel I get #Error. How do I solve this?

Also is there anyway to return the date field with the actual month showing instead of the numerical value? i.e. instead of 1/31/2010 Have it show January 31, 2010 (Doesn't have to be exactly in that format, but want the name value for the month.)

Whether Date & Math function supported in WCF Data service



Whether Data(day,year,hour,minute,month,second) and Math functions(double round, decimal round, double floor, decimal floor, double ceiling, decimal ceiling) are supported in WCF data service using ODATA.

Thank You Benita Blas

ssrs report with todays date and end of month


Hello All,

Iam bit sruggling to get an idea how to create report which needs to populate data based on every day to end of month,

what i mean is , dataset

1  day of month data to end day of month data

2nd  dayof month to end day of month data




and so on

I.e i need generate a report based on two paramerts which should take current date for evry day and end  day of month 


Any ideas will be of great help 





Report Builder Filter shows Field as Date Type =Text instead of Date


My Data was created by retrieving ODBC data - using PowerPivot for Excel 2010, and has Birthdate defined as a Data Type = "Date".  This was uploaded to SharePoint, and a New Dataset was created to point at it.

I'm using Microsoft SQL Server 2008 R2 Report Builder 3.0.  I tried to filter this data on Birthdate, but when I go into the Filter, and specify the field that was defined as DataType = "Date" in PowerPivot - Report Builder changes the type to "Text", and gray's out the field so that I can't change it to "Date".

If I create an Expression as Cdate(Birthdate.Value), it lets me change the Data Type to "Date", and works appropriately, but we don't want to have to do this with every date field.

Why doesn't Report Builder see this field as a Date ? 

ssrs dropdown parameter with Week startDate and end date so that user selects only week and report s


Hi all,

I have a report where I have to show the Week start and end date in the dropdown instead of the date range. Please advice me how to proceed with this report parameter. Thanks a lot in advance, all help is appreciated.


Matrix Report With Month/Year Columns - Display date even when there are no records for that month


I've created a matrix report that displays the quantity of different products  set to expire by month/year. The stored procedure returns records for a variety of products and each record contains an expiration date. If there are no products/records that contain an expiration date of lets say 6/2010 then 6/2010 will not appear as a column in the report.

I need a way to force these month/year columns to appear in the report.


Does anyone have a suggestion on how I can make that happen?


Rick Dowdall


ASP Date Function



I'm having troubling converting my asp date code to vb can someone help me.

<% date_value = date() %>
<%= date_value %><br>
<% dayofweek=weekday(date_value) %>
day number in week <%= dayofweek %>
<% select case dayofweek %>
<% case "1" %>
<% case "2" %>
<% case "3" %>
<% case "4" %>
<% case "5" %>
<% case "6" %>
<% case "7" %>
<% end select %>



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend