Manual Cross Tab Create Dynamics Columns Dates

[Luvsql]

I have data with 3 fields The Item and Qty repeats for each date. The dates relate to a rental of an item so each item could have 5 days, 30 days 60 days etc.

Item,Qty,Date

I would like to have the Item in Rows (Group) but how would have have days go across the columns to match a parameter range? For exmaple, I enter "2012-02-01" to "2012-03-15" and I would like the columns at auto-fill from 02-01, then 02-02, 02-03 all the way to 03-15 and Qty to fill in for the correpsonding date for that item in that column.

I can't use Crosstab because there are many formatting that I want to do such as colours, backgrounds and don't want any group subtotals, totals etc.

 

[lbass]

If you are doing this manually, then you could create columns like this:

//{@Day1}:
if {table.date}=minimum({?DateRange}) then
{table.qty}

//{@Day2}:
if {table.date}=minimum({?DateRange})+1 then
{table.qty}

etc., up to or greater than the maximum number of dates.

You would add the daterange to your selection formula:

{table.date}={?DateRange}

-LB

 

[Luvsql]

The formula works for getting column headers, but when placed into the details section, if one order has 4 days of data, I get 4 rows as well. The great thing about a cross tab is that there's a single line for each group.

 

[lbass]

I thought you understood how to do the manual crosstab. Once you place the formula in the details section, you have to insert a summary (sum) on it at the group level (You will group on your "row" field, ItemID). Then suppress the group header and details sections (drag the groupname into the group footer). Column names would be created by creating label formulas like this:

//{@Day2Label}:
minimum({?DateRange})+1

-LB