Solved

VB6/CR10 Report Question

Posted on 2004-10-20
12
669 Views
Last Modified: 2008-01-09
VB6
CR10
SQL Server 2K

I've currently got a VB application that calls a report based off entries from 2 drop down boxes. I have a different report for each combination of drop down box selections. Well I have to create another set of reports based off of 2 other drop down boxes. There has to be an easier way to do this right?

Here's my situation...
I have a DB table with the following columns:
Name, Supervisor, Manager, EmpType, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dece

Jan - Dece are populated with " " (a blank space) or "X" (a letter X)

I have a form in VB with a drop down for month (January - December)
and a drop down for Manager (list of manager names pulled from separate DB table)

When the user selects 'Joe Blow' from the manager drop down and 'January' from the month drop-down here's what needs to occur:

A report is pulled that gives the following
Name, Supervisor, and EmpType where Manager = 'Joe Blow' and Jan <> 'X'

This would need to change based on the Manager and Month selected from the form.
Is there an easier way to do this that creating a report for each scenario? If so...would I do it in Crystal or VB? How?
0
Comment
Question by:mwmiller78
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 12359936
Sure there's a much easier way!

In your Crystal report, create two new paramters - one for the month and one for the manager.   Make sure they are both created as "string" types.

Now in the select expert you want to create the following formula:

{dbtable.manager} = {?ManagerParameter} and
(
if {?MonthParam} = 'Jan' then
   {dbtable.Jan} = 'X'
if {?MonthParam} = 'Feb' then
   {dbtable.Feb} = 'X'
...etc...
if {?MonthParam} = 'Dec' then
   {dbtable.Dec} = 'X'
)

Now run the report from the Crystal development environment.  You should be prompted for the manager and the month - test and make sure you're getting the correct results.

Once the report is working correctly all you need to do is pass the values from the dropdowns into the Crystal parameters when you call the report.

HTH

frodoman
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12359953
Oops - forgot to add this.

To pass the parameter from VB to Crystal take a look at these samples: http://support.businessobjects.com/communityCS/FilesAndUpdates/cr8_vb_rdc_parameter.exe.asp

frodoman
0
 

Author Comment

by:mwmiller78
ID: 12360204
in my select expert formula i keep getting the following:

The ) is missing

Why? Here's my formula:

{MissingCards.manager} = {?ManagerParameter} and
(
if {?MonthParam} = 'January' then
   {MissingCards.Jan} = 'X'
if {?MonthParam} = 'February' then
   {MissingCards.Feb} = 'X'
if {?MonthParam} = 'March' then
   {MissingCards.Mar} = 'X'
if {?MonthParam} = 'April' then
   {MissingCards.Apr} = 'X'
if {?MonthParam} = 'May' then
   {MissingCards.May} = 'X'
if {?MonthParam} = 'June' then
   {MissingCards.Jun} = 'X'
if {?MonthParam} = 'July' then
   {MissingCards.Jul} = 'X'
if {?MonthParam} = 'August' then
   {MissingCards.Aug} = 'X'
if {?MonthParam} = 'September' then
   {MissingCards.Sep} = 'X'
if {?MonthParam} = 'October' then
   {MissingCards.Oct} = 'X'
if {?MonthParam} = 'November' then
   {MissingCards.Nov} = 'X'
if {?MonthParam} = 'December' then
   {MissingCards.Dece} = 'X'
)
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12360241
My bad - I forgot the semicolons.  Add a semicolon after each 'X'

if {?MonthParam} = 'January' then
   {MissingCards.Jan} = 'X';    <-- Semicolon here
if {?MonthParam} = 'February' then
   {MissingCards.Feb} = 'X';   <-- Semicolon here

etc.

frodoman
0
 

Author Comment

by:mwmiller78
ID: 12360469
Nothing is being returned...

Here's my formula:

{MissingCards.manager} = {?ManagerParameter} and
(
if {?MonthParam} = 'January' then
   {MissingCards.Jan} = 'X';
if {?MonthParam} = 'February' then
   {MissingCards.Feb} = 'X';
if {?MonthParam} = 'March' then
   {MissingCards.Mar} = 'X';
if {?MonthParam} = 'April' then
   {MissingCards.Apr} = 'X';
if {?MonthParam} = 'May' then
   {MissingCards.May} = 'X';
if {?MonthParam} = 'June' then
   {MissingCards.Jun} = 'X';
if {?MonthParam} = 'July' then
   {MissingCards.Jul} = 'X';
if {?MonthParam} = 'August' then
   {MissingCards.Aug} = 'X';
if {?MonthParam} = 'September' then
   {MissingCards.Sep} = 'X';
if {?MonthParam} = 'October' then
   {MissingCards.Oct} = 'X';
if {?MonthParam} = 'November' then
   {MissingCards.Nov} = 'X';
if {?MonthParam} = 'December' then
   {MissingCards.Dece} = 'X';
)

When I run the report, It asks me for the 2 parameters.
I type in Blow,Joe for manager and January for Month
---Blow,Joe has no X for Jan in the table

I also tried Smith,John and January
---Smith,John has all X for Jan in table

neither on returned anything.
Is there something I need to add to the formula?
0
 
LVL 28

Expert Comment

by:bdreed35
ID: 12360782
Try it this way:

{MissingCards.manager} = {?ManagerParameter} and
(if {?MonthParam} = 'January' then
    {MissingCards.Jan} = 'X'
 else if {?MonthParam} <> 'January' then
    True) and
(if {?MonthParam} = 'February' then
   {MissingCards.Feb} = 'X
 else if {?MonthParam} <> 'February' then
    True) and      
(if {?MonthParam} = 'March' then
   {MissingCards.Mar} = 'X
 else if {?MonthParam} <> 'March' then
    True) and
(if {?MonthParam} = 'April' then
   {MissingCards.Apr} = 'X
 else if {?MonthParam} <> 'April' then
    True) and
(if {?MonthParam} = 'May' then
   {MissingCards.May} = 'X
 else if {?MonthParam} <> 'May' then
    True) and
(if {?MonthParam} = 'June' then
   {MissingCards.Jun} = 'X
 else if {?MonthParam} <> 'June' then
    True) and
(if {?MonthParam} = 'July' then
   {MissingCards.Jul} = 'X
 else if {?MonthParam} <> 'July' then
    True) and
(if {?MonthParam} = 'August' then
   {MissingCards.Aug} = 'X
 else if {?MonthParam} <> 'August' then
    True) and
(if {?MonthParam} = 'September' then
   {MissingCards.Sep} = 'X
 else if {?MonthParam} <> 'September' then
    True) and
(if {?MonthParam} = 'October' then
   {MissingCards.Oct} = 'X
 else if {?MonthParam} <> 'October' then
    True) and
(if {?MonthParam} = 'November' then
   {MissingCards.Nov} = 'X
 else if {?MonthParam} <> 'November' then
    True) and
(if {?MonthParam} = 'December' then
   {MissingCards.Dece} = 'X
 else if {?MonthParam} <> 'December' then
    True)

This ensures that the criteria will get passed to the database.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 42

Expert Comment

by:frodoman
ID: 12360831
Or try it this way:

select {?MonthParam}
  case 'Jan': {MissingCards.manager} = {?ManagerParameter}  and {MissingCards.Jan} = 'X'
  case 'Feb': {MissingCards.manager} = {?ManagerParameter} and {MissingCards.Feb} = 'X'
  ...
  case 'Dec': {MissingCards.manager} = {?ManagerParameter} and {MissingCards.Dec} = 'X'
  default: true
0
 
LVL 28

Expert Comment

by:bdreed35
ID: 12360834
BTW, I should have asked this first, but can the user pick more than one month in the Month Parameter?
If so, the above is what you want.

If not, then it can be simplified this way:

{MissingCards.manager} = {?ManagerParameter} and
switch
(
    {?MonthParam} = 'January'  ,{MissingCards.Jan} = 'X',
    {?MonthParam} = 'February' ,{MissingCards.Feb} = 'X',
    {?MonthParam} = 'March'    ,{MissingCards.Mar} = 'X',
    {?MonthParam} = 'April'    ,{MissingCards.Apr} = 'X',
    {?MonthParam} = 'May'      ,{MissingCards.May} = 'X',
    {?MonthParam} = 'June'     ,{MissingCards.Jun} = 'X',
    {?MonthParam} = 'July'     ,{MissingCards.Jul} = 'X',
    {?MonthParam} = 'August'   ,{MissingCards.Aug} = 'X',
    {?MonthParam} = 'September',{MissingCards.Sep} = 'X',
    {?MonthParam} = 'October'  ,{MissingCards.Oct} = 'X',
    {?MonthParam} = 'November' ,{MissingCards.Nov} = 'X',
    {?MonthParam} = 'December' ,{MissingCards.Dece} = 'X'
)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12361390
How about trying to do the modification in the VB application?

Add this to your code where you call the report

dim strSQL as string

strSQL  = "{MissingCards.manager} = '" & ManagerDropDown.text & "' AND {MissingCards." & left(MonthDropDown.text,3)
if MonthDropDown.text = "December" then
  strSQL= strSQL& "e"
end if
strSQL= strSQL & "} = 'X'"

ReportObject.RecordSelectionFormula = strSQL

Call the report

mlmcc
0
 

Author Comment

by:mwmiller78
ID: 12362481
OK the report is working great! I even tried the new way of calling the report. It's pulling up the new
report great. I think I'm using the wrong method (or just plain wrong code) in setting the parameters.
Here's most of the code from my viewers load event...
v_Manager and v_Month are set earlier in the code. This was a shot in the dark. Am I even close?


Screen.MousePointer = vbHourglass
Set CrxReport = crxApplication.OpenReport(App.Path & "\missing.Rpt")
CrxReport.DiscardSavedData
CrxReport.ParameterFields.GetItemByName("ManagerParameter").AddCurrentValue " & v_Manager & "
CrxReport.ParameterFields.GetItemByName("MonthParam").AddCurrentValue " & v_Month & "
' Set the ReportSource of the CrViewer control to the Report object and view the report.
CRViewer1.ReportSource = CrxReport
CRViewer1.ViewReport



' Zoom the preview window to 100%
CRViewer1.Zoom 100
Screen.MousePointer = vbDefault
0
 
LVL 13

Expert Comment

by:vidru
ID: 12388379
Did you get this worked out?

Change these two lines (notice I got rid of the " ''& "):
CrxReport.ParameterFields.GetItemByName("ManagerParameter").AddCurrentValue v_Manager CrxReport.ParameterFields.GetItemByName("MonthParam").AddCurrentValue v_Month

-dave
0
 

Author Comment

by:mwmiller78
ID: 12400033
Sorry guys! I did get this one figured out. Forgot to close out the question.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now