Solved

Dynamic Reports working with tables but not with Stored Proceedures - URGENT

Posted on 2003-11-20
6
1,415 Views
Last Modified: 2008-03-10
We are using Crystal Reports 9

We are able to overide the connection to the tables with the following:
===================================================

Dim objectFactory,ConInfos,ConInfo,DBName
Set objectFactory = CreateObject("CrystalReports.ObjectFactory.2")

Response.ExpiresAbsolute = Now() - 1

Set ConInfos = ObjectFactory.CreateObject("CrystalReports.ConnectionInfos")
Set ConInfo = Server.CreateObject("CrystalReports.ConnectionInfo")

DBName = Trim(Request("DBName"))

Dim ConnAtts : Set ConnAtts = Server.CreateObject("CrystalReports.PropertyBag")
ConnAtts.EnsureCapacity 3
ConnAtts.Item("Database DLL") = "pdsodbc.dll"
ConnAtts.Item("Database Name") = DBName
ConnAtts.Item("Server Name") = DBName

With ConInfo
      .Attributes = ConnAtts
      .UserName = Request("UserName")
      .Password = Request("Password")
      .Kind = 1
End With

===============
etc.

Then recalling the tables with the connection to call the report
===============

Dim clientDoc
Set clientDoc = ObjectFactory.CreateObject("CrystalClientDoc.ReportClientDocument")
Dim tableCollection, table
clientDoc.Open theReportName

Dim tables, newTable

Set tables = clientDoc.Database.Tables

For Each table In Tables
      Set newTable = Server.CreateObject("CrystalReports.Table")
      Set newTable = table.Clone(True)
      newTable.ConnectionInfo = ConInfo
      clientDoc.DatabaseController.SetTableLocation table, newTable
      Set newTable = Nothing      
Next

viewer.ReportSource = clientDoc.ReportSource
=================================================

The above works beautifully to pull the Report, specifiying the DB Dynamically and view the report.... that has tables....

If the report uses Stored Proceedures... the above loop does not work and gives the following error:
=================================================

RptControllers.dll error '800002c5'

The table 'p_NonLevel_Report;1' could not be found. Error in File C:\WINDOWS\TEMP\{667260C3-E94B-41C8-9DF5-05D0E8482FCE}.rpt: The table could not be found
=================================================

Being that the name p_NonLevel_Report:1 is actually a stored proc., not a table at all.


We need a solution to fix the loop to call both tables and stored proceedures and the report understand each.


- Michael
0
Comment
Question by:Michael Krumpe
6 Comments
 
LVL 7

Accepted Solution

by:
mnye earned 500 total points
ID: 9790064
heres the basics on SP with Crystal:
http://support.crystaldecisions.com/communityCS/TechnicalPapers/storproc.pdf


but it sounds like you just need to apply a hotfix:

http://support.crystaldecisions.com/library/kbase/articles/c2013836.asp

hth
matt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 9792871
This question has been asked before and I believe the answer was you cannot switch a report built with tables to use stored procedures through code.

If that is what you are trying to do I'll try to find the reference.

mlmcc
0
 
LVL 4

Author Comment

by:Michael Krumpe
ID: 9797081
mlmcc, the compiled report itself is referencing stored procs, not tables, thus the error. We are preparing to do the Service Pack update above referenced by Matt... hope that will be the fix.

Question: is there any code as with my loop above that we may reference the collection to identify the stored procs vs. tables, so that the report will work correctly?

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 100

Expert Comment

by:mlmcc
ID: 9797436
0
 
LVL 4

Author Comment

by:Michael Krumpe
ID: 9798202
We will be applying the service pack soon...

side note... we got all of the reports working, both with tables and with stored procs, on the db, we were not applying the proper security for the CrystalUser to have access to the stored proc itself.

Cause in point: CHECK YOUR DB SECURITY

Thanks for all your help.

- Michael
0
 

Expert Comment

by:chongwh
ID: 10559212
Hi MlCC,
            After Applying SP also i am getting the same Error as follows:

Error Type:
RptControllers.dll (0x800002C5)
The table 'skwprojects1.dbo.Command' could not be found. Error in File C:\rpttemp\{94BC1513-7F7B-4303-B77E-23D6B5988405}.rpt: The table could not be found.
/skw/ActiveX.asp, line 250


Is there any security level i ahve set in DB, i m using Mysql.
Plz guide me
Thanks a lot
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

16 Experts available now in Live!

Get 1:1 Help Now