Create a datetime dimension table with start and end dates

Hi

I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like

01/01/2004 00:01:00

01/01/2004 00:02:00

01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00

I will use this time dimension in bussiness objects.

please provide me with the SQL sript to do this.

CREATE TABLE DimensionTable

(

IdCol INT IDENTITY(1,1),

Timecol DATETIME

)

GO

DECLARE @.Startdate DATETIME

DECLARE @.Enddate DATETIME

SET @.Startdate = '20040101'

SET @.Enddate = '20080101'

WHILE @.Startdate < @.Enddate

BEGIN

INSERT INTO DimensionTable

(

TimeCol

)

SELECT @.Startdate

SET @.Startdate = DATEADD(mi,1,@.Startdate)

END

That could take some time :-)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot

CR8.5 and VB6 - I dont want it to create the form!

Every time I add a new Crystal Report to my VB6 project it used to ask if I wanted to add a form with the viewer control on it automatically (it also asked something else, but I can't remember what). I got sick of hitting "No" then "OK" so I checked the "don't ask me this question again" style box.

Now, every time I add a report it also adds a form to my project with the viewer control. I don't want it to do this, as it's annoying removing the useless form and it is screwing with my source control. And it does it even though when i checked the "don't ask me... " i told it not to create the form.

So I've checked all the settings I can find and I can't find anything to stop it happening. Does anyone have a quick fix to this before I go trawling through the registry to find a solution?update - i trawled the registry, found some likely candidates, but none of them worked. It's real frustrating, so any help will be appreciated, but I can live with it (I've lived with worse)

CR from only code(VS 2005)

Hey gurus

I need to create a crystal report entirely from code

i have a form where a customer will add table(s) to a list box, depending on the added tables, a list of fields applicable to the respective table can be added to another list. On selection of each of these fields, values may be entered so as to filter the data displayed on the report.

From each of the availble fields that can be "Parametized", a group can be created, with ascending/descending order

Finally summaries can be added such as the sum of all amounts(total,VAT etc) to the end of the report
Report options like drill-down will need to be able to be set dynamically also.

so far everything above here is basicly how to generate my SQL statement, which won'y be that difficult.

But i have no idea how to make my report document show information the way i have described above.

left list < > right list (Panel with combobox, textbox , checkbox(enable/disable))

tables,field, groups are added/removed as depicted above
fields and groups have a panel next to each rightlistbox where parameters can be set like (< = > between like <> asc desc)

at the bottom of the form there are 3 buttons
Preview report data --> shows datagrid of Data retrieved via generated SQL statement
Preview Report --> shows a itempanel with items for each field/group/summary field
Create report - Creates crystal report and shows in report viewer on a tab created before the report begins creation

At this point, i am not very close to being far from completion
any help will be greatly appreciated
Thanks
Jsee attached file of GUI so you can get a better idea of what i am trying to do, sorry bout bad quality 100kb is not alot but sufficient|||I have made some progress on this

The code below shows the population of a dataset and subsequently binding it to a report, then showing that report in a report viewer control on a new tab created here also.

The report is completely blank. There is however data in the TRDS dataset



Private Sub MakeReport()

Dim TRDS As New DataSet

Dim ConString As String = ""

Dim CON As OleDb.OleDbConnection

Dim DA As OleDb.OleDbDataAdapter

Dim NewTabPanel As New DevComponents.DotNetBar.TabControlPanel

Dim RptUC As New ReportOnTab ' Usercontrol with crystal reportviewer on a panel

'Get Data

Try

TRDS = New Report1DataSet

If My.Settings.User_Claims_ManagementConnection_String = "" Then

ConString = My.Settings.Claims_Management_SystemConnectionString.ToString

Else

ConString = My.Settings.User_Claims_ManagementConnection_String.ToString

End If

CON = New OleDb.OleDbConnection(ConString)

DA = New OleDb.OleDbDataAdapter(FullSQL, CON)

'Fill the dataset

DA.Fill(TRDS, "Client")

'Make report on new tab

NewTabPanel.Name = "NewTab_Panel"

NewTabPanel.Text = "This is the new reports panel"

'Report creation

Report = New Report1

'Settings for the report

'Set datasource to the generated dataset

Report.SetDataSource(TRDS)

'At this point there is a blank report, with a dataset attached to it containing, one table and rows filtered by where in FULLSQL

'Set groups

'Add fields that will be shown to detail section

'Add summaries

'Apply formatting

'End of report creation

'Create tab on tab control

ReportTab.CreateTab("Name of this report", -1)

'Attach usercontrol to tab panel and make dock fill

ReportTab.Tabs.Item(ReportTab.Tabs.Count - 1).AttachedControl.Controls.Add(RptUC)

ReportTab.Tabs.Item(ReportTab.Tabs.Count - 1).AttachedControl.Controls.Item(0).Dock = DockStyle.Fill

'Reset the report creation form

ReportDefinitionsClear()

'Select the newly created tab

ReportTab.SelectedTab = ReportTab.Tabs.Item(ReportTab.Tabs.Count - 1)

'Set source of the usercontrol report viewer to this report

RptUC.ReportViewer.ReportSource = Report

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub



now the FULLSQL variable is rather dynamic and is generated through use of the above picture's gui, thats 2000 lines, shud i post that to?

anyway, it works, all i need to do now is...

1.! create fields from the SELECT statement on the report

2.! create groups via the ORDERBY statement on the report

3.! Finally add any other summaries,special fields, and so on to the report

My question is how do i achieve such a feat,

How do i add groups via code to the detail section?
How do i add fields via code to my blank reports detail section?
Is it even possible?

So far have discovered that most things within the report1 class are readonly.

I have looked everywhere online, searched for 2 days and found nothing like this

Please help
Thanks
J

Countries & Cities Tables

I need to have tables of world countries and cities to use in a user registration form, I searched the Internet for scripts,xml,excel files for countries & cities lists but I couldn't find. Can anybody tell me where to get them from?

Hello my friend,

Let me introduce you to a website I used to worked on that has plenty of this information: -

http://www.wshtravel.com/travel_guides

Kind regards

Scotty

|||

Thanks but the link is not working

|||

I think that if you will try to do something like this on your page it will be very slow and very big, so maybe just use countries. You can try to get countries fromwww.usps.com just try to send pckage to international destination and you will get drop down list with countries to which USPS delivers packages. You can also trywww.UPS.com maybe they have also cites?

Thanks

JPazgier