<RESOLVED>Multiple Columns with a single query item using filters

[locus]

Hi folks,

Hopefully you can provide some insight into this.


I have 4 query items [Assignment] [Location Count] [Section] [Status]
There can be 4 location statuses, forcing a single assignment onto a possible 4 rows.

ASSIGNMENT   LOCATIONS   SECTION   STATUS
A100                         20             60     READY
A100                         15             60     BUSY
A100                         10             60     HELD
A100                          5             60     COMPLETE


The requirement is to create a new column for each status of so that each assignment is on a single row
[Assignment] [Location Count] [Section] [Status1] [Status2] [Status3] [Status4]

ASSIGNMENT   LOCATIONS   SECTION   READY   BUSY   HELD   COMPLETE
A100                        50              60          20       15        10          5


I have tried creating a new query subjects for each in Framwork Manager, each with it's own dedicated filter on [Status],
but dropping them onto the list in report studio returns no data.

Is there a way to do this in RS or am I making an error in the modelling?
I am thinking there must be a simple way of doing a query calculation for = [Count of Location] where [Status=X] used mutiple times within a single list report

Thanks a mil,
Locus

[blom0344]

this is all about using the 'bucket' principle, not about using filters. Perhaps the single most discussed technical BI topic. It deserves a sticky   8)

Flattening (pivoting; denormalizing;  whatever you want to call it) is done by using :
1. aggregate functions
2. applying case logic (optional, but usually applied)

For non-numerical values like  strings you would use either minimum or maximum  ,like (schematically):
maximum(case when <<some equation>> then <<some_value>> else null end)

Each expression is used in a new dataitem (creating 'buckets') and avoiding any use of filters, which is really a dead end street unless you decide to build a query OVER a complex union I guess.
In your case  << some equation>> would be about the STATUS being either 1 of the 4 values, returning the STATUS value as <<some_value>>

[locus]

 ;D

Aah...so simple and yet so elusive  :o

Worked like an absolute charm.

Thanks Blom you're a legend!
It will be applied to many reports.

8)