We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Stored Procedures for use with Crystal Reports 2008

Medium Priority
1,041 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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


Author

Commented:
I'm not quite sure how to do this.  Do I just modify the Stored Procedure and put your statement at the top?
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Author

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

Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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!
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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.

Author

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.

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.