Problem on Grouped column??? 1

[anumala28]

Hi,

Can we do the Grouped Column as Prompt?

i.e User can select any column through prompt. Depends upon that column report will display by group. Is it possible in Impromptu????


Thanks,
Sri.

 

[griffindm]

Sri,

Yes, this is possible. Just create the calculated column to populate based on the prompt value, and then group on the calculated column. If you are running on Oracle and have auto decode off, then using the Decode function explicitly here will give you better performance. Even so, you may not be able to force a group by clause to the database for a summary only report using such a dynamic grouping. It depends on the database and the location of the sources for the calculated column.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[anumala28]

I am using oracle8i, How can i create calculated Clolumn on grouped columns??

for ex: Empno,Deptno are grouped columns,

User can select Empno or Deptno.
Please give the steps to create Dynamic grouping.


Thnaks,
Sri

 

[nagrajm]

I am assuming that you want the USER to determine how they want the report to be displayed EITHER grouped by EMPNO or by Grouped by DEPTNO.

If that be the case, what Dave is saying can be accomplished by a calculated column (which is pre-grouped) that says;

If (?prompt? = "Group By Employee&quot then ( EMPNO ) else (DEPTNO)

Alternatively,

Decode (?prompt?, "Group by Employee", EMPNO, DEPTNO)

The user prompt may be made available from a file picklist. The .txt file must have 2 rows

Group by Employee
Group by Department

As Dave said, the Decode option will perform better. Dave, how would you label the calculated column dynamically? Could you?

 

[anumala28]

If it is more than 2 columns, Is it possible to do using Decode??

 

[nagrajm]

Yes.

Decode (?prompt?, "Group By Employee", EMPNO, "Group By Department", DEPTNO, "Group by Country", COUNTRY, .....)

 

[griffindm]

nagrajm,

For a dynamic column header, create a calculated text column, as in

If (Upper(?GrpKey?) = 'C') then ('Country') else if (Upper(?GrpKey?) = 'E') then ('Employee') else ( 'Unclassified') ...

(Use Decode as desired in place of If..Then..Else).

Then suppress the normal column titles and replace with static text, or other calculated columns as required.

It always worked fine for me.

Another fun technique is to have the report layout contain different information in the same columns, based on the values in the grouping. Create all of your displayed columnar data as calculated columns (i.e. DispCol1, DispCol2, etc) and populate the column with other calculated data based on group values. This allows a single report to do the work of multiple reports. I have a billing support report that shows both labor and other direct cost. I use a calculated column for the two types (Labor/ODC) and show either dollars or hours in the same column on different pages, based on the Labor/ODC value. The other trick to this is you may need a custom Style to use with conditional formatting to avoid having dollar signs on hours. If so, the end user needs to have the style in their impromptu.ini for it to display properly.

Hope this helps!

Dave Griffin




The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[anumala28]

Can we put Calculated column as Column heading??

Thanks,
Sri

 

[griffindm]

Sure! Just NOT as a normal column heading. Suppress the automatic ones and create you own.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[anumala28]

Thanks Dave & nagrajm,

Sri

 

[anumala28]

Hi all,

I am applying this logic using If condtions, but Impromptu suspending this filter, what could be the reason????

Thnaks,
Sri

 

[griffindm]

Sri,

None of the discussion on dynamic grouping has referred to filtering. What filter are you talking about?

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com