How to programmatically override a parameter that exists in all reports

Dec 10, 2012 at 3:36 PM

Thank you for a great project, it was a life saver. We were already putting many hours into our own custom SSRS interface built on PHP, then we found your project and it is practically ALL we needed. Thanks again.

The challenge we have, mostly because of inexperience with ASP.Net (we mostly de Java or PHP when we need custom coding, which is not very often), is that we are trying to programmatically manipulate two parameters that exists in all the reports we expose via CrissCross and I don't really know where in the code and how do something like that.

One of parameters is called "Published" and when its value changes from 0 to 1 then the stored procedure(s) that generate the other (visible) parameters required to run the report behave in a different way. The idea here is that we want to make sure that when a report is run from CrissCross the value of "Published" be always "1" regardless of what the default in the report is in SSRS.

The second parameter is similar, but it will contain the "User_ID" of the logged in user, where this User_ID is also used by the stored procedures to generate parameter values that the particular user has access to based on their security settings on the third party data system we are pulling data from.

Your help and guidance is very much appreciated, thank you,

E.Pinzon

Coordinator
Dec 10, 2012 at 4:07 PM
Edited Dec 10, 2012 at 4:11 PM

Thanks : )

This sounds like Dependant parameters, which CrissCross can handle. You need to adjust the ExtraConfig.xml file though ...

see: http://crisscross.codeplex.com/wikipage?title=Dependant%20Parameters&referringTitle=Documentation

Let me know if you need any additional help.

Dec 10, 2012 at 10:26 PM

Thanks for the fast response.

I can see how using EmtpyEquivalentValue smight help with passing a 0 or a 1 in a parameter defined for each report in the ExtraConfig, but I can't see how Dependent Parameters can help when we need to pass the current logged in username (HttpContext.Current.User.Identity.Name) as the value of a particular Report Parameter.

We will be authenticating the users against the remote system that contains the data via Forms authentication (using a webservice provided by the third party), so our SSRS server knows nothing about the users, yet we need to send that information to the reports as a parameter for data filtering purposes.

If you had to force the value of a hidden parameter to be something on the CrissCross application's context where would you do it? I am guessing in the Report.aspx.cs file, but I am not sure how.

Thanks,

E.Pinzon

Coordinator
Dec 14, 2012 at 3:17 PM
Edited Dec 14, 2012 at 3:40 PM

Ah, I see, you want to manipulate the report parameters and set specific parameters to specific values.

The RunReport() method on Report.aspx.cs would be the place to do this.

Parameters are delivered to this method via the hidden text field uxHiddenParamString, initially the parameters are expressed as a continuous string, in the same format as the querystring for an SSRS report (param1=something&param2=something etc)

Then RunReport() transforms this into a more objecty format for checking and verification.

See this line in RunReport():

var choiceCollection = choiceFactory.Create(paramString);

This turns the parameters that the user chose into a CrcParameterChoiceCollection object, which has a List of CrcParameterChoice objects.


