Excel Integration with SSRS...Is this possible?
in [Reports]
|
From: OriginalStealth on 1 Feb 2010 08:41 REPORT A runs. MyFile is an Excel file that contains two tabs(Data and Names) The records in REPORT A correspond to the DATA tab in MyFile. Is there a way to export REPORT A and have it replace the information in the DATA tab and not overwrite the entire file? If so, a code example would be ideal but if you have a link that would be great also. Thanks in advance OS
From: Bruce L-C [MVP] on 1 Feb 2010 10:29 If I am understanding you correctly you would like to pull data from a report into Excel versus push (I.e. export) data to an excel file. Excel supports web queries. You can easily write a macro in excel that deletes the data in the tab you are interested in and then runs the report and pulls the data in. Depending on the parameters you want to pass it can get a little for complex on the excel side. The below is from a chapter I contributed to the book (whose proceeds go to charity) SQL Server MVP Deep Dives www.SQLServerMVPDeepDives.com If you are not familiar with URL access use Books online to see how you create a URL to execute a report. Also, if all you want to do is get a feel for it, try the web query wizard that comes with Excel. You can go to a web page (such as a report) and pick what you want. However, this will end up timing out eventually which is why in the end you need to do something like the code below. I cover this more in depth in the book. Sub CreateWebQuery() Dim strURL As String Dim strFromDate As String Dim strToDate As String Dim strT As String Dim oQuery As QueryTable Dim i As Integer Dim strSheetname As String Listing 3 Script to change URL to include parameters Listing 4 Macro to add date parameters and refresh the query Listing 5 CSV alternative to web query wizards 8 CHAPTER 52 Reporting Services tips and tricks strSheetname = "Test" Sheets(strSheetname).Select For i = 1 To ActiveSheet.QueryTables.Count ActiveSheet.QueryTables(1).Delete Next i strURL = ➥ "URL;http://Servername/ReportServer/Pages/ReportViewer.aspx?/Foldername/ ➥ Reportname&FROMDATE=[""FROMDATE""]&TODATE=[""TODATE""]&rs:Format=CSV" 'This code assumes a sheet called DateRange that has the two necessary ➥ dates strFromDate = Sheets("DateRange").Range("FromDate") strToDate = Sheets("DateRange").Range("ToDate") Set oQuery = ActiveSheet.QueryTables.Add(Destination:=Range("A1"), ➥ Connection:="URL;") With oQuery ..Connection = strURL ..Name = strSheetname 'Naming webquery the same as sheet ..EnableRefresh = False ..FieldNames = True ..RowNumbers = False ..FillAdjacentFormulas = True ..PreserveFormatting = True ..RefreshOnFileOpen = False ..BackgroundQuery = False ..RefreshStyle = xlOverwriteCells ..SaveData = False ..AdjustColumnWidth = False ..RefreshPeriod = 0 ..Refresh BackgroundQuery:=False ..Parameters.Item(1).SetParam xlConstant, strFromDate ..Parameters.Item(2).SetParam xlConstant, strToDate ..EnableRefresh = True ..Refresh End With ActiveSheet.Columns("A:A").TextToColumns Destination:=Range("A1"), ➥ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False ActiveSheet.Columns("A:A").EntireColumn.AutoFit End Sub -- Bruce Loehle-Conger MVP SQL Server Reporting Services "OriginalStealth" <OriginalStealth(a)discussions.microsoft.com> wrote in message news:D955BF49-9BA1-4E93-AE4E-761B1A85A34D(a)microsoft.com... > REPORT A runs. > MyFile is an Excel file that contains two tabs(Data and Names) > The records in REPORT A correspond to the DATA tab in MyFile. > Is there a way to export REPORT A and have it replace the information in > the > DATA tab and not overwrite the entire file? If so, a code example would > be > ideal but if you have a link that would be great also. > > Thanks in advance > > OS >
From: TheOriginalStealth on 1 Feb 2010 14:05 This is close to what I need as it would be my second choice if need be. I think I remember writing some VBA once in MS Access that would allow me to export data to an excel spreadsheet without overwriting the file. I could just overwrite the tab that was being altered. I was thinking since SSRS allows for custom code the same could be accomplished with VB.Net. So the basic scenario would be: 1.user goes goes to the report fills out parameters 2. Selects export to excel(maybe even fills out a file location parameter) 3. The custom code reads the location and exports the data to the proper worksheet tab in the .xls file. and does not overwrite the file. My user has 15 tabs in MyFile.xls (1 data tabs and 14 chart tabs) All the charts read from the data tab so if the file is overwritten it would not be a nice thing. But this way the code would be centrally located and future users could get it seamlessly. "Bruce L-C [MVP]" wrote: > If I am understanding you correctly you would like to pull data from a > report into Excel versus push (I.e. export) data to an excel file. > > Excel supports web queries. You can easily write a macro in excel that > deletes the data in the tab you are interested in and then runs the report > and pulls the data in. Depending on the parameters you want to pass it can > get a little for complex on the excel side. > > The below is from a chapter I contributed to the book (whose proceeds go to > charity) SQL Server MVP Deep Dives > www.SQLServerMVPDeepDives.com > > If you are not familiar with URL access use Books online to see how you > create a URL to execute a report. Also, if all you want to do is get a feel > for it, try the web query wizard that comes with Excel. You can go to a web > page (such as a report) and pick what you want. However, this will end up > timing out eventually which is why in the end you need to do something like > the code below. I cover this more in depth in the book. > > > Sub CreateWebQuery() > Dim strURL As String > Dim strFromDate As String > Dim strToDate As String > Dim strT As String > Dim oQuery As QueryTable > Dim i As Integer > Dim strSheetname As String > Listing 3 Script to change URL to include parameters > Listing 4 Macro to add date parameters and refresh the query > Listing 5 CSV alternative to web query wizards > 8 CHAPTER 52 Reporting Services tips and tricks > strSheetname = "Test" > Sheets(strSheetname).Select > For i = 1 To ActiveSheet.QueryTables.Count > ActiveSheet.QueryTables(1).Delete > Next i > strURL = > ➥ "URL;http://Servername/ReportServer/Pages/ReportViewer.aspx?/Foldername/ > ➥ Reportname&FROMDATE=[""FROMDATE""]&TODATE=[""TODATE""]&rs:Format=CSV" > 'This code assumes a sheet called DateRange that has the two necessary > ➥ dates > strFromDate = Sheets("DateRange").Range("FromDate") > strToDate = Sheets("DateRange").Range("ToDate") > Set oQuery = ActiveSheet.QueryTables.Add(Destination:=Range("A1"), > ➥ Connection:="URL;") > With oQuery > ..Connection = strURL > ..Name = strSheetname 'Naming webquery the same as sheet > ..EnableRefresh = False > ..FieldNames = True > ..RowNumbers = False > ..FillAdjacentFormulas = True > ..PreserveFormatting = True > ..RefreshOnFileOpen = False > ..BackgroundQuery = False > ..RefreshStyle = xlOverwriteCells > ..SaveData = False > ..AdjustColumnWidth = False > ..RefreshPeriod = 0 > ..Refresh BackgroundQuery:=False > ..Parameters.Item(1).SetParam xlConstant, strFromDate > ..Parameters.Item(2).SetParam xlConstant, strToDate > ..EnableRefresh = True > ..Refresh > End With > ActiveSheet.Columns("A:A").TextToColumns Destination:=Range("A1"), > ➥ DataType:=xlDelimited, _ > TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ > Semicolon:=False, Comma:=True, Space:=False, Other:=False > ActiveSheet.Columns("A:A").EntireColumn.AutoFit > End Sub > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "OriginalStealth" <OriginalStealth(a)discussions.microsoft.com> wrote in > message news:D955BF49-9BA1-4E93-AE4E-761B1A85A34D(a)microsoft.com... > > REPORT A runs. > > MyFile is an Excel file that contains two tabs(Data and Names) > > The records in REPORT A correspond to the DATA tab in MyFile. > > Is there a way to export REPORT A and have it replace the information in > > the > > DATA tab and not overwrite the entire file? If so, a code example would > > be > > ideal but if you have a link that would be great also. > > > > Thanks in advance > > > > OS > > > . >
From: Andrew Karcher [SQL] on 1 Feb 2010 14:38 I have not written on myself, but I suppose you could always write your own Custom Renderer. Then you would have complete control over what is happening. MSDN Link: http://msdn.microsoft.com/en-us/library/ms153624.aspx I have not played with this before, but third-party tool could be a potential solution as well. http://www.aspose.com/categories/ssrs-rendering-extensions/aspose.cells-for-reporting-services/default.aspx In SSRS 2008 R2 this is a lot easier, because you could use PowerPivot to bring in the Report Data as an Atom Feed into Excel. Andrew On Mon, 1 Feb 2010 11:05:01 -0800, TheOriginalStealth <TheOriginalStealth(a)discussions.microsoft.com> wrote: >This is close to what I need as it would be my second choice if need be. I >think I remember writing some VBA once in MS Access that would allow me to >export data to an excel spreadsheet without overwriting the file. I could >just overwrite the tab that was being altered. I was thinking since SSRS >allows for custom code the same could be accomplished with VB.Net. So the >basic scenario would be: >1.user goes goes to the report fills out parameters >2. Selects export to excel(maybe even fills out a file location parameter) >3. The custom code reads the location and exports the data to the proper >worksheet tab in the .xls file. and does not overwrite the file. > >My user has 15 tabs in MyFile.xls (1 data tabs and 14 chart tabs) All the >charts read from the data tab so if the file is overwritten it would not be a >nice thing. But this way the code would be centrally located and future >users could get it seamlessly. > > >"Bruce L-C [MVP]" wrote: > >> If I am understanding you correctly you would like to pull data from a >> report into Excel versus push (I.e. export) data to an excel file. >> >> Excel supports web queries. You can easily write a macro in excel that >> deletes the data in the tab you are interested in and then runs the report >> and pulls the data in. Depending on the parameters you want to pass it can >> get a little for complex on the excel side. >> >> The below is from a chapter I contributed to the book (whose proceeds go to >> charity) SQL Server MVP Deep Dives >> www.SQLServerMVPDeepDives.com >> >> If you are not familiar with URL access use Books online to see how you >> create a URL to execute a report. Also, if all you want to do is get a feel >> for it, try the web query wizard that comes with Excel. You can go to a web >> page (such as a report) and pick what you want. However, this will end up >> timing out eventually which is why in the end you need to do something like >> the code below. I cover this more in depth in the book. >> >> >> Sub CreateWebQuery() >> Dim strURL As String >> Dim strFromDate As String >> Dim strToDate As String >> Dim strT As String >> Dim oQuery As QueryTable >> Dim i As Integer >> Dim strSheetname As String >> Listing 3 Script to change URL to include parameters >> Listing 4 Macro to add date parameters and refresh the query >> Listing 5 CSV alternative to web query wizards >> 8 CHAPTER 52 Reporting Services tips and tricks >> strSheetname = "Test" >> Sheets(strSheetname).Select >> For i = 1 To ActiveSheet.QueryTables.Count >> ActiveSheet.QueryTables(1).Delete >> Next i >> strURL = >> ? "URL;http://Servername/ReportServer/Pages/ReportViewer.aspx?/Foldername/ >> ? Reportname&FROMDATE=[""FROMDATE""]&TODATE=[""TODATE""]&rs:Format=CSV" >> 'This code assumes a sheet called DateRange that has the two necessary >> ? dates >> strFromDate = Sheets("DateRange").Range("FromDate") >> strToDate = Sheets("DateRange").Range("ToDate") >> Set oQuery = ActiveSheet.QueryTables.Add(Destination:=Range("A1"), >> ? Connection:="URL;") >> With oQuery >> ..Connection = strURL >> ..Name = strSheetname 'Naming webquery the same as sheet >> ..EnableRefresh = False >> ..FieldNames = True >> ..RowNumbers = False >> ..FillAdjacentFormulas = True >> ..PreserveFormatting = True >> ..RefreshOnFileOpen = False >> ..BackgroundQuery = False >> ..RefreshStyle = xlOverwriteCells >> ..SaveData = False >> ..AdjustColumnWidth = False >> ..RefreshPeriod = 0 >> ..Refresh BackgroundQuery:=False >> ..Parameters.Item(1).SetParam xlConstant, strFromDate >> ..Parameters.Item(2).SetParam xlConstant, strToDate >> ..EnableRefresh = True >> ..Refresh >> End With >> ActiveSheet.Columns("A:A").TextToColumns Destination:=Range("A1"), >> ? DataType:=xlDelimited, _ >> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ >> Semicolon:=False, Comma:=True, Space:=False, Other:=False >> ActiveSheet.Columns("A:A").EntireColumn.AutoFit >> End Sub >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "OriginalStealth" <OriginalStealth(a)discussions.microsoft.com> wrote in >> message news:D955BF49-9BA1-4E93-AE4E-761B1A85A34D(a)microsoft.com... >> > REPORT A runs. >> > MyFile is an Excel file that contains two tabs(Data and Names) >> > The records in REPORT A correspond to the DATA tab in MyFile. >> > Is there a way to export REPORT A and have it replace the information in >> > the >> > DATA tab and not overwrite the entire file? If so, a code example would >> > be >> > ideal but if you have a link that would be great also. >> > >> > Thanks in advance >> > >> > OS >> > >> . >>
From: Ralph-MN on 1 Feb 2010 15:31
"Andrew Karcher [SQL]" wrote: > I have not written on myself, but I suppose you could always write > your own Custom Renderer. Then you would have complete control over > what is happening. > > MSDN Link: http://msdn.microsoft.com/en-us/library/ms153624.aspx > > I have not played with this before, but third-party tool could be a > potential solution as well. > > http://www.aspose.com/categories/ssrs-rendering-extensions/aspose.cells-for-reporting-services/default.aspx > > In SSRS 2008 R2 this is a lot easier, because you could use PowerPivot > to bring in the Report Data as an Atom Feed into Excel. > > Andrew > > On Mon, 1 Feb 2010 11:05:01 -0800, TheOriginalStealth > <TheOriginalStealth(a)discussions.microsoft.com> wrote: > > >This is close to what I need as it would be my second choice if need be. I > >think I remember writing some VBA once in MS Access that would allow me to > >export data to an excel spreadsheet without overwriting the file. I could > >just overwrite the tab that was being altered. I was thinking since SSRS > >allows for custom code the same could be accomplished with VB.Net. So the > >basic scenario would be: > >1.user goes goes to the report fills out parameters > >2. Selects export to excel(maybe even fills out a file location parameter) > >3. The custom code reads the location and exports the data to the proper > >worksheet tab in the .xls file. and does not overwrite the file. > > > >My user has 15 tabs in MyFile.xls (1 data tabs and 14 chart tabs) All the > >charts read from the data tab so if the file is overwritten it would not be a > >nice thing. But this way the code would be centrally located and future > >users could get it seamlessly. > > > > > >"Bruce L-C [MVP]" wrote: > > > >> If I am understanding you correctly you would like to pull data from a > >> report into Excel versus push (I.e. export) data to an excel file. > >> > >> Excel supports web queries. You can easily write a macro in excel that > >> deletes the data in the tab you are interested in and then runs the report > >> and pulls the data in. Depending on the parameters you want to pass it can > >> get a little for complex on the excel side. > >> > >> The below is from a chapter I contributed to the book (whose proceeds go to > >> charity) SQL Server MVP Deep Dives > >> www.SQLServerMVPDeepDives.com > >> > >> If you are not familiar with URL access use Books online to see how you > >> create a URL to execute a report. Also, if all you want to do is get a feel > >> for it, try the web query wizard that comes with Excel. You can go to a web > >> page (such as a report) and pick what you want. However, this will end up > >> timing out eventually which is why in the end you need to do something like > >> the code below. I cover this more in depth in the book. > >> > >> > >> Sub CreateWebQuery() > >> Dim strURL As String > >> Dim strFromDate As String > >> Dim strToDate As String > >> Dim strT As String > >> Dim oQuery As QueryTable > >> Dim i As Integer > >> Dim strSheetname As String > >> Listing 3 Script to change URL to include parameters > >> Listing 4 Macro to add date parameters and refresh the query > >> Listing 5 CSV alternative to web query wizards > >> 8 CHAPTER 52 Reporting Services tips and tricks > >> strSheetname = "Test" > >> Sheets(strSheetname).Select > >> For i = 1 To ActiveSheet.QueryTables.Count > >> ActiveSheet.QueryTables(1).Delete > >> Next i > >> strURL = > >> ? "URL;http://Servername/ReportServer/Pages/ReportViewer.aspx?/Foldername/ > >> ? Reportname&FROMDATE=[""FROMDATE""]&TODATE=[""TODATE""]&rs:Format=CSV" > >> 'This code assumes a sheet called DateRange that has the two necessary > >> ? dates > >> strFromDate = Sheets("DateRange").Range("FromDate") > >> strToDate = Sheets("DateRange").Range("ToDate") > >> Set oQuery = ActiveSheet.QueryTables.Add(Destination:=Range("A1"), > >> ? Connection:="URL;") > >> With oQuery > >> ..Connection = strURL > >> ..Name = strSheetname 'Naming webquery the same as sheet > >> ..EnableRefresh = False > >> ..FieldNames = True > >> ..RowNumbers = False > >> ..FillAdjacentFormulas = True > >> ..PreserveFormatting = True > >> ..RefreshOnFileOpen = False > >> ..BackgroundQuery = False > >> ..RefreshStyle = xlOverwriteCells > >> ..SaveData = False > >> ..AdjustColumnWidth = False > >> ..RefreshPeriod = 0 > >> ..Refresh BackgroundQuery:=False > >> ..Parameters.Item(1).SetParam xlConstant, strFromDate > >> ..Parameters.Item(2).SetParam xlConstant, strToDate > >> ..EnableRefresh = True > >> ..Refresh > >> End With > >> ActiveSheet.Columns("A:A").TextToColumns Destination:=Range("A1"), > >> ? DataType:=xlDelimited, _ > >> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ > >> Semicolon:=False, Comma:=True, Space:=False, Other:=False > >> ActiveSheet.Columns("A:A").EntireColumn.AutoFit > >> End Sub > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "OriginalStealth" <OriginalStealth(a)discussions.microsoft.com> wrote in > >> message news:D955BF49-9BA1-4E93-AE4E-761B1A85A34D(a)microsoft.com... > >> > REPORT A runs. > >> > MyFile is an Excel file that contains two tabs(Data and Names) > >> > The records in REPORT A correspond to the DATA tab in MyFile. > >> > Is there a way to export REPORT A and have it replace the information in > >> > the > >> > DATA tab and not overwrite the entire file? If so, a code example would > >> > be > >> > ideal but if you have a link that would be great also. > >> > > >> > Thanks in advance > >> > > >> > OS > >> > > >> . > >> > . > |