report that can dynamically grow vertically and horizontally

[jcaulder]

I have been asked to create a report that does the following(greatly simplified below):

Store 1 Store 2 Store 3... Sum
Month Rentals 5 7 8 20
Month Returns 3 4 0 7
Month Deliveries 2 2 5 9
Month Pickups 1 1 2 4
_____________________________________________________
Rental/Returns 2 3 8
Sum Deliveries/PU's 3 3 7

This is GREATLY simplified as I mentioned. The actual report would contain over 319 line items(Month Rentals, Month Returns, etc.) and the various sums calculating across all stores as well as in each store. To further complicate the matter, each stores data may reside in a different database and therefore require a different connection which is unknown at design time.

So, my problem is I know of no way to allow for the report to dynamically grow horizontally(i.e. work for x number of stores where x can be 1 to infinity). As mentioned previously, each of the stores data may reside in a different database so the connection string must be built dynamically also.

Ignoring the fact that the number of stores is unknown, it seems to me the only way to create this in Crystal is for every field for every store be a subreport with its own query string. That or either return some recordset that identifies each row as belonging to a specific store and a row type that tells what type of value it is(i.e. Rentals, Returns, Deliveries, etc.) and then use a computed field to display the value in the correct place.

With hundreds of rows each containing different data, each for a different store, and each needing a sum across all stores and across each store, this already seems too complex to create and maintain. However, add the extra wrinkle of an unknown number of stores and I really have no idea how to begin.

Perhaps Crystal can create subreports/computed fields dynamically based on the return value of a query or the parameters entered by a user? So I could do something like:

1)Query store database(returns 15 records that include connection data for each store)
2)Create a subreport/computed field based on each of the 15 records using the records values to create a datasource for each
3)Update the total computed fields such that they correctly sum all appropriate fields for every store added 1 to x.

The report is currently generated using Excel with a macro that examines each recordset and specifically puts that value in a certain cell based upon the store. Similarly, computed fields are used to calculate the sums by adding A1 + A2. I just don't see a comparable way of doing this in Crystal.

Anyone got any ideas?

Thanks!

 

[synapsevampire]

Try Insert->Crosstab and place the Store field in the column, the field showing the column Month types in the row, and the field to sum in the summary field section.

You'll be better served to first supply technical information adn then you can provide a descrition if you feel it's required:

Crystal version
Database/connectivity used
Example data (show the fields and examples of what's in there)
Expected output (you did a good job with this)

The crosstab might supply almost everything you need, it's impossible to tell as what we need to know is what's in the database.

-k

 

[Turkbear]

Hi,
Some basic questions:
How does Excel 'know' which datasets to use?
How are the datasets created, if the number of them that are needed is unknown?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jcaulder]

I had already considered crosstab and tried a sample of this. It did not appear flexible enough to handle the scenario I am dealing with unless I did not understand it completely.

I am currently using Crystal Reports Developer 10 connecting to SQL Server 2000. However, if there is a different version of Crystal that would provide support for this, it may be possible to purchase it.

Currently, a series of stored procedures are executed for each store. The Excel spreadsheet is built dynamically by determining which store number is current and placing the values returned into specific cells(i.e. run stored procedure for Store 1 --> take the value returned in CurBegUOR --> put in cell D6). I will attempt to show a group of related resultset schemas below for clarity:

Stored Procedure: UnitCounts
Fields Returned Sample Value

CurBegUOR 675
CurDeliveries 6
CurPickups 1
MTDBegUOR 662
MTDDeliveries 95
MTDPickups 77

Stored Procedure: ComplementaryRentalsDaily
Fields Returned Sample Value

Count 0

Stored Procedure: ComplementaryRentalsMTD
Fields Returned Sample Value

Count 0

Stored Procedure: ComplementaryRentalsTotal
Fields Returned Sample Value

Count 37

Stored Procedure: ScheduledDeliveries
Fields Returned Sample Value

Count 50

Stored Procedure: ScheduledPickups
Fields Returned Sample Value

Count 48


Now the output of this returned data should be:

Store 1
UOR Completed Daily
Beginning UOR(A) 675
Completed Deliveries(B) 6
Completed Pickups(C) 1
Ending UOR(D) 680 (A + (B - C))
_______________________________________________
Gain/Loss UOR(E) 5 (D - A)
Complimentary Rentals(F) 0

UOR Completed MTD
Beginning UOR(G) 662
Completed Deliveries(H) 95
Completed Pickups(I) 77
Ending UOR(J) 680 (G + (H - I))
_______________________________________________
Gain/Loss UOR(K) 18 (J - G)
Complimentary Rentals(L) 0
Total Complimentary(M) 37

UOR Scheduled Rest of Month
Deliveries(N) 50
Pickups(O) 48
Anticipated Gain/Loss(P) 20 (H + N)-(I + O)
Anticipated Ending UOR(Q) 682 (G + P)

************************************************

Hopefully the notation is self-explanatory. Any number without a formula beside it in the output above is returned directly from the query. As you can see, some of the fields need to be computed from within a crosstab while others would need to access fields/sums from within a previous crosstab to compute grand totals. This format must be repeated across multiple stores with grand totals also across all stores for each row.

I just can't see where the crosstab provides this flexibility. Is this enough information to go on or too much?

And TurkBear, the current version of the Excel spreadsheet is not *smart* enough to expand when new stores are added. It easily could be by simply making the cell that is being populated based upon a variable that is incremented for each store rather than a hardcoded value. Currently, it says 'store 1.curBegUOR' goes into cell D5 but it could just as easily put it in --> strCurCell & '5' or whatever the correct syntax would be.

The start of the entire process would be to accept as input a store number. If this is not null, the report should only pull data for that one store. If it is null, data would be pulled for ALL active stores. This would be queried from a Stores table and the results of this initial query would both determine how many columns of store data will exist as well as provide the connection string to each stores database.

Guess that is enough information for now. Thanks for the replies and let me know what I've omitted.

J