BO Reports Issue

system · July 2, 2004, 6:08pm

I am working on a report and have the following Issue

SCR table has CRS_NB(course number),CRS_VRSN_NB(course version number),STDNT_NB(student number),SCR_DT(score date) and I want to get the count of students for course for year 2002 year 2003 and year 2003 and having problems can any one help mw with the Issue.Create d the universe and cghecked the Integerity.

This is the expected result

Couse name nb vrsn nb year count

MAPS(1): Introduction to MAPS-W98 3 1 2000 16
MAPS(1): Introduction to MAPS-W98 3 1 2001 156
MAPS(1): Introduction to MAPS-W98 3 1 2002 101

But when I do the report

I get

Maps(1) Introduction… 9 3 6003 273

It’s adding all the records.

Queries from Report

SELECT B.CRS_NM,A.crs_nb,A.crs_vrsn_nb,Year(A.scr_Dt),Count(A.STDNT_NB) AS CountOfSTDNT_NB
FROM ZLDT180_SCR AS A ,ZLDT030_CRS AS B
WHERE a.crs_nb =b.crs_nb and a.CRS_VRSN_NB =b.crs_vrsn_nb
GROUP BY A.CRS_NB, A.CRS_VRSN_NB,B.CRS_NM,Year(A.scr_Dt) order by A.crs_nb,A.CRS_VRSN_NB,Year(A.scr_Dt);

Created report by selecting Free hand SQL and when i vIEW THE DATE i GET RIGHT BUT WHEN I RUN THE REPORT i GET WRONG.

Please help.

Thanks,

Prasad

kprasadreddy (BOB member since 2004-07-02)

system · July 2, 2004, 6:09pm

Please, eloborate…

Andreas (BOB member since 2002-06-20)

system · July 2, 2004, 6:17pm

kprasadreddy:

I am working on a report and have the following Issue

SCR table has CRS_NB(course number),CRS_VRSN_NB(course version number),STDNT_NB(student number),SCR_DT(score date) and I want to get the count of students for course for year 2002 year 2003 and year 2003 and having problems can any one help mw with the Issue.Create d the universe and cghecked the Integerity.

This is the expected result

Couse name nb vrsn nb year count

MAPS(1): Introduction to MAPS-W98 3 1 2000 16
MAPS(1): Introduction to MAPS-W98 3 1 2001 156
MAPS(1): Introduction to MAPS-W98 3 1 2002 101

But when I do the report

I get

Maps(1) Introduction… 9 3 6003 273

It’s adding all the records.

Queries from Report

SELECT B.CRS_NM,A.crs_nb,A.crs_vrsn_nb,Year(A.scr_Dt),Count(A.STDNT_NB) AS CountOfSTDNT_NB
FROM ZLDT180_SCR AS A ,ZLDT030_CRS AS B
WHERE a.crs_nb =b.crs_nb and a.CRS_VRSN_NB =b.crs_vrsn_nb
GROUP BY A.CRS_NB, A.CRS_VRSN_NB,B.CRS_NM,Year(A.scr_Dt) order by A.crs_nb,A.CRS_VRSN_NB,Year(A.scr_Dt);

Created report by selecting Free hand SQL and when i vIEW THE DATE i GET RIGHT BUT WHEN I RUN THE REPORT i GET WRONG.

Please help.

Thanks,

Prasad

Couse name nb vrsn nb year count

MAPS(1): Introduction to MAPS-W98 3 1 2000 16
MAPS(1): Introduction to MAPS-W98 3 1 2001 156
MAPS(1): Introduction to MAPS-W98 3 1 2002 101

But when I do the report

I get

Maps(1) Introduction… 9 3 6003 273

It’s adding all the records.

Queries from Report

SELECT B.CRS_NM,A.crs_nb,A.crs_vrsn_nb,Year(A.scr_Dt),Count(A.STDNT_NB) AS CountOfSTDNT_NB
FROM ZLDT180_SCR AS A ,ZLDT030_CRS AS B
WHERE a.crs_nb =b.crs_nb and a.CRS_VRSN_NB =b.crs_vrsn_nb
GROUP BY A.CRS_NB, A.CRS_VRSN_NB,B.CRS_NM,Year(A.scr_Dt) order by A.crs_nb,A.CRS_VRSN_NB,Year(A.scr_Dt);

Created report by selecting Free hand SQL and when i vIEW THE DATE i GET RIGHT BUT WHEN I RUN THE REPORT i GET WRONG.

Please help.

kprasadreddy (BOB member since 2004-07-02)

system · July 2, 2004, 6:28pm

Your “YEAR” object seems to be defined as measure.
Make it a dimension and you should be good.

mkumar (BOB member since 2002-08-26)

