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
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>>
;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)