Skip to main content

How to split(segment) report into multiple reports?

Answered
Venkata Ravi Kumar Mullamuri

Hello Everyone,

I have a requirement where i need to split one single report into multiple reports(of same number of rows).

Ex: I have a report with 1million records and i need to split into 10 segments with 1lakh records each.

Can anyone help me to achieve above requirement?

I know about the segment option in report properties, however it splits based on the column we choose and will generate different record count across different report segments.

 

1

Comments

17 comments

Date Votes
  • Best answer
    Joseph Flesche

    Guillaume Brochet,

    Now I see what Venkata Ravi Kumar Mullamuri is asking.

    Try this as a field in your report; NTILE(10) OVER (ORDER BY RowID) AS Segment.

    Sample SQL for reference.

    IF OBJECT_ID(N'tempdb..#t') IS NOT NULL DROP TABLE #t
    CREATE TABLE #t ([RowID] [int] IDENTITY(1,1) NOT NULL, uid UNIQUEIDENTIFIER NOT NULL)

    INSERT INTO #t (uid)
    SELECT TOP 1234 NewID()
    FROM sys.objects

    SELECT *, NTILE(10) OVER (ORDER BY RowID) AS Segment
    FROM #t

    IF OBJECT_ID(N'tempdb..#t') IS NOT NULL DROP TABLE #t

    2
  • Joseph Flesche

    Will the 10 segments contain the same report / field contents?

    Also is 1lakh = 1 million?

    0
  • Guillaume Brochet

    Hi Venkata Ravi Kumar Mullamuri,

    Do you have the opportunity to add a field in you report, where you coud implement a value based on COUNT and ROW_NUMBER that would output '1' for first 10% record then '2' for second 10%, '3' for third 10 % etc ... then use segment options over that field ?

    br

    0
  • Venkata Ravi Kumar Mullamuri

    Thank you Guillaume Brochet & Joseph Flesche for your responses.

    I followed as suggessted by Joseph Flesche and i worked for me.

    Thanks once again for your response.

    Have a great day!!

    0
  • Venkata Ravi Kumar Mullamuri

    Hi Guillaume Brochet & Joseph Flesche,

    I got few more queries on this topic, could you please help me with below queries,

    1. By default segment column is visible in the report, is there any way we can disable Segment column in       the  generated report?

    2. Is there any way we can download all the segmented reports at once instead of downloading one by one?

    We had to remove segment column manually in the generated reports and also we are downloading one segment at a time, which is time consuming.

    Any suggestion's here will save my time.

    Thanks & Regards

    Ravi

     

    0
  • Joseph Flesche

    Venkata Ravi Kumar Mullamuri,

    1. I am not aware of being able to hide a column for a report created in dspMigrate. You can do this with reports / validation in a custom WebApp page (but then again, you cannot segment here).
    2. I am also not aware of being able to download all of the reports. What is the process that you have identified to download the reports? Are end users doing it or is there an administrator that is doing this?
    0
  • Venkata Ravi Kumar Mullamuri

    Hi Joseph Flesche,

    I am downloading all the segmented reports one by one, which is consuming time. So thought of checking whether is there any option to download all segmented reports with a single click, instead of downloading one by one.

    Thanks & Regards

    Ravi

    0
  • Joseph Flesche

    Venkata Ravi Kumar Mullamuri,

    If you are an administrator, I would suggest downloading the files directly from the server through Windows Explorer. If you wanted to automate it, you could probably even create a BAT file to download the files (maybe even zip the files if needed).

    And if you are going through the automation process, you could even write a VBA script to open the file and delete the segmented column.

    0
  • Venkata Ravi Kumar Mullamuri

    Hi Joseph Flesche,

    Yes i have DSP administrative privilege's, can you please let me know the steps/process how to download it from server.

    Thanks & Regards

    Ravi

    0
  • Joseph Flesche

    I would suggest finding the file location and then open the path in Windows Explorer. From there you can download the reports.

    See the following SQL to find the file location.

    SELECT SegmentByField, FileLocation, WaveProcessAreaObjectTargetReportID FROM DSW.dbo.ttWaveProcessareaObjectTargetReportSegment

    0
  • Venkata Ravi Kumar Mullamuri

    Thank you Joseph!!

    FIles are saved under following directory D:\adm\Transform\Report, however when i try to find it in windows explorer, i am getting following error,

    Do we have to connect to any VPN or something to be able to access this directory?

    Thanks & Regards

    Ravi

    0
  • Joseph Flesche

    If there is a letter designation for the drive, then it's a mapped network drive. You will need to identify the network drive location and then navigate to that folder; \\NetworkDrive\adm\Transform\Report.

    0
  • Ben Bauer

    Hi Ravi,

    That path is on the SST/DSP application server.  You'll need to run the Remote Desktop Connection program on your machine and then connect to the application server.  You will need credentials that have access to the server.  The name of the application server should match the first part of the URL you use to access the SST/DSP webapp.

    0
  • Venkata Ravi Kumar Mullamuri

    Thank you Joseph.

    Hi Ben,

    Can we get credentials for remote desktop, if so whom we need to request?

    Thanks & Regards

    Ravi

    0
  • Ben Bauer

    Hi Ravi,

    My records indicate that your company's SST/DSP application server is hosted by your company (not by Syniti).  This means you'll need to reach out to your company's IT to inquire about the SST/DSP application server and credentials to access it via remote desktop (or shared network path, etc).

    0
  • Venkata Ravi Kumar Mullamuri

    Hi Ben,

    Currently we don't have anyone in our company who maintains these servers and i would like to take this up and maintain these servers. Could please let me know the process like whom i need to reach for initial set up/details.

    Thanks & Regards

    Ravi

    0
  • Ben Bauer
    • Edited

    Hi Ravi,

    Please submit a support ticket so we can get your server access sorted out without sharing any sensitive details on an open forum.  In the ticket, include the name of the application server and/or the URL you use to reach the SST/DSP application.

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post