Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
theboyholty
Posting Yak Master
226 Posts |
 Posted - 2010-12-20 : 06:55:47
|
| I'm showing off a bit here but i've just had a bit of a win and I wanted to share it.'twas the week before christmas and Carl in Credit Control decided he wanted the impossible. "What I want, Nick" he began, "is a report into which I can type a monthly cash collections target. I can also add, each day, the previous day's collections and it'll remember the figures i've previously entered. Then I'll get a graph showing my target collections and each day's performance against that target for a whole month."A sharp intake of breath later and I said, "Well that can't really be done" but then I had an inkling of an impending brainwave, "but leave it with me, i'll see what I can do"The problem, as i'm sure you all know, is that a report is just that - A REPORT. Data goes FROM the database INTO your report so users can VIEW the data via the report - its the most basic premise of reporting. You can't make a report remember parameters entered yesterday, or last week. Or can you?Well yes you can and here's how.In a little used database on a forgotten server, I created a new table - tblCreditControlCollections.Then I created this SP:CREATE PROCEDURE SPCCDailyCollections (@CollectDate, @DailyCollections) ASIF @DailyCollections IS NOT NULLBEGININSERT INTO tblCreditControlCollections (CollectDate, DailyCollections) VALUES (@CollectDate, @DailyCollections)ENDSo this accepts two parameters for date and amount and sticks them into a new tableThen in the report, I put this SP into a dataset. No-one says you can't use an insert or update SP in a report. Well they might, but they'd be wrong. There's no reason why a query used in a SQL Report has to be SELECT FROM WHERE etcI've also got another regular SP which reads from the table and displays it in a line chart.So Carl in Credit Control runs his report, selects a date from the month (drop down list) and enters a figure. This instantly shows up in the chart alongside all the other figures he entered in previous the days. Carl's happy, i'm happy and hopefully, you've all learnt something so you're all happy.I wouldn't suggest using this techniques to enable users to amend data from a live database that they don't like the look of in a report, and this is a pretty basic example, but it shows that nothing is impossible - apart from things that REALLY ARE impossible but I'll tackle those next week.Merry christmas---------------------------------------------------------------------------------http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club |
|
|
|
|
|
|
|
Creating reports that SEND data to a Database