Manual Crosstab with Dynamic Headers 1

[Jcfx]

CR10 with Sql 2000.

If someone has a better idea on how to handle this, I am all ears

User is asked via vb app whether they want to see an expense report for all categories, or just a select few. A view is created from their answer and a SP is created to pass the info to Crystal.

A simple cross tab seems like the best way to handle the dynamic column headings, except they are requesting the ability to drill down to the detail level of each cell, from my reading here, the best way to handle that is via a manual cross tab with a sub report linked to each cell.


Two tables involved, one with the categories:

CategoryID CategoryDescription

One with the Transactions, main fields would be:
TransactionID,Customer,TranAmount, CategoryID.


Group by Customer, expected output:

Name CategoryA CategoryB CategoryC......CategoryXXX
JoeSmoe 100.00 0.00 500.00 600.00
^
Drill down to transaction detail.

Categories could be Category A through XXX or only Category D and J.

Would dumping the categoryid into an array then using that array for the column setups be the solution? If so, could someone walk me through an example array?



Julie
CR 9,10 CE10 Sql DB

 

[lbass]

Julie,

Without going into too much detail, you might be able to adapt the method described in thread149-655589, although I'm not sure how to do that using the vb app for the parameters. This sets up each column dynamically, so that Col1 is dependent upon the first parameter selected. You could then link a subreport for each column using the same parameter as well as the group field.

-LB

 

[Jcfx]

Thanks Lbass,

That was one thread that I had bookmarked as a possibility. The catch is that while there are 10 categories now, the user has the ability to create and assign new categories or remove categories. <bleh> If I understand that thread correctly, you need to know the maximum number of possible columns?

The stored procedure the report is based on just pulls the information from a view where a user has defined the number of categories they wish to see. I can do a count of the categorieId field, which would give me the maximum number of columns.

I basically have the report created, one using a normal cross tab but without the drilldown requested <unacceptable>. The other as a manual crosstab with hard coded column headers <and subreports> based on current possible categories, then trying to suppress the columns based on the view, while it had the requisit drill down functionality, it completely falls apart when the user adds or removes categories. <note to clarify, if the user says they want to see categorie 2 and categorie 4, I am fine, what blows up is when the user creates a brand new categorie changing my default column number from 1 to 10 to 1 to 'X'.>


Is there anyway an array for the column headings would work? I wasn't sure if I could assign the array length using a count statement on the categorieId field. Then calling the column headings based on Heading[1], Heading [2], heading [count(categorieID] etc.

Not sure if this helps explain things.



Julie
CR 9,10 CE10 Sql DB

 

[lbass]

To use my dynamic column suggestion, you would have to know the category options in advance in order to incorporate them in the parameter lists. In terms of the maximum number of columns, you would just have to have more than enough columns--not the exact number.

While I've used arrays on occasion, I'm not an expert. Maybe someone else could answer that question.

-LB