system · July 2, 2004, 6:34pm

Kumar,

Defined Scores2002 as

count(dbo.ZLDT180_SCR.STDNT_NB)
where condition 
(dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC  >= .85  OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC  >= .85)   AND (year(dbo.ZLDT180_SCR.SCR_DT) =2002)

and

Scores2003 as

count(dbo.ZLDT180_SCR.STDNT_NB)
where condition 
(dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC  >= .85  OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC  >= .85)   AND (year(dbo.ZLDT180_SCR.SCR_DT) =2003)

but cannot display data when I pull in scores2002 and scores2003 and get a message No data to fetch…

SQL

SELECT
  ZLDT030_CRS.CRS_NM,
  ZLDT030_CRS.CRS_NB,
  ZLDT030_CRS.CRS_VRSN_NB,
  count(ZLDT180_SCR.STDNT_NB),
  count(ZLDT180_SCR.STDNT_NB)
FROM
  ZLDT030_CRS,
  ZLDT180_SCR
WHERE
  ( ZLDT030_CRS.CRS_NB=ZLDT180_SCR.CRS_NB and ZLDT030_CRS.CRS_VRSN_NB=ZLDT180_SCR.CRS_VRSN_NB  )
  AND  ( ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4)  )
  AND  ( (ZLDT180_SCR.ENTYCHLNGSCRVAL_PC  >= .85  OR ZLDT180_SCR.MSTRCHLNGSCRVAL_PC  >= .85)   AND (year(ZLDT180_SCR.SCR_DT) =2002)  )
  AND  ( (ZLDT180_SCR.ENTYCHLNGSCRVAL_PC  >= .85  OR ZLDT180_SCR.MSTRCHLNGSCRVAL_PC  >= .85)   AND (year(ZLDT180_SCR.SCR_DT) =2003)  )
GROUP BY
  ZLDT030_CRS.CRS_NM, 
  ZLDT030_CRS.CRS_NB, 
  ZLDT030_CRS.CRS_VRSN_NB

[formatted SQL as CODE - Andreas]

kprasadreddy (BOB member since 2004-07-02)

system · July 2, 2004, 6:36pm

Your logically ANDed Year condition is causing the problem.

Do not use WHERE clauses for Measures.
Use the SQL statement CASE WHEN… instead.

If you want more details on CASE WHEN… feel free to do a search on BOB

Andreas (BOB member since 2002-06-20)

system · July 2, 2004, 6:38pm

Created SCR_DT as dimension and scores2002 ansd scores 2003 as dimensions…

Please help…

kprasadreddy (BOB member since 2004-07-02)

system · July 2, 2004, 6:50pm

Can you give me an example Thanks!@Prasad

kprasadreddy (BOB member since 2004-07-02)

system · July 2, 2004, 6:51pm

Give me AN example for case

kprasadreddy (BOB member since 2004-07-02)

system · July 2, 2004, 6:52pm

How about you try a SEARCH first?

Andreas (BOB member since 2002-06-20)

system · July 2, 2004, 6:54pm

Your query is having AND operaror for year.


(year(ZLDT180_SCR.SCR_DT) =2002) 
...
AND 
...
(year(ZLDT180_SCR.SCR_DT) =2003)

which could be the reason for “No data to fetch” message.

Use OR operator or IN operator.

(year(ZLDT180_SCR.SCR_DT) IN (2002, 2003)

or

(year(ZLDT180_SCR.SCR_DT) = 2002 OR 
year(ZLDT180_SCR.SCR_DT) = 2003)

and see if you getting your data back.

mkumar (BOB member since 2002-08-26)

system · July 2, 2004, 7:02pm

Went to Data Provider ans sql and changed it but cannot save and when I run the query it changes back to AND.Is there a different way to deal with this…Please let me know…

Thanks,

Prasad

kprasadreddy (BOB member since 2004-07-02)

system · July 2, 2004, 7:04pm

When you modify the SQL, there is a check box on left botton which says “Don’t Regenerate the SQL”
Check that box and run again.

OR
In query panel, double click on AND operator to change it to OR.

mkumar (BOB member since 2002-08-26)

system · July 2, 2004, 7:19pm

I created dimensions names scores2002

count(dbo.ZLDT180_SCR.STDNT_NB)
where
(dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC  >= .85  OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC  >= .85)   AND (year(dbo.ZLDT180_SCR.SCR_DT) =2002)

scores2003

count(dbo.ZLDT180_SCR.STDNT_NB)
where
(dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC  >= .85  OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC  >= .85)   AND (year(dbo.ZLDT180_SCR.SCR_DT) =2003)

and pulled them to results objects…

Ia m doing something wrong here…Please help…

SELECT
  dbo.ZLDT030_CRS.CRS_NM,
  dbo.ZLDT030_CRS.CRS_NB,
  dbo.ZLDT030_CRS.CRS_VRSN_NB,
  count(dbo.ZLDT180_SCR.STDNT_NB),
  count(dbo.ZLDT180_SCR.STDNT_NB)
FROM
  dbo.ZLDT030_CRS,
  dbo.ZLDT180_SCR
WHERE
  ( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT180_SCR.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT180_SCR.CRS_VRSN_NB  )
  AND  ( dbo.ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4)  )
  AND  ( (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC  >= .85  OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC  >= .85)   AND (year(dbo.ZLDT180_SCR.SCR_DT) =2002)  )
  AND  ( (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC  >= .85  OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC  >= .85)   AND (year(dbo.ZLDT180_SCR.SCR_DT) =2003)  )
