Passing Date Parameters from Crystal Reports 1

[lynchg]

I have a Crystal report where I need to produce counts of service requests per month for a 13 month period. I have a rather complicated solution that will produce the data based on the current date (using getdate()). Now the users would like to be able to select the end date for the report. This might make my current method unworkable because I am using the getdate() function multiple times in a union view that is nested within a view that sums the "hits" per month accumulated by each constituent select within the union.

Here is a sample of my union and the totaling view:

SELECT RQST_ID, CRTE_DATE, 1 AS Mon_13,
0 AS Mon_12, 0 AS Mon_11, 0 AS Mon_10,
0 AS Mon_9, 0 AS Mon_8, 0 AS Mon_7,
0 AS Mon_6, 0 AS Mon_5, 0 AS Mon_4,
0 AS Mon_3, 0 AS Mon_2, 0 AS Mon_1,
0 AS Mon_0
FROM dbo.RQST
WHERE CRTE_DATE between DATEADD(mm, DATEDIFF(mm,0,dateadd(m,-13,getdate())),0)
and dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,dateadd(m,-12,getdate())),0))
UNION
SELECT RQST_ID, CRTE_DATE,0,1,0,0,0,0,0,0,0,0,0,0,0,0,
FROM dbo.RQST
WHERE R.CRTE_DATE between DATEADD(mm, DATEDIFF(mm,0,dateadd(m,-12,getdate())),0)
and dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,dateadd(m,-11,getdate())),0))

The where clauses capture all of the requests with create dates that fall within each of the months, starting with 13 months ago, then 12 months ago, then 11 months, etc. I pasted just 2 of the 13 unioned selects. This view produces a result with a "1" under the appropriate month column for each request created during that month and zeros everywhere else.

Then I sum those columns to produce the monthly counts in the following view:

SELECT sum(Mon_13) as m_13, sum(Mon_12) as m_12,
sum(Mon_11) as m_11, sum(Mon_10) as m_10,
sum(Mon_9) as m_9, sum(Mon_8) as m_8,
sum(Mon_7) as m_7, sum(Mon_6) as m_6,
sum(Mon_5) as m_5, sum(Mon_4) as m_4,
sum(Mon_3) as m_3, sum(Mon_2) as m_2,
sum(Mon_1) as m_1, sum(Mon_0) as m_0,
FROM dbo.vw_MyHugeUnionView

My problem is that I need to come up with a solution that would replace the getdate() with the end date parameter that the user selects when opening the report. I don't see how this could be done with my current approach with nested views. Is it possible to load all of this SQL into a stored procedure, pass the end date parameter, and use that parameter in those where clauses?

I am open to any ideas that make sense, most ideas would probably make a lot more sense than my "solution". One other piece of information is that this is, and will always be a relatively small database, just 8 tables with 30k - 50k records in the largest tables, so performance isn't the most critical factor. I realize this is a very long post, thanks for any feedback.

I am using SQL Server 8 and Crystal 11 Server, both installed on the same server.

 

[gmmastros]

Can you show some sample data with expected results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lynchg]

I hope this displays properly.

Result of union:
rqst_id mon_13 mon_12 mon_11 mon_10 .......

1111 1 0 0 0
1112 1 0 0 0
1113 0 1 0 0
1114 0 0 1 0
1115 0 0 1 0
1116 0 0 0 1
1117 0 0 0 1
1118 0 0 0 1


Result of summary view:

mon_13 mon_12 mon_11 mon_10 .......
2 1 2 3

 

[lynchg]

As expected, the values didn't line up well under the column headers.

 

[gmmastros]

Can you show some sample data from the RQST table. It appears as though only the id and the date are used in the query, so that would be enough info.

BTW, to get things to line up, use the tt tag, like this...

[tt][ignore][tt]This is text.[/tt][/ignore][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lynchg]

I removed the other fields to simplify the posting. They aren't really relevant to the problem of retrieving the counts of service requests for the previous 13 months. Those fields are the work team who is assigned to work on the service request, and the type of request (i.e. broke/fix, production support, etc.).

 

[gmmastros]

Instead of a view, I recommend using a stored procedure to return the data. A view can ONLY be a select statement. With a stored procedure, you can have multiple statments and much more flexibility.

I know you want 13 months of data, but I am only going to show you how to get a couple of them. The process would be the same for the rest.

Create Procedure GetReportData
  @EndDate DateTime
AS
SET NOCOUNT ON

Declare @EndMonth Int
Set @EndMonth = DateDiff(Month, 0, @EndDate)

Select Max(CurrentMonth) As CurrentMonth,
       Max([1 Month Ago]) As [1 Month Ago],
       Max([2 Months Ago]) As [2 Months Ago]
From   (
       Select Sum(Case When @EndMonth - DateDiff(Month, 0, CRTE_DATE) = 0 Then 1 Else 0 End) As CurrentMonth,
              Sum(Case When @EndMonth - DateDiff(Month, 0, CRTE_DATE) = 1 Then 1 Else 0 End) As [1 Month Ago],
              Sum(Case When @EndMonth - DateDiff(Month, 0, CRTE_DATE) = 2 Then 1 Else 0 End) As [2 Months Ago]
       From   RQST
       Group By DateDiff(Month, 0, CRTE_DATE)
       ) As A

This may not be perfect, but hopefully will point you in the right direction. Good Luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lynchg]

George, I didn't need the Max(CurrentMonth), etc. and Group By clause, I am only interested in the monthly sums. But your solution works like a charm, I can't thank you enough.

I assume that I can use the SP as the datasource for my report, prompt the user for the end date, and the report will be populated with the results of the SP.

Thanks again!

 

[gmmastros]

Yes.

I know that Crystal supports stored procedure, but I don't use crystal so I can't really advise on how to do that part. I'm glad that my suggestion has worked out for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[philhege]

The Crystal designer should expose the stored procedure parameters to you, so you can prompt for user input.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[lynchg]

Yes, that works, thanks.