using t-sql, is it possible to replicate a report writer like Crystal which suppress repeating column values.

Hello,

Is there a way to suppress repeating data in tsql;   I used to work in Crystal and you would just suppress the field by simply checking it off.

Now we are converting reports to BIPublisher reports using the plugin not the full blown version(which you can suppress repeating columns) and you require the use of Xpath or variables.  

I believe if I could do it from the backend batch program using a sql script and then export to xml file.  I don't have to make the display portion - Bipublisher contain a ton of if statements.


Attached is the report


So in the attached report the course PRAC 1216 has 2 rows but all the columns on the second row repeat and we want them to suppress.


The table behind the scene is a view with the following fields:
SELECT [CRSE_ATTR_VALUE]
      ,[CAMPUS]
      ,[SUBJECT]
      ,[CRSE_ID]
      ,[CATALOG_NBR]
      ,[CLASS_SECTION]
      ,[DESCR]
      ,[SESSION_CODE]
      ,[ENRL_STAT]
      ,[CLASS_STAT]
      ,[ENRL_CAP]
      ,[ENRL_TOT]
      ,[ACAD_PLAN]
      ,[ACAD_LEVEL_BOT]
      ,[STDNT_TOT_ASSGNED]
      ,[UNITS_MAXIMUM]
      ,[NAME]
      ,[COURSE_DESCR]
      ,[AVAILABLE_SEATS]
  FROM [dbo].[PS_NCSR6111_TMP2]


The  3 fields    ,[ACAD_PLAN]
                         ,[ACAD_LEVEL_BOT]
                         ,[STDNT_TOT_ASSGNED]

can repeat.  

The report is grouped by CRSE_ATTR_VALUE, course_descr, class_section.


The question here is 1. Can you do the suppression of the columns?

Thanks, in advance Brock.
Report-will-look-like.jpg
BrockAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
This should be possible but you'll need to provide more info about your data.
Could I expect the values which are blank in your JPG attachment are constant for the given Course?
0
BrockAuthor Commented:
Hi
pcelba,

The values which are blank are exactly the same values as the row previous within the Course.

Does this answer your question ?
0
pcelbaCommented:
Yes, so you may try the following query:
;WITH cte AS (
    SELECT 
       [CRSE_ATTR_VALUE]
      ,[CAMPUS]
      ,[SUBJECT]
      ,[CRSE_ID]
      ,[CATALOG_NBR]
      ,[CLASS_SECTION]
      ,[DESCR]
      ,[SESSION_CODE]
      ,[ENRL_STAT]
      ,[CLASS_STAT]
      ,[ENRL_CAP]
      ,[ENRL_TOT]
      ,[ACAD_PLAN]
      ,[ACAD_LEVEL_BOT]
      ,[STDNT_TOT_ASSGNED]
      ,[UNITS_MAXIMUM]
      ,[NAME]
      ,[COURSE_DESCR]
      ,[AVAILABLE_SEATS]
      ,ROW_NUMBER() OVER (PARTITION BY [CRSE_ID] ORDER BY [CATALOG_NBR]) rn
  FROM [dbo].[PS_NCSR6111_TMP2]
  )
 SELECT * FROM cte 
  WHERE rn = 1
 UNION ALL
 SELECT null, null, null, null, null, null, null, null, null
         ,[ACAD_PLAN]
         ,[ACAD_LEVEL_BOT]
         ,[STDNT_TOT_ASSGNED]
         ,null
         ,[NAME]
         , rn
   FROM cte
  WHERE rn > 1
  ORDER BY [CATALOG_NBR], rn

Open in new window

The result should contain NULL in places of empty values. It should work in SQL Server but it can contain errors and some tuning is still necessary because I don't know exact meaning of your columns.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

pcelbaCommented:
As you may see from the above query rows having rn=1 are complete on output whereas the rest should have repeated values replaced by NULLs.
0
BrockAuthor Commented:
Hi
pcelba,

I have created the script you have given me.  I am going to incorporate in the batch program.  And I will then be in the testing phase.

I will let you know when I compare the result to the Crystal version.

Thanks, so much.  

Brock
0
BrockAuthor Commented:
Thanks!

Brock
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.