Solved

Dynamic Columns in CR 9?

Posted on 2004-08-05
9
371 Views
Last Modified: 2008-03-04
Hi...

I'm using Crystal Reports 9 with a SQL Server database. I'm attempting to create a report from a query that returns a dynamic set of columns based on a users input. The report displays the number of products sold to date for a user-specified date range (by month). For example, my if a user selects Products A, B, and C and then selects the date range from Jan 2004 to July 2004, the report will contain the following column headings:

Product
JAN_2004
FEB_2004
MAR_2004
APR_2004
MAY_2004
JUN_2004
JULY_2004

I'm creating the final output table using temp tables and actually adding columns (through the ALTER command) dynamically based on the date range specified.

Is there any way in Crystal to display this data dynamically so that the column headers are determined by what is exactly returned in the query?

I'm new to Crystal so please be detailed in your suggested solutions.

Thank You!
0
Comment
Question by:SuperLeon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 11727711
Instead of a 'standard' report you can create a crosstab report - this is exactly the situation a crosstab is intended for.

If you select your 'Product' field for the rows and your 'Date' field for the columns then the crosstab will automatically be created with the rows and columns corresponding to the data returned.  I assume that you have a quantity or value field that you're summarizing and this would go in the summary section of the crosstab.  

The date range parameters that you're using now would still work the same way - by filtering the records coming into Crystal they would filter the crosstab also.

The drawback is that the customization of crosstab reports is limited so if there are more complex things happening in your report that you didn't mention then the crosstab may not help you (and there's probably not much you can do differently than what you're already doing).

HTH

frodoman
0
 
LVL 1

Author Comment

by:SuperLeon
ID: 11727826
I'm not sure I understand...here's a sample of my table:

Product      JAN2004     FEB2004     MAR2004    
A               10             15              20
B                3              12              18
C                23            28              39
D                14            23              31

The totals are running totals of sales to date. So in the above table, the company sold 5 units of Product A in Feb (15-10).

I want that represented EXACTLY the way I have it in my query. Am I doing too much work in the query? Should I let Crystal do the work in the cross-tab? Again, I am brand new at CR and not familiar with Cross-Tab reports. If you could shed some light on all of this, it would be greatly appreciated.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11728621
>>> Should I let Crystal do the work in the cross-tab?

Yes!

Just so I understand, your table layout contains four fields - Product, Jan2004, Feb2004, Mar2004.  In the product field you have text values (A, B, C, etc.).  In Jan2004 you have number values (10, 3, 23, etc.) - same for Feb2004 & Mar2004.

Is this correct?  I just want to tell you how to do this w/out the confusion of guessing at your file layout...

frodoman
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
LVL 1

Author Comment

by:SuperLeon
ID: 11728806
Yes...that is :almost: correct. My table actually contains more columns:

SET COLUMNS:
- Product ID
- Product Name
- Product Group

DYNAMIC COLUMNS
- {month}_{year}
- {month}_{year}
- {month}_{year}
- {month}_{year}
- {month}_{year}
- etc.........

Again, the month columns are determined by the dates selected by the user before the report is generated. There can be any number of columns and the names of the columns are dynamic (month_year).

Please let me know if I'm not clear.  Thanks!
0
 
LVL 42

Accepted Solution

by:
frodoman earned 250 total points
ID: 11728922
Thanks, that's clear, but unfortunately that's going to be tough to do because to setup the crosstab you have to place the specific columns onto the report.  Do you have to use the temp table and/or does it have to be in this format?

If instead you can have your data come through like this:

- Product ID  
- Product Name  
- Product Group  
- Period    (this would be "Jan 2004", "Feb 2004", etc.)
- Qty

Virtually the same as you're using now except the field "Period" would be fixed and it would contain the dynamically determined months.

Data in this format will enable you to create the basic report in a few seconds.  Otherwise the only way you're going to be able to do it is by using an application langauge to create the report on the fly which isn't something I'd consider for a crystal newbie (it also requires either advanced or developer edition of CR9 -- standard / professional edition doesn't allow).

Let me know...

frodoman
0
 
LVL 1

Author Comment

by:SuperLeon
ID: 11729184
Thanks Frodoman...

I'm gonna rework my query to output the data in your format...once that is done, I'm guessing your suggestion is to throw that data in to a cross-tab. If that's the case, can you help me determine what data to put into which areas of the cross-tab wizard (columns/rows/sum fields)?

Thanks again for your guidance!
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11729445
Sure, something like this for your crosstab:

Row:
{Product ID} {Product Name}

Column:
{Period}

Sum:
Sum of {Quantity}  (when you drag the Qty field in it should default so 'sum' but if it doesn't just change the dropdown).

HTH

frodoman
0
 
LVL 1

Author Comment

by:SuperLeon
ID: 11729667
You the man (or woman)! Worked like a charm!

Thanks Again!
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11729690
Glad I could help - frodoMAN

not frodoWOMAN :-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question