GROUP BY
  dbo.ZLDT030_CRS.CRS_NM, 
  dbo.ZLDT030_CRS.CRS_NB, 
  dbo.ZLDT030_CRS.CRS_VRSN_NB

[edited, changed SQL to CODE - Andreas]

kprasadreddy (BOB member since 2004-07-02)

system · July 2, 2004, 7:26pm

You are making count(dbo.ZLDT180_SCR.STDNT_NB) a dimension.
This should be a measure.
You are having where clause set in the object definition.
Don’t do that. Instead add conditions in the conditions block in query panel while creating the report.
Make year(dbo.ZLDT180_SCR.SCR_DT) a dimension and use in condition block of query panel.
Select year(dbo.ZLDT180_SCR.SCR_DT) in your report also.

Your final SQL should look like this

SELECT 
dbo.ZLDT030_CRS.CRS_NM, 
dbo.ZLDT030_CRS.CRS_NB, 
dbo.ZLDT030_CRS.CRS_VRSN_NB, 
year(dbo.ZLDT180_SCR.SCR_DT),
count(dbo.ZLDT180_SCR.STDNT_NB), 
count(dbo.ZLDT180_SCR.STDNT_NB) 
FROM 
dbo.ZLDT030_CRS, 
dbo.ZLDT180_SCR 
WHERE 
( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT180_SCR.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT180_SCR.CRS_VRSN_NB ) 
AND ( dbo.ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4) ) 
AND ( year(dbo.ZLDT180_SCR.SCR_DT) in (2002,2003))
GROUP BY 
dbo.ZLDT030_CRS.CRS_NM, 
dbo.ZLDT030_CRS.CRS_NB, 
dbo.ZLDT030_CRS.CRS_VRSN_NB

[edited, changed SQL to CODE - Andreas]

mkumar (BOB member since 2002-08-26)

system · July 2, 2004, 7:36pm

Get

SELECT
  dbo.ZLDT030_CRS.CRS_NM,
  dbo.ZLDT030_CRS.CRS_NB,
  dbo.ZLDT030_CRS.CRS_VRSN_NB,
  year(dbo.ZLDT180_SCR.SCR_DT),
  count(dbo.ZLDT180_SCR.STDNT_NB),
  count(dbo.ZLDT180_SCR.STDNT_NB),
  count(dbo.ZLDT180_SCR.STDNT_NB)
FROM
  dbo.ZLDT030_CRS,
  dbo.ZLDT180_SCR
WHERE
  ( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT180_SCR.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT180_SCR.CRS_VRSN_NB  )
  AND  ( dbo.ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4)  )
  AND  (
  year(dbo.ZLDT180_SCR.SCR_DT)  IN  (2001, 2002)
  AND  dbo.ZLDT030_CRS.CRS_NB  IN  (3, 4, 5)  AND  (dbo.ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4))
  )
GROUP BY
  dbo.ZLDT030_CRS.CRS_NM, 
  dbo.ZLDT030_CRS.CRS_NB, 
  dbo.ZLDT030_CRS.CRS_VRSN_NB,
year(dbo.ZLDT180_SCR.SCR_DT)

and get

“Columns of the SELECT clause are different from the result object (QP0017)”

[Edited, used bbc to format SQL as Code, please try to format your posts so they are easier to read - Andreas]

kprasadreddy (BOB member since 2004-07-02)

system · July 2, 2004, 7:40pm

This error occurs if you modify and BO generated SQL manually and number of objects in the Select statement of your modified Query differs from the the number of objects you originally selected in the query panel.

Are you modifying the SQL?

mkumar (BOB member since 2002-08-26)

system · July 2, 2004, 7:40pm

kUMAR,

Works great…I really appreciate your help…Now can relax my long weekend.

Thanks,

Prasad

kprasadreddy (BOB member since 2004-07-02)