Solved

How can I view where a report is pulling its data from?

Posted on 2013-05-29
11
385 Views
Last Modified: 2013-05-30
This question is exactly what I want, unfortunately my knowledge of MSSQL is too basic to even understand the answer.

http://www.experts-exchange.com/Database/Miscellaneous/Q_20890718.html

The accepted answer just said "Or you can look at the report in design view and go to properties to see the query that is feeding it".  For me this just triggers more questions.

1. "Design view" in which software?

As I look through the installed programs on the database host, I don't see anything leaping out, like Crystal Reports, to view the contents of a .rpt file.  I can connect to the SQL Server from MSSQL Management Studio, but that doesn't seem to allow me to open the .rpt file.  I have found some .log files on the host, and that is where I found the paths to the .rpt files.  I have tried opening the .rpt files with Notepad, but that results in a lot of ASCII salad.

I can't even promise that it is Crystal Reports producing the reports.  What I know is that our operator can click a button that says "Reports", configure some parameters for the report and then print it.  My ultimate objective is to get the data of the report that gets produced in its own table.  Of course that wouldn't even be necessary if I just knew where the data was stored and how to extract it.
0
Comment
Question by:StudmillGuy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 14

Expert Comment

by:LinInDenver
ID: 39205430
If it is a .RPT file extension, odds are it is Crystal Reports. I believe SSRS uses a different extension. I would recommend downloading the trial version of Crystal Reports (they have 30 day available). You could then attempt to open the file, click the Database Menu, and choose "view SQL"

You could post some screenshots and we could probably tell you pretty quickly from a look if it is Crystal or not. They have a very distinctive look and feel, especially the user parameter window.

If there is an export option once she has the report open, check to see what choices there are. Built in Crystal viewers typically show "Crystal Reports" as an export choice along side Excel, Word, PDF, etc.
0
 

Author Comment

by:StudmillGuy
ID: 39205464
Thanks Lin.  I am now quite certain that it IS crystal reports.  I am running a trial version of something called "The Report Viewer Pro" and it produced the attached error dialog when I tried to open the .rpt file of interest.

So where do you recommend I go from here?
Dialog.jpg
0
 
LVL 1

Expert Comment

by:ForFracksSake
ID: 39205472
Take a look at this pdf file. It's step by step on how it is done. I think it may help in your situation, it did me.

http://gacounts.caes.uga.edu/help/documents/Helpsheet_Pulling_Reports.pdf
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

Author Comment

by:StudmillGuy
ID: 39205478
Wouldn't Crystal Reports have to be running on the host for it to be producing the reports?
0
 
LVL 1

Expert Comment

by:ForFracksSake
ID: 39205481
Yes. It would, ignore that link, that may have been the wrong one.
0
 
LVL 1

Expert Comment

by:ForFracksSake
ID: 39205488
It could be on a public server as well.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39205490
Unless you have a copy of Crystal, there is no easy way to determine the source of the data.

The error from the Report Viewer Pro is because the report can't find the database or the database driver.

Ant idea where the RPT files came from?

mlmcc
0
 
LVL 14

Accepted Solution

by:
LinInDenver earned 500 total points
ID: 39205492
The Viewer tool can only really be used if the report has been exported with data saved in it. It also won't allow you to see the designer specifics (such us the underlying SQL statement).

If you can download the trial version of the Crystal Reports Designer, I think you will be well on your way. You can view the SQL that way. It will prompt you to connect to a database when you do this. Just pick any database (or fake one in Access - it just needs to connect to something before it will allow you to view). It won't change the underlying SQL statement so you should be able to see exactly what and where it is running against.
0
 

Author Comment

by:StudmillGuy
ID: 39205494
Thank goodness.  I definitely couln't find what I needed in that document.
0
 

Author Comment

by:StudmillGuy
ID: 39205518
Hmmm.  I work for a pretty large company.  I'll see if our DBA can see what is in that rpt file.
0
 

Author Closing Comment

by:StudmillGuy
ID: 39207929
Thanks all.  My It department had someone who could open the rpt file and that gave me enough info to get what I wanted.  It turned out that report depended on a rather elaborate stored procedure that created a temporary table that fed the report...and then the table disappeared.  That's why i could never find a table that contained ALL of the data.
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 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