Create Price Grid in Report

[Ericka]

I have a product that is custom built by size. I need to create price grid on a report for each model where the horizontal value is the width, the vertical is the height and the entries are the price. The source table is: Model - Width - Height - Part# - Price. Is this possible?

[pbaldy]

Play with the crosstab query wizard.

[June7]

CROSSTAB works for me, sort of. Set a subreport SourceObject property to query object name. However, not able to apply filter criteria even with using PARAMETERS. Have to define columns if you want to apply filter. How many different widths are there?

[Ericka]

That worked. Thank you. Now another How do I get the right order for the vertical and horizontal headers when the sizes are like 6'11. I was able to get the vertical sizes in the right order by adding a column for width feet only and sorting by that but cannot get the horizontal to be in the right order from smallest to largest. It is starting with 10'11 thru 16'11 and then going through 3'11 to 9'11. I tried adding a column for height feet only but that blew the grid way too big.

[June7]

Have to define columns if you want to control their order. Or use placeholder 0: 03'11"

[Ericka]

I am not sure I understand what you are meaning with defining the columns. I created the crosstab and added a column for the width - defined by foot size only (6 vs 6'11) with the total width displayed in the vertical header. If I add another column for the height following the same format then the grid expands exponentially adding a width for every height - see sample. The left hand grid is condensed the way it needs to be but the horizontal height header is not in the proper order (alphabetical vs number) The sample on the right is my attempt at defining the width and height for proper ordering: I recreated the results in excel just for reference.

[June7]

Review http://allenbrowne.com/ser-67.html#ColHead

Alternative is to use real numbers instead of text for the dimensions.

Think your narrative has left and right reversed when referencing images.

[CJ_London]

for the rows consider a calculated column converting feet and inches to inches (numerical value, not text) to get your order, does not have to be displayed, just ordered. For column headings, you dictate the order by specifying the order of the column headings