[Solved] Passing multiple parameters to a drill through report

[maverick_hs]

Hi, I have a report X with parameters from__date, to_date and item. Now, I want to go to a drill thourgh report Y by clicking on a specific item which will show me the details for that item for the dates I chose in X. Now when I click on X to launch Y, it only passes the value of item, it doesn't pass the date values I chose for X. So the report Y is for all possible dates.

One way I can get around that is to make the prompts for dates in Y Required so that the user has to choose the values again but that defeats the purpose of the drill-through reports.

Is there a way I can pass all the parameters chosen for the master report to the detail report as well?

Thanks for your comments/suggestions...

[jolly]

Have you all the Parameters in both Reports (X AND Y)? I created Parent and child Reports (Drill-Throughs) with more than 12 Parameters. And the Reports are working fine. Check at your Parent Report the Drill-Through Collection! If you shure that there is a missing Parameter you can click the Refresh Button on the Bottom and Report Studio shows you all the Parameters that are in your Child Report.

[jolly]

Hi Maverick_hs,

attached a simple Drillthrough. Be sure that you have the GO SALES AND RETAILERS Package which is included with CRN. The Report identified the Order number and the from to Date. After a klick on the Drillthrough Link an new Window with these Parameters opend.

[JoeBass]

Does your X report contain a Date Field in the query or are you only filtering on the date field?  If you are only filtering, you could try adding the field to the query.

Another limitation is that multiple value parameters cannot be passed.

[BIsrik]

I too have the same problem...In my report i have to just filter on the data on the date range and cannot have it in the query becasue i am displaying a grouped total for every group. If i include dates then this total will surely change. But i couldn't get what is the purpose of including the date in the query. I just want the child report to take the date range selected in the master report also..

Jolly how did u do that drill through report having 12 parameters. I am not getting where i am missing the options. Is this all 12 parameters selected in parent is passed to child reports. Can u share with us how to achieve this...

Srik

[sir_jeroen]

Isn't this the same topic as you posted before?
Please don't repost your topic more than once....

[BIsrik]

I couldn't get what u r talking about...I have a problem of this type in my reports...saw this case and i am contiuning this..

Srik

[sir_jeroen]

It was addressed to maverick and I was referring to the previous post
http://cognoise.com/community/index.php?topic=543.0

[BIsrik]

That case was with one parameter drill through, to many reports...but in this case it having multiple parameters for the drill through...the user selects the date range from the parent report and clicks on a product code for a child report..the product code selected and the date range entered both have to be passed to the child report...

Srik

[sir_jeroen]

maverick_hs = BIsrik??

[Draoued]

BIsrik , if you want to have 12 parameters sent to the details report.
THen you need to have 12 filters in your target report and you need to have the 12 fields available in the Primary report.
The 12 fields in the primary report don't need to be displayed in the report, but they need to be at least in the query definition.

When you are doing the Drill-through definition , by default reportnet is adding all possible filters  from target report. You can of course remove some of them if they are not needed and if they are marked as optional filter.

[BIsrik]

Yeah u r right we need to have the columns in the query. This will not satisfy the report requirements.
If i include the date field in query the grouping will change...for eg: if the data is

Productid        Qty         Order Date
   A1                 10             1/1/2000
   A1                 15             2/1/2000
   A2                 10             3/1/2000

I want to display the total qty in for every productid for the date range..if the date range is 1/1/2000 and 5/1/2000 then the parent report should be like this:

  Productid         Qty
     A1                  25
     A2                  10

I then click the produtid to drill through to the child report for the entire details of the qty..like the customer,his phone etc...

If i include the date then the above result will not come...

Any ideas of how to achieve this...

Srik

[maverick_hs]

Hi,

I had been out for a few days so I couldn't check the postings. Seems like there was some confusion between this post and a previous post of mine. I believe they are very different issues and deserve separate topics.

And maverick <> (not equal to) Bisrik.

And I agree that it's not always feasible to add something in the query definition because it makes aggregation difficult (still possible though I guess).

I think I will try and see if I can include the dates in my query definition without affecting the aggregations.

This topic can be considered closed.

Thanks.

[BIsrik]

