I have been trying to create a pivot table with dynamic cross tabs and columns with little luck. I need it to function just like this, with dynamic cross tabs on top and dynamic columns on the left.
1 http://img689.imageshack.us/img689/1812/crosstabs.jpg
I also only need selected columns and cross tabs to be displayed. For instance if only one column and one cross tab is selected they should be the only ones displayed. I have tried a sql pivot and a grid but i cannot get it to format correctly with the two column categories stacked on top. I also tried crystal reports but there is no way to hide the cross tabs when they are not selected. I tried the Telerik reporting control but cannot get the cross tabs to dynamically generate with the drop down. I am looking for any advice on how to approach this. Also if anybody has created anything like this or has a idea how, please share.
Thanks in advance.
I have never used Telerik, so the following advice applies to Crystal only.
You can hide crosstabs conditionally in Crystal in at least two different ways:
- Through the Format dialog (not the Cross-Tab expert) - right-click on the Crosstab, select Format Cross-Tab... from the menu, press the
x-2 button next to Suppress in the Common tab of the Format Editor dialog and enter an appropriate formula (something like {?crosstab1s1}="n/a").
- By placing the crosstab in its own sub-section and making the sub-section conditionally suppressed - right-click the grey area to the left of the report layout area in the Report Designer Design tab in the relevant sub-section, select Section Expert... from the menu, press the
x-2 button next to Suppress (No Drill-Down) in the Common tab of the Section Expert dialog and enter an appropriate formula.
I suggest using the latter of these two approaches - suppressing the crosstab itself will still leave the whitespace where it was to be displayed.
You could also place the crosstabs each in their own subreport and suppress those conditionally - however, this introduces an additional level of complexity that does not appear to be necessary at this point.
A few other comments:
- Crystal's parameters are much less flexible than what can be achieved in ASP, so if possible do all the parameter selection in ASP and pass the parameters to Crystal.
- Crystal's handling of nulls is somewhat awkward, so pass a specific value indicating that a crosstab is not required, rather than relying on evaluation of a parameter as null.
- Your crosstab parameters are currently arranged as Crosstab 1 and Crosstab 2, each with or in Sections 1, 2 and 3. It isn't clear whether this means that there should be up to six crosstabs in total, or just two:
- If the former, then it isn't clear how the row grouping, column grouping and summarised fields are to be specified.
- If the latter, then I suggest renaming Sections 1, 2 and 3 as Row Grouping, Column Grouping and Summarised Field (as I assume that this is what these are to represent).
- You currently have Show Group Totals and Show Percentages options - these appear to apply to the crosstabs. These options can be specified in Crystal, but not conditionally within the report designer.
- If you are able to access the Crystal API, it should be possible to set these options programatically at runtime.
- Otherwise, your only option if you want to include both options for each crosstab would be to add four versions of each crosstab to the report, each conditionally suppressed based on which options are selected. (I wouldn't recommend this approach unless you have no alternative.)
- Crystal can't handle dynamic data structures (ie. the data structure has to be known when designing the report) so I suggest generating the result set in a SQL stored procedure based on the parameters specified, with a structure something like:
Dataset structure
crosstab1Row
crosstab1Column
crosstab1SumValue
crosstab2Row
crosstab2Column
crosstab2SumValue
reportColumn1
reportColumn2
reportColumn3
reportColumn4
reportColumn5
