Post reply

crosstab subreport query

  • November 20, 2008 at 4:59 am

    #77136

    hi

    i have a main report using the query 'qryForecastCrosstab' and in my where clause i have this: WHERE (((Journal.StatusLookup)=(Forms!frmReports1!Combo11)) And ((Journal.ClientLookup)=Forms!frmReports1!Combo15) And ((Journal.DateInvoiced)>=Forms!frmReports1!txtDateFrom And (Journal.DateInvoiced)<=Forms!frmReports1!txtDateTo))

    i created a crosstab query to use as my subreport 'PARAMETERS [Forms]![frmReports1]![Combo11] Short, [Forms]![frmReports1]![Combo15] Short, [Forms]![frmReports1]![txtDateFrom] DateTime, [Forms]![frmReports1]![txtDateTo] DateTime;

    TRANSFORM Avg(qryForecastCrosstab.Total) AS AvgOfTotal

    SELECT qryForecastCrosstab.Usage, qryForecastCrosstab.ClientLookup, qryForecastCrosstab.Description

    FROM qryForecastCrosstab

    WHERE (((qryForecastCrosstab.StatusLookup)=Forms!frmReports1!Combo11) And ((qryForecastCrosstab.ClientLookup)=Forms!frmReports1!Combo15) And ((qryForecastCrosstab.DateInvoiced) Between Forms!frmReports1!txtDateFrom And Forms!frmReports1!txtDateTo))

    GROUP BY qryForecastCrosstab.Usage, qryForecastCrosstab.ClientLookup, qryForecastCrosstab.Description

    PIVOT Format(qryForecastCrosstab.DateInvoiced,"mmm");'

    when i create the report to use the above crosstab query using the query wizard, the column are not shown from the query, then i added PIVOT Format(qryForecastCrosstab.DateInvoiced,"mmm")In ("Jan","Feb","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"), when i run the report it ask me so many times to input the parameter values.

    another thing is on the crosstab column headers i only want to display the months based on the user input values.

    please assist

  • November 21, 2008 at 12:51 am

    #901475

    Have you entered "Jan","Feb","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec" in the Column Headings property of the crosstab query?

  • November 21, 2008 at 12:55 am

    #901476

    thanks for the response

    yes i did put it in the ColumnHeadings but the thing is i don't want to display the whole 12 months on the report, only those month the user selects from StartDate to EndDate should be displayed

  • November 21, 2008 at 6:58 am

    #901587

    Nomvula (11/20/2008)

    when i run the report it ask me so many times to input the parameter values.

    You should have the form frmReports1 open, with valid entries, when you run the report wizard. That should eliminate all the prompts. (If frmReports1 is modal and/or popup, you'd have to temporarily set those = No while you create the report.)

    Nomvula (11/20/2008)

    another thing is on the crosstab column headers i only want to display the months based on the user input values

    Are you referring to the main report, the subreport, or both?

  • November 21, 2008 at 7:07 am

    #901595

    thanks again, the answer to your last question is

    i want to display on the subreport

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply