Issue filter not working

[shrini]

Hello All,

[Period_Number] Number format, which is having data like 1,2,3,4,5,6,7,8,9,10,11,12

[Period_Year]  Number format

I need to concatenate both and assign it a prompt filter which is text box prompt.

ex:-[Period_Number]||'-'||[Period_Year] between ?from month year? and ?To month year?

?from month year? --- 02-2008

?To month year?------ 03-2009

How to achieve this.

[BigChris]

Assuming you're using a relational model...

I'd suggest switching the format round so that you create something more like

[Period_Year] || '-' || if([Period_Number] < 10) then ('0') else ('') || [Period_Number]

that way you'll end up with 2014-09, 2014-10 etc. which will make it possible to do "between" comparisons much more easily.

[shrini]

Changing as per this:-

to_char([Period_Year])
|| '-' ||
if(to_char([Period_Number]) < '10') then (('0')||''||to_char([Period_Number])) 
else to_char([Period_Number]).

Showing parsing error. Can you help me out.

[shrini]

if i change to case instead of if statement, then it works but zero is not appending wherever i  have month as 1 to 9.

to_char([Period_Year])
|| '-' ||
(case when (to_char([Period_Number]) < '10') then (('0')||''||to_char([Period_Number])) 
else to_char([Period_Number])
end)-- Result 2008-2 etc..

Input value for promt:-02-2008. I f i use like this then months with 10,11,12 passes only one number which is wrong.

[Data Item1] = (substr(?P_FrmYr?,2,3)||'-'||substr(?P_FrmYr?,4,7))

[MFGF]

substring(cast([Period_Year] * 100 + [Period_Number], varchar(6)),1,4) + '-' + substring(cast([Period_Year] * 100 + [Period_Number], varchar(6)),5,2)

MF.

[shrini]

Hi MF,

Now the below logic works to get format of 02-2008

Dataitem1:-
(case when ([Period_Number] < 10) then (('0')||''||to_char([Period_Number])) 
else to_char([Period_Number])
end)
|| '-' ||
to_char([Period_Year])

But now the problem is with filter: Dataitem1 between ?from month year? and ?To month year?.ex if i pass 02-2008 and 02-2009 then it pulls only two months 02-2008,02-2009. In between months between  02-2008 and 02-2009 are not getting displayed.

I even tried >= and <=. But still the same.

Thanks,
Srini.

[Lynn]

Hi MF,

Now the below logic works to get format of 02-2008

Dataitem1:-
(case when ([Period_Number] < 10) then (('0')||''||to_char([Period_Number])) 
else to_char([Period_Number])
end)
|| '-' ||
to_char([Period_Year])

But now the problem is with filter: Dataitem1 between ?from month year? and ?To month year?.ex if i pass 02-2008 and 02-2009 then it pulls only two months 02-2008,02-2009. In between months between  02-2008 and 02-2009 are not getting displayed.

I even tried >= and <=. But still the same.

Thanks,
Srini.

I think this is exactly the point that Chris was making when he suggested you reverse your format to be YYYY-MM.

[MFGF]

I think this is exactly the point that Chris was making when he suggested you reverse your format to be YYYY-MM.

Yes - I agree totally, and this is exactly why the expression I provided above returns YYYY-MM results too.

People are making valid suggestions that would probably help you, but you seem to be ignoring them and pursuing your own approach. Is there a reason you're ignoring all this good advice shrini?

MF.

[shrini]

MF,

I was not ignoring, it still did not work. I just appended 01 to the above logic then it started working.

cast(to_char([Period_Year])|| '-' || if(to_char([Period_Number]) < '10') then (('0')||''||to_char([Period_Number]))  else to_char([Period_Number])|| '-' ||'01' ,date)
between cast(?from month year? || '-' ||'01',date)  and cast(?To month year?|| '-' ||'01' ,date)

Anyways thanks for the suggestions, which made my path to go and fix it.

Thanks.

Regards,
Shrini.

[shrini]

Hello Members,

The below query is working but taking hours to give output for less number records.

For example:- 201308 to 201408 i have 500 records, but it takes 2 hours to get data.

cast(to_char([Period_Year])|| '-' || if(to_char([Period_Number]) < '10') then (('0')||''||to_char([Period_Number]))  else to_char([Period_Number])|| '-' ||'01' ,date)
between cast(?from month year? || '-' ||'01',date)  and cast(?To month year?|| '-' ||'01' ,date).

Can you please suggest the other way if any...


User is not satisfied with report taking long time to show output.

Thanks,
Shrini.

[Lynn]

Hello Members,

The below query is working but taking hours to give output for less number records.

For example:- 201308 to 201408 i have 500 records, but it takes 2 hours to get data.

cast(to_char([Period_Year])|| '-' || if(to_char([Period_Number]) < '10') then (('0')||''||to_char([Period_Number]))  else to_char([Period_Number])|| '-' ||'01' ,date)
between cast(?from month year? || '-' ||'01',date)  and cast(?To month year?|| '-' ||'01' ,date).

Can you please suggest the other way if any...


User is not satisfied with report taking long time to show output.

Thanks,
Shrini.

You should look at the generated SQL to see what is happening on the database side vs on the Cognos side. It could be that you are doing a full table scan rather than hitting an index and/or that you are pulling back massive amounts of data for the Cognos server to process locally. Compare the native SQL with the Cognos SQL to help narrow down the issue.

[shrini]

Both run normal native SQL and Cognos SQL. The issue is only with filter i am using here.

Filter between From and To

Type1:-
Filter:-
cast(to_char([Period_Year])
|| '-' ||
(case when ([Period_Number] < 10) then (('0')||''||to_char([Period_Number])) 
else to_char([Period_Number])
end)||'-'||'01',date)

From;-
cast(substr(?P_FrmYr?,1,4)||'-'||substr(?P_FrmYr?,5,6)||'-'||'01',date)

To:-
cast(substr(?P_ToYr?,1,4)||'-'||substr(?P_ToYr?,5,6)||'-'||'01',date)

Type2:-

Filter between ?P_FrmYr? and ?P_ToYr?
Filter:-
to_char([Period_Year])
|| '-' ||
(case when (to_char([Period_Number]) < '10') then (('0')||''||to_char([Period_Number])) 
else to_char([Period_Number])
end)

The logic should be reframed to reduce time consumption.  Both 1 and 2 take same time to run.

Any ideas please...

Thanks.

[MFGF]

Both run normal native SQL and Cognos SQL.

What does this mean? Lynn was suggesting you compare the native and Cognos SQL to determine whether some of the work was being done locally on the Cognos server (ie the Native SQL does not contain everything the Cognos SQL contains). Saying "Both run normal" doesn't answer this, and neither does it seem to make any sense. While you can check the Native SQL by copying it into your database's SQL tool, you can't generally do this with Cognos SQL, so how can you make the claim above?

MF.

[shrini]

I have ran both cognos sql and native sql. Both queries ran faster. I passed the date filter with static values and checked.

But i can tell you that i have set processing to Limited local to the query.