Topic
9 replies Latest Post - ‏2014-08-28T16:00:56Z by sakthivelmani
sakthivelmani
sakthivelmani
12 Posts
ACCEPTED ANSWER

Pinned topic Can i use count of an attribute as a measure

‏2013-06-05T18:05:45Z |

Hi,

 

I am new to cognos. I am trying to create a dash board. Can i create a column chart with count of an attribute as y-axes. When i tried so, i got no data available. Even though , i can see the data in the report , i could not see it as a column chart. Please help

 

 

Thanks,

Sakthi

  • OpherB
    OpherB
    664 Posts
    ACCEPTED ANSWER

    Re: Can i use count of an attribute as a measure

    ‏2013-06-05T21:41:09Z  in response to sakthivelmani

    It would be helpful if you described in more detail the solution you were looking for. What is the data source. What Studio are you using? What is the data type of the attribute and what other data elements are you working with in the chart?

    Regards,
    Opher

     

  • sakthivelmani
    sakthivelmani
    12 Posts
    ACCEPTED ANSWER

    Re: Can i use count of an attribute as a measure

    ‏2013-06-05T23:15:03Z  in response to sakthivelmani

    Sorry Opher.

    I am getting the data from oracle table. I am using report studio to create this column chart.I have three fields. College_id, department_id and course_name. I get the count of course name in the report.the report looks like

     

    college   department   count(courses)

    A              math          55

    B               sci             75

     

    But am unable to get the column chart with this data. I tried to put college in the X-axis, Department in the series and Count(courses) in Y-axis as measure.Am getting the output as no data available.

     

     

    • OpherB
      OpherB
      664 Posts
      ACCEPTED ANSWER

      Re: Can i use count of an attribute as a measure

      ‏2013-06-10T19:44:01Z  in response to sakthivelmani

      So there is another column (courses) which is not being shown. I would add that column back into the query and change the "count" expression to "1" and just let the chart engine total the value.

      Good luck,
      Opher

       

      • sakthivelmani
        sakthivelmani
        12 Posts
        ACCEPTED ANSWER

        Re: Can i use count of an attribute as a measure

        ‏2013-06-21T15:52:06Z  in response to OpherB

        Hi Opher,

        Thanks for the reply.

         

         

        select "Outcome"."COLLEGE" "COLLEGE", "Outcome"."DEPARTMENT" "DEPARTMENT", "Outcome"."OUTCOME_GRADUATION_DATE" "OUTCOME_GRADUATION_DATE", count("Outcome"."ID")  "ID"
         from "ODSMGR"."OUTCOME" "Outcome"
         where "Outcome"."OUTCOME_GRADUATION_DATE" in (TIMESTAMP '2013-05-30 00:00:00.000000000', TIMESTAMP '2013-05-31 00:00:00.000000000', TIMESTAMP '2013-06-04 00:00:00.000000000', TIMESTAMP '2012-12-30 00:00:00.000000000')
         group by "Outcome"."COLLEGE", "Outcome"."DEPARTMENT", "Outcome"."OUTCOME_GRADUATION_DATE"

         

        I am using a crosstab report. I have college and department in x axis and outcome graduation date in the y axis and count(id) as the measure.I have the above query in the background. But i get null values in the measure fields. I do not know why. Is it because the id field is not numeric?

        So, i thought of converting the id field to integer as in below query. but am getting the following error. Could you please help me ?

         

         

        select "Outcome"."COLLEGE" "COLLEGE", "Outcome"."DEPARTMENT" "DEPARTMENT", "Outcome"."OUTCOME_GRADUATION_DATE" "OUTCOME_GRADUATION_DATE", count(cast("Outcome"."ID"),integer))  "ID"
         from "ODSMGR"."OUTCOME" "Outcome"
         where "Outcome"."OUTCOME_GRADUATION_DATE" in (TIMESTAMP '2013-05-30 00:00:00.000000000', TIMESTAMP '2013-05-31 00:00:00.000000000', TIMESTAMP '2013-06-04 00:00:00.000000000', TIMESTAMP '2012-12-30 00:00:00.000000000')
         group by "Outcome"."COLLEGE", "Outcome"."DEPARTMENT", "Outcome"."OUTCOME_GRADUATION_DATE"

         

         

        UDA-SQL-0107 A general exception has occurred during the operation "prepare".
        ORA-00905: missing keyword

        RSV-SRV-0042 Trace back:
        RSReportService.cpp(756): UDASQLException: CCL_CAUGHT: RSReportService::process()
        RSReportServiceMethod.cpp(263): UDASQLException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_Request
        RSASyncExecutionThread.cpp(828): UDASQLException: RSASyncExecutionThread::checkException
        RSASyncExecutionThread.cpp(277): UDASQLException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_Request
        RSASyncExecutionThread.cpp(884): UDASQLException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_Request
        RSQueryMgr.cpp(1570): UDASQLException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand
        QFSSession.cpp(1147): UDASQLException: CCL_RETHROW: QFSSession::ProcessDoRequest()
        QFSSession.cpp(1145): UDASQLException: CCL_CAUGHT: QFSSession::ProcessDoRequest()
        QFSSession.cpp(1102): UDASQLException: CCL_RETHROW: QFSSession::ProcessDoRequest()
        QFSConnection.cpp(1971): UDASQLException: CCL_RETHROW: QFSConnection::DescribeDataSourceQuery
        QFSQuery.cpp(569): UDASQLException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery
        QFSQuery.cpp(569): UDASQLException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery
        Source/AQE_UDAFacade.cpp(1704): UDASQLException: CCL_RETHROW:
        apidyn.cpp(250): UDASQLException: CCL_RETHROW: sqlPrepareWithOptions

         

        Thanks,

        Sakthi

        • OpherB
          OpherB
          664 Posts
          ACCEPTED ANSWER

          Re: Can i use count of an attribute as a measure

          ‏2013-06-21T17:11:14Z  in response to sakthivelmani

          Is this SQL being generated by Report Studio? It doesn't look right.

          I'm not an SQL analyst, I stick to Report Studio.

          Good luck,
          Opher

           

          • sakthivelmani
            sakthivelmani
            12 Posts
            ACCEPTED ANSWER

            Re: Can i use count of an attribute as a measure

            ‏2013-06-22T01:59:01Z  in response to OpherB

            Thanks Opher,

             

            But can you tell me if i can use an attribute field in place of a measure in crosstab. I tried using ID (which is a string) as a measure and am trying to take its count.

             

            In a cross tab, College is taken as row , year is taken as column and count(ID) is taken as the measure. But when i run the report, am getting null values. I do not understand. Please help me

             

             

            Thanks,

            Sakthi

  • JohnWilkinson
    JohnWilkinson
    16 Posts
    ACCEPTED ANSWER

    Re: Can i use count of an attribute as a measure

    ‏2013-06-22T17:31:03Z  in response to sakthivelmani

    To make this work you need to change the aggregate and rollup properties of the attributes you are using in the categories/series of the chart. Set them to "count" and the report should work.

     

     

  • sakthivelmani
    sakthivelmani
    12 Posts
    ACCEPTED ANSWER

    Re: Can i use count of an attribute as a measure

    ‏2013-07-30T13:27:09Z  in response to sakthivelmani

    Thanks a lot, John

     

  • sakthivelmani
    sakthivelmani
    12 Posts
    ACCEPTED ANSWER

    Re: Can i use count of an attribute as a measure

    ‏2014-08-28T16:00:56Z  in response to sakthivelmani

    Hello All,

    Is anyone familiar with Cognos workspace ?



    Can u please tell me how i can link two different charts in the same workspace. For example, I have a countries in the chart A and Cities in the chart B and both the charts use the same table. When i click on a specific country in Chart A , can i show only the related cities in Chart B. I tried drill through which does not work. 

    Thanks,
    Sakthi