This is a migrated thread and some comments may be shown as answers.

CrossTabs?

10 Answers 344 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

10 Answers, 1 is accepted

answered on 28 Apr 2008, 09:59 PM

For several years now I've had a solution in place that uses GeckoWare SQL Crosstab Builder to generate cross-tabulated data on the fly from a SQL Database.

The data is used to populate a Telerik RadGrid. In code I dynamically re-arrange the columns as appropriate for the report and then use the built-in export to Excel feature to push a very pretty Excel file to my user.

The beauty of the GeckoWare Crosstab Builder is that (like a true crosstab solution) you don't need to know ahead of time what the columns are going to be.

It's worked seamlessly with RadGrid for years now (surviving RadGrid version updates with little or no code tweaking required).

This isn't quite the same as a fully baked cross tab report (like Crystal) but it's close.

answered on 01 May 2008, 02:40 PM

Hi Tonya,

Unfortunately, the database is full of confidential client data so I can't provide a actual screen shot.

As the grid gets immediately exported to excel, we haven't needed to implement any grouping levels.

Here's a simplified example of how the pieces fit:

The application is used by a travel/touring company to record client data. The questions they need to ask vary year to year and trip to trip. The admin environment allows them to configure a trip questionnaire profile. The questionnaire is displayed to an end user via the ASP.Net "dynamic controls" rendering technique (i.e., each registrant is presented with a custom registration environment based on a specific questionnaire profile). The data is stored in the database like this:

Questions table:

QuestionID	QuestionType	QuestionText
1	Textbox	First Name
2	Textbox	Last Name

Answers table:

RegistrationID	QuestionID	Answer
1	1	John
2	1	Jane
1	2	Smith
2	2	Doe

Report Definition table:

ReportID	QuestionID	ColumnOrder
1	1	1
1	2	2

I have a stored procedure that pulls the relevant data for any given report together like this:

RegistrationID	QuestionText	Answer
1	First Name	John
1	Last Name	Smith
2	First Name	Jane
2	Last Name	Doe

Then, the XGS Crosstab Builder is invoked in that same stored procedure to transform the data like this:

RegistrationID	First Name	Last Name
1	John	Smith
2	Jane	Doe

The transformed data is fed to RadGrid. Some code is called that re-sorts the columns based on the report definition (this simple example didn't require re-sorting but most non-trivial reports do), then immediately exports the RadGrid to excel.

Hope this helps to clarify. Let me know if you have any further questions.