Showing posts with label date. Show all posts
Showing posts with label date. Show all posts

Sunday, March 25, 2012

Calling SQL 2005 stored procedure that returns a value

I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table.

I am calling it from an ASP page. Initially I just called it and didn't worry about the return value. However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).

I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value.

I am calling the SP as follows:

Shared Sub createlogentry(ByVal ID, ByVal tme, ByVal val)
Dim result As String
Dim cs As String = ConfigurationManager.ConnectionStrings("connecttion1").ToString
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand()
cmd.Connection = con
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "CreateLogEntry"
cmd.Parameters.Add("@.ChID", Data.SqlDbType.Int)
cmd.Parameters("@.ChID").Value = ID
cmd.Parameters.Add("@.Value", Data.SqlDbType.Int)
cmd.Parameters("@.Value").Value = val
result = cmd.ExecuteNonQuery().ToString
End Using
End Sub

I have tried amending the ExecuteNonQuery line to ExecuteReader()

Any help appreciated

Regards

Clive

Greetings Clive

I would have made the stored procedure use an output parameter as more outputs can always be added. A return can only return one value!

So as output you would add the lines:

cmd.Parameters.Add("Fred"), Data.SqlDbType.Int)

cmd.Parameters("Fred").Direction = Output;

after the result add

Dim iResult As Integer = md.Parameters("Fred").Value

|||

Thanks for that - you are right the ability to return more than one value would be useful.

At the moment in my T-SQL I use the RETURN construct to generate the return value. How would I change that to use an output parameter instead?

regards

Clive

|||

As in

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clive Chinery
-- Create date: 13Aug2007
-- Description: Test for records for today and tomorrow
-- =============================================
CREATE PROCEDURE dbo.DoDateTest
@.Test1 BIT OUTPUT,
@.Test2 BIT OUTPUT
AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tblToday WHERE Date = GetDate()) SET @.Test1 = 1 ELSE SET @.Test1 = 0
IF EXISTS(SELECT * FROM tblToday WHERE Date = DateAdd(day, 1, GetDate()))
SET @.Test2 = 1 ELSE SET @.Test2 = 0
------- This is the End ----
GO

This returns two output values. I normally put the output parameters at the end of the declaration.

Regards

Clive Chinery

Monday, March 19, 2012

Calling All T-SQL Gods & Goddesses

Hi

I'm having trouble with the script below that it just won't do a
correct ORDER BY for a date field.

When executing the two SELECT TOP statements on their own the records
are sorted correctly but when wrapped in the main statement, the ORDER
BY is just ignored. Tried to play around with the script but with no
luck. Script is for paging, in this case, select all records meeting
criteria, order and select Top 25 to display on first page. Any
questions, please don't hesitate contacting me.

Thanks very much to anyone who's having a look at this!

Martin

SELECT COALESCE((i2b_contact.Firstname + CHAR(32) +
i2b_contact.Lastname),i2b_company.CompanyName) AS CName,
i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,
(i2b_address.Address1) AS PropertyAddress,
A.ProgUserName AS ProgUserName,
CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) AS
TransactionDate,
CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
ReturnByDate
FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROM
i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN
(SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESCOn 29 May 2004 12:19:20 -0700, Martin wrote:

>Hi
>I'm having trouble with the script below that it just won't do a
>correct ORDER BY for a date field.
>When executing the two SELECT TOP statements on their own the records
>are sorted correctly but when wrapped in the main statement, the ORDER
>BY is just ignored. Tried to play around with the script but with no
>luck. Script is for paging, in this case, select all records meeting
>criteria, order and select Top 25 to display on first page. Any
>questions, please don't hesitate contacting me.
>Thanks very much to anyone who's having a look at this!
>Martin
>SELECT COALESCE((i2b_contact.Firstname + CHAR(32) +
>i2b_contact.Lastname),i2b_company.CompanyName) AS CName,
>i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,
>(i2b_address.Address1) AS PropertyAddress,
>A.ProgUserName AS ProgUserName,
>CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) AS
>TransactionDate,
>CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
>ReturnByDate
> FROM i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROM
>i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE i2b_keytransactionlog.ProgClientID = 1 AND
>i2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN
> (SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE i2b_keytransactionlog.ProgClientID = 1 AND
>i2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)
> ORDER BY ReturnByDate DESC)
> ORDER BY ReturnByDate DESC

Hi Martin,

I'm not entirely sure why you would use TOP 0 for the NOT IN subquery, but
that's not the cause of the problem you post about.

The ANSI standard for SQL allows only columns appearing in the SELECT list
in the ORDER BY clause. The rationale being that first, the query gets
executed and the result set is determined; the sorting is a seperate,
final stage. ANSI SQL allows things as ORDER BY 3 (3 being the third
column in the SELECT LIST) or SELECT MyColumn AS ColumnAlias ... ORDER BY
ColumnAlias.

