Reporting services and filtering

Hi,

I have being trying this for quiet a while now and I just cant find a solution. Basically I have a field called Total Active. The total active indicates for example..... A project is open or closed. If the project is open, the value 1 is displayed in the database and if closed the value 0 is displayed in the database.

So I have my field in reporting services and now I just want to count all the open projects. I thought you could do this:

= Count(Fields!Active_flag.value = 1)

This does not seem to work.....or my syntax is wrong or something.

Then I tried to pass all the values in Fields!Active_flag as a object using VB custom code. Example:

=code.getTotalActive(Fields!Active_flag.value)

then receive the Object and loop through it to count all the 1's in the Object (Active_flag.value) and pass the total back. However I was not successful with this because I couldn’t get the length of the Object....which I needed to iterate through all the values.

My next attempt which I thought was a sure winner was using a group and filtering out all the 0's which left only 1's that I could count. But this brought about another problem.....the filter only looks for 1's and then counts the 1's and displays then.... But when it doesn’t find any 1's then the entire Field does not display on the report. This is very frustrating because I would rather have is say "no projects active" instead of it not displaying.

Any help will be much appreciated. I have boggled my mind for the solution.

thanks

Ziyaad

I had something similar in my report but it is not that simple because you need CASE Statement to split the column into two Case Active 1, Case Inactive 0 but SQL Server will not recognize your derived columns as valid so you need to put both in temp table and do the calculation. There maybe a more elegant solution but this will give you what you want. I have done many reports both Crystal and SQL Server in the Enterprise so I know that is not an expression problem because SQL can solve it.

So split it do Count(*) of the active column in a temp table.

|||

Not sure I follow. This active and inactive status could change any time. This is because my database pulls information from another SQL database and this process of obtaining my data is run every nigh using a SQL DTS Package.

Could you please explain your solution more?

Thanx

|||If you are using DTS package why not just create separate Active or Inactive destination columns so all you have to do is count the Active column for what you need.|||

Ok, I understand that...I just feel like it's going to be a big effort to create the package all over again. Are you sure there isnt another way?

I am sure using the custom code is easier... if you can get the length of the object passed. I am very new to Reporting Services, which means I basically just started...as well as creating the DTS Packages.

I possible could you maybe explain shortly or give some idea how I would seperate the colums using the DTS packages?

thanks

|||

If you don't want to do it in the DTS package then move the data to a staging table then do an INTO that is SELECT INTO which creates a table with the CASE 1 then Active CASE 0 then Inactive and write a stored procedure which pulls the data from the destination table with COUNT(*) for the Active column and you will get the total. I could not find a more elegant solution, expecting reporting service to filter and give you a count you will need to be an expression expert.

Another option is to create a View with all your query result with Active and Inactive as columns then run a SELECT with COUNT(*) for Active. Run a search for CASE in the BOL and check the link below for more CASE samples.

http://www.craigsmullins.com/ssu_0899.htm

|||

If in the database you have a 1 or 0 (1 being active), couldnt you just do a Sum(Fields!Active.Value) to get the total number of active projects?

Or am I oversimplifying?

BobP

|||

That sound like a good idea, using the CASE. I have done this and added it to my storedPROC which is used to populate my datasource.

However I would like to add that values now to my Report Example:

=Fields!Active.value

However I cannot find this in the expressions box. Is this right and how do i get access to the CASE field from my StoredPROC.

Thanx

|||Modify the CASE statement to add 1 AS Active and 0 AS Inactive, I am sorry I assumed you understand when I told you to do the criteria based conditional split you will know that the derived columns should be renamed. If you are still getting problem use the Case in a Create View which makes both Active and Inactive fields by default and do Select (*) from the View and do the sum or COUNT(*) with SUM.