Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedures for use with Crystal Reports 2008

Posted on 2009-02-11
9
Medium Priority
?
1,022 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

0
Comment
Question by:mattkovo
  • 5
  • 4
9 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23617951
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
 

Author Comment

by:mattkovo
ID: 23618537
I'm not quite sure how to do this.  Do I just modify the Stored Procedure and put your statement at the top?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23618703
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mattkovo
ID: 23618753
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1500 total points
ID: 23621235
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
 

Author Comment

by:mattkovo
ID: 23623444
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23624381
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
 

Author Comment

by:mattkovo
ID: 23624427
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
 

Author Comment

by:mattkovo
ID: 23624487
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question