MS SQL Server expands on this standard. With MS SQL Server, you can ORDER
BY on a column not in the SELECT list. If you use a column name that is
not an alias in the column list, SQL Server will try to locate a column
with that name in one of the tables used in the query and use that for
sorting.

Before I get back to your problem, run the following script. It will
clearly show that SQL Server will first attempt to resolve names in the
ORDER BY clause following ANSI standard before looking at the columns that
make up the data.

CREATE TABLE testtab (a int, b int)
go
INSERT testtab (a, b) SELECT 1, 20
INSERT testtab (a, b) SELECT 2, 10
go
SELECT b
FROM testtab
ORDER BY a
go
SELECT b AS a
FROM testtab
ORDER BY a
go
SELECT b AS a, a AS b
FROM testtab
ORDER BY a
go
DROP TABLE testtab
go

In the first query, no column "a" is in the SELECT list, so SQL Server
will sort on the column a in the table. In the second query, the SELECT
list has a column aliased as "a", so this is used for sorting. In the
third query, the column a itself is used in the select list; aliases are
disregarded and sorting is done on the contents of a.

Now back to your real problem. I'll show you the parts that cause the
unexpected ordering:

>SELECT (...)
> (...)
>CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
>ReturnByDate
> (...)
> ORDER BY ReturnByDate DESC

The column ReturnByDate is not used in the SELECT list (the results of a
function are not cosidered equal to the column itself). There is however
an alias ReturnByDate, so there's no need to get back to the data in the
table either - the aliased column in the result set is used. This column
is the output of a CONVERT function call. It's varchar(10), therefor the
sorting will be done by alphabet, descending. "23-04-2003" will go before
"06-05-2004".

The fix? Either use something else as an alias ([Return By Date], for
example) or use ORDER BY CONVERT(datetime, ReturnByDate).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Thursday, March 8, 2012

Calling .Net Assembly

I create a KPI with the following expression:

