Forum Discussion
Tables of data
Hi All,
Slightly difficult to word this but here goes...
I have tables of data going as far back as 1999/2000. Each table differs in that some of the data items are not consistent through the years, i.e. the data items get added or removed. The organisation code data remains consistent and is seen as the identifier and as the key to build relationships in BI. What I want is to emulate the website below, where the user can select reports to download based on what they chosen from the drop down filters. i.e Year, Level and Section. (For now I'm trying to achieve All Trust and All Site.)
Is this possible with the current functionality of BI?
http://hefs.hscic.gov.uk/ReportFilter.asp
Advice would be appreciated, feel free to ask questions.
Sun1
Hi Sun1，
Based on your description, you want to filter all the fields(column hearder in Trust Level table). We are unable to achieve it, because the slicer is used to filter the row records in the table rather than columns. Thanks for understanding.Best Regards,
Angelia
7 Replies
- Greg_Deckler
Community Champion
Can you provide some sample data from a couple different years, the solution is going to depend on your data and I like to try to replicate this to find the solution. Text data please, screen shots make me type.
Smoupre
Below are 4 tables thats have been editied, 2 tables are at Trust Level and 2 are at Site Level (NB. Each Trust has a number of sites). As a user I should be able to select the year and then the level, Trust or Site to display a full table showing all data items. The concept is straight forward but unsure how to work it out.
Like the link below.
http://hefs.hscic.gov.uk/ReportFilter.asp
Thanks once again.
1999/2000 Site Level data
Organisation Code Organisation Name Commissioning Region Organisation Type Year Available Beds (No.) Site footprint (m²) Occupied Floor Area (m²) Unoccupied Floor Area (m²) Leased In Floor Area (m²) Leased Out Floor Area (m²) Site Land Area (Hectare) RA4 EAST SOMERSET NHS TRUST Dorset & Somerset SMALL ACUTE OUTSIDE LONDON 1999/2000 406 19,594 37,290 266 840 109 6.1990 RAX KINGSTON HOSPITAL NHS TRUST South West London MEDIUM ACUTE LONDON 1999/2000 562 20,753 58,331 0 1,074 0 6.5400 RCF AIREDALE NHS TRUST West Yorkshire MEDIUM MULTI-SERVICE 1999/2000 603 51,985 76,557 0 278 0 27.0370 2003/2004 Site Level data
Organisation Code Organisation Name Commissioning Region Organisation Type Year Percentage of single bedrooms for patients (%) Number of Nightingale wards in use - Acute (No.) Number of Nightingale wards in use - Elderly (No.) 5A1 NEW FOREST PCT Hampshire & Isle of Wight PCT 2003/2004 0.34% 0 0 5A2 NORWICH PCT Norfolk, Suffolk and Cambridgeshire PCT 2003/2004 0.66% 0 0 5A3 SOUTH GLOUCESTERSHIRE PCT Avon, Gloucestershire & Wiltshire PCT 2003/2004 0.00% 0 0 1999/2000 Trust Level data
Organisation Code Organisation Name Commissioning Region Organisation Type Year Estates Service Costs (£) Total number of staff employed (WTE) Telecommunication service cost (£) Average Response Time (Sec's) RIDDOR incidents (No.) Non-RIDDOR incidents (No.) Sterile Services cost (£) R01 ASHWORTH HOSPITAL AUTHORITY Cheshire and Merseyside SPECIALIST 1999/2000 1,065,023 1,612 193,131 4 46 319 R02 BROADMOOR HOSPITAL AUTHORITY North West London SPECIALIST 1999/2000 1,573,817 1,066 114,774 36 51 303 R03 RAMPTON HOSPITAL AUTHORITY Trent SPECIALIST 1999/2000 157,050 1,242 136,167 5 0 0 2003/2004 Trust Level data
Organisation Code Organisation Name Commissioning Region Organisation Type Occupied beds (No.) Available beds (No.) Total number of wards (No.) Year Number of sites - General Acute Hospital (No.) Number of sites - Multi-service Hospital (No.) Number of sites - Short Term Non-Acute Hospital (No.) Number of sites - Long Stay Hospital (No.) 5A1 NEW FOREST PCT Hampshire & Isle of Wight PCT 137 140 34 2003/2004 0 1 4 0 5A2 NORWICH PCT Norfolk, Suffolk and Cambridgeshire PCT 275 320 35 2003/2004 0 0 2 1 5A3 SOUTH GLOUCESTERSHIRE PCT Avon, Gloucestershire & Wiltshire PCT 2003/2004 0 0 0 0 
v-huizhn-msftMicrosoft Employee
Hi Sun1,
For Trust Level, create a slicer, which field should added? By another way, like the choose in the web, what value should be as the options? Please describe clearly. Thanks a lot.
Best Regards,
Angelia
- v-huizhn-msft
Hi Sun1,
You can create the report in Power BI desktop using any native visualization or custom visual. For the drop down filters, i.e. Year, Level and Section in the web, you'd better create three slicers in the report. One year slicer, one Level slicer and another slicer including Section filter. More details about slicer, please review this article. Just like the report in the article, it will return different report when you select the different values in slicer, which is similar in the website report.
If this is what you want, please post the sample data and expected result for further analysis.
Best Regards,
Angelia
