July 16, 2010 at 2:12 am
In reporting Services I am adding a text box to show the sum of closed attendances. In the value I have=Sum(Fields!All_Closed_Attendances_IN_Systalk_Not_On_Census.Value)
So I get a result of, for example 340
In my dimensions I have two fields that allow you to figure out age =DateDiff("yyyy",Fields!Sys_DOB.Value,Fields!Date.Value)
Is there anyway I can create the expression to show for example the total closed attendances for over 16s, under 16s and under 5’s?
So I can see Total 340
Over 16 100
Under 5 40
Between 5 and 16 200
July 16, 2010 at 5:08 am
Hi,
I think the best way to do this is to use the switch statement. You can place the date formula you have in a cell and then refer to that item it will save you time and make your script a bit neater. Your script will be something like
=SWITCH(reportitems!textbox.Value>=16, "Over 16",
reportitems!textbox.Value<=5, "Under 5",
reportitems!textbox.Value>5 and reportitems!textbox.Value<16, "between 5 and 16",
TRUE,"Not one of the above categories")
July 16, 2010 at 6:01 am
Wow, Some of its working.
I have added this to a new field
=SWITCH(reportitems!Ageasat.Value>=16, "Over 16",
reportitems!Ageasat.Value<=5, "Under 5",
reportitems!Ageasat.Value>5 and reportitems!Ageasat.Value<16, "between 5 and 16",
TRUE,"Not one of the above categories")
Which brings up an age group. I then got carried away and added it to the entire row back colour.
=SWITCH(reportitems!Ageasat.Value>=16, "PeachPuff",
reportitems!Ageasat.Value<=5, "Lavender",
reportitems!Ageasat.Value>5 and reportitems!Ageasat.Value<16,"White",
TRUE,"White")
I have now called this textbox a name. AgeGroup. I create a text box at the top of the page.
I want to change this:
=Sum(Fields!All_Closed_Attendances_IN_Systalk_Not_On_Census.Value)
to something like.....
=SUM(IIF(reportitems!Ageasat.Value = "Over 16",1,0))
If its over 16 change to 1 and then count but I get the error message Aggregates can only be used in headers and footers. Hmm I have a SUM() in aniother text box which is fine.
Ooooh I am so almost there, Grrrrr
July 16, 2010 at 6:11 am
Hi
To count you need to create a field for each category and then you should be able to do a simple sum expression.
so for over 16s you will have an expression as follows
=SWITCH(reportitems!Ageasat.Value>=16, 1,
TRUE,0)
name the textbox over16count
this will do the count you can then simply sum this textbox
=sum(reportitems!over16count.value)
😉
July 16, 2010 at 7:03 am
so annoying.
I have created another textbox in my table
=SWITCH(reportitems!Ageasat.Value>=16, 1,TRUE,0)
and called it Over16_1_Closed
I run it and I have a 1 against all my over 16 pupils. I then go to the top of the page and cteate a text box and add =Sum(reportitems!Over16_1_Closed.Value). Again Im told I cant use aggregates unless its a header or footer of a report item.
I would admit defeat but I have above this a textbox with =Sum(Fields!All_Closed_Attendances_IN_Systalk_Not_On_Census.Value) which works fine. How annoying.
Debbie
July 16, 2010 at 7:48 am
How do you want the data to be shown? are you using a table ?
the total fields should be shown in the table footer.
July 16, 2010 at 7:54 am
Thats where its going wrong.
The summary details I just wanted in a text box at the top of the page. not in a table.
I do have a table but I wanted tome big easy to see figures (By Management ahem) at the top
Im having a go at creating a table with all the summary details at the moment. Hopefully then I can hide all the rows of data and just show the Summary but I have 3 columns:
=SWITCH(reportitems!Agegroupings_Open_Att.Value ="Over 16" , 1,TRUE,0)
=SWITCH(reportitems!Agegroupings_Open_Att.Value ="Under 5" , 1,TRUE,0)
=SWITCH(reportitems!Agegroupings_Open_Att.Value = "Between 5 and 16" , 1,
TRUE,0)
the first one works fine. The next 2 only display 0 even when the catagory says Under 5, Between 5 and 16????
This is turning into a right can of worms :ermm:
July 16, 2010 at 8:35 am
I understand the error message you are getting and I don't think we will be able to get round the aggregate issue.
I think it would be probably be easier to create the fields in your dataset, in your sql query
you will be then be able to aggregate these fields in ssrs as they have already been calculated.
you can create a separate field for each group
select *
case when datediff(yyyy,bill_date,getdate()) >=16 then 1 else 0 end as [Over16],
case when datediff(yyyy,bill_date,getdate()) =<5 then 1 else 0 end as [Under5],
case when datediff(yyyy,bill_date,getdate()) >5 and datediff(yyyy,bill_date,getdate()) and 16
then 1 else 0 end as [between 5 and 16],
from mytable
In you report create a new table insert these fields and all the other ones you want to see
in the detail section and then drag the fields you want to agregate into the table footer they should automatically include the sum.
You can then take copy the table and paste it (select the table click on the far top left hand corner cell and just use ctrl c and crtl v) at the top of the report area and remove the data rows for a summary table.
July 19, 2010 at 7:06 am
Still at a loss with the report.
The problem with creating pre the report is that the data is coming from a cube. The DOB is in the child dimension and the date is in the date and time dimension and is specific to the time being answered.
I tried going into analysis services and creating a new named calculation but you can only do this with data in the one dimension.
It looks like I will have to create facts for each age grouping. Ill have a go at that!
Debbie
July 19, 2010 at 7:19 am
Didn't realise you were using a cube, your different group fields will have to come from the dataset ie your cube so you will need to create new facts.
July 20, 2010 at 2:03 am
Create 3 new facts for every question based on the age groups and its all working fine now 🙂
Thanks
July 20, 2010 at 2:05 am
Glad to hear you resolved your query 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply