Passing Parameter to Sql Statement 2

[onlyanarkali]

Hi there....newbie here trying to use Crystal Reports 8.5....

I am trying to create a new sql expression field called BatchCount in the subreport. The sql i wrote for it is:

(select count(distinct batch_id) from audit_batch inner join LU_Application on audit_batch.cur_workstate=LU_Application.workstate
where audit_batch.useraction_id=26 and audit_batch.actiontimestamp between '2008-01-01' and '2008-01-14' and LU_Application.Mnemonic='SubDocRepair')

This works fine and gives me the count as 13.

Now I wanna replace the hardcoded values with values passed by the user. So instead of 'SubDocRepair', I put {?Mnemonic where Mnemonic is a parameter I created which will accept user input. So now it looks like this:

(select count(distinct batch_id) from audit_batch inner join LU_Application on audit_batch.cur_workstate=LU_Application.workstate
where audit_batch.useraction_id=26 and audit_batch.actiontimestamp between '2008-01-01' and '2008-01-14' and LU_Application.Mnemonic={?Mnemonic)

However this keeps erroring up. Also the Mnemonic parameter is a string and the LU_Application.Mnemonic is a varchar field. Does that matter? I wanna be able to do the same for the dates too...

Any clues...pls Help

Thanks in advance

 

[lbass]

You cannot use parameters in SQL expressions. You could do this in a command if you were using a higher version of CR, but there is no option like this in CR 8.5.

-LB

 

[onlyanarkali]

Thanks LBass for your reply...Is there any other way to do this, maybe like a formula field? Initially my report was pulling data from 2 tables, doing simple count and calculatioms etc with it. Now I added 2 more tables and wanna somehow join the 2 like above to get a count. Is the select expert only used for pulling the initial data from the DB?

I really need to figure this out...dont have any other option

Pls help...Thanks again....

 

[Madawc]

'Select Expert' is OK for starting off, but you need to switch to Record Selection (which will be under Reports, though I forget how 8.5 does it).

You can include a formula field in your selection. Maybe a Boolian, a Crystal choice without an If .. Then

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Sure, first insert a group on create a formula like this in the field explorer->formula->new:

if {audit_batch.useraction_id} = 26 and
{audit_batch.actiontimestamp} in date(2008,1,1) to date(2008,1,14) and
{LU_Application.Mnemonic} = {?Mnemonic} then 1

Place this in the detail section, right click on it->insert summary->SUM (Not count) at the group or grand total level. If you have row inflation (repeated details due to table joins), you would need to use a running total with an evaluation formula instead where you define when the count should be executed.

-LB

 

[onlyanarkali]

Thanks for your reply. Like you said, I created a forumula field called Test and placed the condition you wrote in it. Then I saved it and placed it in the details section. The moment I do that, my report doesnt show anything. Inspite of that, I went ahead and inserted a Summary Field with a SUM of this Test and placed in on the Group Footer section coz thats the section below the details section in this subreport.

That doesnt seem to work...did i miss something or am I doign it all wrong

 

[lbass]

I assumed that the tables you used in the SQL expression had been added to the report and linked with other tables. Is this not the case?

Can you also verify that you have records that meet these criteria?

-LB

 

[onlyanarkali]

Well the result of this query from sql gives me abt 200 records. Although I just want a count (distinct batch_id) from it which is 13 in this case:

Select * from audit_batch inner join LU_Application on audit_batch.cur_workstate=LU_Application.workstate
where audit_batch.useraction_id = 26 and LU_Application.Mnemonic='SubDocRepair' and
audit_batch.actiontimestamp between '2008-01-01' and '2008-01-14'

Also I did link the tables with the visual linking and made sure they join on correct columns...

 

[lbass]

You need to upgrade your CR then--this would easily be done using a command in a higher version.

-LB

 

[onlyanarkali]

Well my coworker suggested I create a view and put the joins and everything in that and let this column just pull from there and the group header name is already grouping it by mnemonic so it shud work....Now how do i create a view and make this report get data from there

 

[lbass]

A view would work, but I can't help you with that. Maybe someone else can.

-LB

 

[onlyanarkali]

Well now I wrote a view but again view cannot accept parameters. So next option I wrote a stored procedure and from crystal, I could call the stored proc and pass values and it will return the count. Question is how do I call th stored procedure from Crystal...u prob can help me with tht

Thanks so much for your timely responses...really appreciate this...gotto have an answer for my boss by this evening...sucks

 

[onlyanarkali]

Here's my stored proc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE usp_spname
-- Add the parameters for the stored procedure here
@StartDate datetime,
@EndDate datetime,
@Description varchar(512),
@counter int output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @counter=COUNT(DISTINCT dbo.Audit_Batch.Batch_Id)
FROM dbo.Audit_Batch INNER JOIN
dbo.LU_Application ON dbo.Audit_Batch.Cur_Workstate = dbo.LU_Application.WorkState
WHERE (dbo.Audit_Batch.UserAction_Id = 26)and
audit_batch.actiontimestamp between @StartDate and @EndDate and LU_Application.Description=@Description
END
GO

=========================================================

Now I wanna be able to execute this sp from Crystal. When I go to Visual linking i do see the SP and it prompts me to add values, but when i do nothing really happens, it just shows the SP name with a green checkmark next to it and stays at that screen.....Do i create a new sql expression field and add exec SP name with parameters....if so can someone give me and example....SO LOST

 

[lbass]

Sorry, someone else will have to help you with this as well. I don't ever work with stored procedures, but I thought they returned fields, just as a view or table would, so after you add the sp in the database expert, you should be able to then go into the field explorer and add the count field from it. But I can't tell you whether you have set up the procedure correctly to do that or how to work with sp parameters.

-LB

 

[Madawc]

I do use Stored Procedures, but do my counts in Crystal. You could ask in the SQL forum if your SQL is not working as you expect.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[onlyanarkali]

Well...turns out tht you can have only 1 main query that can pull all the data from the DB and then with formulas etc you can play around with it. Also the sql expression fields can pull only static data tht is without the where clause. So we ended yo creating a column in our table and getting data from there in the main query.

However now we wanna create a separate section that will pull data from 2 totally different tables no way linked to the main sql query? Any ideas?

 

[lbass]

SQL expressions CAN have where clauses--they just don't accept parameters.

You could use subreports for the different tables.

-LB

 

[onlyanarkali]

Thanks LBass....I did use a Subreport and got it working. So now my main report looks something like this:

Process1 100
Process2 200
Process3 300

Total 600

-----------------------------
Process4 500.

The Process1 to Total section is in one subreport and Process4 is in another, coz Process 4 pulls data from whole another tables. Now my boss wants me to move Process4 above the Total and make the Total include Process4's 500 value too. I can physically move it up, but can I access 500 which is a count of a column from Subreport 2 and add it to the Total which is part of Subreport1?

Thanks

 

[lbass]

Within each subreport, set the total to a shared variable in the report footer, as in:

whileprintingrecords;
shared numbervar cnt := count({table.field});

Then you can reference the results and add them together in the main report. How you do this depends upon the location of the subs (what sections) and whether the total you are showing is at the group or grand total level.

-LB

 

[onlyanarkali]

Thanks Lbass...My report is laid out like this:

In the Main Report, in the Details b section, SubReport1 is placed and in Details c section, SubReport2 is placed.

In the Main Report, I created a BSUM Formula Field with
whileprintingrecords;
shared numbervar BSUM := 0;

In SubReport1, I created the same BSUM formula with
whileprintingrecords;
Shared NumberVar BSUM:=Sum ({Stats_DataEntry.Items});

In SubReport2, I created the same BSUM formula with
whileprintingrecords;
shared numbervar BSUM := Count ({@DateDiff});

Now what do i place where and how does it work