Solved

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

Posted on 2010-08-19
15
227 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
ID: 33489214
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
ID: 33496502
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
ID: 33498874
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:BelizeMelanie
ID: 33512373
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
ID: 33512967
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
ID: 33512974
File attached
RecentReportResult.doc
0
 

Accepted Solution

by:
BelizeMelanie earned 0 total points
ID: 33522055
Have solved this i think by using two tables in the report.
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33523218
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
ID: 33563014

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
ID: 33567780
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
ID: 33567841
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
ID: 33576166
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
ID: 33594066
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
ID: 34533074
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

860 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