Rolling date parameters on subscription

Question

Rolling date parameters on subscription

Paul.Mitchell.

Ten Centuries

Points: 1131
More actions
November 1, 2005 at 5:32 am

#110680

I have a report which I need to send to a customer every weekday using a subscription, but without having to re-set report parameters each day.
The report contains satellite 'phone call records; we receive and process the data daily and pass the information on to the end-user.
The data has built up for the first month and will continue to build. However, I don't want to send him every call record every time, but use 'rolling' date parameters instead. The most useful approach would be to send the full month to date up to the last day of the month. At the beginning of the new month I also want to include the new month calls plus the old month up to a certain date. After that date, I want to only include the new month.
For example...
To 31st October - all October records to date.
1st - 10th November - all October records, plus Novemer records to date
11th November - only November records.
Hope that makes sense? Can anyone help with the best approach to solve this?
Thanks!

Viewing 11 posts - 1 through 10 (of 10 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Why not simply add another column that denotes SENT to Customer. Make it a BIT flag and then everytime you send the report update the BIT to 1. This way you only need to pull items where the BIT = 0....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Paul.Mitchell. Ten Centuries Points: 1131 More actions · Answer 2

The customers' requirements are that they can see these daily records building up in a report to the end of the month.

If I did as you suggest, I would effectively be only sending them the latest day of records received with no summary / overview. They like to see their usage trend building-up over the month. Sometimes the data may come in for different service providers later than for others; this is part of the reason I want to keep showing the prior month for up to 10 days - the data can be received up to 8 days after the call date.

Thanks for the reply though. Any ideas appreciated.

rmcknight SSC Eights! Points: 918 More actions · Answer 3

I think AJ has it. Use the current day to determine which unset 'bits' get set. If day of month is less than 10, then set unset bits previous to last month. If over 10, include last month.

Paul.Mitchell. Ten Centuries Points: 1131 More actions · Answer 4

Thanks for your input, but I still think it slightly missed the point of what I want to achieve.

I need to be able to set the reports' parameters to say something along the following lines...

If today is <= 10th of the month, then the range of dates to include should be from 1st of last month to today; if today > 10th of the month, then the date range should be 1st of this month to today.

I'm looking for advice as to how or where this should be implemented - should I push it back into the query, try and set up in report parameters, filters, etc.?

Thanks again, all input appreciated

rmcknight SSC Eights! Points: 918 More actions · Answer 5

What I (and I think AJ) were thinking is you would set the data prior to running the report. Your report would report on data where the 'bit' field was still false.

If < 10th of month - Update reportd data, set bit to true where report_data_date < lastmonth and bit = false.

If > 10th of month - update report data, set bit to true where report_data_date < this month and bit = false.

Run report on bit = false.

Paul.Mitchell. Ten Centuries Points: 1131 More actions · Answer 6

I don't know what you mean by "set the data" - sorry, I'm relatively new at all this.

The table I'm working with gets updated most days with new data; I don't want to have to do anything here, the subscription needs to run daily without me having to manulaly do anything with the report or selection criteria . I just need somewhere to put the logic to say which records to send in the report, based on what day of the month it is today.

rmcknight SSC Eights! Points: 918 More actions · Answer 7

Sorry, brain fart. Forgot I was in reporting services and not DTS.

Just comming on RS myself. Can you run script to set the report parameter prior calling the report or does the logic have to be in the sql of the report?

Paul.Mitchell. Ten Centuries Points: 1131 More actions · Answer 8

"brain fart"

'Cos I'm from a Crystal background (and an accountant - not a programmer) habit would have me want to put the logic into the report selection criteria, maybe with an IIF statement somewhere.

However, as I learn more about RS, I realise I should put the logic in the SQL query.

But mostly I'm just stuck; all the BOL stuff / help material don't provide many worked examples for this kind of thing.

Ian Yates SSCoach Points: 19738 More actions · Answer 9

I too am from a Crystal background and would say that you could have a long query that reporting services runs, or have it call a stored proc. You could then let the stored proc use the datepart function to pull out the day of the month and modify the start & end dates appropriately either by concatenating strings of year + month + day or using dateAdd, etc.

Paul.Mitchell. Ten Centuries Points: 1131 More actions · Answer 10

Sorted! For anyone bothered...

Report parameter, default value, non-queried, called @START:

=IIF(DATEPART("d",today()) >= "10",DateSerial(Year(today()), Month(today()), 1) ,DateSerial(Year(today()), Month(now)-1, 1))

And query parameter of Date <= @START.

Works a treat.