([Measures].[TDV Result],

StrToMember("[TDV Result Date].[Year - Quarter - Month - Date]

.[Month].&[" + MdxFunctions.MdxFunctions.Timeframe.GetCurrentMonth() + "]"),

[Entities].[EntityType].[Site],

[TDVs].[Parameter - Material].&[Coal])

MdxFunctions.MdxFunctions.Timeframe.GetCurrentMonth() is a .Net assembly and returns a string like 2007-02-01. I deploy and look at the Browser View but there is an error stating that "The '2007-02-01' string cannont be converted to the date type". How do I convert the date string?

You are passing 2007-02-01 as a key, are you sure that key of the time dimension is a string and not a date? You should either pass a key or remote &: ("[TDV Result Date].[Year - Quarter - Month - Date].[Month].["|||

I'm not entirely sure, but if I had to guess I would try adding the time component. If you are using a date datatype for the key of your month attribute, it might be expecting a value in the form of "yyyy-mm-dd hh:mm:ss"

A couple of other observations:

I think you should be able to write your .Net function to return an actual member instead of a string, which mean you would not need the StrToMember Call everytime you used GetCurrentMonth (You could look at the LinkMember function from http://www.codeplex.com/ASStoredProcedures )|||Thanks, I did notice I was missing the time part after I sent this post. I would like to just use VBA but the problem is I have other functions to get the current week, quarter, and other functions that require lengthy VBA code. If I put it in a calculation then I need to duplicate it for every cube. I tried the Time Intelligence but only get NA.|||

That's cool, I realise that there are more considerations than pure speed. :)

I just thought you should be aware that there is a performance impact, particularly if you had queries that displayed the KPI for a large number of members. But if you are now up and running and the performance is acceptable then there is no need to worry.

Wednesday, March 7, 2012

Call Stored procedure for last 4 months then combine into final result set row

I want to call my stored proc for that last 4 months. Basically all I need to do is pass each month's first date and it will do the rest. Should I shove this into a UDF first? I'm not sure if I can do that. The struction is here behind my stored proc: http://www.webfound.net/storedproc.txt

EXEC IT_Get_Dashboard_Monthly '2006-05-03 12:03:43.910' <-- change to UDF or leave it? Then how can I loop and change each month to cover the last 4 months?

I also need to ensure all 4 values returned in each interation show up in one row in the final result set that is produced

use a simple while loop
declare @.date datetime,
@.end_date datetime
select @.date = '' -- your start date
select @.end_date = dateadd(month, -4, @.date)
while (@.date >= @.end_date)
begin
exec IT_Get_Dashboard_Monthly @.date
select @.date = dateadd(month, -1, @.date)
end

Saturday, February 25, 2012

Calendars - Hierachy issue

I have date dimension that uses a date_skey as the primary key (format = 20070101). I have both fiscal and calendar hiearchies. My problem is that date based functions (ie YTD and ParallelPeriod) work with my regular calendar hierachy but not the fiscal hierachy. I have the following fields:

Year (type years)

Quarter (type quarters)

Month (type months)

Week (type weeks)

Date (type date)

FiscalYear (type FiscalYears)

FiscalQuarter (type FiscalQuarters)

FiscalMonth (type FiscalMonths)

FiscalWeek (type FiscalWeeks)

date_skey (type regular)

Calendar Hierachy:

Year (type years)

Quarter (type quarters)

Month (type months)

Date (type date)

and

Fiscal Hierachy:

FiscalYear (type fiscalyears)

FiscalQuarters(type fiscalquarters)

FiscalMonths (type fiscalMonths)

Date (type date)

Am I using date and date_skey incorrectly in this setup?

Any recommendations are welcome.

Based on results from the Adventure Works Date dimension, which has both Calendar and Fiscal heirarchies, you may need to use more explicit versions of MDX time series functions for the Fiscal hierarchy. For example, instead of YTD([Date].[Fiscal].CurrentMember), try PeriodsToDate([Date].[Fiscal].[Fiscal Year], [Date].[Fiscal].CurrentMember), etc.|||This was the right answer - YTD, MTD would not work on my Fiscal Calendar hierarchy but I was able to use PeriodsToDate in place of them. Thanks

Calendar slow to load

Is anyone else experiencing performance problems with reports that use the calendar control for date parameters? Whenever we load a report that uses a calendar it takes several seconds to fully load the form (you can see something like this in the status bar:"...ReportViewerWebControl.axd?OpType=Calendar..."). This is not a serious problem, but if the user tries to use the control before it fully loads then the page throws javascript errors. I am wondering if this is a issue with the report itself or the server configuration, or if it is just something we have to live with.

The calendar control works by loading a years worth of dates to allow you to browse through the months more quickly. This is not a overly large amount of data and I have not seen it take a significant amount of time myself. The is, however, no way to change the behavior of this control. Things that might cause this behavior are a slow network connection or a slow server, however you would notice everything having longer load times then.

-Daniel

|||

I have a new RS2005 server in production and the speed at which the calendar control loads is also painfully slow.

On the initial rounds of testing it was so slow that my Business Analysts / QA person reported *bugs* with the calendar control refreshing the page or displaying blank and so forth.

Is there a way to limit how much date information it pulls down initially or to possibly build a custom control based on the out of the box control and then limit the date range loaded by default?

For my users at least the date range is going to be from minus 3 months to present for about 95% of the runs.

|||Just to add to this, I have the same problem, I thought it was a bug too but there doesnt seem to be a way of fixing this....at least none that I know of.|||

We have the same problem with calendar control. I have a filter with a date that should be greater than a value given by the user in the report. If I have a default value on the date the report loads very slow, in about 10 seconds. When I change the value in the report and click view report it still load slow.

On the other hand if I dont give a default value in the filter the report loads within 2 seconds. It also loads within seconds if I change the value in the report?

Any suggestions?

/Stefan

|||

I wonder if this isn't related to bugs in the control?

The two behaviors I notice are an incredible amount of sluggishness associated with having a default value and that each time you change the value it makes a server round-trip even though you haven't clicked on the View Report button.

|||

Can you file a bug on this issue along with your report definition that makes this happen? When you do so, please include infomration on the SQL Server Reporting Services version you're using and what browser and it's version you're using.

You can file the bug here: http://lab.msdn.microsoft.com/productfeedback/

When you have an expression based default value, we need to do a round trip to the server to determine the new value for the parameter. If the parameter depends on a previous parameter, whenever you change the upstream parameter we'll do a round trip. What I'd like to know is if the slowness persists if you were to substitute a string parameter for your datatime parameter. If it does, it is probably that the server is taking a long time to process your request - is it adequately resourced for the work load? Otherwise it might be specific to the data picker control.

Hope that helps,

-Lukasz

Calendar slow to load

Is anyone else experiencing performance problems with reports that use the calendar control for date parameters? Whenever we load a report that uses a calendar it takes several seconds to fully load the form (you can see something like this in the status bar:"...ReportViewerWebControl.axd?OpType=Calendar..."). This is not a serious problem, but if the user tries to use the control before it fully loads then the page throws javascript errors. I am wondering if this is a issue with the report itself or the server configuration, or if it is just something we have to live with.

The calendar control works by loading a years worth of dates to allow you to browse through the months more quickly. This is not a overly large amount of data and I have not seen it take a significant amount of time myself. The is, however, no way to change the behavior of this control. Things that might cause this behavior are a slow network connection or a slow server, however you would notice everything having longer load times then.

-Daniel

|||

I have a new RS2005 server in production and the speed at which the calendar control loads is also painfully slow.

On the initial rounds of testing it was so slow that my Business Analysts / QA person reported *bugs* with the calendar control refreshing the page or displaying blank and so forth.

Is there a way to limit how much date information it pulls down initially or to possibly build a custom control based on the out of the box control and then limit the date range loaded by default?

For my users at least the date range is going to be from minus 3 months to present for about 95% of the runs.

|||Just to add to this, I have the same problem, I thought it was a bug too but there doesnt seem to be a way of fixing this....at least none that I know of.|||

We have the same problem with calendar control. I have a filter with a date that should be greater than a value given by the user in the report. If I have a default value on the date the report loads very slow, in about 10 seconds. When I change the value in the report and click view report it still load slow.

On the other hand if I dont give a default value in the filter the report loads within 2 seconds. It also loads within seconds if I change the value in the report?

Any suggestions?

/Stefan

|||

I wonder if this isn't related to bugs in the control?

The two behaviors I notice are an incredible amount of sluggishness associated with having a default value and that each time you change the value it makes a server round-trip even though you haven't clicked on the View Report button.

|||

Can you file a bug on this issue along with your report definition that makes this happen? When you do so, please include infomration on the SQL Server Reporting Services version you're using and what browser and it's version you're using.

You can file the bug here: http://lab.msdn.microsoft.com/productfeedback/

When you have an expression based default value, we need to do a round trip to the server to determine the new value for the parameter. If the parameter depends on a previous parameter, whenever you change the upstream parameter we'll do a round trip. What I'd like to know is if the slowness persists if you were to substitute a string parameter for your datatime parameter. If it does, it is probably that the server is taking a long time to process your request - is it adequately resourced for the work load? Otherwise it might be specific to the data picker control.

Hope that helps,

-Lukasz

Calendar slow to load

Is anyone else experiencing performance problems with reports that use the calendar control for date parameters? Whenever we load a report that uses a calendar it takes several seconds to fully load the form (you can see something like this in the status bar:"...ReportViewerWebControl.axd?OpType=Calendar..."). This is not a serious problem, but if the user tries to use the control before it fully loads then the page throws javascript errors. I am wondering if this is a issue with the report itself or the server configuration, or if it is just something we have to live with.

The calendar control works by loading a years worth of dates to allow you to browse through the months more quickly. This is not a overly large amount of data and I have not seen it take a significant amount of time myself. The is, however, no way to change the behavior of this control. Things that might cause this behavior are a slow network connection or a slow server, however you would notice everything having longer load times then.

-Daniel

|||

I have a new RS2005 server in production and the speed at which the calendar control loads is also painfully slow.

On the initial rounds of testing it was so slow that my Business Analysts / QA person reported *bugs* with the calendar control refreshing the page or displaying blank and so forth.

Is there a way to limit how much date information it pulls down initially or to possibly build a custom control based on the out of the box control and then limit the date range loaded by default?

For my users at least the date range is going to be from minus 3 months to present for about 95% of the runs.

|||Just to add to this, I have the same problem, I thought it was a bug too but there doesnt seem to be a way of fixing this....at least none that I know of.|||

We have the same problem with calendar control. I have a filter with a date that should be greater than a value given by the user in the report. If I have a default value on the date the report loads very slow, in about 10 seconds. When I change the value in the report and click view report it still load slow.

On the other hand if I dont give a default value in the filter the report loads within 2 seconds. It also loads within seconds if I change the value in the report?

Any suggestions?

/Stefan

|||

I wonder if this isn't related to bugs in the control?

The two behaviors I notice are an incredible amount of sluggishness associated with having a default value and that each time you change the value it makes a server round-trip even though you haven't clicked on the View Report button.

|||

Can you file a bug on this issue along with your report definition that makes this happen? When you do so, please include infomration on the SQL Server Reporting Services version you're using and what browser and it's version you're using.

You can file the bug here: http://lab.msdn.microsoft.com/productfeedback/

When you have an expression based default value, we need to do a round trip to the server to determine the new value for the parameter. If the parameter depends on a previous parameter, whenever you change the upstream parameter we'll do a round trip. What I'd like to know is if the slowness persists if you were to substitute a string parameter for your datatime parameter. If it does, it is probably that the server is taking a long time to process your request - is it adequately resourced for the work load? Otherwise it might be specific to the data picker control.

Hope that helps,

-Lukasz

Calendar picker?

Hi there
I am trying to set up some sort of date picker for my reports in Reporting
Services and found an article which basically uses an asp.net calendar
control as a plug-in to the report (see link below).
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/6e8cee511769b6c/1ddce2c2c6523fc0?q=&_done=/groups?enc_author=uFKGehIAAAAdZvNtw6tRAoeBJxgAOEvh8rhlH0Pnl47z4AZhN98BFg&&_doneTitle=Back%20to%20Search&d=&
Unfortunately I am not very experienced in programming, so i don't fully
understand how you would integrate the asp page with the report.
My reports are based on an Analysis Services cube time dimension. i have an
MDX statement in the Data query window in .NET which populates a Matrix
table. The query looks like this:
SELECT { Measures.members } on Columns ,
NON EMPTY {{[Business].[User].[Business Entity Id].[891].Children}
* {[Time].[2004].[Quarter 1].[1].[January]:[Time].[2005].[Quarter
1].[1].[January]}} on Rows
FROM UsageStats_Phase1
I then deploy the report to the Reporting Server manually. How would I
integrate an asp calendar control into this set up? Any help would be most
appreciated!!!
Thanks and regardsRight now, there's not a good way to customize the parameter input process
other than programming. The hyperlink you posted is a very good starting
point.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Neile" <Neile@.discussions.microsoft.com> wrote in message
news:BE9EB249-A29F-47A2-9D17-18632D47A1E6@.microsoft.com...
> Hi there
> I am trying to set up some sort of date picker for my reports in Reporting
> Services and found an article which basically uses an asp.net calendar
> control as a plug-in to the report (see link below).
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/6e8cee511769b6c/1ddce2c2c6523fc0?q=&_done=/groups?enc_author=uFKGehIAAAAdZvNtw6tRAoeBJxgAOEvh8rhlH0Pnl47z4AZhN98BFg&&_doneTitle=Back%20to%20Search&d=&
> Unfortunately I am not very experienced in programming, so i don't fully
> understand how you would integrate the asp page with the report.
> My reports are based on an Analysis Services cube time dimension. i have
> an
> MDX statement in the Data query window in .NET which populates a Matrix
> table. The query looks like this:
> SELECT { Measures.members } on Columns ,
> NON EMPTY {{[Business].[User].[Business Entity Id].[891].Children}
> * {[Time].[2004].[Quarter 1].[1].[January]:[Time].[2005].[Quarter
> 1].[1].[January]}} on Rows
> FROM UsageStats_Phase1
> I then deploy the report to the Reporting Server manually. How would I
> integrate an asp calendar control into this set up? Any help would be most
> appreciated!!!
> Thanks and regards
>

Calendar parameter in OLAP report

Hi,

I am trying to find the best way to use a date parameter on a OLAP report and get the calendar pop to be used instead of a dropdown.

Whenever I change from string to datetime for the parameter I (of course) get an error about different data types, since OLAP is returning the fields as strings and formatted.

Any easy (or best) approach to use to get a calendar pop to work with an OLAP report? I am mainly doing this for date range searches and do not want people to pick from huge dropdowns for dates.

Thanks.

I have made a few posts explaining how to use a calendar control with MDX, here is a link to probably the most helpful (as it has examples): http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1506211&SiteID=1

Basically, you need to take the output of the calendar control, CDate() it, then use Format() to generate a string that is compatable with how you have set up your heirarchy members.

Friday, February 24, 2012

Calendar in parameter drop down

Hi all
Is it possiable to have a calendar drop down in a date parameter field? I
am not a programmer so be kind. This would be much easier for users than
typing in a date.
thanksThis, for me, was one of the more shocking revelations when I started
to use RS, but the answer is "no". It's pretty amazing to me that for
a tool that has so much promise, some very fundamental things like
this are lacking. In addition to the calendar control, another one
that we needed but couldn't use is a simple checkbox for a boolean.
And going a small step further, it is also surprising to realize that
you can't easily save the parameters a user enters so that they'll be
there for next time.
Still, hopefully these features are coming soon! :-)
Brad.
On Thu, 2 Sep 2004 10:44:39 -0700, "darwin" <dguillory @.
goodwillaz.org> wrote:
>Hi all
>Is it possiable to have a calendar drop down in a date parameter field? I
>am not a programmer so be kind. This would be much easier for users than
>typing in a date.
>thanks
>|||I think there us a work around for the problem of saving
user's input.
Write your own component which has functions that can take
in user's parameters as input and write code to save those
values in the database. Add a reference to this component
in your report and call the specific function to save
user's input.
Alternatively just write an SP which takes in these
parameters and write them into a table.
About the other points , even I would like to see those
features.
>--Original Message--
>This, for me, was one of the more shocking revelations
when I started
>to use RS, but the answer is "no". It's pretty amazing
to me that for
>a tool that has so much promise, some very fundamental
things like
>this are lacking. In addition to the calendar control,
another one
>that we needed but couldn't use is a simple checkbox for
a boolean.
>And going a small step further, it is also surprising to
realize that
>you can't easily save the parameters a user enters so
that they'll be
>there for next time.
>Still, hopefully these features are coming soon! :-)
>Brad.
>On Thu, 2 Sep 2004 10:44:39 -0700, "darwin" <dguillory @.
>goodwillaz.org> wrote:
>>Hi all
>>Is it possiable to have a calendar drop down in a date
parameter field? I
>>am not a programmer so be kind. This would be much
easier for users than
>>typing in a date.
>>thanks
>.
>

Calendar in parameter

This is probably pretty simple, but i've never done it before.
Im trying to put a Calendar up for the parameter, So they can click a date
on the calendar, and get it quiered. Is this done in the dataset? or in the
report Parameters? and what do i need to do?Make sure your parameter is a Date time parameter, and you get the Calendar
control when you run the report.
Kaisa M. Lindahl Lervik
"Tenchy" <Tenchy@.discussions.microsoft.com> wrote in message
news:064A832C-EB50-44E9-AAB6-57F4FFA87C38@.microsoft.com...
> This is probably pretty simple, but i've never done it before.
> Im trying to put a Calendar up for the parameter, So they can click a date
> on the calendar, and get it quiered. Is this done in the dataset? or in
> the
> report Parameters? and what do i need to do?

Calendar Controls + Multi-Value causes Error?

I have a Report with 2 Date Parameters using Calendar Controls, followed by 2
multi-valued (cascading) parameters. This worked fine with the June CTP15.
After upgrading to the Sept CTP16, if I change the default date (yesterday)
on either calendar control, an error page is generated, with this message:
"Value cannot be null. Parameter name: value". Of course, this is only after
deploying the report; it still previews fine in Report Designer.
Pressing the "Back" button on the browser returns to the report page, with
the date selection intact. After selecting the second date, the same thing
happens, and I have to press "Back" in the browser. However, no errors are
generated when I now set the multi-select parameters themselves.
I've been able to repro this problem with a trivial report having 2 date
parameters and 1 multi-select parameter. The behavior isn't identical,
because the error page is generated only when changing the first date from
its default. There is also no error when no default dates are configured for
the 2 calendar controls.
Wondering if anyone else has encountered similar quirks?Thanks for reporting this. It has been fixed in builds after CTP 16 and will
work e.g. in the final SQL Server 2005 RTM build.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Deepak" <Deepak@.discussions.microsoft.com> wrote in message
news:696B6FCF-BE89-46A0-A7C8-8588D4F4C9B9@.microsoft.com...
>I have a Report with 2 Date Parameters using Calendar Controls, followed by
>2
> multi-valued (cascading) parameters. This worked fine with the June CTP15.
> After upgrading to the Sept CTP16, if I change the default date
> (yesterday)
> on either calendar control, an error page is generated, with this message:
> "Value cannot be null. Parameter name: value". Of course, this is only
> after
> deploying the report; it still previews fine in Report Designer.
> Pressing the "Back" button on the browser returns to the report page, with
> the date selection intact. After selecting the second date, the same thing
> happens, and I have to press "Back" in the browser. However, no errors are
> generated when I now set the multi-select parameters themselves.
> I've been able to repro this problem with a trivial report having 2 date
> parameters and 1 multi-select parameter. The behavior isn't identical,
> because the error page is generated only when changing the first date from
> its default. There is also no error when no default dates are configured
> for
> the 2 calendar controls.
> Wondering if anyone else has encountered similar quirks?|||Robert,
Thanks for confirming that this has already been fixed - FYI I'd submitted a
bug report to BetaPlace, with Track ID: 876023652.
- Deepak
"Robert Bruckner [MSFT]" wrote:
> Thanks for reporting this. It has been fixed in builds after CTP 16 and will
> work e.g. in the final SQL Server 2005 RTM build.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Deepak" <Deepak@.discussions.microsoft.com> wrote in message
> news:696B6FCF-BE89-46A0-A7C8-8588D4F4C9B9@.microsoft.com...
> >I have a Report with 2 Date Parameters using Calendar Controls, followed by
> >2
> > multi-valued (cascading) parameters. This worked fine with the June CTP15.
> > After upgrading to the Sept CTP16, if I change the default date
> > (yesterday)
> > on either calendar control, an error page is generated, with this message:
> > "Value cannot be null. Parameter name: value". Of course, this is only
> > after
> > deploying the report; it still previews fine in Report Designer.
> >
> > Pressing the "Back" button on the browser returns to the report page, with
> > the date selection intact. After selecting the second date, the same thing
> > happens, and I have to press "Back" in the browser. However, no errors are
> > generated when I now set the multi-select parameters themselves.
> >
> > I've been able to repro this problem with a trivial report having 2 date
> > parameters and 1 multi-select parameter. The behavior isn't identical,
> > because the error page is generated only when changing the first date from
> > its default. There is also no error when no default dates are configured
> > for
> > the 2 calendar controls.
> >
> > Wondering if anyone else has encountered similar quirks?
>
>

Calendar Control issues

Hi all,

When I click on a calendar control for date parameters the page will freeze or throw a java script error. This does not happen all the time, and is not isolated to one PC. It happens randomly on several PCs. Is there any known issue with this control?

regards,

Bill

The only issue that I have seen is if you try to open the calendar control while it is still loading the iframe for the calendar. Next time it happens see if the IE status bar is still showing the page loading.|||It is in the process of loading. There are times though, when it takes 10 seconds or more to load locally, and much longer if a user is accessing the page outside of the local LAN. If the user clicks on the control during the load, a java error occurs and the page is disabled. This cant be by design , could it? :-)|||

