question

satishreddy-3196 avatar image
0 Votes"
satishreddy-3196 asked Zoehui-MSFT answered

How to export one tablix in excel and other tablix to .dat file from single rdl in ssrs?

As per requirement i have created a report which has 2 Tablix's both have different data. Now i have to export tablix1 data to Excel(tablix2 should not come in excel) Tablix2 to .dat file/text file(tablix1 data should not come). Right now if i export to .dat file data from tablix1 also coming. Note:i have updated report server config to generate .dat file Please suggest how we can do this in ssrs.

sql-server-reporting-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Zoehui-MSFT , i tried by keeping the expressions you mentioned . observed that no data is displaying in report server however if i export to excel i can see the data in excel..

can you please let me know possible solution where it should display the result in report server and also in export as per above requirement.

0 Votes 0 ·
Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered satishreddy-3196 commented

Hi @satishreddy-3196,

You may set the tablix1's hidden property with expression:

=IIF(Globals!RenderFormat.Name = "EXCELOPENXML", False,True)

Set the tablix2's hidden property with expression:

=IIF(Globals!RenderFormat.Name = "TXT", False,True)

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @Zoehui-MSFT , let me try this...

0 Votes 0 ·
RusselLoski-0634 avatar image
0 Votes"
RusselLoski-0634 answered satishreddy-3196 commented

A modification of @Zoehui-MSFT answer:

 Set the tablix2's hidden property with expression:
    
 =IIF(Globals!RenderFormat.Name = "EXCELOPENXML", True,False)
    
 Set the tablix1's hidden property with expression:
    
 =IIF(Globals!RenderFormat.Name = "TXT", True,False)

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @RusselLoski-0634. I have tried above and now am able to see data in report server and Excel export is working fine but when i do txt/.dat file export am getting tablix1 data also along with tablix2.

Please check below custom code used for dat file creation and expression used in tablix1 and let me know if am missing any thing here.
expression used in tablix1 :
=IIF(Globals!RenderFormat.Name = "TabDelimited", True,False)



0 Votes 0 ·

custom code for fixed width dat file added in report server config file
<Extension Name="TabDelimited" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
<OverrideNames><Name Language="en-US">Tab Delimited</Name>
</OverrideNames>
<Configuration>
<DeviceInfo>FieldDelimiter>&#9;</FieldDelimiter>
<Useformattedvalues>False</Useformattedvalues>
<NoHeader>True</NoHeader>
<FileExtension>dat</FileExtension>
<RecordDelimiter>&#013;&#010;</RecordDelimiter>
<Qualifier>"</Qualifier>
</DeviceInfo>
</Configuration>
</Extension>

0 Votes 0 ·
Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered satishreddy-3196 commented

Hi @satishreddy-3196,

Thanks for your reply, just change the expression to meet your needs as you commented.

You may set the tablix1's hidden property with expression:

 =Globals!RenderFormat.Name = "TXT"

Set the tablix2's hidden property with expression:

 =Globals!RenderFormat.Name = "EXCELOPENXML"

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Zoe, i am getting the same issue as mentioned above...excel working fine but in .dat file export (tablix2) am getting tablx1 data also.plz refer custome code for .dat file and let me know if you have any solution

0 Votes 0 ·
Zoehui-MSFT avatar image Zoehui-MSFT satishreddy-3196 ·

Hi @satishreddy-3196,

The provided custom code has some garbled characters so I couldn't use it directly.

Please double check your .dat file name.

Mine is TXT (Pipe Delimited Text File) and then set the expression like

 =Globals!RenderFormat.Name = "TXT (Pipe Delimited Text File)"

It works well when exporting.

80893-screenshot-2021-03-24-101214.jpg


0 Votes 0 ·

Can you please send me the custom code you used for pipe delimited text flle?

0 Votes 0 ·
Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered

Hi @satishreddy-3196,

Please refer below:

  <Extension Name="CSVTXT" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
         <OverrideNames>
           <Name Language="en-US">TXT (Pipe Delimited Text File)</Name>
         </OverrideNames>
         <Configuration>
           <DeviceInfo>
             <FileExtension>TXT</FileExtension>
             <NoHeader>true</NoHeader>
           </DeviceInfo>
         </Configuration>
       </Extension>

or

 <Extension Name="PIPE" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
                <OverrideNames>
                      <Name Language="en-US">TXT (Pipe delimited)</Name>
                </OverrideNames>
                <Configuration>
                  <DeviceInfo>
                      <FieldDelimiter>|</FieldDelimiter>
                      <FileExtension>txt</FileExtension>
                  </DeviceInfo>
                </Configuration>
 </Extension>

Don't forget to back up your config file and restart SSRS.

Regards,
Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

What can I do if my transaction log is full?--- Hot issues November

How to convert Profiler trace into a SQL Server table -- Hot issues November

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.