Solved

How to resolve layout of report in Reporting Services or SQL Server 2005 query

Posted on 2010-08-19
15
210 Views
Last Modified: 2012-05-10
I have a query that extracts the data that i require from a database.
I have totals for each of these groupings:
District
Gender
TM
SENStatus
CL
YOT
(then the total), see attached spreadsheet of raw data extract

E>G>

District      Gender      TM      SENStatus      CL      YOT      Total
Craddock       Male      FALSE      Stmt      FALSE      FALSE      9
Craddock       Male      FALSE      SA+      FALSE      FALSE      3
Craddock      Female      FALSE      SA+      FALSE      FALSE      0
Craddock      Female      FALSE      No LDD      FALSE      FALSE      42.98
Craddock       Female      TRUE       SA      FALSE      FALSE      1
Craddock       Male      FALSE      LDDNS      FALSE      TRUE       2

I need to add to my query or create an end report that looks like this:

Page Group District      E.G. Craddock Avenue

      Total
All Clients      210.92
All Males      99.54
All Females      111.38
TM      38.58
Stmt      17.16
SA      22.16
SA+      13.08
LDDNS      9.66
No LDD      148.86
CL      6.58
YOT      37.98


I seem to have been going around in depressing circles and this is the closest i have got to the correct answer buteach time i try to create a report i am not getting the correct reuslt, numbers are ok but the layout is stepped or with drill down and i simply want what is displayed in the example.
I have never used reporting services before and my SQL is limited so please keep it simple for me.

Thanks in anticipation of expert help

WITH base AS (

SELECT     

dbo.DimCxsClients.LeadLEA, 

dbo.DimPostcodes.[District Name] AS District, 

dbo.DimCxsClients.Gender, 

dbo.DimCxsClientsIndCircs.TeenageMother, 

dbo.DimCxsClients.SENStatus, 

dbo.DimCxsClientsIndCircs.CareLeaver, 

dbo.DimCxsClientsIndCircs.[SupervisedYOT-Ever], 

COUNT(dbo.DimCxsClients.AspireID) AS TotalCohort, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS TotalNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS TotalEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS TotalCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS TotalCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS TotalNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS TotalCurrencyNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Not EET or NEET' THEN 1 ELSE 0 END) AS TotalNotEETorNEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency Not EET or NEET' THEN 1 ELSE 0 END) AS TotalCurrencyNotEETorNEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Not Known (NI)' THEN 1 ELSE 0 END) AS [TotalNotKnown(NI)], 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS TotalInLearning

FROM          

dbo.DimCxsClients INNER JOIN

dbo.DimPostcodes ON dbo.DimCxsClients.StatsPostcodeKey = dbo.DimPostcodes.PostcodeKey LEFT OUTER JOIN

dbo.DimCxsClientsIndCircs ON dbo.DimCxsClients.IndCircKey = dbo.DimCxsClientsIndCircs.IndCircKey

WHERE      

(dbo.DimCxsClients.IsCurrentRow = 'true') AND 

(dbo.DimCxsClients.Cohort = 'postcomped') AND 

(dbo.DimCxsClients.LeadLEA IN ('staffordshire', 'stoke')) AND 

(dbo.DimCxsClients.Age BETWEEN 16 AND 18)

GROUP BY 

dbo.DimCxsClients.LeadLEA, 

dbo.DimPostcodes.[District Name], 

dbo.DimCxsClients.Gender, 

dbo.DimCxsClientsIndCircs.TeenageMother, 

dbo.DimCxsClients.SENStatus, 

dbo.DimCxsClientsIndCircs.CareLeaver, 

dbo.DimCxsClientsIndCircs.[SupervisedYOT-Ever]) 



, basecalculate AS (

SELECT     

LeadLEA, 

District, 

Gender, 

TeenageMother, 

SENStatus, 

CareLeaver, 

[SupervisedYOT-Ever], 

SUM(TotalCohort) AS [Cohort16-18], 

SUM(TotalEET) AS [EET16-18], 

SUM(TotalCurrencyEET) AS [EET$16-18], 

SUM((TotalEET + 0.92 * TotalCurrencyEET) + 0.42 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA)) AS [EETAdj16-18], 

SUM(TotalNEETAvail + TotalNEETNA) AS [NEET16-18], 

SUM(TotalCurrencyNEETAvail + TotalCurrencyNEETNA) AS [NEET$16-18], 

SUM(((TotalNEETAvail + TotalNEETNA) + 0.08 * TotalCurrencyEET) + 0.58 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA)) AS [NEETAdj16-18], 

SUM([TotalNotKnown(NI)]) AS [OSNK16-18], 

