Hey, trying to set up a report that I would count the number of ranks I have in my db with the entries that have that value in the db.

Hey Menelaos, have you tried using the View by and Stack by options, on the Analytical Designer? After dragging the fact to the Metric section (which gave you this COUNT metric), select the Attribute you want to ‘slice’ the report by, and drag it to the View by or Stack by boxes. It should then contextualize the report by the selected attribute. Take a look at this article for some more information and examples.

Hey, thank you for your response. Just added it but seems like I can't get what I want. It only shows the distinct values I have and one for all the occurences

I see. Here’s another idea: remove the attribute from the View by and try instead to filter it directly on the metric: Expand the metric and click the Add attribute filter option; select the required attribute, and then the value you want to filter. If that works, you can then add the same COUNT metric again, but filtering by another attribute value, and so on. Here is how it looks on my test environment (apologies for the weird attribute names 🙂 )

Got it, I'll try that and revert. Thank you @Francisco Antunes

You can definitely write a custom metric, using a WHERE clause to specify the values you want to be displayed. e.g.:

SELECT COUNT(X) WHERE X = 'value'

But the filtering I proposed earlier would have done the same thing, so I suspect that the metric is a bit more involved than that. Would you mind showing me the specific MAQL metric that you are trying to run?

Hi @Menelaos Kotoglou, just to make sure I understand what you are trying to do (thanks for the SQL that really helps): You have a table “y” with column “x” in it (and some other columns possibly) and for that table you want a chart that will for each value of “x” display a number of occurences in table “y” - basically in how many rows each value of “x” appears in “y”. Is that correct? If yes, then this should be very simple in GoodData and you do not even need to filter for specific values. All you would need is the COUNT metric, but it should not be COUNT(x) - that will give you count of distinct values of X in GoodData. I assume this happened here.) So you need to instruct the system that it should be counting the rows in “y”. Depending on whether you have a unique ID in your table “y”: • If your table “y” does have a column which serves as its primary key (i.e. “y_ID”) , you can simply do

SELECT COUNT(y_ID)

. (or simply drag&drop this “y_ID” to the metrics bucket in analytical designer). • If your table “y” does not have a single column primary key, depending on the GoodData edition you are using, you can: ◦ In GoodData Cloud and GoodData.CN use

SELECT COUNT(y)

- here you can use count with the dataset directly ◦ In GoodData Platform use SELECT COUNT(Records of y) - there will be a special attribute Records of … for each dataset without primary key. It is not visible in the Analytical designer list of attributes, you will need to select it in the metric editor. In any case, this metric that you create this way will be universal. That means it does return number of records in general and can be used with different attributes/columns. To get number of rows by values of ‘x’ just use it together with ‘x’ in the view by/stack by. To see number of records by some other column ‘z’ just use it with column ‘z’ there. Or combine columns ‘x’ and ‘z’ to see number of occurrences of various combinations of these columns. basically it represents the

COUNT(*)

in your SQL example and the

SELECT x,

and

FROM y GROUP BY x

or

SELECT x,z

and

FROM y GROUP BY x,z

are automatic and dynamic based on the metric usage in GoodData. I hope this helps.

Oh got it. I was able to successfully create the report I want to. Thank you very much about it @Michal Hauzírek!!