Using an Existing Data Source to Create New Reports

OK, let me show you how you can use existing data sources that are already here to create reports.

We will go to Create New and we have a couple of choices here, analytic analysis reports, interactive report, dashboards, data source, etcetera.

So I will choose interactive report and in a moment we’ll be asked which data source should we use.

The pop up will appear in a second.

OK, and I will choose data source FOIA log data source and I will use this data source to show you that we can create from existing data source many different reports.

Based on this data source, we already have created one big report and the name of that report is called FOIA log all requests. This is just a drag and drop reminder that we use to get started.

We will hide tips and as we can see here, the name of the data source is mentioned here and all available fields that can be used on the reports.

I’m not talking about one report because from this data source we can create multiple reports.

And here we can see we have appeal and request fields.

And from these fields, I will today use just five and I will create a report that I will name FOIA log pending requests.

This report will have data only for pending requests and to mention again, this report is using the same data set as the one for all requests.

So I will get the request number as a first field. I can double click it and automatically it will go on the report.

The second field I will choose is official date received. I can drag and drop it — I will move it to the left.

From now I will just click and it will add those columns sequentially. I will choose requester name, I will choose exemptions, and I will choose cost.

For this report we have 2 pages here. It is no more than 500 rows. This is the limit from Pentaho because if the query is trying to return many rows, you will experience timeout. So you can put it here to be 100, 200, or however you feel is good enough for you.

So currently we have 2 pages. We will go to the second page and we can see that we have appeals here, right?

And we don’t want that because the name of the report is called FOIA log pending requests, not appeals.

Because of that, we have a requester type field that we can use to create a filter based on this field. But we will not create a filter. We will create a prompt.

Prompt is a fancier filter, because it gives you the opportunity to prompt the report, to choose what you filter — in real time you can interact with it.

So I will create a prompt. It’s a filter in the background, but Pentaho gives you the possibility to choose.

You can show prompts here and you can see this pencil is for editing. We want multiple things to be selected, so we’ll go with the list. We will allow multiple selection and we will choose three for displayed values because we want to show all three different types of requests.

So the different types of requests that we have here are new request, PIR request, and appeal. We can select these two types of request and that will exclude appeals in real time.

This is how to submit and that’s when the change is made. The screen refreshes and the change is applied.

OK, so now we don’t have appeals, but here we have requests that are not pending, that are closed.

So what we’re going to do is use this field here — Is Pending — and we create a prompt from this one as well. And we will choose it.

So now we have correct data in the report that matches the title of the report. We have now the FOIA log of all pending requests in the system.

And what we’re going to do now is make it more appealing. We have three tabs here.

We can set up the page in Page Setup. We can click on the page setup and we can choose orientation. I prefer landscape, so I will choose landscape. You can choose page format — there are many page formats and you can play with the margins here as well. The other things we will leave as default.

So the report looks like this right now. Also, I can select from the templates different looks and this is the one we will choose.

What else can we do? We can format everything. The cost column — we want it on the left side. It’s showing a numeric format and the appropriate format for this is this one. I will choose a format that will be more appealing.

So what I’m trying to show you is that everything here can be formatted and edited — and not just that, you can put filters, prompts, you can sort.

So this report is not sorted. We can sort it on official date received. And now we have sorted pending requests.

As you noticed, we ended up with only one page after the filters. So we have only those requests here.