So right after that line, you need to look through that list, see if "Published" is in the list, and if it is change its value (or if it isn't, add it).
Then you need to see if "User_ID" is in that list, change it if it is, or add it if it isn't.

I don't have a compiler right now but the code would be something like:

var publishChoice = choiceCollection.ParameterChoiceList.FirstOrDefault( c => c.Name == "Published");
if (publishChoice != null)
{
publishChoice.SingleValue = "1";
}
else
{
var newpublishChoice = new CrcParameterChoice("Published");
newpublishChoice.SingleValue = "1";
choiceCollection.ParameterChoiceList.Add(newpublishChoice);
}



... and then similar for User_ID

Processing will then continue as if the user had chosen those values.

Dec 14, 2012 at 3:34 PM

That's great!

Thank you, that is exactly what I was looking for. The code sample makes it even easier for a .Net novice like me.

Jan 10, 2013 at 12:37 AM

codeulike, I was trying not to bother you any more but I am still unable to accomplish what I need, so I hope you can help me some more as you have already done.

Your suggestion to add/change the value of my required parameters righ after the choiceCollection is created in the RunReport method would work wonderfully if what I needed was to send the parameters straight to the report as it is being run, but  our case is a little different. What we are trying to do is alter the content (valid values) on a multipick parameter before the user gets to see it in the screen and based on the ID he used to login to CrissCross.

This would be the ideal sequence of events:

1. report and parameter definition read from SSRS.

2. CrissCross changes the value of the "user_id" and "published" mandatory parameters, which are hidden and never available for the user to manually change.

3. Parameters that are dependent on "user_id" and "published" are refreshed from SSRS.

4. Finally the user gets to see and interact with all other parameters, including those that where refreshed from SSRS

5. User runs the report

In other words, how would you update the parameter values (as your sample code does) and then force a dependent parameter refresh before the parameters are displayed in the screen for the first time?

Thanks again for sharing your code, I have learned a ton since I started looking at it.

E.Pinzon

Coordinator
Jan 18, 2013 at 8:21 PM
Edited Jan 18, 2013 at 8:23 PM

OK, first off, the normal way to default a parameter to the current user is described here:

http://stackoverflow.com/questions/875939/reporting-services-2008-using-useruserid-in-parameters

... but I think from what you said above that wont work for you, because the 'user' is only known to CrissCross, and not to SSRS (presumably you are using a 'fixed user' to actually run the SSRS reports). So I mention it just in case.

Anyhow, I think what you need to do is similar to what I described above - a change to Report.asp.cs to change the parameter choices - except now we want to do it before the parameters are presented to the user, rather than when they press 'Run Report'.

So in Report.aspx.cs, find PrepareReportDefn()

Take a look at this bit:

 

       if (!String.IsNullOrEmpty(this.InitialParameterString))
            {
                var choiceFactory = new CrcParameterChoiceFactory();
                var choiceCollection = choiceFactory.Create(this.InitialParameterString);
                if (crcRepDefn.HasDependantParameters)
                    crServices.RefreshDependantParameters(crcRepDefn, choiceCollection);
                else
             . . .

 

What you need to do is make sure the first  if() always evaulates to true, and then change the choiceCollection, something like this:

 

        if (true)
            {
                var choiceFactory = new CrcParameterChoiceFactory();
                var choiceCollection = choiceFactory.Create(this.InitialParameterString);
                // . . .
                // add code here to change Published and User_ID in the choiceCollection
                // . . .
                if (crcRepDefn.HasDependantParameters)
                    crServices.RefreshDependantParameters(crcRepDefn, choiceCollection);
                else
             . . .

 

The code to change the choiceCollection will be the same as described above - use choiceCollection.ParameterChoiceList.FirstOrDefault() to see if the Published parameter is there, then change it or add it, etc.

You need to make sure that CrissCross notices that other parameters are 'Dependant' on Published and User_ID, dealing with dependant parameters is described here:

http://crisscross.codeplex.com/wikipage?title=Dependant%20Parameters&referringTitle=Documentation

Finally, you may get problems with the call to choiceFactory.Create() if 'this.InitialParameterString' evaluates to null, so you may need to add a null check there and use an empty string instead of null or something.

Hope that helps, let me know how you get on.

Jan 25, 2013 at 3:09 AM

With your input we were finally able to get the desired behavior. It is working great, we authenticate our users using Forms authentication against an external application via a web service, then the users get to see/run via CrissCross only those reports available under a specific folder, and the actual content of the report(s) is filtered using the authenticated user credentials. The same reports can be run directly from SSRS and would not filter out any data; which is exactly what we wanted to acomplish.

 

The only outstanding (big) issues are the fact that the datepicker control is using the dd/mm/yyyy format and I can't figure out where to change that behavior. The reports run just fine but that date format is extremelly confusing for our users. I know we forced en-US in the IIS server and the website itself, also, all  our browsers default to en-US locale. Any ideas?

The second problem is with Firefox (tested versions 11, 15 and 18) where each report parameter generated from a DB query has to be clicked on twice for it to be actually selected; that is, the first time when you click/select a value from the list the dropdown control goes back to "Select Options", the second click works. This issue is not present at all in IE7, IE8, IE9, Safari 5 or Chrome 24.

 

Thank you again,

 

E. Pinzon.

Coordinator
Jan 25, 2013 at 9:01 AM

Ok, glad its working better now.

Setting the Date Format is a bit fiddly at the moment (I will try and improve that ...). I've written some documentation here about setting the format - you need to do it in two places basically:

http://crisscross.codeplex.com/wikipage?title=Date%20Formats

The problem with parameter selection sounds like a bug in the Multi-pick control, I'll look into it ....

Jan 25, 2013 at 3:22 PM

After applying the changes you suggested to CrcParameterConverted.cs and CrissCrossClient.js this is how the date picker controls behave:

IE 7, 8, 9 (on Windows 7 and Server 2008/R2) and Safari (on OS X at least) just work, dates are accepted in the correct encoding/locale as well as displayed correctly when the user uses the datepicker calendar.

The same applies to Chrome after we reset the browser's default encoding by changing it from UTF-8 to Iso-8859-1 and back again; plus selecting the Auto Detect option.

Firefox is a different story, our oldest version of Firefox is 11 and dates are picked/entered in the correct locale, the same is not true for Firefox 18; I think it has more to do with all the new HTML5 stuff creating conflicts with jQuery, but I don't know enough to figure it out.

 

For now we are going to advice our users to use IE, Chrome or Safari if they can and be aware of the date format if they have to use Firefox.

 

About the parameters' unexpected behavior, there is the issue of having to select the value twice for lists that are not multi-pick type, but there are other issues that affected all other browsers but we found "good enough" workarounds for them for now. I probably should start a different discussion to give you more details.

 

Thanks again,