Ad Hoc Reporting

Forum|Forum|10 years ago
March 24, 2015
2 replies
0 views

Dominique Sacre
Rocketeer

I work for an Agriculture software company as a Crystal Reports Developer. I’ve been asked to research a way that we can deliver Ad Hoc reporting capabilities to our customers. I came from a Microsoft SQL environment and now utilizing the Micro focus tools.

Considering our database structure is massive and too many fields/tables for the customers to deal with directly (support nightmare). I was thinking I could deliver pre-defined stored procedures that included the most common fields and linkage to other supporting tables already done. Then the end-user could write a report using these SP’s. Come to find out Relativity would not be able to support this.

The software tools that I’m familiar with is the Relativity Designer and Crystal reports. The programmers use the RM – COBOL software.

My Question: Is there any tools or a way to solve this with the Micro-Focus supported software?

Thomas Morrison
Participating Frequently
Forum|Forum|10 years ago
March 24, 2015

I work for an Agriculture software company as a Crystal Reports Developer. I’ve been asked to research a way that we can deliver Ad Hoc reporting capabilities to our customers. I came from a Microsoft SQL environment and now utilizing the Micro focus tools.

Considering our database structure is massive and too many fields/tables for the customers to deal with directly (support nightmare). I was thinking I could deliver pre-defined stored procedures that included the most common fields and linkage to other supporting tables already done. Then the end-user could write a report using these SP’s. Come to find out Relativity would not be able to support this.

The software tools that I’m familiar with is the Relativity Designer and Crystal reports. The programmers use the RM – COBOL software.

My Question: Is there any tools or a way to solve this with the Micro-Focus supported software?

"Come to find out Relativity would not be able to support this."

Who told you this?

Tom Morrison, Consultant, South Carolina, USA

Like

Thomas Morrison
Participating Frequently
Forum|Forum|10 years ago
March 24, 2015

I work for an Agriculture software company as a Crystal Reports Developer. I’ve been asked to research a way that we can deliver Ad Hoc reporting capabilities to our customers. I came from a Microsoft SQL environment and now utilizing the Micro focus tools.

Considering our database structure is massive and too many fields/tables for the customers to deal with directly (support nightmare). I was thinking I could deliver pre-defined stored procedures that included the most common fields and linkage to other supporting tables already done. Then the end-user could write a report using these SP’s. Come to find out Relativity would not be able to support this.

The software tools that I’m familiar with is the Relativity Designer and Crystal reports. The programmers use the RM – COBOL software.

My Question: Is there any tools or a way to solve this with the Micro-Focus supported software?

What you are describing is view. You can use the CREATE VIEW sql statement to save such simplified glimpses of the database. (And thank you for calling it a database!) You don't need to create stored procedures.

Now for the small problem: you cannot easily assemble a CREATE VIEW in the Relativity Designer. I think you can execute a CREATE VIEW from the SQL window, but I have not tried it. If that works then all is well. If not, then there are any number of open source (free) products around that will allow you to execute a CREATE VIEW. I used to do it using pass-through queries on MS-Access.

ALSO

An underused feature of Relativity is the ability to add descriptions on the columns. This description is then passed through to the ODBC application when it uses the SQLColumns function; the description is in the REMARKS column of the result set. I am not enough familiar with Crystal Reports to know if Crystal's designer makes the REMARKS available through its user interface.

Adding column descriptions can help guide the end user when assembling an ad hoc report.

ALSO

Relativity also supports column and table privileges via GRANT/REVOKE. This is administered through the Relativity DBA tool. Privileges require the use of user names so that the correct privileges can be generated. I mention this because you can segregate your users into beginners, advanced, and power, exposing more and more of the database features as you go along. You can also use privileges to limit the number of tables that particular users can see - perhaps based upon the function of the user.

Relativity has a lot of 'hidden talent'. Big Smile