none
Extreme Newbie - Question(s) on Report Expressions - something like SELECT DISTINCT, WHERE? RRS feed

  • Question

  • Hey all,

      I am fumbling my way thru this.  I am building a couple reports and have a few questions.  I will start with the simplest.  I have a textbox that I want to display the total number of Cows  However, if I simply select: "=Count(Fields!CowId.Value, "CowEventsDS_CowEvents")" as the expression, it does not give me an accurate count as the same Cow can be there multiple times.  Is there someway to do the equivalent of SQL's SELECT DISTINCT CowID?

     Futhermore, is there a way to do a WHERE clause in the expression? Something like this:

    SELECT DISCTINCT CowID AS CowID, (*) FROM CowEvents WHERE FarmID=_____ ?
    Wednesday, September 17, 2008 11:40 PM

Answers

  • Your first question is simple, use CountDistinct instead of Count

    =CountDistinct(Fields!CowId.Value, "CowEventsDS_CowEvents"

    For your second question, one possibility is to group by FarmID, then do CountDistinct at the grouping level.

    In your table, if you right click a row header button, you should be presented with "Insert Group" as one option. Select that and set Fields!FarmID.Value as the grouping expression. Run your report and you should find the table is now grouped by FarmIDs. You should also have found, in the designer, a new row was added above and below your data row. These rows are for the group header and footer. You can place in one of the cells in the footer row "=CountDistinct(Fields!CowId.Value), and it will scope itself to the group, returning the number of distinct cows in that group.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by DigitalFusion Friday, September 19, 2008 5:47 AM
    • Unmarked as answer by DigitalFusion Friday, September 19, 2008 5:47 AM
    • Marked as answer by DigitalFusion Friday, September 19, 2008 5:47 AM
    Thursday, September 18, 2008 1:48 AM
  • The Count function in RS takes an expression as its parameter, so combining the iif function is what you want

    =Count(iif(Fields!Action.Value = "Wrapped", 1, Nothing), "NameOfTheDataSet") 

    Since it is a textbox, you need to include the name of the dataset it should use.

    You can always add a second (or more) dataset to your report. Add a new dataset that selects from the other table, and use that dataset in the other textboxes.

    What do you mean by globals? The report has its own notion of global variables, like page number, you can do something like this in your header

    =Globals!PageNumber & " out of " & Globals!TotalPages 


    The expression editor has all of this listed for you, anywhere you see the "fx" button, that brings up the expression editor. In there you will find all the functions separated by category, all the available globals, etc.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by DigitalFusion Friday, September 19, 2008 5:48 AM
    Thursday, September 18, 2008 7:49 AM

All replies

  • Your first question is simple, use CountDistinct instead of Count

    =CountDistinct(Fields!CowId.Value, "CowEventsDS_CowEvents"

    For your second question, one possibility is to group by FarmID, then do CountDistinct at the grouping level.

    In your table, if you right click a row header button, you should be presented with "Insert Group" as one option. Select that and set Fields!FarmID.Value as the grouping expression. Run your report and you should find the table is now grouped by FarmIDs. You should also have found, in the designer, a new row was added above and below your data row. These rows are for the group header and footer. You can place in one of the cells in the footer row "=CountDistinct(Fields!CowId.Value), and it will scope itself to the group, returning the number of distinct cows in that group.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by DigitalFusion Friday, September 19, 2008 5:47 AM
    • Unmarked as answer by DigitalFusion Friday, September 19, 2008 5:47 AM
    • Marked as answer by DigitalFusion Friday, September 19, 2008 5:47 AM
    Thursday, September 18, 2008 1:48 AM
  • THANK YOU!  I knew it was most likely something simple, but I had no idea what to start trying.  Is there somewhere there is a reference list of supported  expressions like that?

    My second question used a bad example. I realized that I already have the WHERE clause when I fill the table adapter on the form where the report is docked.  However, I do have a few text boxes where I want to something more like this:

    SELECT COUNT(Action) WHERE Action='Wrapped'
    SELECT COUNT(Type) WHERE Type='Routine'



    I have a third question, where I want to get data from a completely different table for a few text box values.  Is that even possible with the reportviewer or do I need to have my SQL query do a JOIN or something?


    thanks so much for the first answer, and in advance if I can be lucky enough to have you respond to this!


    (EDIT: and one last question... is there anyway to reference any of my global variables in the report?  )
    Thursday, September 18, 2008 2:18 AM
  • The Count function in RS takes an expression as its parameter, so combining the iif function is what you want

    =Count(iif(Fields!Action.Value = "Wrapped", 1, Nothing), "NameOfTheDataSet") 

    Since it is a textbox, you need to include the name of the dataset it should use.

    You can always add a second (or more) dataset to your report. Add a new dataset that selects from the other table, and use that dataset in the other textboxes.

    What do you mean by globals? The report has its own notion of global variables, like page number, you can do something like this in your header

    =Globals!PageNumber & " out of " & Globals!TotalPages 


    The expression editor has all of this listed for you, anywhere you see the "fx" button, that brings up the expression editor. In there you will find all the functions separated by category, all the available globals, etc.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by DigitalFusion Friday, September 19, 2008 5:48 AM
    Thursday, September 18, 2008 7:49 AM
  • awesome awesome awesome... thank you so much! That iif function will work great for counting the Actions.  After I get this all situated I will definately be marking all these as answers!


    1.) How do take it a step further?   =Count(iif(Fields!EventType.Value = "Routine", 1, Nothing), "NameOfTheDataSet") but I want to count only Routine EventTypes for each Distinct CowID.  Ex: CowID is listed 4 times as "Routine".  I dont want it to count 4 "Routines, only 1 Routine for that cow. 


    2.) I have a few globals of my own in my app (username, currentfarm, etc) that I would like to reference in the report.  However, when I add =GlobalsClass.strUserName to the expression for say, a textbox, it givesme the error saying GlobalsClass is not declared.  Is it possible to pass vars to the report from the form that calls the report?


    Thursday, September 18, 2008 4:34 PM