Using parameters in stored procedures

[goodspeed7]

Hi all!

In my report I am calling a stored procedure to supply the data. When the report is run the user gets to enter in the 'to' and 'from' dates and the report filters all dates not in that range.

The problem is that I am doing calculations in the stored procedure and all the unfiltered data is included in the calculations. I tried sticking parameters in the stored procedure and then making them the same name as the parameters in the report but that didn’t work.


This may be simple i just have no clue, thanks in advance

-Bob

 

[EFREE]

no that should work, just make the parameters in your stored procedure the same as the parameters in your report and it will not include the other data in the calculations.

 

[goodspeed7]

No i said above that it didnt work, is there another way such as calling a stored procedure once all the data has been pulled and filtered?

 

[Catadmin]

The problem still might be the way you're passing the params to the stored procedure. Are you sure your calculations are taking those dates and using them in the calculations' where clauses? It sounds to me that's where the issue is.

And, no, there's no way to filter out your calculations once the SQL Server has already done them. There is a way to filter out plan data on the Report (go to the properties of your Table/Matrix and there should be a filters tab), but the problem you're asking about happens on the SQL side of the equation, not the report side, so filtering your data wouldn't do you any good.

Unless you code your calculations in the Report properties instead of on the SQL Server via the stored procedure.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[goodspeed7]

The SQL query works fine when i define the parameters. It is when i try to pass them in through the report that it doesnt work, it will filter the data correctly but will have the calculations for all the data in the dataset.

I see what you are saying about the two different sides, where would one go to code the equations in the report properties so it can be called once the data has been filtered?

 

[Catadmin]

Go to Tools -> Report Properties -> Code. You'll have to do your code in Visual Basic, but that's the spot to do any report calculations needed.

BTW, just to double check, you executed your SP in QA with the input parameters in the code and it calculates only on the data with the inputed dates, but doesn't when you call the SP via the report?

If this is true, you have something set up wrong in your Report Groups. I've had a similar problem pulling straight data from a table into a report. I created a group once and it repeated the first line of each group for each record in the group. So if I have a record with the value of 'A' for the first record, and there are 3 records in that particular grouping, it would repeat 'A' three times.

Stands to reason the same thing might be happening to your calculations. Try removing the groupings and see what you get.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???