Stored Procedures for use with Crystal Reports 2008

I would like to creat a stored procedure and place the values in my report. I COULD create 100 different procedures and achieve what I want but that seems like a waste of time and space.

I am trying to find a sum of groups and would like to use each sum in the report.  My Stored Procedure works fine but when I use it in Crystal, only the first select statement is displayed.  I was hoping to get a list of sums under the Stored Procedure so I could just drag and drop the fields into the report.  Is there an easy way to do this so I don't have (literally) 500 individual Stored Procedures just to create one report? I know I can just create groups but with the format of my report I just cant get the numbers to add up properly!

Here is my Stored Procedure below.  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ds_rgNetTotals]
@StoreID int, 
@StartDate datetime, 
@EndDate datetime
 
AS
SELECT sum([Extension] + [DISCOUNT_AMOUNT]) as rgNetTotal_1
      
  FROM [Database].[dbo].[CHECK_ITEM_RECORD]
  WHERE STOREid = @StoreID AND
  CHECK_FILE_DATE between @StartDate and @EndDate
  and flag_voided is null
  and REPORT_GROUPID = 1
  
SELECT sum([Extension] + [DISCOUNT_AMOUNT]) as rgNetTotal_1
      
  FROM [Database].[dbo].[CHECK_ITEM_RECORD]
  WHERE STOREid = @StoreID AND
  CHECK_FILE_DATE between @StartDate and @EndDate
  and flag_voided is null
  and REPORT_GROUPID = 2
  
SELECT sum([Extension] + [DISCOUNT_AMOUNT]) as rgNetTotal_1
      
  FROM [Database].[dbo].[CHECK_ITEM_RECORD]
  WHERE STOREid = @StoreID AND
  CHECK_FILE_DATE between @StartDate and @EndDate
  and flag_voided is null
  and REPORT_GROUPID = 3
  
SELECT sum([Extension] + [DISCOUNT_AMOUNT]) as rgNetTotal_1
      
  FROM [Database].[dbo].[CHECK_ITEM_RECORD]
  WHERE STOREid = @StoreID AND
  CHECK_FILE_DATE between @StartDate and @EndDate
  and flag_voided is null
  and REPORT_GROUPID = 4

Open in new window

mattkovoAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
yeah, sorry about that, need a group by... There is an aggregate function SUM() so we need to aggregate against something. Fine if it is the only thing in the select, but, if there are other columns then they have to be either an aggregated value, or, part of the group by... For our purposes, we do want the total for each report_groupid so, we need to group by that column to get the corresponding total.

  INSERT #tbl_totals (subtotal_group,subtotal_value)
  SELECT report_groupid, sum([Extension] + [DISCOUNT_AMOUNT]) as rgNetTotal_1
  FROM [BEEFS].[dbo].[CHECK_ITEM_RECORD]
  WHERE STOREid = @StoreID AND
  CHECK_FILE_DATE between @StartDate and @EndDate
  and flag_voided is null
  GROUP BY report_groupid
0
 
Mark WillsTopic AdvisorCommented:
Create a temp table inside the stored procedure, and populate it using the summary total, and return a table of summaries to Crystal...

e.g.

create table #tbl_totals (id int identity, subtotal_group int, subtotal_value decimal (18,5))

insert #tbl_totals (subtotal_group,subtotal_value)
SELECT REPORT_GROUPID, sum([Extension] + [DISCOUNT_AMOUNT]) as rgNetTotal_1
FROM [Database].[dbo].[CHECK_ITEM_RECORD]
WHERE STOREid = @StoreID
AND CHECK_FILE_DATE between @StartDate and @EndDate
AND flag_voided is null
AND REPORT_GROUPID = 3    -- maybe not needed anymore


then down the bottom,

select * from #tbl_totals


0
 
mattkovoAuthor Commented:
I'm not quite sure how to do this.  Do I just modify the Stored Procedure and put your statement at the top?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Mark WillsTopic AdvisorCommented:
Pretty much... have a look at :

ALTER PROCEDURE [dbo].[ds_rgNetTotals]
@StoreID int, 
@StartDate datetime, 
@EndDate datetime
 
AS
 
  CREATE table #tbl_totals (id int identity, subtotal_group int, subtotal_value decimal (18,5))
 
  INSERT #tbl_totals (subtotal_group,subtotal_value)
  SELECT report_groupid, sum([Extension] + [DISCOUNT_AMOUNT]) as rgNetTotal_1
  FROM [Database].[dbo].[CHECK_ITEM_RECORD]
  WHERE STOREid = @StoreID AND
  CHECK_FILE_DATE between @StartDate and @EndDate
  and flag_voided is null
 
  select * from #tbl_totals
 
END
GO

Open in new window

0
 
mattkovoAuthor Commented:
Alright.  Getting closer so I tried putting this in an actual database.  Here is my code and the error I get.

Error:
Msg 8120, Level 16, State 1, Procedure ds_rgNetTotals, Line 10
Column 'BEEFS.dbo.CHECK_ITEM_RECORD.REPORT_GROUPID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

ALTER PROCEDURE [dbo].[ds_rgNetTotals]
@StoreID int, 
@StartDate datetime, 
@EndDate datetime
 
AS
 
  CREATE table #tbl_totals (id int identity, subtotal_group int, subtotal_value decimal (18,5))
 
  INSERT #tbl_totals (subtotal_group,subtotal_value)
  SELECT report_groupid, sum([Extension] + [DISCOUNT_AMOUNT]) as rgNetTotal_1
  FROM [BEEFS].[dbo].[CHECK_ITEM_RECORD]
  WHERE STOREid = @StoreID AND
  CHECK_FILE_DATE between @StartDate and @EndDate
  and flag_voided is null
 
  select * from #tbl_totals
 
 
GO

Open in new window

0
 
mattkovoAuthor Commented:
That works great.  Thanks!

Now of course I have another question!

This works fine if I put it in a detail section. Suppose now I want to put this in the report Footer or Header. I tried something like this in a formula hoping to display only the value I want displayed.

if ({ds_rgNetTotals;1.subtotal_id}) = 2 then ({ds_rgNetTotals;1.subtotal_value})
else 0.00

but always get 0.00.  I would like to display the selected Report_GroupID value. Am I missing something here?

Thanks for all the help so far!
0
 
Mark WillsTopic AdvisorCommented:
Might need to set up global variables (expressions) and in the detail section, populate that expression with the correspond value, then in the group report on the MAX() of that expression.
0
 
mattkovoAuthor Commented:
I need the values in the Footer, not the detail section. I have other values that are in the detail and my numbers are all off when I add them there.
0
 
mattkovoAuthor Commented:
The 2nd to last value is displayed if I perform a

previous ({ds_rgNetTotals;1.subtotal_value})

I just can't figure out how to display the specific value I'm looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.