Topic
  • 5 replies
  • Latest Post - ‏2013-08-28T23:53:08Z by OpherB
CalebMF
CalebMF
55 Posts

Pinned topic Using a count of occurrences in a crosstab

‏2013-08-28T21:28:47Z |

I am currently having an issue using a count of occurences in a crosstab.

For example, going along the left axis of the crosstab, I am showing the month that an individual called in.

Then accross the top axis, I would like to show the number of occurrences.

Then as the measure I would like to count the number of individual meeting both criteria.

So for example, there were 10 people who called 5 times in Jan, so there would be a 10 at the intersection of Jan and 5.

What is the formula I would need to right to be able to show the number of occurences? I am sure you will need more data on this, I am just unsure as to what. So please let me know and I will be happy to provide.

Thank you!

  • OpherB
    OpherB
    1407 Posts

    Re: Using a count of occurrences in a crosstab

    ‏2013-08-28T23:02:21Z  

    Well, let's start with what version you are working in, what Studio and what the data source is.

    Opher

     

  • CalebMF
    CalebMF
    55 Posts

    Re: Using a count of occurrences in a crosstab

    ‏2013-08-28T23:06:34Z  
    • OpherB
    • ‏2013-08-28T23:02:21Z

    Well, let's start with what version you are working in, what Studio and what the data source is.

    Opher

     

    Good point. I am in 10.1, Report Studio and SQL Server.

    Thanks for looking into this.

  • OpherB
    OpherB
    1407 Posts

    Re: Using a count of occurrences in a crosstab

    ‏2013-08-28T23:18:22Z  
    • CalebMF
    • ‏2013-08-28T23:06:34Z

    Good point. I am in 10.1, Report Studio and SQL Server.

    Thanks for looking into this.

    I'll guess that by SQL Server you also mean it's relational data, not a cube or DMR.

    So, the next question is what does the data look like? Is anything alredy aggregated, or are you looking at "call tickets" which the report must count/group/etc?

    Basically, if all you have are the call tickets, you have a call date and the name of the caller (or a unique caller identifier of some kind, since NAME is not unique enough to work) - pretty thin for the kind of analysis you want to do. I would press for an aggregate table to be built using the ETL tool (which is made to do this kind of work), rather than trying to do it all in the report. You MAY be able to do it in a report, but it would be quite slow, and probably not pretty to look at how it was done.

     

  • CalebMF
    CalebMF
    55 Posts

    Re: Using a count of occurrences in a crosstab

    ‏2013-08-28T23:32:21Z  
    • OpherB
    • ‏2013-08-28T23:18:22Z

    I'll guess that by SQL Server you also mean it's relational data, not a cube or DMR.

    So, the next question is what does the data look like? Is anything alredy aggregated, or are you looking at "call tickets" which the report must count/group/etc?

    Basically, if all you have are the call tickets, you have a call date and the name of the caller (or a unique caller identifier of some kind, since NAME is not unique enough to work) - pretty thin for the kind of analysis you want to do. I would press for an aggregate table to be built using the ETL tool (which is made to do this kind of work), rather than trying to do it all in the report. You MAY be able to do it in a report, but it would be quite slow, and probably not pretty to look at how it was done.

     

    Yes, the data is at it's lowest detail without any aggregates. I have a unique identifier that I created within the report to identify each person. I have not used the ETL tool before. Could you point me to any tutorials on how to do this?

    I come from a background where I used Business Objects extensively (only on the front end though). I would have no problem creating a Crosstab within a report, and assigning a count of Calls to each individual. I would think that Cognos could do the same thing.

    When laying the data in a list, without detail works fine. But once I place the same formula into the Crosstab, it no longer gives me the value of "1 call", "2 calls" etc. Is there a formula, or setting on the crosstab to make this happen? Please be patient with me since I am still transitioning over to Cognos.

    Thanks

  • OpherB
    OpherB
    1407 Posts

    Re: Using a count of occurrences in a crosstab

    ‏2013-08-28T23:53:08Z  
    • CalebMF
    • ‏2013-08-28T23:32:21Z

    Yes, the data is at it's lowest detail without any aggregates. I have a unique identifier that I created within the report to identify each person. I have not used the ETL tool before. Could you point me to any tutorials on how to do this?

    I come from a background where I used Business Objects extensively (only on the front end though). I would have no problem creating a Crosstab within a report, and assigning a count of Calls to each individual. I would think that Cognos could do the same thing.

    When laying the data in a list, without detail works fine. But once I place the same formula into the Crosstab, it no longer gives me the value of "1 call", "2 calls" etc. Is there a formula, or setting on the crosstab to make this happen? Please be patient with me since I am still transitioning over to Cognos.

    Thanks

    It doesn't matter what you've done in the past - we're all here to learn. I've been using Report Studio since 2002 and have never created a report like this.

    I have some similar data I can work with. It's transactions by agent, so it nicely parallels your call tickets.

    The first thing to do is ensure that there is only one transaction date per month, which will be the rows for the crosstab. The easiest way to do that is to apply the function

    _first_of_month()

    to your [transaction_date] data item.

    Next, create the "Call Count", which will be your columns, using this expression:

    count(rows for [Table].[Caller_ID] , [Trans Date])

    Next, bring [Caller_ID] in as a separate data element. You don't want to use the entry in the Call Count.

    Finally, create the measure, which will be the count of unique callers under each call bucket per month:

    total(count(distinct [Caller_ID2] for [Trans Date], [Call Count]))

    This expression counts the unique caller_ID values for each date and call bucket, and then total's the count. It looks strange (Why total the count? Isn't there just one count?) but a little experimenting will show you why this works.

    Good luck - and thanks for the challenge.
    Opher