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; } } }

Hide chart conditionally

Rob77

Hello,
let's assume I monitor fuel prices in different cities during few months. I put the data to cross tab, with:
- months in columns
- cities/different fuel in rows
So far so good.
Then I want to present the data in charts. Either one kind of fuel in different cities for given period, or one city with different fuels for given period.
I added two charts to my report. And they do their job. Unfortunately, there are some minor glitches:
1. Sometimes I select only few months (1 or 2, for example). I don't need charts to be drawn then. I thought about using number of cross tab columns + visible rule in chart, but I still have troubles calculating cross tab columns.
2. When I select one city and some fuels, I need only one of the charts. When I select one fuel in different cities, I need the other one. How can I hide them conditionally? How can I know number of inner/outer rows of crosstab?
3. When I select multiple cities and fuels, I don't want charts, because comparisons could be meaningless. (At the moment I use 2D charts only)

What would be your advise?
I have thought about using chart data instead of crosstab, but I haven't found proper method to access the data.

Regards,
Robert

Find more posts tagged with

Comments

mwilliams

Hi Robert,

I'll look into a way to count the columns for question #1. For #2, you should be able to use the visibility feature to check the city parameter to notice you only chose 1 city. For #3, again, you could do a check on your parameter where you are selecting multiple cities in the visibility expression.

If you could provide a sample chunk of data that I could make a report out of, maybe we could figure a way to create the charts directly from the data and not use the crosstab.

Rob77

As usually, thanks a lot. I haven't thought of using parameters for that purpose, although now it looks as the simplest solution

In the attachment you can find sample CSV file, which could be used to model the report.
Regards,
Robert

mwilliams

Robert,

Is this how your data looks inside your dataSet? Where the yyyy.MM is actually the column header? If not, I'll need to know the structure of you dataSet that you're wanting to use as the source for the chart so that I can mimic its exact setup with the .csv file.

Rob77

Yes,
that's the way my data look like.

I was able to develop your idea in no time (using parameters). I choose all three dimensions using report parameters, so it appeared to be very easy.
The only disadvantage I see is that sometimes there are no data for given month or given city or given fuel (i.e. some cells can be empty). Then number of chosen parameter values is different than number of columns in cross tab.
Ideally, I would prefer to decide whether display chart or not basing on crosstab dimensions, not parameters number, when they differ.
But that appears to be more complicated.
By the way - what difference would make if I change columns format?

Regards,
Robert

mwilliams

Robert,

You can count the months by doing something like the following.

In the report's "initialize" script, put:

monthCount= 0;
temp = "";

In the onCreate script of one of the summary fields of the crosstab, put:

if (data["Month"] != temp){
temp = data["Month"];
monthCount++;
}

Then you should be able to use this value in your visibility expressions. Let me know how it goes.

mwilliams

You could also do the same with this.getValue() if you put the script in the column dimension element's script.

mwilliams

Robert,

With the dates as column titles, how are you using them as a crosstab dimension?

Rob77

My apologies,
the above data have been directly exported from manually created data set in OpenOffice. I haven't tested it afterwards.
Thank you for your suggestions with calculations. If I understand correctly, it is based on internal way of calculating cross tab cells values:
for (i=0; i<columns; i++)
for (j=0; j<rows; j++)
calculate_cell(i, j); /* i - horizontal axis, j - vertical */
Am I right? In my opinion it would be much more convenient to get relevant values directly from crosstab. And the behaviour may change in the future...

As for the original data. I have prepared something more representative (csv as well as report using it). It doesn't contain parameters nor charts, but at least show the way I see the data.

Thank you very much again for your time and help.
Robert

mwilliams

Robert,

The first calculation that I gave you would be in incrementation based on how many columns were in the crosstab. It would execute for every cell, but would only increment when there was a change in the value. The easiest way I can see to find the number of columns in a crosstab, is to initialize a variable in the initialize method of the report i.e. columnCount = 0. Then, in the column dimension you're wanting to count, you can simply put columnCount++. Then, you can use this value to decide whether to hide or not. I haven't found a way for the crosstab to just tell you how many columns there are. That doesn't mean that there isn't a way to do that though.

I'll take a look at the report design you attached.

mwilliams

Robert,

If you could include sample charts like the ones you're trying to hide in the report, that'd be helpful at seeing what you're trying to hide as well.

Rob77

I'll try to attach full report tomorrow, including parameters and charts.

Regards,
Robert

mwilliams

Robert,

Sounds good. Not sure if you saw that I had posted in here twice before, as the second post pushed the thread to two pages. The first one explained the incrementation I was doing.

Rob77

Ok, thanks, I have seen previous page, so that's fine.
In the following attachment you can find my raw report, but containing all required things:
- parameters
- cross tab
- charts
According to my first posts, I don't need both charts and want to hide them basing on number of:
a) columns (Date)
b) outer rows (City)
c) inner rows (Fuel)

Regards,
Robert

mwilliams

Robert,

Take a look at this modified version.

Rob77

Yes, the attached version looks perfect.

Thanks a lot,
Robert

mwilliams

No problem. Glad to help. Let us know whenever you have questions.

Rob77

Actually I have one...
Is it possible to display pop-up window when I am above crosstab cell (and maybe click mouse button)? This window could contain additional information, like all the fuel prices, when there's only average displayed. Or name of person submitting data. Or any other which would be too big to fit into cell.
For charts I think I saw some interactivity possibilities (mouse events, things like that). But what about crosstab?
To make that simplier - is it possible to display pop-up window with static text when mouse action is performed on crosstab (mouse over/mouse click)?
If not, I would probably think of subreport, which would be activated by cell's contents acting as link. So it would open another page instead of popup.
I assume it would work only for html output. Am I right?

Regards,
Robert

mwilliams

Robert,

I'm not sure of an on click or mouse over solution, but you could definitely do the sub report that would be called through a hyperlink. This would work in all output formats, not just HTML.

Rob77

Yes, that would do it.
Thanks a lot.

Regards,
Robert