CODEWITHSUNDEEP
c#sql-serverreporting-servicescrystal-reports
am0r
am0r

Reputation: 69

SQL Dynamic Columns to Crystal Reports

I have the following result set from a certain stored procedure.

Tag  | Name  | Color  |   Qty   |     Sizes
 A     Joey    Black    1,2,3,4    S, M, L, XL
 A     Joey    Indigo   5,4,3,2    S, M, L, XL

The expected output to crystal report view.

                       S   M   L   XL
A   Joey    Black      1   2   3   4 
A   Joey    Indigo     5   4   3   2

First, I tried separating the comma separated strings using SQL and managed to convert it to table rows.

S
M
L
XL

My initial approach was to create separate result sets and construct the report using these blocks of data. Eventually, the separate blocks of data yielded results in multiple sub reports and I am having difficulties combining details from columns as well as rows.

Is there a way to create a report directly from a datatable? I was hoping to modify the SQL Result using .net DataTables and bind it directly to the report. Or maybe there is a much better approach in accomplishing this report?

Any help/idea will be very much appreciated.

PS: Please note that the Sizes are dynamic data and can vary for every record. Also, Qty is directly related to size. i.e Qty 1 = Size S

Upvotes: 0

Views: 1228

Answers (1)

CoSpringsGuy
CoSpringsGuy

Reputation: 1645

I can see several ways to approach this in Crystal but give this a try. Rather tedious but should work. create a formula called sz1Hdr

if ubound(Split({@Sizes},",")) > 1 then Split({@Sizes},",")[1]

repeat that for the maximum number of sizes you might have incrementing the number 1 in both the name of the formula and the content of the formula. so the next one would be sz2Hdr

if ubound(Split({@Sizes},",")) > 2 then Split({@Sizes},",")[2]

Place those in the header of the group you would create for Tag.

Now create a formula called qty1

if ubound(Split({@QTY},",")) > 1 then tonumber(Split({@QTY},",")[1])

Create one of those for each of the sz#Hdr formulas you made above incrementing the 1 as well. Place each of these in the detail section below its corresponding header. Right click each qty# Formula on the canvas and select format field - common and click the X-2 to the right of Suppress and enter this formula in each.

CurrentFieldValue=0

You can now sum each of those fields to your group footers and/or report footers... Not the cleanest solution but if I understand your requirements this should work.

Upvotes: 1

Related Questions