Zahid Ahamed
asked on
Section based report in SSRS
Hi Expert,
This is my following query but there are 3 condition ebs, non-ebs and open
select
case when i.is_ebs = 1 then 'EBS'
when i.is_ebs <> 1 then 'Non-EBS'
when i.instancename IS NULL then 'OPEN'
when i.is_ebs is null then 'Non-EBS'
end as is_ebs
,o.hostname
,o.appname
, o.processorcount
, o.memorysize
,i.instancename
from oraclehosts o
left join oracleinstances i on o.hostname = i.hostname
left join hostwarranty w on w.hostname = o.hostname
Collate SQL_Latin1_General_CP1_CI_ AS
left join puppetdb_certname_facts p
on p.certname = o.hostname where ipaddress not in ('xx.jj/cc', 'cc.uu/dd') and
p.fact = 'oracle_license' and p.fact = 'oracle_license' AND p.value = 'true'
group by
i.instanceName
, o.hostname
, o.processorcount
, o.memorysize
, i.is_ebs
,o.appname
Query Output:--
is_ebs Hostname appname Processorcount memory instancename
OPEN xyz ddd 4 15.42 GB NULL
OPEN xxx ccc 48 125.64 GB NULL
EBS yzx eee 10 251.57 GB DEV
EBS crs rrr 10 251.86 GB xyx
NB. I want create three section report and each section want to add processor count
is_ebs Hostname appname Processorcount memory instancename
OPEN xyz ddd 4 15.42 GB NULL
OPEN xxx ccc 48 125.64 GB NULL
=Total Core count xyz
is_ebs Hostname appname Processorcount memory instancename
EBS yzx eee 10 251.57 GB DEV
=Total Core count xyz
is_ebs Hostname appname Processorcount memory instancename
NON-EBS crs rrr 10 251.86 GB xyx
=Total Core count xyz
Please help me!
This is my following query but there are 3 condition ebs, non-ebs and open
select
case when i.is_ebs = 1 then 'EBS'
when i.is_ebs <> 1 then 'Non-EBS'
when i.instancename IS NULL then 'OPEN'
when i.is_ebs is null then 'Non-EBS'
end as is_ebs
,o.hostname
,o.appname
, o.processorcount
, o.memorysize
,i.instancename
from oraclehosts o
left join oracleinstances i on o.hostname = i.hostname
left join hostwarranty w on w.hostname = o.hostname
Collate SQL_Latin1_General_CP1_CI_
left join puppetdb_certname_facts p
on p.certname = o.hostname where ipaddress not in ('xx.jj/cc', 'cc.uu/dd') and
p.fact = 'oracle_license' and p.fact = 'oracle_license' AND p.value = 'true'
group by
i.instanceName
, o.hostname
, o.processorcount
, o.memorysize
, i.is_ebs
,o.appname
Query Output:--
is_ebs Hostname appname Processorcount memory instancename
OPEN xyz ddd 4 15.42 GB NULL
OPEN xxx ccc 48 125.64 GB NULL
EBS yzx eee 10 251.57 GB DEV
EBS crs rrr 10 251.86 GB xyx
NB. I want create three section report and each section want to add processor count
is_ebs Hostname appname Processorcount memory instancename
OPEN xyz ddd 4 15.42 GB NULL
OPEN xxx ccc 48 125.64 GB NULL
=Total Core count xyz
is_ebs Hostname appname Processorcount memory instancename
EBS yzx eee 10 251.57 GB DEV
=Total Core count xyz
is_ebs Hostname appname Processorcount memory instancename
NON-EBS crs rrr 10 251.86 GB xyx
=Total Core count xyz
Please help me!
If you found this helpful, please accept as a solution.
Thanks
Arif
Thanks
Arif
ASKER
I was trying to run but The report definition has invalid namespace "http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" which cannot be upgraded
Which version of SSDT you have ?
ASKER
I have created this report as stepped down using group by. But my boss asked me to do section based that would be helpful for him
ASKER
2012 SSDT
wait let me attache rdl with that version
ASKER
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent! It is working. But do i have to create using report wizard or do i have to create table separately?
I have never used report wizard. I normally create separately.
ASKER
If i go through creating report then it got created stepped down report.
ASKER
Ok, trying this.
ASKER
It worked for me. Thanks for your help! appreciated.
Zahid
Zahid
So it will group the detail and also have the separate total.
I have attached the rdl file. Please check.
Separate-Section.rdl