Link to home
Start Free TrialLog in
Avatar of Zahid Ahamed
Zahid AhamedFlag for United States of America

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!
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

You can group the data on "is_ebs" column in SSRS report and you can also put Total In that group too.

So it will group the detail and also have the separate total.

I have attached the rdl file. Please check.
Separate-Section.rdl
If you found this helpful, please accept as a solution.

Thanks
Arif
Avatar of Zahid Ahamed

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 ?
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
2012 SSDT
wait let me attache rdl with that version
Thanks
ASKER CERTIFIED SOLUTION
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
If i go through creating report then it got created stepped down report.
Ok, trying this.
It worked for me. Thanks for your help! appreciated.

Zahid