BusinessObjects Board

Report Issue

building reports desktopintelligence
#1

Hello Everyone…

I have the following columns in table ZLDT025_STDNTRSPNS

STDNT_NB – student number
CRS_NB - Course Number
CRS_VRSN_NB – Course Version Number
QSTN_SQNC_NB –Question Number values (1-15)
ANSW_OPT_CD —Answer Option Code (A-E)
RSPNS_DT -Response Date

I am trying to create a report to get the responses(count) with answ_opt_Cd A by total number of responses for questions 1-5 Grouping by Response year and responses(count) with answ_opt_Cd A by total number of responses for questions 6-10 Grouping by Response year .I am having Issues…

Can anyone help me with this….

Thanks,

Prasad

kprasadreddy (BOB member since 2004-07-02)

#2

Please, be more specific: What problems are you facing?

Are you trying to do something along the lines of:

=Count(<Work Date>) Where (<Service Type> = "REG")

And did you take the time to read through the Reporter FAQ?
If you encountered an error message did you try a search on BOB? :mrgreen:

On a side note:
A more descriptive title other than “Report Issue” such as “Count problem with conditions” etc. might get you better responses.

Andreas :de: (BOB member since 2002-06-20)

#3

I don’t get any error messages but am not getting any values.Let me know the best approach…

kprasadreddy (BOB member since 2004-07-02)

#4

You do not get any values when trying what exactly? Please, be more specific…

Andreas :de: (BOB member since 2002-06-20)

#5

Following are the sql statments…

SELECT
  dbo.ZLDT030_CRS.CRS_NM,
  dbo.ZLDT075_CRRCLM_CRS.CRS_NB,
  dbo.ZLDT075_CRRCLM_CRS.CRS_VRSN_NB
FROM
  dbo.ZLDT030_CRS,
  dbo.ZLDT075_CRRCLM_CRS
WHERE
  ( dbo.ZLDT075_CRRCLM_CRS.CRS_NB=dbo.ZLDT030_CRS.CRS_NB and dbo.ZLDT075_CRRCLM_CRS.CRS_VRSN_NB=dbo.ZLDT030_CRS.CRS_VRSN_NB  )
  AND  (
  dbo.ZLDT030_CRS.CRS_DLVRY_CD  IN  (4, 2, 1)
  AND  dbo.ZLDT030_CRS.CRS_STAT_CD   =  '1'
  )
SELECT
  dbo.ZLDT030_CRS.CRS_NM,
  dbo.ZLDT025_STDNTRSPNS.ANSW_OPT_CD,
  count(dbo.ZLDT025_STDNTRSPNS.STDNT_NB),
  count(dbo.ZLDT025_STDNTRSPNS.STDNT_NB),
  dbo.ZLDT025_STDNTRSPNS.QSTN_SQNC_NB,
  count(dbo.ZLDT025_STDNTRSPNS.STDNT_NB)
FROM
  dbo.ZLDT030_CRS,
  dbo.ZLDT025_STDNTRSPNS
WHERE
  ( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT025_STDNTRSPNS.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT025_STDNTRSPNS.CRS_VRSN_NB  )
  AND  ( dbo.ZLDT025_STDNTRSPNS.QSTN_SQNC_NB  IN (1,2,3,4,5) and dbo.ZLDT025_STDNTRSPNS.QSTN_CATG_CD in('EC','MC')  )
  AND  ( dbo.ZLDT025_STDNTRSPNS.QSTN_SQNC_NB  IN (6,7,8,9,10)) and dbo.ZLDT025_STDNTRSPNS.QSTN_CATG_CD in('EC','MC')  )
  AND  (
  dbo.ZLDT030_CRS.CRS_DLVRY_CD  IN  (4, 2, 1)
  AND  dbo.ZLDT030_CRS.CRS_STAT_CD   =  '1'
  )
GROUP BY
  dbo.ZLDT030_CRS.CRS_NM, 
  dbo.ZLDT025_STDNTRSPNS.ANSW_OPT_CD, 
  dbo.ZLDT025_STDNTRSPNS.QSTN_SQNC_NB

[Used bbc CODE formatting, please use the formatting options when you post - Andreas]

kprasadreddy (BOB member since 2004-07-02)

#6

So when you’re trying to retrieve data from the database, nothing happens?

Paul Williams :uk: (BOB member since 2002-07-10)

#7

Your problem are conflicting WHERE clauses:

AND ( dbo.ZLDT025_STDNTRSPNS.QSTN_SQNC_NB IN (1,2,3,4,5) and dbo.ZLDT025_STDNTRSPNS.QSTN_CATG_CD in(‘EC’,‘MC’) )
AND ( dbo.ZLDT025_STDNTRSPNS.QSTN_SQNC_NB IN (6,7,8,9,10)) and

Andreas :de: (BOB member since 2002-06-20)

#8

Yes…

have the following columns in table ZLDT025_STDNTRSPNS

STDNT_NB – student number
CRS_NB - Course Number
CRS_VRSN_NB – Course Version Number
QSTN_SQNC_NB –Question Number values (1-15)
ANSW_OPT_CD —Answer Option Code (A-E)
RSPNS_DT -Response Date

I am trying to create a report to get the responses(count) with answ_opt_Cd A by total number of responses for questions 1-5 Grouping by Response year and responses(count) with answ_opt_Cd A by total number of responses for questions 6-10 Grouping by Response year .I am having Issues…

Can you help me with this….

kprasadreddy (BOB member since 2004-07-02)

#9

Avoid WHERE clauses in your universe measures, use CASE WHEN… instead.

Try a search on BOB (Designer forum), suggested keywords: CASE WHEN measure

This (in principle) is more or less the very same issue you already discussed here…

Andreas :de: (BOB member since 2002-06-20)

#10

You have the same problem with your measures in universe as we discussed in your previous post last week.


Avoid having WHERE clause in object definition in your universe. These WHERE conditions are joined by AND operator in the BO generated Query.
Instead

  1. Use predefined conditions in universe
  2. Use conditions block in Query panel to create conditions
  3. Use CASE WHEN statements in object definition as suggested by Andreas.
  4. Do report level calculations.

mkumar (BOB member since 2002-08-26)

#11

Kumar,

I am new to BO and am having Issues…Please help me with the case statements…

kprasadreddy (BOB member since 2004-07-02)

#12

Have a look on www.google.com for a SQL reference/tutorial download for the RDBMS that you are using. This should be your priority over Designer. Then book some training.
Please use SEARCH as CASE statements have been covered many times. This topic will appears as a pointless post for anyone in future who is searching for examples of CASE statements.

Regards,
Mark

#13

If anyone can help me with the report I am working on…I will pay accordingly.I have to complete the report,…

Thanks,

Prasad

kprasadreddy (BOB member since 2004-07-02)

#14

This is a FREE knowledge sharing forum and I love being a part of it.
If possible, email your report to me. I will take a look. You need not to pay anything 8).

mkumar (BOB member since 2002-08-26)

#15

Manish,

Emailed you…the requirements…

Thanks,

Prasad

kprasadreddy (BOB member since 2004-07-02)

#16

Removing WHERE clause from object definition solved the issue.
You need to create some report level variables to get the exact output.

mkumar (BOB member since 2002-08-26)