11. april 2002 - 23:03Der er
9 kommentarer og 1 løsning
Automatisering af rapport-generering i SQL-database
Jeg arbejder et sted, hvor vi har et større kundedatabase-system (POB lavet af Wendia), som jeg laver rapporter på i Crystal Reports. Jeg er nu blevet spurgt om jeg kan lave en rapport, der kan automatiseres med et par variabler, sådan at alle teknikerne får tilsendt en rapport hver dag/uge/måned, der indeholder alle de sager, de fik dagen før, eller forrige uge, eller hvad det nu er. Selve den del, at lave rapporten, er nem nok - men problemet er at få skidtet automatiseret. Jeg har prøvet med compiling af CR og distribution, men eftersom jeg aldrig har prøvet det før, så virker det som om jeg gør noget forkert, i og med, at der kommer en menu op, hvor der skal indtastes nogle data. Derfor er det overhovedet ikke automatiseret. Det er meningen man bare skal kunne schedule, og så kører resten af sig selv ud i al fremtid, med specificerede intervaller, indtil det bliver stoppet. Det er mig ikke vigtigt, om det er Crystal Reports, der behandler dataene, men jeg kan ikke bare hive det ud med SQL-querys i og med, at det giver uoverskuelig listeform af data, som ikke kan bruges for teknikeren. Er der nogen alternativer? Rapporten, eller dataene, skal sendes per mail, per automatik, efter specifikt interval. 1000 tak til enhver, der kan give mig en smule hjælp med den her :-)
That was a mouth full :o( ) No nothing at ALL about crytsal reports so I canty help you on that side. Is it possible to program in CR, make a loop which loops through a "tekniker" table you have in the database? This contains the information you have to enter. If that is possible then you wouldnt have to enter enter any data.
What database are you using? POB/Wendia what are these?
MS SQL Sever has its own scheduler and something called DTS Data Transformation Services. This I am quite sure could automate it all for you. You could get it to loop through a table and INSERT the data for each "tekniker" into say a TEMP table where after it starts Crystal Reports for you to print THAT "Tekniker" info. Then carry on through the rest. Not sure how it would STOP Crystal reports though, but I am sur eit could be done to automate it all.
Anyways, it's unfortunately not quite that easy, as it's the whole process of running it through CR that I have found hard to automate - so even if I manage to get the MS SQL to take out the data I need, the CR will still open up a window, which needs to be manually controlled before it will finish off the work. Another problem is that there are a lot of test-users put into the system, which don't really have a good function, so by such a loop, you will get it to loop through users/technicians that don't really exist. Also because Wendia are so nice in their structure, so they put groups in under the same as users, but users then have a value in parent-column, telling them which group they belong to.
POB (Point of Business) is a sort-of-CRM-solution that works on an SQL server - it's produced by Wendia, and is highly expensive. Unfortunately, their idea of database structure has lead many of their programmers to quit, and makes working with it EXTREMELY difficult. Unfortunately I'm not in a position to tell them to get something else :-\
But again, it all comes down to getting out formatted data, which is sent in a mail, either as a file or as actual text, to all the specified technicians, at a specified time, after a specified interval - and every single bit should run automatic, once you've scheduled the batch-file or whatever you use for running it.
Good idea with the DTS though - I think I'll try it to see if I can get something out of it. Admittingly, I know a LOT more about CR than about SQL...
Test users, you have a field to indicate those to print, IE test users set to false! Why dont you use Access for your reporting, this can to link the tables in the SQL database. It also has a VERY good report gernerator and can send emails too. This could be atomated with Windows scheduler.
Hmmm, cool enough - I'll try that. I never used Access for that kind of stuff - only for small, quickmade databases, which I didn't really need for anything special. But if it can do this, it'll save me big time :-D Thanks a lot. I'll keep the question here open for a couple of days, but of course close it, if I manage to get it to work within that timeframe. Thx again :-)
I tried it, and I got most of it to work - my problem just is that keeps on asking for logon to the server whenever I ask it to start up the program, so the macros don't work, unless somebody manually types in the username and password for logon to the MS SQL. Do you have any solution to that? - And the report generator presents me with a few problems as well, as I don't seem to be able to make formula-fields - meaning that I f.ex. can't make a field that converts the date-field in the database to a true date-field (in the database it's just a 22-digit number, believe it or not) - can you help me with that one as well, I would be most gratious. :-)
If you use a DSN you can choose it to be a trusted connection this uses you login and password. Then you can add you user name in security. (Through Enterprise manager)
Cncerning 22 digit date! Is this actually a date or is it a text field? If it is a text field what is the layout?
It is actually a text field, which holds the American standard-formatting for dates: YYYYMMDD and nothing else - don't ask me why they need 22 chars for that. Anyways, I have no idea how to make formular fields, as I'm used to in Crystal Reports, but that's the least of my worries. Thanks a lot for your help :-) If you want to continue helping me, I would be very happy, but there is of course no obligation. I can only be extremely content and happy with the help you've given me so far :-)
If its anything to do with Access then I have no problems in helping you. You can contact me at terry@santhell.dk
Did you get your connection working?
and thanks for the points :o)
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
