How to pass parameter in vb6.0 for crystal report 11

You should no be using CRAXDDRT but rather CRAXDRT.

CRAXDDRT is a superset of CRAXDRT.  The caveat is there are calls in CRAXDDRT that require further/additional licensing

Unless you are providing a report editor to the end users, you don't need CRAXDDRT.

I don't have the code here but will look it up tonight.

mlmcc

Thanks..

If you have only 1 parameter it is easy

 crRpt.ParameterFields(1).AddCurrentValue    YourValue

 crRpt.ParameterFields(1).AddCurrentValue    5

If you have more than 1 then either you need to know the correct order or verify the parameter name

crRpt.ParameterFields(1).Name

I think you can use

crRpt.ParameterFields("ParameterName")

You may need to use {?ParameterName}

mlmcc

it says invalid name and highlighted on following code

crRept.ParameterFields.GetItemByName("{@MachNo}").AddCurrentValue Combo1.Text

full code is

Dim crApp As New CRAXDRT.Application
Dim crRept As New CRAXDRT.Report
Dim crParamDefs As CRAXDRT.ParameterFieldDefinitions

Dim crDBTab As CRAXDRT.DatabaseTable

Set crRept = crApp.OpenReport(App.Path & "\APsheet.rpt")

For Each crDBTab In crRept.Database.Tables
  crDBTab.SetLogOnInfo "'" & Sys_Name & "'", "TimeManager", "crystal", "crystalad"
Next


crRept.EnableParameterPrompting = True


Set crParamDefs = crRept.ParameterFields

crRept.ParameterFields.GetItemByName("{@MachNo}").AddCurrentValue Combo1.Text

crViewer1.ReportSource = crRept
crViewer1.ViewReport

Select allOpen in new window

Are those parameters for the query or the report?

Crystal Report parameters all start with a ?

If there is only 1 did you try using the number method?

mlmcc

tried but no luck
crRept.ParameterFields(1).AddCurrentValue Combo1.Text

and i tried this one also but no luck
crRept.ParameterFields.GetItemByName("{@MachNo}").AddCurrentValue Text1.Text

total 3 paramete i want to pass
1 - machine number
2 - from date
3 - to date

Submitted Report please check
May be you will find what am i doing wrong
APsheet.rpt

I don't see any parameters in the report.

mlmcc

Please Assist me how to create parameter and how to call it from vb
i tried several ways but no luck

DO you know how to create a parameter in the report?

Just create 3 parameters in the report then try the code.

mlmcc

Created Three Parameter..
and pass those parameter using following code..
But now getting "login failed error"
Please check attached report also..

Dim crApp As New CRAXDRT.Application
Dim crRept As New CRAXDRT.Report
Dim crParamDefs As CRAXDRT.ParameterFieldDefinitions
''Dim crParamDef As CRAXDRT.ParameterFieldDefiniton
Dim crDBTab As CRAXDRT.DatabaseTable

Set crRept = crApp.OpenReport(App.Path & "\APsheet.rpt")

crRept.Database.LogOnServer "p2ssql.dll", "203.192.***.***", "TimeMan", "crystal", "crystal"

For Each crDBTab In crRept.Database.Tables
  crDBTab.SetLogOnInfo "2**.192.***.***", "TimeMan", "crystal", "crystal"
Next

crRept.EnableParameterPrompting = True

Set crParamDefs = crRept.ParameterFields
   
For Each crParamDef In crParamDefs
  Select Case crParamDef.ParameterFieldName
'    Case "SubTitle"
'      crParamDef.SetCurrentValue "My Report Subtitle Goes Here"
    Case "McnNo"
      crParamDef.SetCurrentValue Val(Int(Combo1.Text))
      
    Case "FrmDate"
      crParamDef.SetCurrentValue DateValue(DTPicker1(0).Value)

    Case "ToDate"
      crParamDef.SetCurrentValue DateValue(DTPicker1(1).Value)
'    Case "@IntegerParam"
  End Select
Next

crViewer1.ReportSource = crRept
crViewer1.ViewReport

Select allOpen in new window

APsheet.rpt

i tried following code but Getting run time error and highlighted on same line
"object doesn't support this property or method"

crViewer1.LogOnInfo(0) = "DSN = CrystalTime;UID =crystime;PWD = cryst;DSQ = TimeMa"

Select allOpen in new window

ok..

i tried following code and its working even i can see parameters value in report but
i am not getting filter data from parameter...
getting WHOLE data from beginning to end (complete data)

code i tried is following (not getting any error)

Dim Appl As New CRAXDRT.Application
Dim Report As New CRAXDRT.Report
crViewer1.Top = 0
crViewer1.Left = 0
crViewer1.Height = ScaleHeight
crViewer1.Width = ScaleWidth
Set Report = Appl.OpenReport(App.Path & "\APsheet.rpt")

Report.EnableParameterPrompting = True

Set crParamDefs = Report.ParameterFields
   
For Each crParamDef In crParamDefs
  Select Case crParamDef.ParameterFieldName
