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

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

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

    ‏2013-06-05T21:41:09Z  

    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

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

    ‏2013-06-05T23:15:03Z  

    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

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

    ‏2013-06-10T19:44:01Z  

    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.

     

     

    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

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

    ‏2013-06-21T15:52:06Z  
    • OpherB
    • ‏2013-06-10T19:44:01Z

    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

     

    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

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

    ‏2013-06-21T17:11:14Z  

    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

    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

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

    ‏2013-06-22T01:59:01Z  
    • OpherB
    • ‏2013-06-21T17:11:14Z

    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

     

    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

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

    ‏2013-06-22T17:31:03Z  

    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

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

    ‏2013-07-30T13:27:09Z  

    Thanks a lot, John

     

  • sakthivelmani
    sakthivelmani
    12 Posts

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

    ‏2014-08-28T16:00:56Z  

    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