Mittwoch, 12. Mai 2010

A viewer for Crystal Reports with PowerShell (partially working)

Hello,
this time I tried to build a small Report Viewer using the components from Visual Studio 2008 with PowerShell. It shows that using PowerShell here makes some things rather easy.

I'm using this to display old Reports made with Crystal Reports 8.5 using ODBC Datasources to SQL-Server.

Reports based on views works fine, I can set database and formulars.

But I have still a problem with reports based on stored procedures. Setting $table.location doesn't work anymore as in the past. I go the error table not found. Any hints welcome.

And here is the code if you want to try something like this:

Function Show-CrystalReport            
{            
    param(            
        $reportPath,             
        $servername,             
        $databasename,             
        $userId,             
        $password,            
        $RecordSelectionFormula,            
        $formulas,            
        $parameters            
        )            
                    
            
    [reflection.assembly]::LoadWithPartialName('CrystalDecisions.Shared')            
    [reflection.assembly]::LoadWithPartialName('CrystalDecisions.CrystalReports.Engine')            
    [reflection.assembly]::LoadWithPartialName('CrystalDecisions.Windows.Forms')            
            
    $report = New-Object CrystalDecisions.CrystalReports.Engine.ReportDocument            
    $report.load($reportPath)            
            
    $report.ParameterFields | % {             
        if ($parameters.keys -contains $_.Name)            
        {            
            $report.SetParameterValue($_.Name, $parameters[$_.Name])            
        }            
     }               
            
    $report.DataDefinition.FormulaFields | % {            
        if ($formulas.keys -contains $_.Name)            
        {            
            $_.Text = $formulas[$_.Name]            
        }            
    }            
            
    if ($RecordSelectionFormula) {            
        $report.RecordSelectionFormula = $RecordSelectionFormula            
    }            
            
    foreach ($Table in $report.Database.Tables)            
    {            
        $table            
        $tli = $Table.LogonInfo            
        $li = $tli.ConnectionInfo            
        Write-host "location : $($table.location)"            
                    
        $li.ServerName = $servername            
        $li.DatabaseName = $databasename             
        $li.UserID = $userId            
        $li.Password = $password            
        $Table.ApplyLogOnInfo($tli)            
        # the following doesn't work as in the past            
        if ( $table.location -contains '.')            
        {            
            $table.location -match  '(.*)\.(.*)'            
            $table.location = "$($databasename).dbo.$($matches[2])"            
        }            
        $table.location             
                   
    }            
            
    $rv = New-Object CrystalDecisions.Windows.Forms.CrystalReportViewer            
    $rv.ReportSource = $report            
    $rv.Dock = [System.Windows.Forms.DockStyle]::Fill            
            
            
    $form = New-Object Windows.Forms.Form            
                 
    $form.Height = 810            
    $form.Width= 1210            
    $form.Controls.Add($rv)            
    $rv.Show()            
    $form.ShowDialog()            
            
    $rv.Dispose()            
    $report.Dispose()            
    $form.Dispose()            
            
}            

Keine Kommentare:

Kommentar veröffentlichen