|
Good for you Matt, you have a learning curve in front of you but it will definitely be worth it. When you have the database and app working it will be a natural step to move the report(s) to a proper platform (SSRS or Active etc, NOT Crystal Reports).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Now that you have the data in SQL Server you need to create the Views (these are something like queries). In the view you have the main table and add in all the tables that have foreign key (lookup) connections to it. If you use the view designer SQL will create the link for you.
It is important that when creating a view the view has the same number of records as the transaction table, this tells you that your joins are correct. Then just add in the description fields you want the user to see.
The access app should use the views instead of the tables to see the data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks. I had partially figured that out on my own, before seeing your message. But now I'm even more on the right track.
I received the approval to migrate to SQL Server. So this afternoon I installed an instance on one of our server machines at work (SQL Server 2008 Express). And I can manage it from my dev. machine via SQL Server Management Studio Express. I setup a few views and played with them in Access prior to that. And what you just told me will help when I go back to it.
BTW, I now know why I was told to get away from Access. Displaying a Windows Form using data from a query could take 5-15sec., maybe even 20sec. Since I migrated to SQL Server, the same operations are practically instantaneous. :-P
About the reports. I actually created my own report generation (for weekly and monthly reports). My boss basically said he doesn't require the ability to design the reports himself, but rather he simply needs specific data in preset designs. The weekly and monthly reports, as far as I know, are all he uses. In doing so, I also learned quite a bit about LINQ To Objects today.  I've been interested in learning more about it, though I hadn't taken the time to until today. It made manipulation of the report data so much easier and so much quicker. Then, as requested, I designed the reports in PDF format using iTextSharp.
Is there any general advice you have for using SQL Server? I mean, any tips to maintain good performance, security, etc.?
|
|
|
|
|
Matt U. wrote: I mean, any tips to maintain good performance, security, etc
Well there goes a book or 2 
To be honest your data requirements are probably so low that I doubt performance will be come an issue. It is more important to get your structure correct, only store data once being the basic rule.
SQLServerCentral.com is an excellent resource for articles and tips, I'm not sure how good their basic info is.
I would concentrate on your presentation layer and reports. You'll get the biggest bang for your buck there. User really don't give a rats about the database but will argue over the width and colour of a textbox.
Matt U. wrote: iTextSharp
This is only a PDF generator, not a report generator. You will at some time need to look into proper reports, I use SQL Reporting Services, the option to embed a report in your app without haveing to use the Server component is excellent for small apps.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: User really don't give a rats about the database but will argue over the width and colour of a textbox.
LoL. I wouldn't be surprised if I received some sort of similar feedback. There are quite a few weirdos where I work. xD
Mycroft Holmes wrote: This is only a PDF generator, not a report generator.
I am aware of that fact. What I mean is I create reports based on data, using iTextSharp to lay the data out in a PDF. The reports are basic. For each new "Model" in a list (of records from the current week or month), it pulls each record for that model. Certain fields are then retrieved and laid out in a table in the PDF file. At the end of each model there is a "Summary" section, which simply sums that model's data up, total handled, total repaired, etc. It works just fine.
Does it sound like I should still use SQL Reporting Services? And if so, do you have any good resources for it? Before I created them myself and placing them into PDF I had researched reporting a bit. I couldn't really find any good material that taught the subject well enough for a "beginner". And I didn't find much useful on SQL Reporting Services specifically, as I had seen it mentioned in numerous places.
EDIT----------
Well, I don't know what I was thinking before. Maybe I did not search correctly. I just found numerous tutorials on SQL Reporting Services. Haha. Thanks a lot, I will read into it now.
|
|
|
|
|
Make sure you don't get into the Server side of it, you don't need it yet, concentrate on "local" reports or RDLC these are the embedded version (same report just delivered without the server).
SSRS exports in PDF format, among others.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What tools will I need in order to create SQL Server reports? I can't seem to find that information. I saw "Report" in VS2010's "Add New Item..." dialog box, with the "RDL" extension (or whatever it is; I know I saw it, just can't remember it). The main thing I could not figure out was how to populate the report. I saw the report design view and such. But I can't figure out how to add data controls and bind them to data. My application does not use a generated DataSet. I have a connection class which handles all the retrieval of data from the server.
|
|
|
|
|
Reports is a fairly steep learning curve, after adding the report you then add a data source and design against that. I always create a stored procedure that service the data requirements of the report. This is then usd as the data source.
When you have completed the design you cahnge the extension to RDLC and supply the data from your application by getting the data from the stored proc and passing it and the rdlc to the report viewer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Alright, that sounds reasonable. I will look into it more. Until then I will use my current "report" generation.
|
|
|
|
|
I am seeing a weird behavior in reading a column(of type TIMESTAMP WITH LOCAL TIME ZONE) from Oracle 9i version as compared to othe versions of Oracle (10g and 11g). I have developed a windows client which connects to the foriegn database(ORACLE) and do the transactions(WRITE and READ). Though the value which is written(storage value) is same in all the versions, the value which I read from the database is different wih Oracle 9i. If anyone has faced this issue, please help me resolving this. Thanks in advance.
|
|
|
|
|
..and what's the timezone set to? Checked "Daylight Saving Time"?
Bastard Programmer from Hell 
|
|
|
|
|
No, the Daylight savings time is not checked. The timezone is set to 'US/Canada' "-5:00 Hrs".
|
|
|
|
|
|
|
From SQL Server Management Studio 2008 R2: you can RT-click the database in question, choose Tasks -> Generate Scripts and select the objects you'd like to script out that way. There is an option to save each object to a separate file ("single file per object" option, I believe).
|
|
|
|
|
I have an SQL Server 2005 Express instance which has a database on it and no problems - all the data is visible and everything works.
I have just created another database and in the Visual Studio environment everything is fine and I can 'see' the data.
If I compile the setup and install the program on the same machine the program sees no data at all. It has to be something to do with these User instances I am thinking, yet I have not (knowingly) invoked one.
Both have an identical connection format:
Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & strDatabase & ";Data Source=" & strServer & ";Type System Version=SQL Server 2005;MultipleActiveResultSets=True
In the development environment there are 'jobs' set up but it does not see them in the 'non-development' environment.
I have checked the properties of both databases and I cannot see any differences between them.
I am stumped and could do with someone to come up with the magic solution!! 
|
|
|
|
|
Rephrasing your question to be sure I understand it.
You have a SQL server installed on your local box (no other.)
You have data in tables in that database.
When you use SQL Server Managment Studio you can see the data in the tables.
You have a program which when run in Visual Studio produces data from the tables.
When you run the same program from the command line, on the local box, you do not see the data.
If all of that is true...
Are you catching exceptions and ignoring them?
|
|
|
|
|
Yes pretty much true. The program is a windows exe. No I am not ignoring exceptions, they are written to the Event log - and there are none.
The program runs just fine, but there is no data visible, in other words when I go to the configuration screen which draws its data from the SQL Express database there is no data, yet it is there running inside Visual Studio. It is as if the database is fresh with no data in it.
|
|
|
|
|
Then you are making an assumption which is not true.
The following are possible assumptions. There could be others.
- You are connecting to different databases.
- You are connecting to different servers.
- The data in VS is not commited.
|
|
|
|
|
Sorry what assumption am I making?
There is only one database
There is only one server which is the machine the program, VS and data are on.
The data in VS is not committed because it is already in the database. I am reading it not writing it.
Thanks for your time on this one but I have decided to change over to MySql. I can't be doing with the vagaries of Microsoft.
|
|
|
|
|
TheComputerMan wrote: Sorry what assumption am I making?
It would of course be your assumption, so there is no way for me to know what it is.
You have an impossible situation.
Since computers are deterministic that means that you made an assumption which is wrong.
You can't find assumptions by looking at code (although code might have been written with an assumption in mind.)
|
|
|
|
|
Trying to get the output of this code down to 1 line vs 1886 it currently displays. When I remove the ship date it gives me an error. Can anyone help?
/* This template is an aging report for private pay open balances*/
USE AR
SELECT SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,ARO.INS_CO_AR,
--CONVERT(VARCHAR, CLM.SHIP_DT_CH, 101) AS SHIP_DATE,
--CONVERT(VARCHAR, GETDATE(), 102) AS CUR_DATE,
--DATEDIFF(dd, CLM.SHIP_DT_CH, GETDATE()) AS AGE,
CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=30
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [0-30 DAYS],
CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=31
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=60
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [31-60 DAYS],
CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=61
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=90
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [61-90 DAYS],
CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=91
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=180
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [91-180 DAYS],
CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=181
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=360
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [181-360 DAYS],
CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=361
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [OVER 360 DAYS]
FROM dbo.tblAROPEN AS ARO
INNER JOIN dbo.tblCLMHDR AS CLM
ON ARO.CUS_NUM_AR = CLM.CUSTNUM_CH
AND ARO.APPLY_TO_AR = CLM.fINVNUM_CH
WHERE ARO.INS_CO_AR IN ('0','000')
AND ARO.TOTALAMOUNT >0
GROUP BY ARO.INS_CO_AR, CLM.SHIP_DT_CH
--ORDER BY ARO.TOTALAMOUNT DESC
|
|
|
|
|
Assuming the fields you ultimately want are:
SUM_OF_TOTALAMOUNT
INS_CO_AR
[0-30 DAYS]
[31-60 DAYS]
[61-90 DAYS]
[91-180 DAYS]
[181-360 DAYS]
[OVER 360 DAYS]...
Then, use your query as a sub-select and do a select from there.
SELECT sum(SUM_OF_TOTALAMOUNT) as TotalAmount,
sum([0-30 DAYS]),
sum([31-60 DAYS]),
sum([61-90 DAYS]),
sum([91-180 DAYS]),
sum([181-360 DAYS]),
sum([OVER 360 DAYS]),
INS_CO_AR
from ( your query ) as DT
group by INS_CO_AR
Or something to that effect...
|
|
|
|
|
Do you mean to put all the code I have written into a sub query ????
|
|
|
|
|
I'd probably do it like this. The main difference is making each [xx-yy] field a subquery, and using BETWEEN rather than doubling up the datediff's.
select SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,
[0-30 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) <= 30),
[31-60 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 31 and 60),
[61-90 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 61 and 90),
[91-180 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 91 and 180),
[181-360 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 181 and 360),
[over 360 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) >= 361)
from <your table joins>
group by <your grouping>
order by <your ordering> desc
modified on Tuesday, June 21, 2011 3:23 PM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
