Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Print parameters in a no data report... Post Reply Post New Topic
Author Message
.netter
Newbie
Newbie


Joined: 06 Dec 2007
Location: United States
Online Status: Offline
Posts: 1
Quote .netter Replybullet Topic: Print parameters in a no data report...
    Posted: 06 Dec 2007 at 4:17pm
Using CR in .NET 2003...
 
I have a stored procedure that performs 2 different selects. 
The first selects records to print in CR.  The second selects report parms
entered into a asp.net form.
 
The trouble I'm having is that, in the page header of the CR, I want to print the parameters the user input from the asp.net form... but if it is a report with no data from the first select, the report parameters from the second select don't print either.
 
What I need to have happen is that the report parameters from the 2nd select print, regardless of whether there is any data for the report.
 
Here is what the SP looks like:
 
---------------------------------------------------
CREATE PROCEDURE spITIlocStockSummRpt
@sprojNum varchar(5),
@sprojName varchar(150),
@slocNum bigint,
@slocName varchar(150)
AS
DECLARE @parmItems TABLE
(
   projCode varchar(25),
   projName varchar(150),
   locNum bigint,
   locName varchar(150)
)
INSERT INTO @parmItems (projCode, projName, locNum, locName) VALUES (@sprojNum, @sprojName, @slocNum, @slocName)
 
SELECT pli.ITIprodCode, pli.qtyIN, p.prodName, p.vendprodCode, p.prodUOM, v.vndName, ptc.productTransactionCnt
FROM ProjectLocationInventory pli LEFT OUTER JOIN ITIProductTransCntByProject ptc ON ptc.projCode = pli.projCode AND ptc.ITIprodCode = pli.ITIprodCode AND ptc.projCode = @sprojNum, ITIProducts p, ITIvendors v
WHERE pli.projCode = @sprojNum
AND pli.plID = @slocNum
AND p.ITIprodCode = pli.ITIprodCode
AND v.vndCode = p.vendCode
ORDER BY p.prodName
;
SELECT projCode, projName, locNum, locName
FROM @parmItems
GO
---------------------------------------------------
 
So, the values from the select against the @parmItems need to print in the report, regardless of whether there is data from the other select.
 
Both tables, with fields, show up in the CR database viewer.
 
Help, this is driving me nuts!
 
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 07 Dec 2007 at 7:47am
Question #1:  Are these parameters being passed to parameter fields in Crystal?  If so, you know you can just print parameter fields right on the report, right?  I'm going to assume that, for the sake of this reply, this is not the case.

Crystal is going to have real problems trying to process disconnected recordsets. 

Normally, in a case like this, I would strongly advocate the use of a subreport.  But, I'm at something of a loss, off-hand, as to how to do that with this structure.

As a question, why are you putting your parameters into a table?  There's only one of each parameter, right?

My solution would be to use a UNION query to create a dummy record with the parameter values in it.  So, after your first SELECT statement, put:

UNION ALL

SELECT projCode AS ITIprodCode, 0 AS qtyIN, projName AS prodName, slocNum AS vendprodCode, 'DUMMY' AS prodUOM, slocName AS vndName, 0 AS productTransactionCnt
FROM @parmItems



In your report, make sure to suppress the record with 'DUMMY' in prodUOM.  Create SQL Expression fields to pull the values where prodUOM = 'DUMMY', and put those fields in your report header.


IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.