Count Function on a Crosstab

[Revan]

I have a report that I am running on a relational database where I collect all of the processed applications in a given time span. I render that on page 1 of my report. On page 2, I want a crosstab that shows totals by license category (column) and by transaction type (row). I'm using the formula

Code Select Expand

count([Transaction Code] for [License Type]), which isn't working the way I expect it to. I'm expecting that to generate a count of each transaction code that matches the license type, but it's actually generating a number I just don't understand at all (see attachment). It's not the total number of [Transaction Code]s for that license type, and certainly not the actual count of Transaction Codes by License Type that I'm hoping for. On my crosstab, I am using [License Type] as the Column, [Transaction Code] as the Row, and my calculated field [Transaction Count] as the measure. Can anyone explain what I'm missing here?

Also, is there a way to hard-code which License type shows up in each column? I know I can sort it ascending or descending, but, for this report, it would actually be better to be able to strictly define which column each license type shows up in (it would also be nice to be able to do that for the rows). There will always be 6 license types and 6 transaction types. It never changes.

[New_Guy]

Hi,
What are you getting if you just use the count([Transaction Code]) ?
Good luck
New guy

[Revan]

Huh. That works perfectly. But that doesn't make sense to me. Why wouldn't that formula count ALL of the transaction codes and put the grand total on every line? Or at least give the counts of each transaction code, regardless of which license type it is attached to? It works, so awesome! But I'm baffled by it.

Any chance you have any advice about the second part of my question? About hard-coding the columns and rows?

[New_Guy]

Hi,
You can do that with a case statement for each license type and transaction and use conditional styles to hide the null columns. I will get back to you by tomorrow.
Good luck
New guy

[Revan]

Hey New_Guy, if you have time, would you mind posting a sample case statement that would allow me to hardcode a 6x7 table (I had the numbers off by one before, but 6 license types, and 7 transaction codes), so that the values are in the same order every time (which doesn't correspond to an alphabetical order), and show regardless of whether there is data for column/row?

[New_Guy]

Hi,
Create the data items using the below expressions, and substitute the = to values to the values you want to and place them as per your requirement. You will see a null column or row after each of this data item. use suppress rows to suppress this rows/columns. If the actual columns taht you want to see are getting suppressed too then try using a case statement for count data item.

case when [Transaction] = '100' then [Transaction] end

case when [License Type] = '1010' then [License Type] end

Good luck
New guy

[Revan]

New Guy, so I'm trying out what you recommended. I set up case statements for each of the seven transaction codes, and each of license types as well. I then created a crosstab, selected all six of the license types, and placed them into the columns section. Did the same thing with the transaction codes, and placed them in the rows section. Then I put my transaction count in the measures section, and ran the report. I'm attaching a screenshot of the outcome. I've played with this for a day or so trying to get it to work properly, and I can't figure it out.

I'm getting several extra columns (as you predicted), but they're not null columns. They seem to be the difference between the total count of that transaction code and the actual intersection count of license type by transaction code. For example, Transaction Code 1011 has 96 total count. If you take the blank column to the left of column 1001 (92), and add that to the actual intersection between 1001 and 1011 (4), you get 96 again. The numbers that I've highlighted are actually the correct data, and it's counted them correctly, which is awesome, but I can't figure out why the extra columns are there or how to get rid of them. Also, even without suppression, one of my columns (1003) is missing. It should be displaying a zero for each row, but instead it's decided to just take the day off.

[New_Guy]

Hi,
Are you getting the not required columns and rows in the same place all the time, whenever you run the report.
If so use a conditional style using rownumber and column number functions available and do the box type none.
It is a pain to do the reports with requirements like this. Regarding the column(1003) missing, I don't know why it is not appearing. Are you doing any suppression.
Good luck
New guy

[Revan]

It's the same each time I run it for this month, but if I change the date range, the columns change as well. I'm not doing any suppression, no. I've tried it both with and without suppression. Neither has an effect on the output.

[Lynn]

A crosstab will attempt to find the value that occurs at each row/column intersection. That is why New Guy's suggestion back at the beginning of this thread to use count([Transaction Code]) worked perfectly. Cognos spins up a little cube to render the crosstab and it found the value for that metric at each license type/transaction type intersection.

Another approach for the second part of your question about ordering is to create a sort key field. Put license type on columns and transaction type on rows with transaction count as your default measure. Create a license type sort key field something like this:

Code Select Expand


case
   when [License Type] = '1007' then 1
   when [License Type] = '1001' then 2
   when [License Type] = '1002' then 3
   when [License Type] = '1005' then 4
   when [License Type] = '1004' then 5


Click on columns in your crosstab and edit layout grouping and sorting and specify the sort key field.

You could do the same thing for rows.

[Revan]

Lynn, that's amazing. I didn't realize you could sort like that from a field not on your crosstab. It's working very well for me. Also, thanks for the explanation about the count([Transaction Code]). Very clear and concise, helped me to understand that better.

One last issue: License type 1003, we don't get very many of these, and so it's often blank when we run the report. On my crosstab, that column only shows up if there are instances of that license type. Otherwise, it just isn't there. Is there a way to force that column to show up regardless of whether there is data to show? Blank values are fine, as are zeroes, or N/A, whatever. I just want the column to always be there.

[Lynn]

Lynn, that's amazing. I didn't realize you could sort like that from a field not on your crosstab. It's working very well for me. Also, thanks for the explanation about the count([Transaction Code]). Very clear and concise, helped me to understand that better.

One last issue: License type 1003, we don't get very many of these, and so it's often blank when we run the report. On my crosstab, that column only shows up if there are instances of that license type. Otherwise, it just isn't there. Is there a way to force that column to show up regardless of whether there is data to show? Blank values are fine, as are zeroes, or N/A, whatever. I just want the column to always be there.

With relational sources it is a challenge to report on something that simply doesn't exist. One way to address it is to have a query that retrieves all license types, say from a dimension table. Outer join that query to your main query and associate the joined query to your layout. The issue you will have also is that there won't be any transaction type for the 1003 license type so you'll have a blank row as well I suspect.

Alternatively, create a dimensional model, but that is a whole different kettle of fish  :-)

[Revan]

Lynn, once again, great suggestion. It's now working exactly how I wanted it to. You were right about the blank row too (why does that happen?), but a simple suppress blank rows fixed that issue.

New Guy, Lynn, thanks for taking the time to help me out with this. Very appreciated.

[Lynn]

Lynn, once again, great suggestion. It's now working exactly how I wanted it to. You were right about the blank row too (why does that happen?), but a simple suppress blank rows fixed that issue.

New Guy, Lynn, thanks for taking the time to help me out with this. Very appreciated.

The blank row happens because you've manufactured a row in your result set with a 1003 license type and a blank transaction type. When you produce a crosstab you are asking for all license types on columns and all transaction types on rows. You've got a blank transaction type so therefore you've got a blank row.

Glad you got it all sorted.