Post reply

Showing Off: How I created a report that SENT data to a database.

  • December 20, 2010 at 4:50 am

    #388470

    '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 showiong 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) AS

    IF @DailyCollections IS NOT NULL

    BEGIN

    INSERT INTO tblCreditControlCollections (CollectDate,DailyCollections) VALUES (@CollectDate,@DailyCollections)

    END

    So this accepts two parameters for date and amount and sticks them into a new table

    Then 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 etc

    I'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.

    Merry christmas.

  • December 20, 2010 at 7:20 am

    #1264613

    So, if I understand, you use a report parameter to write to the db before calling your data for the report. Nice.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply