Microsoft Office 2010 – Change the NavePaneSearch to the old Find and Replace dialog box (Ctrl-F)

I personally don’t like the new NavPaneSearch.

You can change the behavior of Ctrl-F to bring up the old ‘Find and Replace’ dialog box.

a) In options, click ‘Customize Ribbon’
b) Click the ‘Customize…’ button to the right of label, ‘Keyboard shortcuts:’
c) Select ‘Home Tab’ in the Categories dropdownlist (on left)
d) Select ‘EditFind in the Commands dropdownlist (on right)
e) Click inside the ‘Press new shortcut key’ textbox
f) On keyboard, press Ctrl key then the F key. ‘Ctrl+F’ now appears in the textbox.
g) Click the ‘Assign’ button
h) Click the ‘Close’ button
i) Click the ‘OK’ button
j) Press Ctrl key then the F key and you should now see the old ‘Find and Replace’ dialog box.

Active Reports 6 – How to display page header information on a subreport within a parent report

You can create a page header for a report, but if that report is used as a sub-report, the page header and page footer of that report does not render. Instead, only the parent report’s page header and page footer will display.

Good news. This can be done by using a group header on your subreport instead. Here is how:

1)    Create a group header for your sub report. Inside the group header, add a textbox (let’s say we call it txtGroupHeader). In the code-behind of your sub-report, you can add any value you want to the textbox as such:

txtGroupHeader.text = "Group Header information"

 

 

 

 

 

 

 

 

2)      Set the RepeatStyle property of your group header to ‘OnPage

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3)  What if you want to suppress the group header of your sub-report on page 1 of the rendered report? This may be desirable if at the top of your detail section of your parent report, you have a report header, and only want to see the group header on preceding pages of your sub-report.

To do this, add the following logic to your script behind.

a)  At the top of your script of the sub-report, add the following boolean variable:

Dim mGroupHeaderFirstTime as boolean = True

b)  In your group header format sub-routine of the sub-report, add the following logic:

Sub MyGroupHeader_Format
    If mGroupHeaderFirstTime = True Then
       mGroupHeaderFirstTime = False
       txtGroupHeader.Visible = False
       MyGroupHeader.Height = 0
    Else
       textboxes.Visible = True
       txtGroupHeader.text = "Customer #: 11223344"
       MyGroupHeader.Height = 0.328
    End If
End Sub

 

 

Active Reports 6 – How to communicate from sub report to parent report

The following illustrates one way to communicate information from a sub report back to its parent or main report. It may not be the cleanest way, but it works.

Let’s say you have a main report, RPT-001.rpx. This report has a page header. We already suppress the page header for page 1 and produce it for the remainder of the pages. (Interrogate rpt.pagenumber = 1 in the ActiveReport_PageStart to handle this).

Now, this report calls five sub reports. The user asks to suppress the page header of the entire report for the last sub report, SUB-RPT-005.rpx (as we will call it here for example).

Here is how it can be done:
1) Add a textbox to your report header of the main report (Let’s call it txtSuppress). Set its Visible property to False. Set its DataField property to String.Empty (or blank).
2) In the ActiveReport_PageStart sub routine of the main report, add the following logic:

If txtSuppress.DataField = "SUPPRESS" Then
    ‘Set all your controls Visible property = False
    ‘Set the Height of your page header Name = 0
Else
    ‘Set all your controls Visible property = True
    ‘Set the values of all your controls for display
    ‘Set the Height of your page header Name = design-time height
End If

3) In the last sub report, SUB-RPT-005.rpx, add the following logic to the ActiveReport_ReportStart sub routine:

rpt.ParentReport.Sections("your main report’s page header Name").controls("txtSuppress").DataField = "SUPPRESS"

So, what is going on here?

All the sub reports get processed in the main report’s ActiveReport_ReportStart section first. That means the logic (Step 3 above) in our last sub report, SUB-RPT-005.rpx, sets the DataField property of our invisible textbox, txtSuppress, on our main report next.

Lastly, the ActiveReport_PageStart sub routine of the main report gets executed. Since we initialized our txtSuppress.DataField = String.Empty, the If statement (Step 2 above) checks for a value of “SUPPRESS” in order to set the visibility of our controls in the page header of the main report to False and sets the height = 0 with the result of NOT seeing the page header for sub report SUB-RPT-005.

SQL Server – Convert NULL to 0 in Select Statement

The first SELECT statement gives you a value of ‘NULL’.
The second statement gives you a value of ‘0’ because the true value returned is
‘NULL’

SELECT SUM(number_of_whatever) AS CalculatedValue
        FROM some_table
        WHERE some_column = 'some_value'
          AND (some_date_column >= '2011-12-01' + cast('00:00' as smalldatetime))
          AND (some_date_column <= '2011-12-31' + cast('00:00' as smalldatetime))
SELECT ISNULL(SUM(number_of_whatever)),0) AS CalculatedValue
        FROM some_table
        WHERE some_column = 'some_value'
          AND (some_date_column >= '2011-12-01' + cast('00:00' as smalldatetime))
          AND (some_date_column <= '2011-12-31' + cast('00:00' as smalldatetime))