SUM(TotalCurrencyNEETAvail + TotalCurrencyNEETNA + TotalCurrencyEET + TotalCurrencyNotEETorNEET) AS [TotalCurrency16-18], 

SUM(TotalNEETNA) AS [NEETNA16-18], 

SUM(TotalInLearning) AS [InLearning16-18]    

FROM base

GROUP BY 

LeadLEA, 

District, 

Gender, 

TeenageMother, 

SENStatus, 

CareLeaver, 

[SupervisedYOT-Ever])



, calculatetotals AS(

SELECT     

LeadLEA, 

District, 

Gender, 

TeenageMother, 

SENStatus, 

CareLeaver, 

[SupervisedYOT-Ever], 

[Cohort16-18], 

[EET16-18], 

[EETAdj16-18], 

[NEET16-18], 

[NEETAdj16-18], 100.0 * [NEET16-18] / [Cohort16-18] AS [NEET%16-18], 

CASE WHEN [NEETAdj16-18] = 0 THEN 0 ELSE (100.0 * [NEETAdj16-18]) / ([EETAdj16-18] + [NEETAdj16-18]) END AS [NEETAdj%16-18], 

[NEETNA16-18], 

CASE WHEN [NEETNA16-18] = 0 THEN 0 ELSE (100.0 * [NEETNA16-18] / [NEET16-18]) END AS [NEETNA%16-18], 

[EET$16-18], 

[NEET$16-18], 

[OSNK16-18], 

[TotalCurrency16-18] + [OSNK16-18] AS [NK16-18], 

100.0 * ([TotalCurrency16-18] + [OSNK16-18]) / [Cohort16-18] AS [NK%16-18], 

[InLearning16-18], 100.0 * [InLearning16-18] / [Cohort16-18] AS [InLearning%16-18]

FROM          

basecalculate 

GROUP BY 

LeadLEA, 

District, 

Gender, 

TeenageMother, 

SENStatus, 

CareLeaver, 

[SupervisedYOT-Ever], 

[Cohort16-18], 

[EET16-18], 

[EETAdj16-18], 

[NEET16-18], 

[NEETAdj16-18], 

[NEETNA16-18], 

[EET$16-18], 

[NEET$16-18], 

[OSNK16-18], 

[TotalCurrency16-18], 

[InLearning16-18]

)



SELECT     

LeadLEA, 

District, 

Gender, 

TeenageMother, 

SENStatus, 

CareLeaver, 

[SupervisedYOT-Ever], 

[NEETAdj16-18] 

FROM         

calculatetotals

GROUP BY 

LeadLEA, District, Gender, TeenageMother, SENStatus, CareLeaver, [SupervisedYOT-Ever], [NEETAdj16-18]

ORDER BY 

LeadLEA, District

Open in new window

RawFile.xls
0
Comment
Question by:BelizeMelanie
  • 7
  • 6
15 Comments
 
LVL 10

Expert Comment

by:itcouple
Comment Utility
Hi

If you have correct data in query (columns) and you want to display them in specific way ("rows") the easiest approach is to add list item from the toolbar. This special object allows you to add text boxes + display your columns in them and arrange them the way you want it... like you showed.

Hope that helps.
Regards
Emil

0
 

Author Comment

by:BelizeMelanie
Comment Utility
Thanks for advice Emil but i really dont know enough to be clear what i should be doing with the list and rows, back to the drawing board for me i think
0
 
LVL 10

Expert Comment

by:itcouple
Comment Utility
Hi

Attach the rdl file and I will try to play with design and send it back to you. if that is ok with you.

Regards
Emil
0
 

Author Comment

by:BelizeMelanie
Comment Utility
Hi Emil