No it is not by design. :) I am surprised that it does take so long to load. Is this happening the first time you view the report or when navigating through calendar months? If it is the first time, the calendar is one of the last things loaded on the page, if the entire page is still loading (IE will render objects and allow interaction even though the page hasn't finished loading) the calander may not be loaded until the page is loaded. Are you viewing the reports through Report Manager or your own pages? If they are your own pages, perhaps there is some improvement that can be made there to speed up the page load.

As far as anything you can do to Report Server, there isn't anything that can currently be done to affect this behavior. We are aware of the issue and are looking to fix it in a future version. In the meantime users need to be aware of the issue and wait to open the drop down until the page has fully loaded.

|||Thank you for your answer. The page is displayed through report services, not my own page. I hope you have a fix soon, as it is making report services for my company very difficult to use. On a side note, report services displayed in IE7 with a calendar control will not work at all.|||

There have been quite a few reports of problems with the calendar control (just search the forums) and the basic answer seems to be "we cant replicate any problem" so I think MS dont know what to fix.

Certainly I have the same issue as you, the calendar controls throwing script errors and taking ages to load.

|||I am just hoping that some sort of hot fix is available soon. Like I said though, it does not work at all with IE7 so if MS is planning to release IE7 as a critical update like it is rumored, it will break all RS sites.|||IE7 is only in beta 3 mode, I believe the issues with IE7 and RS will be resolved but there is no fix for IE7 Beta 3.|||So I guess the answer is "deal with it" At least most companies dont use parameters in reporting , so it should not be a big issue. |||Just to clarify, we are aware of the issue with the script errors and how to fix it. In order to be fixed in a QFE, a customer will need to contact CSS and request the fix, otherwise look for the fix in a future release. If your are hindered by this issue, your best course of action is to contact Customer Support and request a fix.|||It appears it is an IE issue. I am able to work with the calendar control fine with Firefox. Can anyone verify this?

Calendar Control issues

Hi all,

When I click on a calendar control for date parameters the page will freeze or throw a java script error. This does not happen all the time, and is not isolated to one PC. It happens randomly on several PCs. Is there any known issue with this control?

regards,

Bill

The only issue that I have seen is if you try to open the calendar control while it is still loading the iframe for the calendar. Next time it happens see if the IE status bar is still showing the page loading.|||It is in the process of loading. There are times though, when it takes 10 seconds or more to load locally, and much longer if a user is accessing the page outside of the local LAN. If the user clicks on the control during the load, a java error occurs and the page is disabled. This cant be by design , could it? :-)|||

