A simple stored procedure, but I need help

[eo]

Hi

CR10. I am fairly new to SP's.
I have a table that shows the Start_Date_Range, End_Date_Range, Week_Number and Year of all weeks, from 1985 till 2015.
I have a report which contains a Date, I need to allocate the Week_Number in which that Date exists to that Date.
I though a good excuse/ reason/ solution to use a Stored Procedure to solve this.

My coding looks like this:

Create Procedure sp_Get_week_no_1
       @ReportDate DateTime

AS
Select WK_Week_Num, 
       WK_Year
From MAGIC.PanAshLive.dbo.Etienne_Weeks
Where @ReportDate >= Wk_Start_Date and 
      @ReportDate <= Wk_End_Date

GO

My idea is to pass the Date from CR to the SP and have it return the applicable Week_Number and Year.

So I add the Stored Procedure to the report (in the same way I would a table or a view). The report then immediately requires me to select a @ReportDate (the option is to select a Date or set value to null - I have tried both)...
Then the report automatically brings up the linking expert. I assume that there will be no link, as this is exactly the reason for the SP...it needs to allocate the Week_Number to the Date.
The report returns no results. What will be the link between the report data and the SP. Is it done in the Select Expert. I have tried Date = {?@ReportDate}

Before I delve any further, do I seem to be on the correct track???



EO
Hertfordshire, England

 

[BCrowe]

Your code looks fine to me.

I would suggest that you run the sp from query analyzer to see what your return is. Verify that the problem isn't with the stored procedure before looking at Crystal. You could accomplish the same thing by accessing the table directly and using record silection criteria and a parameter but then you would be doing all of the filtering on the client side whereas the sp does the filtering on the server side.

BCrowe

 

[synapsevampire]

The fact that it brings up the linking expert states that something is wrong.

There shouldn't be a linking expert unless you are trying to link an SP to another data source within the report, which isn't supported, you should either include the data for the other data source within the SP or use a subreport.

-k

 

[eo]

I have tested the theory in Query Analyser and the SP seems to be working fine. As a test I selected a date in the place of @ReportDate...

Select WK_Week_Num, 
       WK_Year
From MAGIC.PanAshLive.dbo.Etienne_Weeks
Where '2004-01-18 00:00:00' >= Wk_Start_Date and 
      '2004-01-18 00:00:00' <= Wk_End_Date

And it returned the correct result.

I still cannot get this to work...

The CR contains a Date (DateTime) which I assumed must be passed to the SP via the select expert, so that the SP can populate the data with the correct Week numbers.
Both SP and original table are in the same Data source and there are no SQL Expression Fields.

MY select expert looks like

{vw_ash_Capacity_Bdown.CAB_Date} = {?@ReportDate}

As I said, I am new to Stored Procedures, and I am finding it quite hard to make sense of the lot. I cannot find any 'idiots guide' to SP, especially in relation to how CR fits in with it.



EO
Hertfordshire, England

 

[eo]

Hi

Related to what I said above...in a CR training guid it states, "Stored procedures are predefined queries that generally prompt the user to enter a parameter value..."
Does this then mean that the pice of the SP code

@ReportDate DateTime

and later

Where @ReportDate >= Wk_Start_Date and 
      @ReportDate <= Wk_End_Date

implies that the user will enter a date via CR which will then generate the required answer? Is it not possible for CR to automatically pass this date (there could be thousands in a report) to the SP, in the same way that a user would?

I hope I am making sense!


EO
Hertfordshire, England