nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

static design using dynamic query

alexis

I am developping BIRT report with Eclipse, using MySQL database.
For the sake of simplicity, i am joining CSV extracts (with TXT extension) of tables I'm using.

My purpose is to display data in a 'table' :
- day D minus 1 to D minus 8 as columns
- metrics per protocol as rows
Per protocol, I have 2 metrics to display, plus 1 computed rate.

In the report I joined,
- the 'Grid - detail' is the layout I would like to obtain, but I don't know how to :
* display the metrics I want in the correct cells. I.E. how to specify a specific column/row of a data set along with conditions.
* add row of computed data every 2 rows (rate) which is the quotient of the 2 previous rows

- the 'Cross Tab - detail2' is the way I found to display metrics.
* have the desired layout
* bind data set parameters (in the same way as with table, grid, chart, ...)
* add row of computed data (as above)

I couldn't manage with both, so I require help.

Find more posts tagged with

Comments

mwilliams

From the looks of it, it'd be easiest to use a crosstab for the grid, as well. You'd use the date as the column dimension and split your service description with computed columns to get your row dimensions. Then, you could add a subtotal row to the crosstab and put your own label and data element in the totals row to show what you're wanting for the rate row.

alexis

looks great

thanks a lot, Michael !

mwilliams

You're welcome. Let us know whenever you have questions!

alexis

Hi,

I have some issues with the cross tab use :
- how do I bind the parameters of the originating query (MySQL) parameters to report parameters or outer elements
- how can I have more than 1 row of values per row criteria (group), row not colum.
Alexis

mwilliams

Maybe I'm misunderstanding, but with a crosstab, to use an outer element to limit the crosstab, you'd have to use a filter. For the other question, can you explain more? Thanks!

alexis

I really need to use an outer element to pass as a parameter to the MySQL query of the data set. I could not find a way through the cube definition nor in the cross tab parameter (as it is possible with table, graph, ...). Do you mean it is possible with a filter ?

For the other question, I have made tests to understand the cross tab, and I wil start a new topic if necessary.

mwilliams

To limit the data of an embedded crosstab, you'd need to have a cube created that has all of the data you'll need for each embedded crosstab. Then, you would use a filter to limit the data in each crosstab to the outer table's grouping value. The filter on the crosstab won't limit the dataSet that's used. It will simply filter the cube results, so all of the necessary data will need to be in the cube. I'm pretty certain this is still the only way to limit an embedded crosstab. Let me know if I'm misunderstanding your issue.

alexis

Ok thanks. your explanation is clear.

Now I come back to the cross tab I would like to build.
using the example report you provided (testReport1.rptdesign), I manage to build the main part (see included file testReport2.rptdesign).
For each "stream" I have 2 "metric", and I would like to add a 'computed' line with a combination of each couple of 'metric' values with a ratio (1st value divided by the 2nd).
I tried to use SubTotal functionality but ther is not the relevant function.
Any idea ?

mwilliams

Take a look at this. I used script in the measure element to set global variables to be used in the calculation. Then, I recall the global variables based on the dimensions and compute the percentage, in a dynamic text. Hope this helps.

alexis

Thanks Michael !

I just see a formatting issue of percent value, as dynamic text does not allow number formating. Instead, I used a data ans it seems it gives the same result.

Last, I wonder about how to filter and have a crosstab per host. My understanding of your reply #8 is that the filter is at crosstab level, so if I want to have a crosstab per host I have to create a group with hosts and then a filter between host group and the outer list host name. The attachment figures out my try, can you check if it is the best way to realize this ?

It seems I will eventually overcome the problem, thanks to your help !

mwilliams

Yes, you would need to have the host field as a part of your cube. The field could be passed as an attribute through another dimension field for use in the filter though, so that it doesn't have to be apart of the actual crosstab. I'm taking a look at the report, right now.

mwilliams

Yeah. What you have there seems correct. Are you having any issues with it?

alexis

Thanks Michael.

I have no issue with this, just want to make sure.

I have 2 further problems :
- the Run mode fails with a Java exception, e.g. Run->View Report->In Web Viewer, and I don't know how to debug nor investigate. the reported error detail does not give detailed information about where the problem is.

- When I set borders to the crosstab, I get a shift in header line of the drawn table. This is not very visible in the preview, but the rendering is worse in a Run->View Report->As PDF (see joined image). In my humble opinion, it is due to the fact that the crosstab visual header is split among several cells. Can you help me solve this issue ?

mwilliams

Is the error you get the null pointer error?

As for the difference in the header, if you select the crosstab in the layout and go to the general section of the property editor, you'll see an option to hide the measure header. That should take care of that.

alexis

yes it is the null pointer error. I don't have it with preview, but with all Run->View Report attempts.

Ok for the 'hide the measure header' option, thanks.

mwilliams

Try grouping your list and putting the crosstab in the group footer or header instead of in the detail row. That fixed the error for me.