Dynamically generated list headers

[barney]

Hi - I have a requirement to design a report, that looks fairly similar to a crosstab but the measure value needs to be text, which I realise is not possible. My data looks similar to this.

Name          Date       Shift
Name 1       10/11       A
Name 2       10/12       B
Name 3       10/13       B
Name 4       10/14       C


I need to create a report that looks like this




Name        10/12    10/13    10/14     10/15
Name 1          A          A           A           A
Name 2          C          C           C           C
Name 3          A          A           A           A


he date columns need to be dynamically generated as they can be any number of dates, between 1 and 28 days.
Would anyone have any ideas please on how I might accomplish this please?  :D

[prikala]

No guarantee and I definitely do not recommend this but if there is absolutely no other way to solve:
If you "unlock" the report, you can insert your own text items  in the crosstab value cells.
If your "Shift" can be expressed in the query as numbers (1==A, 2==B etc) and number of distinct values is limited, you might be able to add separate text items containing 'A', 'B' etc and add conditionally render them based on the numerical "Shift" values.
You can also hide the actual "Shift" numerical cell value.

[BigChris]

It's not as elegant as a proper crosstab, but you could create a list that looks like a crosstab. It looks like you've got future dates in your data, but you can amend this to suit. For each of your dates you could create a calculation that looks something like:

Code Select Expand


[Date1Shift]:
if([Date]=_add_days(current_date,1)) then ([Shift]) else (Null)

You'd obviously then need to repeat that for each of your dates.

Then you need to work out the dynamic dates in the headers. For that you need corresponding calculations of

Code Select Expand


[Date1]
_add_days(current_date,1)

You'll then need to unlock the report, delete the default heading for each column and insert the calculation.

To tidy up the layout you might want to play with render variables so that you only display the columns that have got got data in them - you'd probably need to use something like maximum([Date1Shift] for Report) is not missing

This is all off the top of my head, so you'll need to do your own testing.

[Invisi]

Have you tried a crosstab? and your 'measure' without any aggregation?