Crystal report 8.5 help

i m using access 2000 from there i m calling crystal report 8.5 , every thing working fine except one query havib=ng having Criteria with parameters , how to pass this parameter to the Crystal report.

would some body help me

SELECT Assignment.BNo, Trade.Trade, Assignment.Source, Assignment.MobDate, Project.ProjectID
FROM Trade INNER JOIN (Project INNER JOIN (Orders INNER JOIN (Employees INNER JOIN Assignment ON Employees.[Badge Number] = Assignment.BNo) ON Orders.OrderID = Assignment.OrderNo) ON Project.ProjectID = Orders.ProjectID) ON Trade.[Serial No] = Assignment.Trade
GROUP BY Assignment.BNo, Trade.Trade, Assignment.Source, Assignment.MobDate, Project.ProjectID, Month([Assignment].[mobdate]), Year([Assignment].[mobdate])
HAVING (((Assignment.MobDate)=[enter date]) AND ((Month([Assignment].[mobdate]))=Month(Date())) AND ((Year([Assignment].[mobdate]))=Year(Date())))
ORDER BY Assignment.BNo;

this is the queryWhile writing the query, u may have some option to create parameter.

CR 9 has that option. No idea about CR 8.5

Crystal report - using sub report

i am using a sub report in my main report. I am able to count/ total a field using the Running total within that subreport which match the criteria of selection. i am passing a parameter from my main report say Issue number to subreport.

For each issue no passed i am totalling and writing in the report - one row per issue no ( with different columns like January, Feb, March,...)

Problem: At the end of the report i want the Verticle total of each coulmn say total in January, Feb, March,...I am not able to do this.

Is there a way to do this?

In the sub report i use Formula field in running total as i need to pick up only those records in the database which match the selection criteria provided. I tried other options like Sum of a field,..but no success.

let me know if any one of you have answer. THANKSdo you use grouping in the report(Jan, Feb, Mar...)?|||NO.

I have formula fields to calculate the First and the last day of each of the months using the current date. Using these i check the date in the record to count that record whether that should fall in Jan or Feb or march in the sub report.

Data comes from 2 seperate db tables. Issueno table(main report) and Tickets table (sub report)
Only Selected Issue no is passed as parameter to the sub report to do the above calculation (count no of tickets with date in Jan or Feb or Mar..).
Sub Report footer has these running total fields with the formula to count the tickets.

Report looks like this:

Issue# Rec in Jan Recs..Feb RecMarch Recs..April Total
5 0 1 2 0 3
12 2 0 7 2 11
14 0 2 0 4 6
17 11 4 0 0 15
24 0 7 1 0 8
======================================
GTotal ?? ?? ?? ?? ??
========================================
Or is there a way to count the figures in each column at the end of the Report?

(Data: First Row:- Issue no=5, Jan=0, Feb=1, March=2, Apr=0, Total=3)|||Your bottom line is to count the tickets on january, february..., i im right?

If that is you case, try this... add running total field(january) and the type of summary is count. Then in "evaluate", click the formula option and type this

month({ticket.date})=1

This means fo the month of january...

Add the another running total field for the month of february and so for...

Sorry, im slow in english so i cant understand yor question clearly.|||Your English: Not a problem at all. Thanks for the Feedback.

My problem in Short:
I am trying to Grand Total a DERIVED Field/Column like Jan, Feb, Mar...SO there is no Month field as such to Grand Total. All this calculation is done ina Sub report using the Formula field to determine in which coulmn (jan/feb/..) the record falls and accordingly counted in that month. For every issue no passed from main report as a parameter to subreport all the attached tickets to that Issue no are validated and if the selection criteria matched then counted.
Only vertical total of a devived field like Jan/feb/.. is a problem

Crystal Formula - SQL

I started with this selection criteria:

({@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date})

Then I did this:

{@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date} and
({LabSpecLPatientIndex.Prefixes} in ["IM", "R"] and {LabLSpecResultTests.Result} <> "")

I got data just fine on that.

Then I further altered it with this:

({@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date}) and
(({LabSpecLPatientIndex.Prefixes} ="IM" and {LabLSpecResultTests.Test} = "902.9550"
and {LabLSpecResultTests.Result} <> "") or ({LabSpecLPatientIndex.Prefixes} ="R" and
{LabLSpecResultTests.Result} <> ""))

And I get no data. What did I miss? I want all records with between those dates, that have prefixes = IM or R that have results (not null results). If the prefix = IM, then I only want it if Test = "902.9550" ...but I want all records with a Prefix R.

TIA.
Briana{@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date} and
({LabSpecLPatientIndex.Prefixes} in ["IM", "R"] and {LabLSpecResultTests.Result} <> "") and {LabLSpecResultTests.Test} = "902.9550"

Crystal Alerts but in Reporting services

In Crystal Reports you can setup Alerts to alert you to a certain criteria when ever you refresh the report..

Is this possible within Reporting services?

If so how do you set them up?

Thanks

I'm not sure how well it would work for you because I am not sure of your criteria, but you could add a reference to System.Windows.Forms and then add code to the code window that displays a message box if certain criteria is met. If you can give me an example of your criteria I can try to give you an example.

Simone

|||

Hi Simone

Its a really simple criteria

In crystal I have setup

Sum ({@.Euro to Sterling GP}, {ACCOUNT_NAME}) < 25.00

Thanks for your help

|||

This is somewhat of a hack, but it should work:

Add a reference to System.Windows.Forms
In the code window, add a function similar to the following:

function ShowMessage(value as decimal) as decimal

If value < 25 then System.Windows.Forms.MessageBox.Show("Your Message")
ShowMessage = value
End function

In the field where you have the summary:

Add the following expression:
=Code.ShowMessage(Sum(FieldToSum,ScopeName))

I hope this helps. I've never used alerts in Crystal, but this will show a message box. You could also change the color of the field by using an expression if this is a better option for you. You may want to set a variable to only show the message once if multiple values are < 25.

Simone

|||This is not possible with Reporting Services. If you use the ReportViewer control you can trap events but not down to the data changes. With web reporting and rendering the report in a frame, you may try to get try to get a reference to the field but to track the change you need to track the old value so it will hairy.|||Thanks guys for all your help|||

I see that this only works in the development studio. I wondered how it would behave when displayed through IIS.

Thanks for the explanation.