Post reply

Problems using a stored procedure with a matrix report

  • June 8, 2010 at 5:28 am

    #236260

    I want to use a stored procedure to feed my matrix report. The stored procedure first builds a temp table and then I want to select the columns from this temp table to display on my matrix report. I have never tried a stored procedure with temp tables in a report so obviously I am doing something wrong. If I run the stored procedure in SQL, it returns the coulmns like I want. Here is my stored procedure:

    CREATE TABLE #Temp_Table (

    Bacteria varchar(50),

    Drug varchar(50),

    Value int,

    ColOrder varchar(1))

    INSERT INTO #Temp_Table

    SELECT DISTINCT antibiogram_bacteria.description, reference_values.rva_name,

    CAST(COUNT(CASE WHEN organism_sens_resis.ose_sens_resis = 'S' THEN 1 END) * 100.0 / COUNT(organism_infections.oin_ref_no) AS Decimal(6))

    AS SensitivePct, '1' as ColOrder

    FROM organism_infections INNER JOIN

    organism_sens_resis ON organism_infections.oin_ref_no = organism_sens_resis.ose_oin_ref_no INNER JOIN

    antibiogram_bacteria ON organism_infections.oin_result_value LIKE '%' + antibiogram_bacteria.description + '%' INNER JOIN

    reference_values ON organism_sens_resis.ose_rva_code = reference_values.rva_code

    WHERE (reference_values.rva_rdo_code = 'DRUGS') AND (organism_infections.oin_created BETWEEN CONVERT(datetime, @startdate, 120) AND

    CONVERT(datetime, @enddate, 120) + 1) AND antibiogram_bacteria.gram_neg_pos = 'N'

    GROUP BY reference_values.rva_name, antibiogram_bacteria.description

    ORDER BY reference_values.rva_name, antibiogram_bacteria.description

    INSERT INTO #Temp_Table

    SELECT antibiogram_bacteria.description, 'Total Isolates' AS Drug,

    COUNT(organism_infections.oin_ref_no) AS Value, '0' as ColOrder

    FROM organism_infections INNER JOIN

    antibiogram_bacteria ON organism_infections.oin_result_value LIKE '%' + antibiogram_bacteria.description + '%'

    WHERE (organism_infections.oin_created BETWEEN CONVERT(datetime, @startdate, 120) AND CONVERT(datetime, @enddate, 120) + 1)

    GROUP BY antibiogram_bacteria.description

    ORDER BY antibiogram_bacteria.description

    SELECT * FROM #Temp_Table ORDER BY ColOrder, Drug, Bacteria

    Like I said, if I run this stored procedure in SQL, it returns the columns (Bacteria, Drug, Value, and ColOrder) from the temp table just like I want. But I do not know how to get these columns into my matrix report. When I build my matrix report, I select the stored procedure option. If I run the datset from the Data tab, the rows and columns I want are returned perfectly. But when I click on the dataset on the Layout tab, no columns exist. What am I doing wrong?

    Bob

  • June 8, 2010 at 8:16 am

    #1177495

    I got it working. I had to strip out the old column names from my old query in my report. It works now.

Viewing 2 posts - 1 through 1 (of 1 total)

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