ASP - Crystal Reports in ActiveX viewer

jay-are
jay-are used Ask the Experts™
on
I have some code that uses the activeX viewer to show crystal reports.  When you try to refresh the report it asks for the sql db username and password of course.  How can I code this with each asp page so it doesn't ask the user for it?  Security isn't an issue here so I just want to make it as easy as possible for the users to view the reports.  Hopefully this is something that can be done in the code.  I'll include it here:

<%@ Language=VBScript%>
<html>
<head>
<title>
Crystal Reports Viewer
</title>
</head>

<OBJECT ID="CRViewer"
CLASSID="CLSID:2DEF4530-8CE6-41C9-84B6-A54536C90213"
WIDTH=100% HEIGHT=100% CODEBASE="activexviewer.cab#Version=9,2,0,442" >
<PARAM NAME="EnableDrillDown" VALUE=1>
<PARAM NAME="EnableExportButton" VALUE=1>
<PARAM NAME="DisplayGroupTree" VALUE=0>
<PARAM NAME="EnableGroupTree" VALUE=0>
<PARAM NAME="EnableAnimationControl" VALUE=1>
<PARAM NAME="EnablePrintButton" VALUE=1>
<PARAM NAME="EnableRefreshButton" VALUE=1>
<PARAM NAME="EnableSearchControl" VALUE=1>
<PARAM NAME="EnableZoomControl" VALUE=1>
<PARAM NAME="EnableSelectExpertButton" VALUE=1>
</OBJECT>

<SCRIPT LANGUAGE="VBScript">
<!--
Sub window_onLoad()
Page_Initialize()
End Sub

Sub Page_Initialize
On Error Resume Next
Dim webBroker
Set webBroker = CreateObject("WebReportBroker9.WebReportBroker")
if err.number <> 0 then
window.alert err.description
window.alert "_LOC_The Crystal ActiveX Viewer is unable to create its resource objects_ENDLOC_."
else
Dim webSource0
Set webSource0 = CreateObject("WebReportSource9.WebReportSource")
webSource0.ReportSource = webBroker
webSource0.URL = "mistakes new detail2.rpt"
webSource0.PromptOnRefresh = True
CRViewer.ReportSource = webSource0
end if

CRViewer.ViewReport
End Sub

-->

</SCRIPT>
</html>

I've tried adding something like mistakes new detail2.rpt?init=activexl;username=blah blah

All that gave me was a blank window.  Help!


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2005

Commented:
Does this make a difference?

webSource0.PromptOnRefresh = True


What if that is set to false?

FtB

Author

Commented:
When I load up the page it shows the report and everything fine.  It only asks for the db logon and pass when you hit refresh.  With that set to false it asks for it as soon as you load up the viewer.
Top Expert 2005

Commented:
Oh, that's even worse!

Once again, let apologize for my ignorance about the .net environment before I start...

What would happen if you set up and opened your connection object for the report explicitly first? That way, you shouldn't be prompted...


FtB
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
I'm not sure.  This is a classic asp page so I'm even further lost than if it was asp.net!  :)

I *borrowed* this code from someone else on EE that was doing the same thing I was.  

Top Expert 2005
Commented:

Author

Commented:
FtB:

I've looked over their docs before and haven't had much luck.  I did find this in there:

 With crConnectionInfo
            .ServerName = "EnterServerNameHere"
            .DatabaseName = "Pubs"
            .UserID = "EnterUserIDHere"
            .Password = "EnterPasswordHere"
        End With

So I changed my code:
ebSource0.ServerName = "Gwpf-web01"
webSource0.DatabaseName = "Gwpf"
webSource0.UserID = "userid"
webSource0.Password = "pass"

So I did that but when I hit refresh on the viewer its still asking for the username and pass.  This particular set of reports is also pulling data from 2 tables in 2 different databases.  I'll spend some more time looking through these samples!!
Top Expert 2005

Commented:
I thought that you ended up consolodating your DB's or am I thinking about someone else?

In any case, if you can work with one the simple examples there, that would be best. The code that you posted above is IE only and relies a fair deal on client-side scripting. I would prefer to do as much of this server side as possible.

FtB

Author

Commented:
Yeah I did consolidate db's but these reports haven't been rebuilt yet and are for testing purposes for now.  I will go back later and change them to the one db when they go live on the website.

