Date format - Parameter/Field

Hi,

I have some difficulties to format a Date value in my reporting service.

As you know you can use this kind of expression:

=DateTime.Parse(Parameters!ReportingDate.Value).ToString("dd/MM/yyyy")

But it does not work, and when I just want to use the format property of the textbox content such as "dd/MM/yyyy", it displays "dd/MM/yyyy", not the real formatted data, this property works for "=Today()" but not for parameters and fields. What is the diffrence?

Have you an idea to format a Parameter/Field of Date type such as dd/MM/yyyy?

Many thanks!

Vin,

You can make your parameter of data type DateTime, this would eliminate the need to parse a string. If not, try

Format(CDate(Parameters!Report_Parameter_0.Value),"d")

Ham

|||

With your code, I get this error:

Cast from string "20061222" to type 'Date' is not valid.

Strange because the string has a correct date format :S

|||

Vin,

What's your Parameter data type?

Ham

|||

Also,

the value that is stored in this parameter.

Ham

|||

The parameter is an integer, it s the reason why I m using now:

=DateTime.Parse(Parameters!ReportingDate.Value.ToString())

The problem is the value of ReportingDate is "20061222" and to work as DateTime you have to have "2006/12/22", after you can display it as you want with ToString function.

I would like to have this format 2006/12/22 with / in fact, but properly...

|||

Vins,

That's a tough one, if you're using integer data type for Datetime. I guess my approach would be to change to integer into a string value

LEFT(Parameters!ReportingDate.Value.ToString,4) & "/" & Mid(Parameters!ReportingDate.Value.ToString,5,2) & "/" & RIGHT(Fields!SSN.Value,2)

The catch here is that you'll have to return always 4 digit years, 2 digit months and 2 digit days, instead of 2007/1/1 you would need 2007/01/01

Ham

|||Thanks Ham, it s working very well ;-)

date design issue in my cube

I have a simple fact_service table: service_type, start_date, end_date, service_charge. If the service is not done yet, the end_date is null. I can't process the cube because I don't have a null record in my dim_date dimension.

I tried to create a vew v_fact_service_by_end_date, that processes the cube but it doesn't give me what I needed. I would end up with one time dim and two measures: charge_amt and charge_amt_from_view.

I need to have two time_dim (roll play) and one measure (charge amt) so that I can see the start_date and end_date of each service_type (to determine how long it takes to perform the service).

How should I model this? Thanks for any input.

This is a relatively common issue. You have what we refer to as an accumulating snapshot fact table wherein some dimensions are not populated at the time a transaction is first loaded. One question, do you have a transaction identifier? Is that the Service_Type reference?

Regarding the date, is this a date time field or an integer pointer to a Date dimension table? If you had a Date dimension table such as below, you could simply add a NULL date and set a reference to that record (DateID = -1):

Code Snippet

create table DateDimension (

DateID int not null identity(1,1),

Date datetime null,

....

)

alter table DateDimension add

constraint PK_DateDimension primary key (dateid),

constraint AK_DateDimension unique (date)

go

set identity_insert DateDimension ON

insert into DateDimension (DateID, Date) values(-1, NULL)

set identity_insert DateDimension OFF

go

If you aren't using a Date dimension table, your other option is to use the UNKNOWN MEMBER functionality in SSAS. You have to configure an unknown member in the dimension and also configure how you want your cube to handle missing references. Check out Books Online topic "Defining the Unknown Member and Null Processing Properties" for more details.

Hope that helps,
Bryan

|||That worked! I added a dummy record of "-1" in the time_dim. Thanks much.

date design issue in my cube

I have a simple fact_service table: service_type, start_date, end_date, service_charge. If the service is not done yet, the end_date is null. I can't process the cube because I don't have a null record in my dim_date dimension.

I tried to create a vew v_fact_service_by_end_date, that processes the cube but it doesn't give me what I needed. I would end up with one time dim and two measures: charge_amt and charge_amt_from_view.

I need to have two time_dim (roll play) and one measure (charge amt) so that I can see the start_date and end_date of each service_type (to determine how long it takes to perform the service).

How should I model this? Thanks for any input.

This is a relatively common issue. You have what we refer to as an accumulating snapshot fact table wherein some dimensions are not populated at the time a transaction is first loaded. One question, do you have a transaction identifier? Is that the Service_Type reference?

