Date Parameters
Mar 20, 2008
See Code below -->> I need help with date parameters. I was told to pass in parameters for the meters date >>see underlined<<. I originally had specific years there but they do not want the years to be hard coded into the script. So I tried to replace with a parameter but need help. I have placed the years next to the parameters that were originally in the 'where' clause where the underline is.
SET NOCOUNT ON
DECLARE
@mtr_ageAdatetime, --<=1/1/94
@mtr_ageBdatetime, --<=1/1/07
@mtr_ageCdatetime; --<=1/1/07
set @mtr_ageA = (getdate()); (this should be a year not current date)
set @mtr_ageB =(getdate());
set @mtr_ageC =(getdate());
create table #aged_a
(
Entnovarchar (4),
Customer_Name varchar(100),
Loc_Nbrvarchar (10),
--Contract_Nbrvarchar (10),
Ref_Novarchar(20),
Meter_Novarchar(20),
Service_Areavarchar(10),
Pol_Subvarchar(10),
Kind_Codevarchar(10),
Mtr_Type varchar(10), --group a,b,c
Set_Datedatetime,
Mtr_Statusvarchar (1),
Meter_Locvarchar(20),
Addressvarchar(100),
Cityvarchar(30),
Statevarchar(2),
Zipvarchar(10)
)
Insert Into #aged_a
(Entno, Loc_Nbr, Meter_No, Service_Area, Pol_Sub, Kind_Code, Mtr_Type, Set_Date, Mtr_Status,
Meter_Loc, Address, City, State, Zip)
select
locn_entno,locn_locno, mplm_metno, locn_serv_area, locn_polsub_id, winv_subclass, winv_inv_type, mplm_set_date, mplm_meter_status,
ISNULL(mprl_rloc_desc,''), ISNULL(locn_addr, '') + ' ' + ISNULL(locn_addr2, ''), locn_city, locn_state,
locn_postal
FROM rs3_prod.dbo.MP_Location_Meter (NOLOCK)
INNER JOIN rs3_prod.dbo.WO_Inventory (NOLOCK)
ON mplm_entno = winv_entno
and mplm_metno = winv_invno
INNER JOIN rs3_prod.dbo.RS_Location (NOLOCK)
ON winv_entno = locn_entno
and winv_locno = locn_locno
Left outer join rs3_prod.dbo.MP_Read_Location (NOLOCK)
on mprl_rloc_code = mplm_rloc_code
WHERE (winv_inv_type = 'GROUP A' and mplm_set_date <=@mtr_ageA )
or (winv_inv_type = 'Group B'and mplm_set_date <=@mtr_ageB )
or (winv_inv_type ='Group C' and mplm_set_date <=@mtr_ageC )
View 1 Replies
ADVERTISEMENT
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Oct 24, 2007
Any way to disable the calendar datepicker show it does not even show up on the report?
View 2 Replies
View Related
Mar 31, 2008
Hi,
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?
Thanks.
View 3 Replies
View Related
Nov 5, 2007
I'm using MDX to query a cube. I have two identical data sets:
WITH MEMBER [Measures].[ParameterCaption] AS '[Date Shipped].[Year-Month].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date Shipped].[Year-Month].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date Shipped].[Year-Month].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date Shipped].[Year-Month].ALLMEMBERS ON ROWS FROM [Heidtman DW]
How do I get one to be the "From" date and the other to be the "Thru" date?
I'm passing the correct parameters ([Date Shipped].[Month].&[2006]&[4]&[12]) & ([Date Shipped].[Month].&[2007]&[4]&[11]) but I don't know how to tell the query to use them correctly.
View 5 Replies
View Related
Nov 26, 2007
hi all
I am having problems with parameters
WHERE (CONVERT(datetime, Ticket.CreatedDate) >= @StartDate) AND (CONVERT(datetime, Ticket.CreatedDate) <= @EndDate)
My Report Parameters are date/time
I am in Data view of Reporting Services.
If I run this in Preview it works ok but sometimes I need to run in data view - what is incorrect with this?
thanks
Dianne
View 10 Replies
View Related
Jun 12, 2007
Hi,
I follow MAK's instruction here: http://www.databasejournal.com/features/mssql/article.php/10894_3515886_1
The problem is that it works only on the local machine server 2k3 with sql 2k5 installed. I have 2 other boxes( 2k3 and 2k server) that need to extract the security log.
I manually ran the logparser.exe with the same sql parameter but it didn't work either. If I leave out the WHERE clause, it works fine. But then, it pulls entire log file.
It does not spit out any hint nor error.
Please help.
TIA,
Cal
View 2 Replies
View Related
Oct 14, 2013
i have 3 table
DECLARE @a1 table(
p1 int ,
date1 smalldatetime
)
Insert Into @a1
(p1,date1)
Values
[code]....
how i get resulting table
num_p1 num_p2 num_p3
1 3 5
View 13 Replies
View Related
Jan 4, 2007
hi all
We are a multi site / multi language company so do not want to hardcode the date format - these reports will be on intranet
Using MS Sql - Reporting Services 2005
RecvdDate field = yyyy-mm-dd - is how it is displayed.
I added a @startdate and an @enddate parameter and am using the
calendar - so a user picks off the calendar
I read up and saw this information:
Report properties set
'Language' setting to '=User!Language' to detect user
culture automatically
My regional settings are set to English (New Zealand)
but whatever I do I receive no data - (data is definately there eg when run without parameters) so it is obviously the parameter that is incorrect.
Can someone give me some advice on what else to check.
thanks
View 2 Replies
View Related
Mar 21, 2007
Hello all,
Hope that someone can help with this. I am trying to write an expression that will return a date using the two of the parameters on the report. I tried using CAST but VS will not allow that.
The parameters are..
Parameters!Month.Value
Parameters!Year.Value
There are going to be twelve columns on the report, each one representing a month out of the time frame selected. If the user were to select January 2007 the columns would then display --Jan 07, Dec 06, Nov 06 and so on.
Thanks so much for any help in this.
View 1 Replies
View Related
May 8, 2008
Hi,
I have start and end date parameters on my report. The requirement is to dynamically update the start and end date parameters of a report based on another parameter 'DateRangeSelect' selected as week, month and Year. Based on the value as week, month, the start and end dates will be for a week / for a month selected dynamically and stored in a dataset,
When Default values are set for the start and end dates as fields from the above dataset, the dates are populated first time correctly. But when I modify the criteria from week to month, the start and end dates are not refreshed. Instead the start and end dates remain the same as of the first selection, irrespective of my subsequent change in selections from week to Year. On the other hand, if I assign the dataset to the Available values, the dates are assigned properly after refreshing but now the problem is, the datepicker is not available for picking and changing the date.
So, just wondering how I can get both, ie., the date ranges refresh based on my selection of week or month and datepicker also intact for changing the dates. Thanks.
View 1 Replies
View Related
Nov 14, 2006
I have a problem in that I have a report that hooks into oracle that returns data between 2 specified dates. I have created the two parameters startdate and enddate.
The query runs nicely and returns the desired results in the Data view screen, however when I run the report in the Preview screen no results are returned and no error messages pop up. The query is below:
SELECT APK052_WARDSTAY.WARD, COUNT(APK052_WARDSTAY.X_CN) AS NUM_PATIENTS
FROM APK052_WARDSTAY, APK051_CONEPIS, APK050_HPROVSPELL
WHERE APK052_WARDSTAY.CEP_NO = APK051_CONEPIS.CEP_NO AND APK051_CONEPIS.HSP_NO = APK050_HPROVSPELL.HSP_NO AND
(APK052_WARDSTAY.WS_EDATE BETWEEN TO_DATE(:StartDate, 'DD/MM/YYYY') AND TO_DATE(:EndDate, 'DD/MM/YYYY')) AND
(APK050_HPROVSPELL.MI <> '2')
GROUP BY APK052_WARDSTAY.WARD
As a bit of a test in the preview screen I spit out the value of the parameter fields and I notice that the date value are switched to the us format. Not sure if this has anything to with the problem or not.
Can someone please help / advise.
Thanks
Matt
View 1 Replies
View Related
Aug 28, 2007
I have a standard on-demand report that now also needs to be e-mailed to a group of clerks. No problem there as I have several subscriptions in place already, however, they want this subscription to report from the system date to three days in the past. I could clone the report and change the date range to @Startdate= GETDATE()-3 and @Enddate = GETDATE() but then I would have to manage multiple reports that return the same thing. Is there a method in the date parameter field in subscriptions that can achieve the same thing?
View 3 Replies
View Related
Feb 20, 2005
HI
I want to retrieve data in between two date formats using a query in SQL?
can i do it w/o using GUI tools?
For Exp i have sales data from date 11/11/2000 to 11/2004.
now as a user i want to give Input paramete value ranging between 06/06/2002 and 07/07/2002?
Is there any SQL query which i can use to retrieve the above date values?
Thanx in Advance
VS
View 9 Replies
View Related
Feb 15, 2012
I need to set date parameters within Stored Procedures using a sql 2008 R2, with an access 2007 front end. The procedure needs to allow me to set parameters for a start date and an end date.
View 1 Replies
View Related
Mar 26, 2008
Hi,
Not sure if I will be able to get the answer I need on this forum but hopefully I will!
We have a previous report that uses the code:
AND T1."Decision_Date" BETWEEN '2005-07-01 00:00:00.000' AND '2006-06-30 00:00:00.000' AND T1."Decision_Type_Description"
With this the report was run with the "static" dates, the new report (in MS reporting services) requires that the user be able to enter in the start date range and the end date range.
I have come up with something like:
(T1."Decision_Date" = @Start_Year) AND (T1."Decision_Date" = @End_Year)
But not working. Can anyone assist?
Thanks,
jonathanr
View 1 Replies
View Related
Jun 2, 2007
Hi all,
I'm looking to return certain rows from our db into an SSRS based on a user selected date range using the parameters calendar.
My query/analyzer returns all required fields/rows, but how do I
pull the specific rows, (that are based on a date range that the user enters),into the report? I've tried expressions, and vb functions to no avail. The users will be using the calendar parameter to select date ranges .So far the reports pull in all rows from my query.
Thanks,
Scott
View 1 Replies
View Related
Dec 13, 2007
I am creating a search page for master detail tables. The search criteria is mainly on the header table. However, there is also one criteria which is in detail table, let said product number.In my SqlDataSource, I setup the SQL like this.select fieldA, fieldB, ..., fieldZ from masterTable where (1 = 1)Then, the additional search criteria is appended to the SqlDataSource select command once the user click the search button. If user wants to search product number, the following will be appendedand exists (select 1 from detailTable where pid = masterTable.id and productNo = @productNo)The problem is when I provides both the sub-query criteria and 2 date fields criteria. The page will raise an timeout exception. I don't have any clue on this as I can copy the SQL and run it inside the SQL Server Management Studio. The result come up in a second.Any suggestion on tackling this problem? Thanks!
View 4 Replies
View Related
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
Oct 19, 2007
Hello,
I have a problem with date parameters in my Visual Studio reports. I have set parameters €œStart date€? and €œend date€? as €œdate time€? fields, and the report works fine on my computer, but as soon as I put it in report server and start opening from other computers in the costumers place, the date stays right only about half of the times, I gues it depends on regional settings of the computer that opens the report. For example date 02/12/2007 changes to 12/02/2007 that in effect changes the month from February to December, and if the day is 13 or higher I do not get any result at all.
How should I set the date parameters that they could be correctly open and wieved from any computer no matter what regional settings are?
View 3 Replies
View Related
Jun 30, 2006
Can anyone help,
I have a problem with calling my stored procedure in vb and passing in its parameters. the system is supposed to then use the store the procedures recordset and use this to pass into a crystal report.
I am getting the following message:
Procedure 'TestReport' expects parameters '@StartDate', which was not supplied.
This happens on the objCom.Execute line.
This is my code:
Dim CRXApplication As New CRAXDRT.Application
Dim CRXReport As CRAXDRT.Report
Dim CRXDatabase As CRAXDRT.Database
Set CRXReport = CRXApplication.OpenReport(APPConst.DatabasePath & stReport & ".rpt")
Dim objCom As adodb.Command
Set objCom = New adodb.Command
Dim prm_one As adodb.Parameter
Dim prm_two As adodb.Parameter
Dim prm_three As adodb.Parameter
Dim fdate, tdate As Date
Call CRXReport.Database.Tables(1).SetLogOnInfo("dell330", "Rota", "RotaAdmin", "dadcahadfu")
Set CRXDatabase = CRXReport.Database
fdate = cboFrom.Value
tdate = cboTo.Value
With objCom
.CommandText = "TestReport"
.CommandType = adCmdStoredProc
.ActiveConnection = cn.ConnectionString
Set prm_one = .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 8, fdate)
.Parameters.Append prm_one
Set prm_two = .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 8, tdate)
.Parameters.Append prm_two
Set prm_three = .CreateParameter("@Team", adInteger, adParamInput, 4, 2)
.Parameters.Append prm_three
.Parameters.Refresh
End With
Set SQLRecs = objCom.Execute
CRXReport.DiscardSavedData
CRXDatabase.SetDataSource SQLRecs, 3, 1
This is my Stored Procedure:
CREATE PROCEDURE [dbo].[TestReport] @StartDate datetime, @EndDate datetime, @Team int AS
SELECT dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode AS Expr1,
COUNT(dbo._StaffUnavailable.ID) AS CountOfID, dbo._StaffUnavailable.[Date], dbo._Team.Description AS Team, dbo._Staff.Team_ID
FROM dbo._Staff INNER JOIN
dbo._StaffUnavailable ON dbo._Staff.Staff_ID = dbo._StaffUnavailable.Staff_ID INNER JOIN
dbo._Team ON dbo._Staff.Team_ID = dbo._Team.ID
GROUP BY dbo._Staff.Staff_ID, dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode,
dbo._StaffUnavailable.[Date], dbo._Team.Description, dbo._Staff.Team_ID
Having Team_ID = @Team AND ([Date] >= @StartDate AND [Date] <= @EndDate)
GO
Thanks in advance
Ian.
View 4 Replies
View Related
Aug 4, 2015
I am working on an Excel VBA report which is linked to an SQL Server database. The front end is Excel VBA routine, the backend is SQL Server stored procedure.
The VBA routine passes 2 dates to the stored procedure but it seems that it doesn't accept them.
The 2 date parameters in the stored procedure are @OrderDateRangeStart and @OrderDateRangeEnd.
Here is a portion of the stored procedure:
alter proc uspSalesCommission
...
...
@IncludeOrderDateAsCriterion int,
@OrderDateRangeStart date,
@OrderDateRangeEnd date
as
BEGIN
...
...
WHERE (@IncludeSalesPersonsAsCriterion=0 or Staff.name in (@Salespersons)) and
(@IncludeOrderDateAsCriterion=0 or SALESORD_HDR.ORDERDATE between @OrderDateRangeStart and @OrderDateRangeEnd)
In Excel VBA, the code passing the parameters are:
cmd1.parameters("@OrderDateRangeStart").value = cdate(me.startDate)
cmd1.parameters("@OrderDateRangeEnd").value = cdate(me.EndDate)
where cmd1 is a command object, me.startdate is start date field in Excel me.enddate is the end date field in Excel.
The Excel VBA routine works only when the day and month are both 1,(e.g. 1/1/2015), when they are other values (e.g. 31/5/2014) , it failed.
View 5 Replies
View Related
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
Jun 14, 2007
Good morning all,
I have a report which measures supplier performance for the previous month. It takes an age to generate so I am trying to cache a copy to a null location first thing in the morning to speed up the process. The problem I'm having is in getting the report to select the first day and last day of the month for the two parameters that the report needs to run.
In BIDS the report runs perfectly when previewed, however, when it is deployed to the report server I get the following error: 'Error during processing of €˜RP2€™ report parameter. (rsReportParameterProcessingError)'
I have used the following two statements for the default values of the parameters, which work in BIDS, so I can't understand why they don't when it's deployed. I'm also sure there is an easier way of doing this, but after about an hour searching yesterday and not finding anything it only took me about half that time to use these statements:
for opening date:
=IIf(Month(Now()) = 1, CDate("01/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("01/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("01/02/" & Year(Now())), IIf(Month(NOw()) = 4, CDate("01/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("01/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("01/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("01/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("01/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("01/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("01/09/" & Year(Now())), IIf(Month(Now()) = 11, Cdate("01/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("01/11/" & Year(Now())), CDate("01/12/1900")))))))))))))
for closing date (pretty similar really, this is the parameter with which the report server finds an error):
=IIf(Month(Now()) = 1, CDate("31/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("31/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("28/02/" & Year(Now())), IIf(Month(Now()) = 4, CDate("31/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("30/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("31/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("30/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("31/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("31/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("30/09/" & Year(Now())), IIf(Month(Now()) = 11, CDate("31/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("30/11/" & Year(Now())), CDate("31/12/1900")))))))))))))
The only caviate to using these statements is that it wont recognise when a leap year occurs, other than that, if it would work when deployed to the report server it would work perfectly for the purposes of what we need.
If anyone can see the flaw, or knows of a better and easier way of doing this please let me know.
Humble thanks,
Paul
View 3 Replies
View Related
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
Dec 11, 2007
I'm a SQL newbie, and I'm trying to write a report that returns records based on a beginning and end date that the user supplies. But when I run the query and supply the dates (begin 11/29/2007 / end 11/30/2007, for example), I'm only getting back one record, when there should be at least 3. It appears that my query is ignoring the =, and only returning those records that have a date > or <, not <= or >=.
I was told that it's possible that the time needs to be converted to UTC as well, but I'm not sure how to do that ( I tried, but I really don't know what I'm doing so I commented it out)... Can someone offer some guidance on how to get all the records to show up? I've pasted my query below, thanks in advance!
SELECT FilteredContact.fullname, FilteredContact.new_memberleadsourcename, FilteredContact.new_prospectforclubname, FilteredContact.owneridname,
FilteredContact.new_referred_by, convert(varchar(10),FilteredContact.createdon,101) as Date--, SELECT GETDATE(FilteredContact.createdon) AS CurrentTime, GETUTCDATE(FilteredContact.createdon) AS UTCTime
FROM FilteredContact JOIN filteredOpportunity on
FilteredOpportunity.contactid = Filteredcontact.contactid
where filteredopportunity.new_opportunitytype = 1 and (FilteredContact.createdon >= '11/29/2007') and (FilteredContact.createdon <= '11/30/2007')
View 3 Replies
View Related
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 .......to...............
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.
cheers
Dianne
View 5 Replies
View Related
Sep 27, 2005
Greetings....
View 4 Replies
View Related
Jul 4, 2007
All,
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.
Thanks.
View 5 Replies
View Related
Feb 12, 2015
I have a SSRS report using 2008 R2. It prompts the user for the start and end dates. This all works. But now I want the start date parm to default to the first day of the current month and the end date parm to default to the last day of the current month.In the new query window in SQL Server Management Studio, I can run this chunk of code to get the first day of current month:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
And this code to get the last day of current month:
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),
DATEADD(MONTH, 1, GETDATE()))
But I don't know how to do this in SSRS 2008. How can I make my start / end parms to get these values.
View 5 Replies
View Related
Jul 7, 2015
I have a detailed report in ssrs in which data can come from start date and end date parameters.
but the problem is. for example i gave startdate as 01/01/2015 and end date as 09/31/2015 then the data must be in displayed in such a way that jan month in one tab and feb month data in one tab and sooo on to sep month data in new tab when i export to Excel.
Is this possible in ssrs ?
View 1 Replies
View Related
Jun 4, 2015
I have an SSRS report with parameters for Created On Start and Created On End. Users run this manually and choose the date range to display records for.
I would like to set up two different subscriptions
1) to send weekly on Monday morning for "last weeks" records and
2) to send monthly to send "last month's" records.
View 2 Replies
View Related