How to use SQL server store procedure to generate dashboard?


I am new to TIBCO jasper soft, I have a store procedure which takes some parameters like user ID, and some other Ids and returns records in thousands, and I want to create dashboard with charts, graphs and list of these records with some filters for data analysis.

For this I have converted my store procedure query into SQL view (because my store procedures is not showing in the tables tab while creating Ad Hoc Domain), and added this SQL view to Ad Hoc domain. When I use this domain to create Ad Hoc View it took lots of time to render or get data. My plan was to generate dashboard with this ad hoc view, but it failed because of slow speed, store procedure is much faster than SQL view because of some technical reason.

Now I want to use my SQL store procedure to get data in TIBCO jasper soft directly to create Ad hoc view or dashboard, but I am not able to find any way to use store procedure to get data for TIBCO jasper soft’s ad hoc view, domain or dashboard.

My end goal is to show the data on TIBCO jasper soft ad hoc dashboard with some filters and charts for the sake of data analysis and show the same dashboard in my MVC application. I also want to generate some reports of this data based on custom filters. Please suggest me any way to achieve this. My main questions are:-

1.     Can I get data from parameterize store procedure for Ad Hoc View or domain or dashboard?

2.      Is it possible to pass parameter’s value dynamically and get data in ad Hoc view or dashboard with graph and charts representation?

3.     How can I show my TIBCO jasper soft’s dashboard in mvc application? Do you guys provide and kind of API or embedded URL, so I can show it in iFrame or something similar to that?    

If I am missing any explanation or my question is not clear to you guys, Please let me know.
Its bit urgent so please reply ASAP.


Rajender kumar's picture
Joined: Jun 28 2017 - 2:23am
Last seen: 5 years 7 months ago

1 Answer:

1. Not domain. If you want to use stored procedure for ad hoc you'll need to use a regular topic. Upload a JRXML that calls your stored procedure as a query to the /adhoc components/topics folder and then you can select it when creating a view. You can add parameters to the report and then use these parameters in the query and build input controls on these parameters on the server. When in ad hoc editor you can press a button to bring up the parameter window and pass parameters to the topic.

2. See 1.

3. You can use Visualize.js to embed the dashboard. Visualize.js is a javascript library that allows you to embed content from JasperServer into your own applications. See visualize.js guide for more info on that.

Friendly User's picture
Joined: Oct 8 2009 - 5:59am
Last seen: 3 weeks 15 hours ago