• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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

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
BelizeMelanie
Asked:
BelizeMelanie
  • 7
  • 6
1 Solution
 
itcoupleCommented:
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
 
BelizeMelanieAuthor Commented:
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
 
itcoupleCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
BelizeMelanieAuthor Commented:
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
 
BelizeMelanieAuthor Commented:
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
 
BelizeMelanieAuthor Commented:
File attached
RecentReportResult.doc
0
 
BelizeMelanieAuthor Commented:
Have solved this i think by using two tables in the report.
0
 
itcoupleCommented:
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
 
BelizeMelanieAuthor Commented:

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
 
itcoupleCommented:
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
 
itcoupleCommented:
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
 
BelizeMelanieAuthor Commented:
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
 
itcoupleCommented:
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
 
mlmccCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now