this is done...u can pass the date parameters to the child report and the grouping can also be done in the way specified...

U need to set the 'auto group and summarize' property of the TM of parent report to 'NO' and include a data item for start data parameter and another one for end date parameter in the query of parent report.

Then u can even drill through to the child report and even the aggregation is done proper..

Srik

[JGirl]

BIsrik is correct

Assuming that your parent report would run with a date selection feeding two parameters.....You need to have in your parent report two data items in the query called FromDate and ToDate (and you don't need to display them in your list - just include them in the query).

Set the derivation of FromDate to ?parm_From? and the derivation of ToDate to ?parm_To?, and choose the aggregate function as automatic.

Your child report will need to have two filters created:
   [ReportDateColName] >= ?parm_From?
   [ReportDateColName] <= ?parm_To?

Then when you setup the drill through from the parent report, do it as follows:
   target parameter ?parm_From? = Source query item FromDate
   target parameter ?parm_To? = Source query item ToDate

Works a treat
J

[avaldez]

The below code was sent to me as a workaround for allowing multiple values to be passed in a drill-through.  I have not been able to get this to work.  It needs to be added as a custom toolbox object in Report Studio.  If anybody is familiar with the custom toolbox technique and would like to give this a shot, feel free and please post your results. 

<script>
   //
=========================
=================
   // multi-select parameter pass on drillthrough (version 2004-11-28)
   // Tested through v 1.1 MR2
   // requires addition to prompting.js at line 79
   // To use: Build drillthroughs as normal,
   // drag script to anywhere within report.
   //
=========================
=================

   // needs to be changed if crn is not on same server
   var crnPath = "http://"+document.domain+sGateway;
   // output locale. change this if needed
   var locale = "en-us";
   //
=========================
=================
   // no need to change anything below here
   //
=========================
=================
   //
=========================
=================

   var cookies = document.cookie.split(";");

   cookies = cookies[0].split("prompts=");
   // remove encoding
   cookies = cookies[1].split("*").join("%").split("|").join("/");
   cookies = unescape(cookies).split(",");

   var allprompts = new Array();
   for (var i=0;i<cookies.length;i++) {
      allprompts.push(new Array(cookies,cookies[i+1]));
      i++;
   }
   //alert(allprompts.toString());
   function prepareFilters() {
      var param = unescape("%26");
      var out = "";
      var stemp = "";
      var str = "";
      for (var i=0; i<allprompts.length; i++) {
         var tmp = allprompts[1].split("useValue=");
         if (tmp.length<3) {
            stemp = tmp[1].split(" selected=");
            str = stemp[0].substr(1,String(stemp[0]).length-2);
            str = ((str.split(" ")).join("+"));
            out += param + allprompts

• + "=" + escape(str);
           } else {
              for (var j=1; j<tmp.length; j++) {
                 stemp = tmp[j].split(" selected=");
                 str = stemp[0].substr(1,String(stemp[0]).length-2);
                 str = ((str.split(" ")).join("+"));
                 out += param + allprompts
  • + "=" + escape(str);
                }
             }
          }
          //var c = new Date();
          //document.cookie = "prompts=;expires="+c.toGMTString()+";"+";";
          return out;
       }
       function buildURL(cell,num) {
          var param = unescape("%26");
          if (!num) {
             var id = cell.nextSibling.innerHTML;
             var co = cell.innerHTML;
          } else {
             var id = num;
             var co = cell;
          }
          if (hlinkArray[Number(id)] == 0) {
             return;
          }
          if (!num) {
             var obj =
    unescape(hlinkArray[Number(id)].split("obj\",\"")[1].split("\"), new
    Array")[0]);
             var tname =
    escape(obj.split("report[@name='")[1].split("']")[0]);
             var param = unescape("%26");
             var link = hlinkArray[Number(id)].split("\""+co.split("
    ").join("+")+"\"")[0].split("p_");
          } else {
             // 1.1 MR1
             var obj =
    unescape(hlinkArray[Number(id)].split("obj','")[1].split("'),createArray"
    )[0]);
             var tname =
    escape(obj.split("report[@name='")[1].split("']")[0]);
             var param = unescape("%26");
             var link = hlinkArray[Number(id)].split("'"+co.split("
    ").join("+")+"'")[0].split("p_");
          }
          //
    =========================
    ==
          // added for cw
          var rawparams = hlinkArray[Number(id)].split("p_");
          var otherParams = "";
          for (var i=1; i<rawparams.length; i++) {
             var t =
    param+"p_"+rawparams.split("'")[0]+"="+rawparams.split("'")[2];
             otherParams += t;
          }
          //
    =========================
    ==
          if (link.toString().charAt(link.toString().length-1) == "}") {
             pdiag = confirm("There is no drill parameter for this selection.
    Are you sure you want to continue?");
             if (pdiag == false) {
                return;
             }
          } else {
             if (!num) {
                link =
    param+"p_"+link[link.length-1].split("\",")[0]+"="+co.split("
    ").join("+");
             } else {
                // 1.1 MR1
                link =
    param+"p_"+link[link.length-1].split("',")[0]+"="+co.split("
    ").join("+");
             }
          }
          var par = prepareFilters();
          par += link + otherParams;
          //alert(par);
          // remove duplicate params (added 11-28-2004)
          var allpars = "";
          var p = par.split(param);
          for (var i=1; i<p.length; i++) {
             var n = p.split("=")[1];
             if (allpars.split(n).length < 2) {
                allpars += param + p;
             }
          }
          par = allpars;
          //alert(par);
          var url = crnPath +
    "?b_action=xts.run"+param+"m=portal/report-viewer.xts"+param+"method=
    execute"+param+"m_obj="+escape(obj)+param+"outputFormat=HTML"+param+"
    outputLocale="+locale+param+"prompt=false"+param+"m_name="+tname+pa
    ram+"runoptions=true"+par+param+"backURL="+escape(sBackURL);
          document.location = url;
       }
       function calcLinks() {
          var spans = document.body.getElementsByTagName("span");
          for (var i=0; i<spans.length; i++) {
             if (spans.className == "hyperlink") {
                if (spans.parentNode.onclick != null) {
                   hlinkArray.push(spans.parentNode.onclick.toString());
                   spans.parentNode.onclick = function () { return false; };
                   var nnum = document.createElement("tag");
                   nnum.style.display = "none";
                   nnum.innerHTML = i;
                   spans.parentNode.appendChild(nnum);
                   spans.onclick = function () { buildURL(this); return false; };
                   // 1.1 MR1 uses HREF ***
                } else if (spans.parentNode.parentNode.href != null &&
    spans.parentNode.parentNode.onclick == null) {
                   hlinkArray.push(spans.parentNode.parentNode.href);
                   var nnum = document.createElement("tag");
                   nnum.style.display = "none";
                   nnum.innerHTML = i;
                   spans.parentNode.appendChild(nnum);
                   spans.parentNode.parentNode.href =
    "javascript:buildURL('"+spans.innerHTML+"','"+i+"');";
                   // 1.1 MR2 uses onclick but href is not null: href="#"
                } else if (spans.parentNode.parentNode.onclick != null) {
                   hlinkArray.push(spans.parentNode.parentNode.onclick.toString());
                   spans.parentNode.parentNode.onclick = "";
                   var nnum = document.createElement("tag");
                   nnum.style.display = "none";
                   nnum.innerHTML = i;
                   spans.parentNode.appendChild(nnum);
                   spans.parentNode.parentNode.href =
    "javascript:buildURL('"+spans.innerHTML+"','"+i+"');";
                } else {
                   hlinkArray.push(0);
                }
             } else {
                hlinkArray.push("");
             }
          }
       }
       var hlinkArray = new Array();
       calcLinks();   
    </script>

[bdybldr]

From reading the posts, it seems that this issue has been solved.  Is it still an issue, Maverick?  If so please provide more info so we can assist you.  Thanks.

[maverick_hs]

This is extremely late I guess but I was on a month's vacation and wasn't checking the forum posts. This problem is resolved. I am not sure how to change the status of the post to closed or solved...

[maverick_hs]

I figured I just need to change the subject to change the status... :-)

[zenith]

Hi,

Can anyone please attach a copy of reports for the above stated scenario
for the To and FROM date scenario given.

Thanks
;D