Regarding the date, is this a date time field or an integer pointer to a Date dimension table? If you had a Date dimension table such as below, you could simply add a NULL date and set a reference to that record (DateID = -1):

Code Snippet

create table DateDimension (

DateID int not null identity(1,1),

Date datetime null,

....

)

alter table DateDimension add

constraint PK_DateDimension primary key (dateid),

constraint AK_DateDimension unique (date)

go

set identity_insert DateDimension ON

insert into DateDimension (DateID, Date) values(-1, NULL)

set identity_insert DateDimension OFF

go

If you aren't using a Date dimension table, your other option is to use the UNKNOWN MEMBER functionality in SSAS. You have to configure an unknown member in the dimension and also configure how you want your cube to handle missing references. Check out Books Online topic "Defining the Unknown Member and Null Processing Properties" for more details.

Hope that helps,
Bryan

|||That worked! I added a dummy record of "-1" in the time_dim. Thanks much.

Date between 1/1/0001 to 31/12/9999 possible in Sql server

I installed SQL Server Express and SQL Server manager, then tried to start manager. SQL Service is running, but SQL Manager attempt to connect to SQL Server instance times-out even though I correctly identify instance as SQLEXPRESS? Everything seems to be normal - both SQL Server and Manager are on a single Windows XP home machine. Any suggestions. ThanksHi,
Try using .\SQLEXPRESS as the instance name...

cheers,
Paul June A. Domag|||Great - It Works! Thanks|||After reading this thread and another one I've tried entering the following in express manager and I still can't connect:

.\MSSQLSERVER
(local)\MSSQLSERVER

I am running SQL Server Express June CTP on a Win2K3 box. SQL Express Manager is also running on the same Win2K3 box.

I have opened all the network protocols for SQL Server Express, I've also enabled the SQL Server browser. There are no firewalls involved. This is probably the most insecure setup, yet I still cannot connect locally

Any ideas?

I followed all the instructions here:
http://download.microsoft.com/download/a/6/3/a63ec922-913e-4228-93e3-970abff3fd66/ReadmeSQLExpMgr.htm
and here
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Nothing seems to work.
|||Hi,
Actually its .\SQLServer not .\MSSQLSERVER.
Have you tried it? got any versions installed before the June CTP?
cheers,
Paul June A. Domag|||Unfortunately that didn't work either.
All I have installed is June CTP on a fresh install of Win2K3 Standard.
In the SQL Server Configuration Manager under "name" it says: SQL Server (MSSQLSERVER).
This would indicate the name of the SQL server is "MSSQLSERVER" correct? I chose "default" instance during the install and that is the name it gave itself.
This is getting frustrating... I thought programming with MS stuff would be easy. I could have connected to MySQL a hundred different ways by now.
|||Just a two cents worth general comment - the MS beta's tend to have problems that the production versions don't - having used SQL Server from the time it was Sybase Server to date I can say it is great software that occassionally suffers from growing pains. PS|||Yeah, I don't expect Beta software to be 100% functional but I do expect the basic functionality to be working and straightforward. Connecting to the server shouldn't be this difficult. Especially if you consider the target market for this product.
|||Hi,
Oops, I guess I had a typo. Its .\SqlExpress, If you choose default instance...
cheers,
Paul June A. Domag|||After all that I accidently stumbled upon a solution.
The only thing that works for my installation is just plain ".\" (no quotes) and nothing else. Go figure. Hopefully this post saves someone else a lot of time.
|||Hi,
Weird... Could you report a bug in Product Feedback? So that Microsoft would be aware of this...

cheers,
Paul June A. Domag|||thanks a lot this helped!!!!!

Paul Domag wrote:

Hi,
Try using .\SQLEXPRESS as the instance name...

cheers,
Paul June A. Domag

|||Thanks!!!! I have been pulling my hair out all night trying to figure out why I couldn't connect. That did the trick. Why doesn't MS tell you about these important details.|||

I'm getting error like

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I want to set it bet 1/1/0001 to 31/12/9999. how to do this?

So do u have any solution over it pls.

I think u have solution over it because I have seen same problem u had .

At last Happy New Year 2006 at this first meet ing with u

Balasaheb

|||Hi,

There are only two datatypes that could handle date and time values in sqlserver. These are DateTime and SmallDateTime. Both of these types cold not handle data between1/1/0001 to 31/12/9999. But you can store these dates as a varchar and just parse the dates to calculate them in your program...

cheers,

Paul June A. Domag