No it is not by design. :) I am surprised that it does take so long to load. Is this happening the first time you view the report or when navigating through calendar months? If it is the first time, the calendar is one of the last things loaded on the page, if the entire page is still loading (IE will render objects and allow interaction even though the page hasn't finished loading) the calander may not be loaded until the page is loaded. Are you viewing the reports through Report Manager or your own pages? If they are your own pages, perhaps there is some improvement that can be made there to speed up the page load.

As far as anything you can do to Report Server, there isn't anything that can currently be done to affect this behavior. We are aware of the issue and are looking to fix it in a future version. In the meantime users need to be aware of the issue and wait to open the drop down until the page has fully loaded.

|||Thank you for your answer. The page is displayed through report services, not my own page. I hope you have a fix soon, as it is making report services for my company very difficult to use. On a side note, report services displayed in IE7 with a calendar control will not work at all.|||

There have been quite a few reports of problems with the calendar control (just search the forums) and the basic answer seems to be "we cant replicate any problem" so I think MS dont know what to fix.

Certainly I have the same issue as you, the calendar controls throwing script errors and taking ages to load.

|||I am just hoping that some sort of hot fix is available soon. Like I said though, it does not work at all with IE7 so if MS is planning to release IE7 as a critical update like it is rumored, it will break all RS sites.|||IE7 is only in beta 3 mode, I believe the issues with IE7 and RS will be resolved but there is no fix for IE7 Beta 3.|||So I guess the answer is "deal with it" At least most companies dont use parameters in reporting , so it should not be a big issue. |||Just to clarify, we are aware of the issue with the script errors and how to fix it. In order to be fixed in a QFE, a customer will need to contact CSS and request the fix, otherwise look for the fix in a future release. If your are hindered by this issue, your best course of action is to contact Customer Support and request a fix.|||It appears it is an IE issue. I am able to work with the calendar control fine with Firefox. Can anyone verify this?

Calendar control in SP2

I know that the SP2 Beta is around for testing. Does anyone know if in
this version the Calendar control is made available for use for date
parameter input ? I believe the Calendar control is the most needed
control in any kind of reporting. Comments Please.
thanks,
Anand Sagar
IN4VelocityAnand Sagar wrote:
> I know that the SP2 Beta is around for testing. Does anyone know if in
> this version the Calendar control is made available for use for date
> parameter input ? I believe the Calendar control is the most needed
> control in any kind of reporting. Comments Please.
For what?
I use a calendar-control in a normal aspx-page and open the report in my
aspx-page
regards
Frank|||It is not SP2. Look for it in the upcoming version Yukon of SQL Server and
Reporting Services.
--
| From: anandsagar@.gmail.com (Anand Sagar)
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Calendar control in SP2
| Date: 27 Jan 2005 01:50:02 -0800
| Organization: http://groups.google.com
| Lines: 8
| Message-ID: <3f76a771.0501270150.17a13aea@.posting.google.com>
| NNTP-Posting-Host: 61.95.202.80
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1106819402 1467 127.0.0.1 (27 Jan 2005
09:50:02 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Thu, 27 Jan 2005 09:50:02 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA02.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08|||Oh, and I was so wanting it in SP2 :(
--
Adrian M.
MCP
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:ZHvXhpJBFHA.1092@.cpmsftngxa10.phx.gbl...
> It is not SP2. Look for it in the upcoming version Yukon of SQL Server and
> Reporting Services.
> --
> | From: anandsagar@.gmail.com (Anand Sagar)
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | Subject: Calendar control in SP2
> | Date: 27 Jan 2005 01:50:02 -0800
> | Organization: http://groups.google.com
> | Lines: 8
> | Message-ID: <3f76a771.0501270150.17a13aea@.posting.google.com>
> | NNTP-Posting-Host: 61.95.202.80
> | Content-Type: text/plain; charset=ISO-8859-1
> | Content-Transfer-Encoding: 8bit
> | X-Trace: posting.google.com 1106819402 1467 127.0.0.1 (27 Jan 2005
> 09:50:02 GMT)
> | X-Complaints-To: groups-abuse@.google.com
> | NNTP-Posting-Date: Thu, 27 Jan 2005 09:50:02 +0000 (UTC)
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA02.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08
> phx.gbl!newsfeed00.sul.t-online.de!t-online.de!news.glorb.com!postnews.goog
> le.com!not-for-mail
> | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:41015
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I know that the SP2 Beta is around for testing. Does anyone know if in
> | this version the Calendar control is made available for use for date
> | parameter input ? I believe the Calendar control is the most needed
> | control in any kind of reporting. Comments Please.
> |
> | thanks,
> | Anand Sagar
> | IN4Velocity
> |
>

Calendar control for date selection ?

Hello,
I have a very simple report...
SELECT AmountDue from Table where DueDate Between @.Dt1 and @.Dt2...
So in above case i will see two parameter in my report. Is there any way so
that i can select dates from calendar control in Reporting Service ?
ThxHi,
Not yet. In SQL RS 2000, the date parameters default to using a text box for
input.
In SQL RS 2005, you will be able to use date pickers for date parameters.
In the meantime, it is possible to write your own ASP-type front ends to
allow users to pick parameters. These parameters can then be passed to your
report using query string parameters. (See RS books online for details...)
"mvp" wrote:
> Hello,
> I have a very simple report...
> SELECT AmountDue from Table where DueDate Between @.Dt1 and @.Dt2...
>
> So in above case i will see two parameter in my report. Is there any way so
> that i can select dates from calendar control in Reporting Service ?
> Thx

Calendar Control

We use the RS 2005 Calendar control for date/time parameters on our
reporting. Unfortunatley we have been running into a performance problem
becuase the Calendar control does a round trip to the server once a date is
chosen. On many of our desktops, this round trip is very evident.
Is there any way to provent the round trip to the server when a date is
chosen?
Thanks,
EricNo way except you need to write a custom code.
Amarnath
"echeeze" wrote:
> We use the RS 2005 Calendar control for date/time parameters on our
> reporting. Unfortunatley we have been running into a performance problem
> becuase the Calendar control does a round trip to the server once a date is
> chosen. On many of our desktops, this round trip is very evident.
> Is there any way to provent the round trip to the server when a date is
> chosen?
> Thanks,
> Eric

Calendar Control

Is there a way to use calendar controls to select dates. I have a
report that requires a start date and an end date as parameters. I
would rather have a date selector then have the user type in the dates.
thanksIt will be in SQL 2005, otherwise you need to put an html page in front of
the report to do this
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<jeffj924@.gmail.com> wrote in message
news:1126119866.508176.4670@.g44g2000cwa.googlegroups.com...
> Is there a way to use calendar controls to select dates. I have a
> report that requires a start date and an end date as parameters. I
> would rather have a date selector then have the user type in the dates.
> thanks
>

calculations in a self-join (was "Query")

Hi all,
If I have a table like the following: (Table Name is A)
ID Date Time DeviceNumber Sum
1 1/12/2004 06:00:00 1 200
2 1/12/2004 08:00:00 1 600
3 1/12/2004 06:00:00 2 300
4 1/12/2004 08:00:00 2 800

If I write the following SQL statment:
"Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.ID=Six.ID
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");

How SQL interpret this statment. I make the same table innered joined be ID field.
By purpose is to calculate the Difference between the Sum of the different hourse of the two devices.
I mean,
'06:00:00 - 08:00:00' 1 400 --> (600-200)
'06:00:00 - 08:00:00' 2 500 --> (800-300)

Please help me ti under stand and solce the problem.Looks look you shoudl be joining on DeviceNumber and not on ID.|||Hi,
I don't understand right now, How SQL pull out data according to the where criteria.
This will help me to design the query.
Here I thought to make two copies of the same table, and substract the Sum of 06:00:00 o'clock from 08:00: o'clock.

Can you help me to correct the statment.
thanks...|||What I meant was:

Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.DeviceNumber=Six.DeviceNumber
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");