• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2888
  • Last Modified:

How to repeat group values in a cross-tab.

I have a cross-tab in my report.

In the row selection the are two values, Date and Code.  Because I have selected the two row values, Crystal has automatically grouped on Date.  The result being the date is only shown once when the value changes.

We need to export this report to Excel for further analysis, so we need all the values in all the rows to be displayed.

I cannot see any tick box to allow this, so I guess if it is possible, it will required some formula.

I toyed with the idea of concatenating the two values into a single value so we only have one row value, but we need the values seperated in the exported file for the analysis to work.

I have noticed lots of people of the net have asked the same question, but I have not been able to find a workable solution.

We are using CR 2008 and Crystal for VS2010, but niether seem to have a solution.

Any an dall help would be greatly appreciated.
0
townsma
Asked:
townsma
  • 4
  • 2
  • 2
  • +1
1 Solution
 
peter57rCommented:
As far as I know there is no solution other than the one you have identified (Joining the fields)
But I would have thought that for an Excel user it would be a simple step to split the columns up again.
0
 
townsmaAuthor Commented:
Thanks for the reply.  

I would add, that whilst it may be simple to split the fields, or manuually fill the missing data, in Excel.  This report / procedure will be undertaken several times a day, every day, so performing any manual tasks will very soon become extremely tedious. Hence me trying to find a way to do this without forcing the end user to perform any manual tasks.
0
 
mlmccCommented:
Is the report just the cross tab?

Does it expand horizontally?

Have you tried using a manual cross tab?

mlmcc
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
townsmaAuthor Commented:
Hi,  Yes the report contains just the cross-tab, apart from the usual header, footer, logo etc.

No the width is fixed, there are four summaries per row, and this is fixed.

What do you mean by a manual cross-tab?  I wasn;t aware in Crystal Reports that there is more than one type of cross-tab.
0
 
mlmccCommented:
A "manual" cross tab is really just a report but you suppress the details and show the group footers for the data.

mlmcc
0
 
townsmaAuthor Commented:
OK, never thought of the manual cross-tab, will have alook at that.

Thanks
0
 
vastoCommented:
You can pivot the data on the database side instead of in Crystal Reports. This will allow you to export it directly and the process will be faster and simpler than to trick Crystal Reports. For example if the database is SQLServer 2005 and above you can use PIVOT operator and to present the data in similar way as it will be presented in Crystal Reports cross table. However , because the data will be returned in a datatable export will be with values per each column and row. You will need to write a function in VS2008, which will retrieve the data and export it in Excel. I guess this will be pretty easy task since you already have VS2008 available. Instead of calling the report you will call this function and get the excel file directly. You can check this link for how to export a datatable to Excel with just few lines of code: http://www.codeproject.com/Articles/18420/DataTable-to-Excel. There are plenty of other examples too.

Here is an example how to use PIVOT operator in SQLServer to create a dynamic pivot table. I don't know what is your database but you can probably find similar samples for each of the major database types.
0
 
townsmaAuthor Commented:
In my case, as the columns were fixed in the horizontal plane, using a PIVOT in the original SQL worked well for me.

Although I am not sure it would work well in all situations.

I do no know why SAP do not simply put a tick box of whether to hide the duplicate values, there appear to many requests for this feature on the web.
0
 
vastoCommented:
Sorry I missed the link for dynamic pivot table:http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

You don't need this example if the columns are fixed, but it might be useful for other scenarios
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now