I guess I need to scrap this borrowed asp page and use one of crystal's sample asp.net pages since I'm a little more familiar with it.  I will continue reading up on this tonight and tomorrow morning and let you know what I find out.
Thanks!
Top Expert 2005

Commented:
I know that it is a pain, but since you are working in .net already, it might be best to learn how to use CR in that environment so that you don't have to switch back and forth. Once you get one report working (and make it a very simple test report--maybe something that just displays today's date) the rest should be easier.

FtB
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I don't know how well CR handles report names with blanks.

You might try renaming the report
webSource0.URL = "mistakes_new_detail2.rpt"

mlmcc

Author

Commented:
Actually I haven't had any problems with the report names.  I just can't figure out how to embed the database logon info in the asp code.  I've found some examples at crystal decisions but they all seem to be visual basic programs.  I don't have that program so that didn't help much.  

My boss may buy it for me as an xmas gift so we shall see.  Otherwise if you have any ideas on adding the UID & PWD to the code that would help!  :)

Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
I use VB and we are using NT integrated security on the database so I don't have to pass the username and password.

This might help

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/aspxmps8.exe.asp

If not which version and edition of Crystal?

mlmcc

Author

Commented:
We are using CR9 with the enterprise pack.  So the logons are set to Enterprise instead of NT.  I don't think that's something I can change easily.


I've found all sorts of examples but haven't had much luck so far.  Keep in mind I'm a noob to VB and Crystal.

I have an asp page that does work but it uses the DHTML viewer.  It logs onto the enterprise server first I guess and find the published report and displays it.  I've been trying to adapt it for the activex viewer with no luck.

here is the code:
<%
            dim SessionManager, Sess
            Set SessionManager = Server.CreateObject("CrystalEnterprise.SessionMgr")
            Set Sess = SessionManager.Logon("administrator", "", "myserver", "secEnterprise")


    ' Get the current info store object.
    Dim iStore, id, result
    Set iStore = Sess.Service ("", "InfoStore")

      Function DisplayReport(strReportName)

            Set result = iStore.Query("Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_NAME = 'New' And SI_INSTANCE = 0")

    ' Get the ID of the report to be viewed.
    id = result.Item(1).ID

    ' Create the viewer and its report source.
    Dim viewer1, rptFactory, rptSource
    Set viewer1 = Server.CreateObject( "CrystalReports.CrystalReportViewer" )
    Set rptFactory = iStore.EnterpriseSession.Service("", "PSReportFactory")
    Set rptSource = rptFactory.OpenReportSource(CInt(id))

    ' Set the viewer's properties to prepare it for viewing.
    With viewer1
                        .HasCrystalLogo = False
                        .IsDisplayGroupTree = False
                        .IsEnableDrillDown = True

        .ReportSource = rptSource
        .EnterpriseLogon = iStore.EnterpriseSession
          .Refresh
        .HasRefreshButton = True

    End With

    If Err.Number <> 0 Then
    'There was an error setting up the viewer.
        Response.Write "Unable to create report viewer. " & Err.description
    Else
    'Process the request and launch the viewer.
        viewer1.ProcessHttpRequest Request, Response, Session
    End If

End Function

%>

Any ideas?  I finally got a copy of Visual Studio.Net so I've been trying to work with it but it will take me some time to learn how to do everything with it...

Author

Commented:
I've solved it!  Actually someone else helped me but its working.

The solution:

webSource0.URL = "mistakes new detail3.rpt?user0=sa&password0=bigdellsql1&user1=sa&password1=bigdellsql1&user2=sa&password2=bigdellsql1"

When using the RDC you have to just pass the username and pass with the url.
I tried this once but didn't have the syntax right so I moved onto something else.
I'm going to split the points since both of you gave me links to stuff that helped!

Thanks!
Top Expert 2005

Commented:
I am sorry not to have helped any more--it seems that all of the questions that you have been asking of late have fallen outside my area of experience.

I am glad that you are set.

Fritz the Blank

Author

Commented:
Actually the examples you pointed me towards helped out.  I just had to sit down with a Crystal Enterprise guy and talk about the examples and why it wouldn't work and bam we got it to work.

Thank God, cause the plain HTML viewer that I was using is nasty!

Thanks for your help and I'm sure I'll have more questions soon!
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Glad I could help

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial