Display Row data as Columns 1

[nb4884]

Hi All,

Here is my scenario and need a suggestion? Using CR XI R2

Have a table with columns Project Name and Start date. Lets say its 10(R) x 2(C) table

Now if i were to display project and date as in columns its pretty simple to display , but what if i want to display 10 projects as columns and 10 dates related to those projects as a single row?
As 2(R) x 10(C).

My thought would be create 10 queries with a filter on project name?
Is there any other option for better performance of report?

Thanks

 

[lbass]

You could just insert a crosstab, use project name as the column field, and use maximum of date as the summary field (assuming there is one date per project name.

-LB

 

[nb4884]

Thanks LB , there is one data per project name but I have to pick only particular projects, not sure if I can pick those particular ones.

Also apart from these two columns I have about 10 more columns (attributes) on Department and project is a subobject to department.

So in the final layout i have 10 columnsfrom department and then 10 columns for those selected projects.

What do u suggest?

 

[lbass]

Your description makes it sound like you want 100 columns, 10 projects within each department. What are you displaying per department/project?

I think it would help if you showed a mockup of how you want the report to look.

-LB

 

[nb4884]

Sorry for not explaining very clearly.

So my report has a total of 20 columns.

10 columns from department like ID, name, location etc and then 10 columns for project names. These 10 projects are within a department.

So first 10 columns are from department and next 10 are project names within that department. I have around 500 projects in a department but I am only picking those 10 projects and their dates

sample data :

d.id d.name d.loc d.phno d.address..10cols P.1 P.2 P.3 P.4..P.10
1 dep US 123 xx d.1 d.2 d.3 d.4..d.10

This was example of dept 1 , similarly for dept 2 , 10 columns for dep and 10 columns for projects remain same only different data and so on.

Every time those 10 projs remain the same.

 

[lbass]

I still think you should try a crosstab. Add the department attributes as rows, in the order you want to see them displayed, add projects as the column, and then add multiple summaries as you wish. You can go into the customize style tab and check "suppress subtotals" for each of the attributes, so you don't get multiple lines (this will work as long as the nested attributes are unique). You can also in this screen, choose to format summaries to display horizontally or vertically and to show labels for them or not.

For the 10 projects, you should be limiting to this in the record selection formula.

Try this first. Otherwise you would have to manually create one formula for each summary per project, which would quickly become onerous.

-LB

 

[nb4884]

Thanks for the suggestion, Crosstab doesnt work in this case.

With creating formula manually ...do you mean creating a query for each projects and hence creating 10 those queries?

As my situation is if project = 1 then startdate.

As in my question I would assume , i will have to create 10 queries, each query filtered by its project name?

Or do u mean by writing a formula for every project and place that formula in report?

Kindly Suggest

 

[lbass]

Please explain why you think a crosstab won't work, rather than just stating that it doesn't. What went wrong when you tried to implement it? I'm not convinced--there is nothing in your example that suggests a crosstab wouldn't work, and it is simpler and more efficient than other approaches.

To do a manual crosstab, you would group on department, and then create one formula per project (per summary), as in:

if {table.project} = 1 then
{table.startdate}

if {table.project = 2 then
{table.startdate}

Place these in the detail section and then insert a maximum on each at the group level. Place the other fields (unique to the department) in the group footer section, along with the summaries, and then suppress the group header and detail section.

If you are also summarizing other values per project, you would need additional formulas.

Another approach might be to group on department and then on project and lay out a variety of summaries in the project group section.

-LB

 

[nb4884]

Thanks LB, I think the cross tab did not work since I have few more columns for Projects apart from date. I have Project Start Date , Project Finish Date, Project Costs. Not sure if I can fit all the 3 columns for a project and similarly for 10 projects. i know its a large report. Let me know if that is still possible , I would prefer a cross tab than to manually create one

However the manual cross tab worked very well. That ways I can get rid of 10 queries I created for each project. I am still wondering what has Maximum done to the summary. If i dont summarize by max i get different results, but if i summarize by max , i get the results I want. Still would like to know.

Thanks

 

[lbass]

With an inserted crosstab, you would set up Projects as the column field, and Department and your other fields as row fields. You can then add multiple summaries and either lay them out horizontally or vertically as noted earlier. An inserted crosstab will create virtual pages to the right if more space is necessary. There is also the ability to format the row fields to repeat and to add a horizontal page number.

Using a maximum is a way of showing a record when there is one record per group. I'm concerned about your statement that you get different results depending upon the summary. Are there multiple start dates per project? You have to think through the logic for the results you want when choosing the summary type.

-LB