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.
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
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?
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.
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.
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.
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.
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.
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?
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
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.
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)
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.
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!
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.
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?
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
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:
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?
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:
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.
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!
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!
FilteredContact.new_referred_by, convert(varchar(10),FilteredContact.createdon,101) as Date--, SELECT GETDATE(FilteredContact.createdon) AS CurrentTime, GETUTCDATE(FilteredContact.createdon) AS UTCTime
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.
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?
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:
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.
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.