'    Case "SubTitle"
'      crParamDef.SetCurrentValue "My Report Subtitle Goes Here"
    Case "McnNo"
      crParamDef.SetCurrentValue Val(Int(Combo1.Text))
      
    Case "FrmDate"
      crParamDef.SetCurrentValue DateValue(DTPicker1(0).Value)

    Case "ToDate"
      crParamDef.SetCurrentValue DateValue(DTPicker1(1).Value)
'    Case "@IntegerParam"
  End Select
Next

crViewer1.ReportSource = Report
crViewer1.ViewReport

Select allOpen in new window

I assume you have  a filter in the select expert.

Try looking at the SQL
Click DATABASE --> VIEW SQL

Is there anything in the WHERE clause?
IN the loop try adding a MSGBOX

MSGBOX  crParamDef

Make sure the names are correct.

mlmcc

oh ok now i got it..
i created selection formulas (it was empty)

Report > Selection Formulas > Record
"{DayWiseAtnd.EMachineNo} = {?McnNo} AND {DayWiseAtnd.EmpDateTime} >={?FrmDate} and {DayWiseAtnd.EmpDateTime} <= {?ToDate}"

and its just fine i am getting expected records but
the problem is "summarized total"
Column Total is correct but Row Total is wrong

How are they being calculated?

 mlmcc

in Report
Right Click on Row Total >>> in edit summary >>> daywiseatnd.enrollno (under Choosethe filed to summarized)
and Distinct Count (Under Calculate this summary)

is this ok..?
SmrzTTL.bmp

Sounds like it should work assuming that is the correct field and summary.

Why do you think the numbers are wrong?

mlmcc

Sir
in a row total - I want to know how many days employee present
and in column Total i want to know how many employee was present on that day..

please check attached file
row total is supposed to be 6 in first three row and 4 in forth row
WrongTTL.bmp

Have you tried just COUNT as the summary?

What does the column formula use?

mlmcc

yeh i tried its also not that total which i want

is it possible to count value (p) and put in a formula..??

Are the underlying values in the spreadsheet P and A?

mlmcc

No Sir
Original values is 1 and 0 in cross tab,
as per your suggestion i am using "Dispay String" Function in report

I got Row Total (Using Distinct Date (removed Time))..But
Columns Total is wrong now
Now i want column Total using Distinct Enroll No. is it possible..?
Please Assist

Probably not since the column doesn't have that information.

What is the underlying data in the cross tab?

mlmcc

SQL Query is and sample Data

SELECT     EnrollNo, EMachineNo, DATEADD(dd, 0, DATEDIFF(dd, 0, EmpDateTime)) AS EmpDateTime, DATENAME(dw, EmpDateTime) 
                      AS DayName
FROM         dbo.tblEmpGenralLog

Select allOpen in new window

Sample Data is

1      1      2011-04-01 00:00:00.000      Friday
2      1      2011-04-01 00:00:00.000      Friday
3      1      2011-04-01 00:00:00.000      Friday
14      1      2011-04-01 00:00:00.000      Friday
13      1      2011-04-01 00:00:00.000      Friday
3      1      2011-04-01 00:00:00.000      Friday
5      1      2011-04-01 00:00:00.000      Friday
6      1      2011-04-01 00:00:00.000      Friday
8      1      2011-04-01 00:00:00.000      Friday
11      1      2011-04-01 00:00:00.000      Friday
4      1      2011-04-01 00:00:00.000      Friday
9      1      2011-04-01 00:00:00.000      Friday
1      1      2011-04-01 00:00:00.000      Friday
2      1      2011-04-01 00:00:00.000      Friday
7      1      2011-04-01 00:00:00.000      Friday
1      1      2011-04-02 00:00:00.000      Saturday
11      1      2011-04-02 00:00:00.000      Saturday
6      1      2011-04-02 00:00:00.000      Saturday
8      1      2011-04-02 00:00:00.000      Saturday

The original data is something like

1      1      2011-04-01 08:00:00.000      
2      1      2011-04-01 08:15:00.000      
3      1      2011-04-01 09:25:00.000      

What you want to see is how many days was an employee there (row total) and how employees attended each day (column total)

Try this idea



Create a formula
 {EnrollNo }  & {EmpDateTime}


Add that as the cross tab summary
Set it to DISTINCT COUNT

Put your display string formula on it so it show P/A

mlmcc
empdays.rpt
EMPDays.xls

Create a formula
 {EnrollNo }  & {EmpDateTime}

how many formula must i create two or one

One.  Look at the attached report

mlmcc

What am i doing wrong..?

Created Formula Name : EmpNo
dragged to the summarized Field
Cross Tab Expert >> EmpNo >> Summarized Field >> Select This Formula Change Summary to Distinct Count
Got Column Total But Lost Row Total

Please Check Attached Report

I kept as it is previous design
APsheet-New.rpt

Check the modification on this one

mlmcc
APsheet-New.rpt

Exactly I did, it wasn't working..!!

Anyway But now Now I got it and its working perfectly.. :-)

Thanks mlmcc

mlmcc please help me on my another report :

https://www.experts-exchange.com/questions/28701124/How-to-calculate-hours-and-miniutes.html

Thanks