How do I define hierarchy on OCSV data generated from SQL Server database?

Answered
Tunde asked on June 28, 2018

I am pulling data from an SQL server DB, passing it through the compressor before outputting to the report. It is working fine but the challenge is that I want to group some of the fields as hierarchy. How do I achieve this? Find below a snippet of my code.
 .....
 .....

            cmd.CommandType = CommandType.StoredProcedure;           
            cmd.Parameters.Add(new SqlParameter("@module", module));            
            DbDataReader dataReader = cmd.ExecuteReader();

            // get compressed stream
            Stream inputStream = Flexmonster.Compressor.Compressor.CompressDb(dataReader);

            // return streaming response
            HttpResponseMessage response = Request.CreateResponse();
            response.Content = new PushStreamContent((Stream outputStream, HttpContent content, TransportContext context) =>
            {
                int count = 0;
                byte[] buffer = new byte[1024];
                while ((count = inputStream.Read(buffer, 0, buffer.Length)) > 0)
                {
                    outputStream.Write(buffer, 0, count);
                    outputStream.Flush();
                }
                outputStream.Close();
            }, new MediaTypeHeaderValue("application/ocsv"));
            //}, new MediaTypeHeaderValue("text/plain"));
            return response;
.......
.......

......

2 answers

Public
Dmytro Zvazhii Dmytro Zvazhii Flexmonster July 2, 2018

Hello Tunde,
Thank you for your question. When you need to define hierarchy with levels for OCSV you should use the ':' sign in query alias to set levels of the hierarchy. Please have a look at the following example: 
SELECT category AS ‘Category’, subcategory AS ‘+Category:Subcategory’
FROM table_name;
The hierarchy structure is defined in query alias. The Category field is the first level of the hierarchy and Subcategory is the second level of the hierarchy. Also, we recommend updating the component and the compressor to the latest version. Please let us know if the suggested approach solves the case.
Regards,
Dmytro

Public
Tunde July 11, 2018

Thank you so much. It worked.

Please login or Register to Submit Answer