Dynamic Column Heading

[Videla]

Hi,

We are developing a report where one column heading should change dynamically based on the data in a meta table. Let me explain the problem with a simple example.

Let us assume asp.net page has simple filter compnay. values comp1, comp2, comp. When the user click the "get report" button it will displays all the employees in that company.

Meta table will have data as below.
Compnay Desc
--------------------------------
comp Application Developer
comp1 Programmer Analyst
comp2 System Engineer.

Now if user selects comp from the asp.net page output should be as below.

Application Developer
---------------------
employee1
empolyee2
employee3

If user selects comp1, output is

Programmer Analyst
---------------------
employee1
empolyee2
employee3

If user selects comp2, output is

System Engineer
---------------------
employee1
empolyee2
employee3

You could clearly see that in all the cases we give only list of employees. ONly difference is column heading should be different based on user selection and should be maintained in a database table.
any help is highly appreciated.

Thanks
Ravi

 

[MisterMo]

you could pass a parameter to the reprot with a different int value for any choice

then a formula on your report whic could have something like;

if @param = 1 then
'Programmer Analyst'
else if @Param = 2 then
'System Engineer'

then put this formula on you page header for your column heading.

Mo

 

[GJParker]

If you are filtering teh report to somply show one company then you can use the Table.desc field as the column header.

If this doesn't suit your needs then try posting actual table and report structure structure as well as CR version

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[GJParker]

If you are filtering the report to simply show one company, then you can use the Table.desc field as the column header.

If this doesn't suit your needs then try posting actual table and report structure as well as CR version.

I must start sense checking my posts before I submit them LOL

Gary Parker
MIS Data Analyst
Manchester, England

 

[Videla]

Hi MisterMo,

Thanks for your response. I can do this by creating a formula and writing If else statement. But i dont want to hard code this logic. As i mentioned this should be driven by a table present in the database. It is something like

select desc from meta_table where company = @param;

In other words how can i create this as formual so that my heading changes dynamically based on the user input and by querying meta table. appreciate any help.

Thanks
Ravi

 

[Videla]

Hi GParker..Thanks for your response..
Just to simplify i used this example..we are using CR XI. My original report is somewhat complicated..but let me explain you in simple words..

initially user gets summary report showing broker dealer, sales. He can drilldown on broker dealer to see the sales at selling group level, then by level1, level2, level3. The problem is from the drilldown report the label for selling group, level1, level2, level3 vary based on the broker dealer he selects.

In other words i want to create a formula kind of thing for column header where i could specify some sql like

select desc from meta_table where company = @param;
appreciate any help.

Thanks
Ravi

 

[MisterMo]

You could use a sub-report for your header and simply pass the parameter to it.

Mo

 

[Videla]

Hi Mo,

I am new to CR and just started creating some simple reports. It will be very helpful if you can further explain on how to create sub report which i can use for dynamic heading.

Thanks
Ravi

 

[MisterMo]

There are two ways of doing this, you can do it from within the report you are working on, or create a new one and then insert it onto your report.

from your report Insert>SubReport

choose Create SubReport Using the wizard.

Link to the table you want as you would normally do, add the field you want and add a parameter that fit your criteria which will be passed from the main report.

suppress headers footers and column headings because your field will be the heading for the main report.


once you are done go to the subreport will be created and all you need to is to set the link between the main report and the subreport.

Edit>Subreport Link.

Mo

 

[Videla]

Hi Mo,

I guess this should solve my problem. But unfortunately i was not able to create subreport successfully by passing the parameter.

i created a subreport which will have bus_desc and with no header&footer. But i dont see the parameter which is available in the main report (company dropdown value). So i created one parameter in subreport (s_p_company). Then i did edit links, did as below..

from the available fields section selected the parameter name (p_company).
and in subreprot parameter field to use, i selected ?s_p_compnay. when i refresh the reprot, i see a blank heading..am i missing something!!!

Thanks
Ravi

 

[MisterMo]

if you create a parameter in the subreport, then you have to set the recorselection formula for the subreport as well

{MyTable.MyField}= @MyParam

but you can also set this from the subreport links

where MainReport.Param = Select from the drop down list on the right side and find the field you need

?s_p_compnay = {MyTable.MyField}


Mo

 

[Videla]

Hi Mo..i apologize for the previous post. it is working perfectly..there was some problme from my side.
Just to extend this conversation (as i am a beginner, just trying to learn more from these conversations ), I am not very sure whether subreport is a good option for just changing the heading and how much resources it will take. Is there any other way of writing simple formula where i could write selct query.

Once again thanks very much, it perfectly solves the problem and this question is only to explore further possibilities.

Thanks
Ravi

 

[MisterMo]

Of course there are many ways to achieve the same objective.

but to do that we need some more informations on what database and tables used.

one way could be using a stored procedure where you do all the select work and have a table returned with a structure like:

Application Developer emp1
Application Developer Emp2
Application Developer emp3

and then group by the first field and use the group header as a column.



Mo

 

[Videla]

Hi Mo,

we use Oracle as the database where the meta table and the reporting tables are stored. Here the heading is based on the meta table. Pls note that there is no relation between reporting tables and meta table. Only heading should change based on the meta table & passed parameter.

Not sure if i am able to give you more information. Let me stick to this sub report option itself. Thanks again.

Ravi

 

[MisterMo]

The important thing is to get the report working.

don't worry about performances as the subreport is only run once.

Mo