Solved

Dynamic Columns in CR 9?

Posted on 2004-08-05
9
361 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
  • 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now