Hi All,
I found the below link useful in creating the drill down reports in SSRS
http://www.youtube.com/watch?v=W6gk9VqnGMs
I found the below link useful in creating the drill down reports in SSRS
http://www.youtube.com/watch?v=W6gk9VqnGMs
Friday, 28 October 2011
Thursday, 27 October 2011
Macro to upload excel file to sharepoint
Hi All,
Okay only 30 minutes to log out..within that time was able to come up with a macro which will save an excel sheet to the sharepoint folder. Find below the macro
ActiveWorkbook.SaveAs Filename:= _
"http://xxxxxxxxxxx.com/teams/analytical/Shared Documents/Test/Sharepoint1.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If you want the excel sheet to be saved with the sheet name then below is the macro for it
Sub savesheet()
Dim FSN As String
FSN = ActiveSheet.Name & ".xlsm"
ActiveWorkbook.SaveAs Filename:= _
"\\texxxxxxxxxxxxxxxxx\teams\analytical\Shared Documents\Test" & FSN _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
Okay only 30 minutes to log out..within that time was able to come up with a macro which will save an excel sheet to the sharepoint folder. Find below the macro
ActiveWorkbook.SaveAs Filename:= _
"http://xxxxxxxxxxx.com/teams/analytical/Shared Documents/Test/Sharepoint1.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If you want the excel sheet to be saved with the sheet name then below is the macro for it
Sub savesheet()
Dim FSN As String
FSN = ActiveSheet.Name & ".xlsm"
ActiveWorkbook.SaveAs Filename:= _
"\\texxxxxxxxxxxxxxxxx\teams\analytical\Shared Documents\Test" & FSN _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
Tuesday, 25 October 2011
Error "A connection was successfully established with the server, but then an error occurred during the pre-login handshake" occurs when connecting to remote SQL Server databases on SQL Server 2005 or 2008
Hi All,
Today I encountered the below error when I tried to log on to SQL management studio database engine.
"Error "A connection was successfully established with the server, but then an error occurred during the pre-login handshake" occurs when connecting to remote SQL Server databases on SQL Server 2005 or 2008"
This issue was easily resolved when I enabled the Named Pipe in the SQL configuration manager and then restarted the SQL services
Today I encountered the below error when I tried to log on to SQL management studio database engine.
"Error "A connection was successfully established with the server, but then an error occurred during the pre-login handshake" occurs when connecting to remote SQL Server databases on SQL Server 2005 or 2008"
This issue was easily resolved when I enabled the Named Pipe in the SQL configuration manager and then restarted the SQL services
Friday, 21 October 2011
Resize SSRS report page
Hi All,
When I deployed my First SSRS report, did not like the look of it. At first the reports came up in many pages and the window size was so small. I changed the size of Interactive size and Page size. Set all these values to 444 (445 should be the max) and bingo! I got all data in 1 page.
However the report manager still pointed out the old data..I realized that I have to upload the file again and them open the frame in new window. Copy this URL to your webpage from which you access the links\
You may also change the size by dragging the cells in the 'Design' mode in visual studio
When I deployed my First SSRS report, did not like the look of it. At first the reports came up in many pages and the window size was so small. I changed the size of Interactive size and Page size. Set all these values to 444 (445 should be the max) and bingo! I got all data in 1 page.
However the report manager still pointed out the old data..I realized that I have to upload the file again and them open the frame in new window. Copy this URL to your webpage from which you access the links\
You may also change the size by dragging the cells in the 'Design' mode in visual studio
An error occurred during client rendering.An error has occurred during report processing.Cannot create a connection to data source 'AW'.Login failed for user '
Hi All,
While configuring report manager I followed the below steps
Add New folder called 'Test'. Add new Data Source. Make sure that you fill the connection string. Upload new file which is your report file with an extension of .rdl.
When I was running this I got the below error message
An error occurred during client rendering.An error has occurred during report processing.Cannot create a connection to data source 'AW'.Login failed for user '
This error message indicates that the credentials applied is wrong..I played with varios options for the credentials and finally 'Windows Integrated Security' did the trick
You may try different credentials to make it work..Just wanted to let you know guys that this error message is related to credentials
While configuring report manager I followed the below steps
Add New folder called 'Test'. Add new Data Source. Make sure that you fill the connection string. Upload new file which is your report file with an extension of .rdl.
When I was running this I got the below error message
An error occurred during client rendering.An error has occurred during report processing.Cannot create a connection to data source 'AW'.Login failed for user '
This error message indicates that the credentials applied is wrong..I played with varios options for the credentials and finally 'Windows Integrated Security' did the trick
You may try different credentials to make it work..Just wanted to let you know guys that this error message is related to credentials
The remote name could not be resolved - SSRS
Hi All,
Well today I guess things happened cos SSRS wanted me to learn some of its features....When I tried to run reports today, I got the error "The remote name could not be resolved". When I browsed for answers for this..The best solution was to change the computer name in reportconfig (The default location for this file on my development machine is C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager.)
When I checked for computer name, I noticed that the computer name was same but the domain name was different. Now since my computer is on the companies network, i,e domain name is set by the company..I did not have an option to change it...So I had to set SSL for this domain..
My server has vista on it..to set the SSL certificate..I opened IIS window>SSL certificate> Create Self signed Certificate> Specified test name as 'TestCert'> Click okay. Now SSL has picked the most recent domain name of the computer automaticaly.
Come back to reporting server, reconfigure the new SSL. While doing this it will certify the new SSL.
After this is done, I encountered the below errors
The request failed with HTTP status 401: Unauthorized.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
The remote name could not be resolved
This was resolved by following the below step
Set the "Service Account" in the SSRS report configuation tp "Network Service"
And then follow the below steps
1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Right-click Lsa, point to New, and then click DWORD Value.
4. Type DisableLoopbackCheck, and then press ENTER.
5. Right-click DisableLoopbackCheck, and then click Modify.
6. In the Value data box, type 1, and then click OK.
7. Quit Registry Editor, and then restart your computer.
Bingo..my reporting manager was back....
Well today I guess things happened cos SSRS wanted me to learn some of its features....When I tried to run reports today, I got the error "The remote name could not be resolved". When I browsed for answers for this..The best solution was to change the computer name in reportconfig (The default location for this file on my development machine is C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager.)
When I checked for computer name, I noticed that the computer name was same but the domain name was different. Now since my computer is on the companies network, i,e domain name is set by the company..I did not have an option to change it...So I had to set SSL for this domain..
My server has vista on it..to set the SSL certificate..I opened IIS window>SSL certificate> Create Self signed Certificate> Specified test name as 'TestCert'> Click okay. Now SSL has picked the most recent domain name of the computer automaticaly.
Come back to reporting server, reconfigure the new SSL. While doing this it will certify the new SSL.
After this is done, I encountered the below errors
The request failed with HTTP status 401: Unauthorized.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
The remote name could not be resolved
This was resolved by following the below step
Set the "Service Account" in the SSRS report configuation tp "Network Service"
And then follow the below steps
1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Right-click Lsa, point to New, and then click DWORD Value.
4. Type DisableLoopbackCheck, and then press ENTER.
5. Right-click DisableLoopbackCheck, and then click Modify.
6. In the Value data box, type 1, and then click OK.
7. Quit Registry Editor, and then restart your computer.
Bingo..my reporting manager was back....
Wednesday, 19 October 2011
Macro for TextToColumns
Hi All,
I have an excel sheet in which the row B contains consolidated data. The macro to seperate the data in this column is as given below..Ex data - Request:AD:Account:Activate:
Sub Macro1()
'
' Macro1 Macro
Columns("C:C").Select
Selection.ClearContents
Columns("B:B").Select
Selection.Copy
Columns("C:C").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo _
:=Array(1, 1)
Columns("C:C").Select
Range("C2").Select
Selection.Value = "L1"
Columns("D:D").Select
Range("D2").Select
Selection.Value = "L2"
Columns("E:E").Select
Range("E2").Select
Selection.Value = "L3"
Columns("F:F").Select
Range("F2").Select
Selection.Value = "L4"
Columns("G:G").Select
Range("G2").Select
Selection.Value = "L5"
End Sub
I have an excel sheet in which the row B contains consolidated data. The macro to seperate the data in this column is as given below..Ex data - Request:AD:Account:Activate:
Sub Macro1()
'
' Macro1 Macro
Columns("C:C").Select
Selection.ClearContents
Columns("B:B").Select
Selection.Copy
Columns("C:C").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo _
:=Array(1, 1)
Columns("C:C").Select
Range("C2").Select
Selection.Value = "L1"
Columns("D:D").Select
Range("D2").Select
Selection.Value = "L2"
Columns("E:E").Select
Range("E2").Select
Selection.Value = "L3"
Columns("F:F").Select
Range("F2").Select
Selection.Value = "L4"
Columns("G:G").Select
Range("G2").Select
Selection.Value = "L5"
End Sub
SSRS 2008 report manager was blank with no folders
Hi All,
Today I worked for 5 hours to resolve the issue. I had earlier worked on SSRS 2005 and I knew how the report manager would look. But in Vista when I installed 2008, I could see the report manager as below
Am happy to find the solution for this. Install IIS in Vista..I had trouble installing IIS..it gave me an error message ‘not able to install all features’..so i installed iis60rkt.exe from internet first and then ran the file and tried to install IIS from control panel again..This time I succeeded..I then clicked on the second link in the ‘Report manager’ URL with the IE or Firefox in administrator mode..Bingo..it worked
Note..I also refreshed the ‘Report manager URL’ by retying the same name in the Virtual directory and clicking on ‘Apply’
Today I worked for 5 hours to resolve the issue. I had earlier worked on SSRS 2005 and I knew how the report manager would look. But in Vista when I installed 2008, I could see the report manager as below
Am happy to find the solution for this. Install IIS in Vista..I had trouble installing IIS..it gave me an error message ‘not able to install all features’..so i installed iis60rkt.exe from internet first and then ran the file and tried to install IIS from control panel again..This time I succeeded..I then clicked on the second link in the ‘Report manager’ URL with the IE or Firefox in administrator mode..Bingo..it worked
Note..I also refreshed the ‘Report manager URL’ by retying the same name in the Virtual directory and clicking on ‘Apply’
Macro to remove duplicates in an excel sheet and retain the latest or the last entry
Hi All,
The below macro will delete the duplicate entry in an excel sheet and it will only retain the latest or last entry made
Sub deleteDuplicate(Book2 As String)
Dim cRow As Integer
Dim cRow2 As Integer
Dim MNOT As Integer
Dim cCol As Integer
Dim foundDuplicate As Boolean
For MNOT = 1 To 500
cRow = 2
Do While IsEmpty(Worksheets(Book2).Cells(cRow, 1)) = False
cRow2 = cRow + 1
Do While IsEmpty(Worksheets(Book2).Cells(cRow2, 1)) = False
foundDuplicate = True
For cCol = 1 To 4
If Worksheets(Book2).Cells(cRow, cCol).Value <> Worksheets(Book2).Cells(cRow2, cCol).Value Then
foundDuplicate = False
Exit For
End If
Next
If foundDuplicate = True Then
Worksheets(Book2).Rows(cRow).Delete xlShiftUp
Else
cRow2 = cRow2 + 1
End If
Loop
cRow = cRow + 1
Loop
Next
End Sub
Sub test()
deleteDuplicate "Sheet1"
End Sub
The below macro will delete the duplicate entry in an excel sheet and it will only retain the latest or last entry made
Sub deleteDuplicate(Book2 As String)
Dim cRow As Integer
Dim cRow2 As Integer
Dim MNOT As Integer
Dim cCol As Integer
Dim foundDuplicate As Boolean
For MNOT = 1 To 500
cRow = 2
Do While IsEmpty(Worksheets(Book2).Cells(cRow, 1)) = False
cRow2 = cRow + 1
Do While IsEmpty(Worksheets(Book2).Cells(cRow2, 1)) = False
foundDuplicate = True
For cCol = 1 To 4
If Worksheets(Book2).Cells(cRow, cCol).Value <> Worksheets(Book2).Cells(cRow2, cCol).Value Then
foundDuplicate = False
Exit For
End If
Next
If foundDuplicate = True Then
Worksheets(Book2).Rows(cRow).Delete xlShiftUp
Else
cRow2 = cRow2 + 1
End If
Loop
cRow = cRow + 1
Loop
Next
End Sub
Sub test()
deleteDuplicate "Sheet1"
End Sub
Macro to merge excel sheets in a sharepoint folder
This summary is not available. Please
click here to view the post.
Subscribe to:
Posts (Atom)