I have got this to a point where it is doing what i needed after what seems like hours of banging my head on a brick wall :-) and many thanks to you for setting me off on the right path but now have further complications :-(
I have attached a screen dump of my current report result (reportscreendump) along with a copy of the rdl code (xml version), i have a page for each district result but this screen dump is just the first page for your reference.
The query behind this is as previously loaded on Experts Exchange.

This current query and report run for just where the field 'iscurrentrow' = 'true' in the database. I now need the query and report to show this month compared to the same month last year. I have amended the query to retrieve the data for the two months and that works fine but now am struggling to know how to report this as required. So basically instead of having where iscurrentrow = true i now have rowstarte = this month and this month last year and then i group the query on rowstartdate. This means i know have group by on leadlea, district and rowstartdate and then all my columns of results.
The report needs to look like the second file attached named 'screen dump of new requirements'  (on this the figures are the same in the 2 sets of columns as i have just copied the previous two columns so i can show you what i need), i can see how to group by lead lea and district on the report as that is what i want, i can now see how to put my fields in as needed for reporting on one month but cannot get my head around how to layout as required to compare two months. HELP!!!!!!

Thanks
Melanie
ReportScreen-Dump.doc
screen-dump-of-new-requirements.doc
xml-version.doc
0
 

Author Comment

by:BelizeMelanie
Comment Utility
I have got to this new point with the report, the last thing i need to resolve is how to get the two date results side by side instead of on top of each other.
0
 

Author Comment

by:BelizeMelanie
Comment Utility
File attached
RecentReportResult.doc
0
 

Accepted Solution

by:
BelizeMelanie earned 0 total points
Comment Utility
Have solved this i think by using two tables in the report.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 10

Expert Comment

by:itcouple
Comment Utility
Hi

Sorry for no response; I was unavailable for several days.... I'm glad to hear you resolved all your challenges. Two tables sounds like a good solution. If number of rows in each "table" is the same than using matrix might also work for instance the same data column for different years (matrix column grouped by year).

Regards
Emil
0
 

Author Comment

by:BelizeMelanie
Comment Utility

Now i have a further problem and i am not sure how best i deal with this.
It seems that Reporting Services 2005 does not allow for caluclations to be completed on my two tables.
As you know i have a query that calculates my required data results, with two date parameters (@date and @date1), resulting in two data sets. I have put these into the rpeort and that works fine.

Now i need to calculate the difference in the two data sets. I have tried putting a text box with the formula in, e.g. =ReportItem!Text1.value - ReportItem!Text2.value but i get the error;
"[rsReportItemReference] The Value expression for the textbox ‘textbox71’ refers to the report item ‘textbox1’.  Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
I have read endless comments about this not being possible in reporting services (unlike Crystal) but i really need a report that states the results for two given dates and claulates the difference in the two figures for each row.

On EE i have found what seems like a solution but it is a little beyond my understanding so i am not sure if it would be possible for you to interpret. I understand putting the 3 text boes, writing th code on the code tab but am  alittle lost about how it works. I have wirtten the code as stated, put two hidden textboxes called priorval and curval with the code expressions written in plus a third text box with the expression written in but i get a 0 as the result. This may appear very stupid but i will ask anyway - the 3 text boes all seem to refer to each other and not to bring in the values from the orginal data sets??!!

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_24917877.html?sfQueryTermInfo=1+10+30+calcul+differ+how+report+servic+tabl+two

Failing this being a solution i do not know how to get the original query to give me the results. How do i ask the query to claulate the differnces on the results based on the fact that i am entering the two date parameters on running?
e.g. after my final select query how do i say:
calcdiff between (@date neetadj16-18) and (@date1 neetadj16-18) by district.

I would be grateful for any help or advice you may be able to offer as my time is running out and i have stuggled for days with this. Should i report the question on EE?

Thanks
0
 
LVL 10

Expert Comment

by:itcouple
Comment Utility
Hi

Unfortunatelly I will have to point you to alternative solution. Using matrix can resolve your issue. In order to help you I have created test data and test report. See screenshot below. I will upload rdl file but I'm afraid it might not work in 2005 so I might have to reupload it once I get back home (using 2005 format).

p.s. Regarding Crystal reports.... I have never used it but it seems to be very different approach and I'm very glad I haven't used it because I think using SSRS approach not Crystal reports approach :p

I will also play with expressions because you CAN refer to different datasets. (It is much easier with 2008 R2 as they provide lookup functions but I think still doable in 2005/2008)

Regards
Emil


MatrixReport.bmp
0
 
LVL 10

Expert Comment

by:itcouple
Comment Utility
I'm attaching rdl file. I added .txt extension in order to upload it which you will have to remove. I don't think it will work with 2005 but it is worth trying if it doesn't I will do the same in 2005.

Regards
Emil
MatrixReport.rdl.txt
0
 

Author Comment

by:BelizeMelanie
Comment Utility
Hi Emil

I have removed txt extension and uploaded RDL file to BIDS but I am not any the wiser. I get an error that Report Element was not found and my knowledge is so basic i do not know how to amend the code file or even understand it!
Could you send me a screen dump of the design view of the matrix report that you have created as i cannot get it to even resemble yours.
Many thanks
Melanie
0
 
LVL 10

Expert Comment

by:itcouple
Comment Utility
Hi Melanie,

I'm attaching the design picture. I have access to 2005 version and will try to upload 2005 rdl file but I won't be able to do that until tomorrow.

Regards
Emil
MatrixDesign.bmp
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now