Crystal Report Add Columns Dynamically based on Pivot Query Result

1
votes

I am using VB.Net 2010 with Crystal Reports. I have designed a pivot query that returns the required summary information.

NOTE: I do not want to use cross tab because I need to display the information from right to left.

The following is a sample result:

enter image description here

Simply I am summarizing the number of employees for each rank for every nationality. That is when I modify any employee's nationality, that nationality will appear as a column in the result: PIVOT.

I designed a crystal report that uses the same query and display the same data.

I set the data source of the report to a command and added the query in the command, But here is the problem: The number of columns returned by the query are only those retrieved when running the query at design time! That is, if new employee from an other nationality is added to the database it would not be possible to be displayed in the report. Or even, the maximum number of columns as per the columns set is 3.

My question is:

How can I add these columns dynamically based on the number of columns returned by the pivot query?

MODIFICATION:
As per the suggestions below, I designed a crystal report that uses a dataset with one datatable "DataTable1" and added 6 columns: one for Rank that is fixed, and the other columns are for the possible nationalities that may exist in my employee data table as the following picture: enter image description here

The question now is how display the data returned by the pivot query in these columns? And how hide the other columns that no data are placed on them?

2
parameterscrystal-reportsreportpivotadd
I think your problem can be solved easily if you add a mapping table to your data base for nationality of three columns lets say e.g. NationalityId, NationalityName and NationalityArabicName and in the table above add one column instead of the last three columns called FK_NationalityId holds the value of each Nationality linked with the mapping table in the SP that fills the report in that case you would not have to add a column to the report each time – Abdulrahman_88
Thank you Mr. Abdulrahman, But the above results is not a table, it is data returned by pivot query. Also I already have table with country names having the same columns that you mentioned. I also did not understand how can I display all nationalities based on you suggestion! – Hilal Al-Rajhi
Al-Rajihi as for the displaying you can use inner join between tables, as for the main question i seem to miss understood the problem, do you need to show the count of the countries base on gradecode?? or you need to add columns to your report based on some conditions?? – Abdulrahman_88
Not the count of countries. Basically I have employees table with column nationality that is a foreign key from country master table. I designed a query to get detailed summary of the grades and how many employee from each nationality in that grade, where the above picture shows sample result when running the query – Hilal Al-Rajhi

2 Answers

2
votes

Only solution that I see would work without using crosstab is to create the report as many columns as possible from right to left and then conditionally show and hide as the new columns gets added from database.

1
votes

Crystal Reports uses a static definition at design time. The dynamic option that i know is the cross tab. But it had not attended me in any situation, because there are a lot of design restrictions. If cross tab is good enough to you, stick to Siva's answer. Otherwise, try something similar to Abdulrahman_88's solution.

When i need to create dynamic columns, i simulate this behavior in a similiar way suggested by Abdulrahman_88. I will try to describe as i am used to, i mean, the data source is a .NET DataSet. You can adapt it to your case.

The simpler case, is to assume you have a max number of columns. In your case, a max number of nationalities.

First, i would create a XSD describing the columns:

  • gradeCode
  • Rank
  • currGrade
  • sequence
  • nationality1
  • nationalityDescription1
  • nationality2
  • nationalityDescription2
  • ...
  • nationalityN, where N is the max number of columns.
  • nationalityDescriptionN

So, i would create a similar DataSet in my code and fill it. Note that you could do it in .NET using your pivot table query and a DataAdapter.

Then, i would use the XSD as my data source and design the report with fixed columns and suppress empty columns labels.

If you cannot assume a max number of countries, then you need a "trickier" solution: create a page column. But i works only if you want to break page when the number of columns is wider than the page.

Tell me if you want to try it and need more details.

Here is a link for a full example in .NET. (not avaliable 5 years later, sorry)