Need help with reporting a range

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.

All Forums

Development Tools

Reporting Services Development

Need help with reporting a range

Author

Topic

kristilee
Starting Member

1 Post

Posted - 2010-03-17 : 21:13:29

Hi there,

I have a report that has parameters based on a couple of different time parameters because my folks dont like to put in just a start and end date. They like to see Current Month, Last Month, Next Month and also when they feel like it put in Adhoc dates. I need to be able to show dates on the final report of the information being run based on the parameter they chose but I am not sure how to go about doing that. Also, Is there a better way to do this type of thing within SSRS? I am new to all this stuff and using alot of recycled code from my predecessors.
The code is below and any guidance would be appreciated:

select * from Report
WHERE (:DateRunOption <> 'CM' OR TO_CHAR(NG00_RECVD_PKG_DT, 'Month YYYY') = TO_CHAR(CURRENT_DATE, 'Month YYYY') OR TO_CHAR(NG11_SNT_TO_MBRSHP_DT, 'Month YYYY') = TO_CHAR(CURRENT_DATE, 'Month YYYY'))
 AND (:DateRunOption <> 'LM' OR TO_CHAR(NG00_RECVD_PKG_DT, 'Month YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'Month YYYY')OR TO_CHAR(NG11_SNT_TO_MBRSHP_DT, 'Month YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'Month YYYY'))
 AND (:DateRunOption <> 'NX' OR TO_CHAR(NG00_RECVD_PKG_DT, 'Month YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, 1), 'Month YYYY') OR TO_CHAR(NG11_SNT_TO_MBRSHP_DT, 'Month YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, 1), 'Month YYYY'))
  AND (:DateRunOption <> 'AD' OR NG00_RECVD_PKG_DT >= :BeginDateFrom OR NG11_SNT_TO_MBRSHP_DT>= :BeginDateFrom)
  AND (:DateRunOption <> 'AD' OR NG00_RECVD_PKG_DT<= :BeginDateTo OR NG11_SNT_TO_MBRSHP_DT<= :BeginDateTo)

klsrao
Starting Member

4 Posts

Posted - 2010-04-13 : 23:57:10

What you need to do is take the parameters as is. if nothing is entered, take null.
In the report SP, check if the parameter date is null, then display CM / LM / NM. else display dates.
This way, you will be using only one report to display both options.

Hope this will help. Let me know if you need more details.

Learn how not to speak negative.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources