vba vs crystal reports

[le1burg]

I'm totally new to VBA. Just today I was asked to learn it and some how this will replace what I've been doing in Crystal Reports (8.5). The crystal work ended up being exported to excel ultimately so this path is to replace the crystal thus eliminating a step, I believe.

Having a little trouble getting my head around it. Anybody care to explain to me our VBA can be used as a reporting tool and point me to some good tutorials thought worthy. Reckon I gotta learn how to interface VBA with SQL database...

Many thanks in advance!

 

[SkipVought]

Hi,

A better question is, How can EXCEL be used as a repoting tool.

1. Get the source data into Excel, using Data > Get External Data...

2. Sort/Filter/Pivot the data in Excel

3. Format the data in Excel.

Once you have figgured out exactly how to do all thos things exactly as required, THEN turn on your macro recorder and do each of those tasks again, capturing the code.

Post back with your recorded code for help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[le1burg]

Thanks, Skip. Exactly what I needed, a place to start.

I'll be back!

Thanks again.

 

[le1burg]

Okay. I found the mountain and have established a base camp...

I'm finding the query end a bit lacking or is it me? I have yet to find a clean way into the table selection/linking screen and when I did get there I learned that you cannot use a outer join if you have multiple (more than 2?) tables involved in the query. The Crystal reports I would hope to emulate do have multiple tables and left outers in many places. Am I barking up the wrong tree? Ought Microsoft Query to Excel be able to replicate a Crystal Report (to Excel)? Any knowledgable input is valuable to me and appreciated...

Skip?

 

[SkipVought]

MS Query has it's limitations. When using multiple outer joins, I have had to resort to using ADO for my query.

From
  Table1 T1
, Table2 T2
, Table3 T3
Where T1.Field1=T2.Field1(+)
  And T1.Field1=T3.Field1(+)

for instance

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

You may use MS-Query with multiple OUTER JOINs, but not in the GUI: you have to write the SQL code in the SQL dialog.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[le1burg]

Hmmm, but where does that come in, I'm fuzzy.

How I got here...
I invoke EXCEL > Data > Import External Data > Import Data...brings up My Data Sources

I choose +Connect to New Data Source.odc which invokes the Data Connection Wizard, I choose ODBC DSN and given a list of ODBC data sources

I select my database which is represented in the list, a click and I'm ask by the SQL server for id/pw, this opens up a Select database and table screen. I'm not going where I want to go because this path seems to only let me choose one table for importing? Then at the very end I see now I can edit the query, but alas I've been working Microsoft Query the whole time.

I scouted around some and found some instructions on a Microsoft site...following them, I opened an Excel spreadsheet, hopped into the VB editor, opened a new module and inserted:
Sub DataExtract()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=pubs;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "SELECT * FROM Authors"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsPubs

' Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub

This dies with a runtime error. I'm using a VPN and didn't think this would work cleanly. Where's the id/pw?

My server is like Collection2008. Would (Local) cut it?

I'm feeling overwhelmed...need a trot...any input muchly appreciated...

 

[SkipVought]

Why are you doing Data > Import External Data > Import Data?

I would think that you'd want to Data > Import External Data > New Database Query

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Don't import data but create a query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[le1burg]

Okay, I just tried the new database query option, that works, but here's where I run into the outer join problem.

Found the SQL option, snatched the SQL from one of the Crystal reports and cut and pasted it into the SQL box and presto, data! So that works, good. But Crystal does so much more once the data is retrieved. Am I do think somehow this can be duplicated with SQL statements? I doubt it. Crystal allows one to prompt for input and how do some reports get invoked etc. One giant can of worms if you'd ask me now...is there light at the end of this tunnel once I begin to venture down it? But in all its progress, I know more now then I did yesterday! Data is data, but where do I go from here. Will the VBAs further massage the retrieved data? Can I pass in parameters...

Your thoughts are appreciated and highly regarded...thanks.

 

[le1burg]

I meant to type 'subreports' not 'some reports'....doh!

Does this forum have an edit function?

 

[SkipVought]

Could CR do complex spredsheet stuff? I doubt it. CR is a REPORT MANAGER. Excel is a SPEADSHEET application. Each have their pros and cons.

"Crystal allows one to prompt for input and how do some reports get invoked etc."

You can have MANY "prompts". I often use ComboBoxes, or Data > Validation - LIST boxes.

"I pass in parameters..."

Your parameters must be data values within your database. On a separate sheet, query on that field, with appropriate criteria to return a SELECT DISTINCT list of values, that will be the basis for Selection List.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ettienne]

Another option is to pull the data into an Access database and do some manipulation, then extract what you want into Excel. There are many options, it all depends on what your source data looks like and what you want on the report.

 

[le1burg]

Skip, thanks I'll investigate. Can you show me an example of some kind. It's a lot to take in all up front but I feel I'm learning here.

I learn from looking at code best...

Thanks much for your input.

 

[le1burg]

Ettienne, thank you for your input.

We've been using Crystal which in the end allows for an export to Excel and I've become pretty good at it. Not like it wysiwyg, takes some wrangling. Anyway, the clients are looking to avoid the Crystal run altogether and are asking for in all upfront in Excel, well, iow, they want all the magic to take place there. I feel we are reinventing the wheel.

 

[SkipVought]

I have Cost Centers (CC) that each have multiple Machine Groups (MG)

Sub GetCCList()
    If bDebug Then Debug.Print "GetCCList - IN"
    Dim sSQL As String
    
    sSQL = "SELECT DISTINCT CC"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM FPRPTSAR.MC_BUILD_SCHEDULE"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE (substr(CC,1,2) Between '5A' And '5Z')"
    sSQL = sSQL & "   OR (CC In ('161','165'))"
    
    With wsCCList.QueryTables(1)
        .Connection =  _
        "ODBC;DSN=A010PROD;;DBQ=A010PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;"
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
    If bDebug Then Debug.Print "GetCCList - OUT"
End Sub

The user makes a CC selection from a ComboBox that is linked to the above query resultset, and the SelectedCC is used as a criteria for the MG list...

Sub GetMGList()
    If bDebug Then Debug.Print "GetMGList - IN"
    Dim sSQL As String
    
    sSQL = "SELECT DISTINCT"
    sSQL = sSQL & "  Case When Length(Trim(MACH_GRP))<5 then "
    sSQL = sSQL & "     Substr('00000',1,5-Length(Trim(MACH_GRP)))||Trim(MACH_GRP)"
    sSQL = sSQL & "  Else"
    sSQL = sSQL & "     MACH_GRP"
    sSQL = sSQL & "  END"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM FPRPTSAR.MC_BUILD_SCHEDULE "
    sSQL = sSQL & "WHERE (SF_CC Is Not Null)"
    If [b][SelectedCC][/b] <> " ALL" Then
        sSQL = sSQL & "  AND CC='" & [b][SelectedCC][/b] & "'"
    End If
    sSQL = sSQL & vbCrLf
'    sSQL = sSQL & "Group By"
'    sSQL = sSQL & "  Case When Length(Trim(MACH_GRP))<5 then "
'    sSQL = sSQL & "  Substr('00000',1,5-Length(Trim(MACH_GRP)))||Trim(MACH_GRP)"
'    sSQL = sSQL & "Having Sum((sysdate-lpst)*run_hours)>0"
'    sSQL = sSQL & "   and Sum(run_hours)>0"
    
    Debug.Print sSQL
    
    With wsMGList.QueryTables("qMG_List")
        .Connection = _
        "ODBC;DSN=A010PROD;;DBQ=A010PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;" & _
        "LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;"
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
    If bDebug Then Debug.Print "GetMGList - OUT"
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ettienne]

To expand on my earlier post, I have some clients with some rather complex reports. Crystal was not cutting it because the reports were too complex and took too long to run. Some form of data processing was required before printing the reports, so I pulled the data into Access and got the data to the point where the Crystal report does not have to do any calculations. Then I started running into Crystal version conflicts and so on, and since the user wants the information in Excel anyway I decided to look at the Office Web Components and have never looked back. With OWC you have easy access to pivots and spreadsheets and with a single click the user can get the data in Excel and take it further.
So in the end the user gets his results in Excel, but there is some magic taking place in the middle.

 

[SkipVought]

All kind of ways to skin a cat.

Depends on your level of experience with various application and whatever risk you're willing to take to venture into new areas.

One thing is that whatever tack you take, there is some level of experience from members of Tek-Tips, that will render aid.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]